Saving record during data entry

[mkallover]

I have a form that I am creating for data entry and on that form there is a combo box where a user can add multiple items that are added to another table and then display in a list box (see the attachment for a screenshot.)

The code behind the "Add" button is:

    Me.Refresh

    Dim Adbs As DAO.Database
    Dim Arst As DAO.Recordset
    Dim Aqd As DAO.QueryDef
    
    Set Adbs = CurrentDb
    Set Aqd = Adbs.QueryDefs!qryAddAttest
    Set Arst = Aqd.OpenRecordset

        Arst.AddNew
        Arst!AuditNumber = Me.AuditNumber
        Arst!AttestNumber = Me.AttestChoice
        Arst.Update

    Set Arst = Nothing
    Set Aqd = Nothing

    Me.AttestList.Requery

This works just fine as long as you are adding a Attestation to an existing record. However, when you create a new record and try to add an Attestation, it throws up an error message: "No current record"

I think what's going on is that the main record is not getting saved and so there is no AuditNumber in the main table that corresponds to the AuditNumber that it's trying to add to the Attestation table.

How can I fix this?

 

[MasterRacker]

[tt] DoCmd.RunCommand acCmdSaveRecord[/tt]

You need to do that for a new record before adding linked detail records.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[mkallover]

I tried that but then I get a "No current record" error.

 

[TheAceMan1]

How are ya mkallover . . .

Your code is attempting to add records to the recordset of a query. If this query is [blue]not updateable[/blue] ... no can do! Try adding records to the table directly:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("[purple][b]YourTableName[/b][/purple]", dbOpenDynaset)
   
   rst.AddNew
      rst!AuditNumber = Me.AuditNumber
      rst!AttestNumber = Me.AttestChoice
   rst.Update

   Set Arst = Nothing
   Set Aqd = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mkallover]

When I change it to that I still get the "No current record" error. When I select Debug it brings up the VBA editor with the first line "DoCmd.RunCommand acCmdSaveRecord" highlighted.

 

[TheAceMan1]

mkallover . . .

Post what you changed it too!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]