Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Saving a record problem (Acc97 v2)

    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,
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving a record problem (Acc97 v2)

    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
    <UL><LI>User A creates a new record, but the Save action is slow.
    <LI>User B creates a new record before A's record has actually been saved, so B gets the same quote number.
    <LI>Access saves A's record.
    <LI>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.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Saving a record problem (Acc97 v2)

    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

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Saving a record problem (Acc97 v2)

    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.
    <<how to make copy buffer = [QuoteNum]?????>>
    !QuoteNum = [QuoteNum] + 1 ' Is this correct???
    .Update ' Save changes.
    End With

    rst.Close
    Set dbs = Nothing
    "Heading for the deep end"

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Saving a record problem (Acc97 v2)

    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
    "Heading for the deep end"

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Saving a record problem (Acc97 v2)

    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

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a record problem (Acc97 v2)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving a record problem (Acc97 v2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •