how to update a table bound to a data entry form without closing the form

[betsyr]

I am fairly new to VB and have the following problem.

I have a data entry form that allows the user to select multiple objects from a list box, which then show up in a subform. I am doing this by setting the record source for the subform to an sql query generated in the form's code. I want to create new records in a table for each record in the subform.

The problem is this: I am using data from both the form and the subform to populate the table on the Many side of a 1:M relationship. The main form will update the 1 side. I get an error because there is not yet a record on the 1 side of the relationship. I do not know how to get the table to update with data from the main form without moving to a new record or closing the form.

Any help would be greatly appreciated!!

Here is my code so far:

Private Sub cmdOpenInvoiceInfo_Click()

Dim SubformSQL As String
Dim rstInvPieTable As Recordset
Dim rstPiecesSubform As DAO.Recordset

SubformSQL = Forms![AddNewInvoiceForm]![InvoiceSubform].[Form].RecordSource
Debug.Print SubformSQL

Set rstPiecesSubform = CurrentDb.OpenRecordset(SubformSQL)
Debug.Print rstPiecesSubform.RecordCount

Set rstInvPieTable = New Recordset
rstInvPieTable.ActiveConnection = CurrentProject.Connection
rstInvPieTable.CursorType = adOpenKeyset
rstInvPieTable.LockType = adLockOptimistic

rstInvPieTable.Open Source:="InvoicePiecesTable", Options:=adCmdTableDirect
'how many are in the recordset?
Debug.Print rstInvPieTable.RecordCount

rstPiecesSubform.MoveFirst
Do While Not rstPiecesSubform.EOF
'add records from subform to Inv Pieces table.
rstInvPieTable.AddNew
rstInvPieTable.Fields("InvoiceNumber") = Forms![AddNewInvoiceForm].txtInvoiceNumber
rstInvPieTable.Fields("PieceID") = rstPiecesSubform.Fields("PieceID")
'fill in fields...
Debug.Print rstInvPieTable.Fields("InvoiceNumber")
Debug.Print rstInvPieTable.Fields("PieceID")
Loop


Set rstPiecesSubform = Nothing
Set rstInvPieTable = Nothing
End Sub

Thanks!!

betsyr

 

[PearlGI]

Not sure if this will solve your problem, but it's worth a mention!

Try using the Requery method on your main form, this should update the data underlying the form and hence the many side should allow data entry.

 

[betsyr]

The requery works - thanks for your help!

The only problem is that the form now points to the first record in the table, instead of the newest record. Any suggestions on how to get back to the new record? Does a bookmark work in this situation?

 

[Pat Hartman]

Use the following instead of Requery:

DoCmd.RunCommand acCmdSaveRecord