Saving a record problem (Acc97 v2)

Topic

New Reply

I am writing this from home so it will be a bit vague

I have built a database that is used to enter the jobs for the shop (medium engineering firm)
Lately I was asked to add a form to provide sequential quote numbers. I did this by when a new record was added, I used dlookup to find the max value of the field in the table behind it and added one to it. This works fine. I tried to save the new record by docmd acsaverecord so that the record is written immediately so that if someone else gets a new quote number before the form is closed by the first user, they get the next number.

This is not working, the record is not saved until the form is closed. Which means that on occasions, I am getting double ups.

I don’t understand recordsets, but am I only saving the new record to the recordset and not to the actual table??

If you need more info, just say so.

Regards,

Reply | Quote

Replies

The instruction for saving a record is:
RunCommand acCmdSaveRecord
Using this, the method you described should work.
However, if your users are on a slow network, the following might still occur

• User A creates a new record, but the Save action is slow.
• User B creates a new record before A’s record has actually been saved, so B gets the same quote number.
• Access saves A’s record.
• Access tries to save B’s record, resulting in a conflict.
  [/list]Having said that, I hasten to add that I have used this method with success in several multi-user databases.

Reply | Quote

Should RunCommand acCmdSaveRecord be placed in the After Update event of the Quote Number field?

Reply | Quote

Allan already solved his problem in a different way, but this is about rhconley’s question.

Fields don’t have events (in Access), controls in a form do. I don’t think you want the Quote Number to be in an editable text box; in that case the user would be able to mess up things. So you can’t use the AfterUpdate event.
Put the code in the BeforeInsert event of the form. This event fires as soon as the user starts typing (entering data) in a new record:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim d As Long
d = DMax(“QuoteNum”, “tblWhatever”)
QuoteNum = (d + 1)
RunCommand acCmdSaveRecord
End Sub

In this example, tblWhatever is the record source of the form, and QuoteNum is the field containing the number to be assigned.

Reply | Quote

Another approach is to keep the “Next” number in a special table. When a number is required simply read the table using .edit, add 1 to the number and update the table by using an .update, then read the table for the number.
I have used this approach quite a few times.
HTH
Pat

Reply | Quote

Pat,

I am using the command shown by HansV but without luck. I would like to try and use your method.

I’ve looked at the Edit Example in the help file, but I don’t understand.

.edit reads the value into the copy buffer, how do I refer to the copy buffer to paste the value into my form?

“tblQuoteNum” is a single record table with 1 number field called “QuoteNum” as suggested

This is what I have so far:

Dim dbs As Database, rst As Recordset
‘ Return reference to current database.
Set dbs = CurrentDb
‘ Set search criteria.
‘ Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset(“tblQuoteNum”, dbOpenDynaset)

With rst
.Edit ‘ Enable editing.
<>
!QuoteNum = [QuoteNum] + 1 ‘ Is this correct???
.Update ‘ Save changes.
End With

rst.Close
Set dbs = Nothing

Reply | Quote

I solved it myself!

Ended up with:

Dim dbs As Database, rst As Recordset
‘ Return reference to current database.
Set dbs = CurrentDb
‘ Set search criteria.
‘ Create dynaset-type Recordset object.
Set rst = dbs.OpenRecordset(“tblQuoteNum”, dbOpenDynaset)

With rst
.Edit ‘ Enable editing.
[QuoteNum] = !QuoteNum
!QuoteNum = [QuoteNum] + 1
.Update ‘ Save changes.
End With

rst.Close
Set dbs = Nothing

Reply | Quote

Unfortunately I cannot send messages to the Lounge while I’m at work, so here I am hours later
.
You may have solved the problem, but does it do what you want it to?
Cheers,
Pat

Reply | Quote