Form doesn't display data from record it's told to use

[ramblinwreck]

I have a form with a command button. The form works directly with one table. The function of the command button is to create a new record in two additional tables. After clicking the command button then, three records are created: one in the table associated with the form, and the two additional tables and all three tables are linked to each other.

When I create the new record(s) however, the form does not show this new record. I can go to the tables and see the new record has been created. I can close and reopen the form and then it shows the new record. I can requery and again, the form will correctly show the new record.


here's what I want to happen that isn't happening:

After I create the new record, I have attempted to tell the form via code to display the contents of that new record on the form. But no matter what, it simply displays the contents of the first record in the database.

Looking for your help.

thanks.

Here's the code:

Private Sub cmdNewPartThisMaintenance_Click()
'this sub simply assigns the maintenance entry number from the current record from the maintenance list table to the
'field llngzmaintenanceentrynolink in the parts info table
On Error GoTo Error_Handler:
Dim dbs As Database
Dim rst As DAO.Recordset
Dim lngMaintNo As Long
Dim inttest As Integer
Dim intcompanykey As Integer
'check to make sure an entry has been made in the frmMaintenanceEntries txtDescription field
If IsNull(Me.txtDescription) Then
    MsgBox "You must enter a description of the maintenance entry before proceeding.", vbCritical, "Motorhome Maintenance Records"
    GoTo exit_procedure
End If
'arriving at this point in the code means an entry has already been made in the txtDescription field of frMaintenanceEntries and therefore the associated
'record in the tblMaintenanceList has already been created
'assign value from field txtEntryNo of the frmMaintenanceEntries form to lngMaintNo
lngMaintNo = Me.txtEntryNo
Set dbs = CurrentDb
'now open the parts info table
Set rst = dbs.OpenRecordset("tblpartsinfo", dbOpenDynaset)
'add new record with llngzmaintentrynolink equal the variable
With rst
    .MoveLast
    .AddNew
    !llngzMaintEntryNoLink = lngMaintNo
    'update the record
    .Update
    .MoveLast
    inttest = !idsPartsInfoID
End With
Close
Set rst = Nothing
Set rst = dbs.OpenRecordset("tblCompanyInformation", dbOpenDynaset)
With rst
    .AddNew
    !lngzPartsKey = inttest
    !lngzmaintenancekey = lngMaintNo
    .Update
    .MoveLast
    intcompanykey = !idsCompanyInfoID
End With
Close
Set rst = Nothing
Set rst = dbs.OpenRecordset("tblPartsinfo", dbOpenTable)
With rst
    .Index = "idspartsinfoid"
    .Seek "=", inttest
    .Edit
    !lngzCompanyInfoLink = intcompanykey
    .Update
    'Me.Recordset.FindFirst "[idspartsinfoid]='" & inttest & "'"
End With
exit_procedure:
Close
Set rst = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
DoCmd.Requery
'Me.Recordset.FindFirst "[CustomerName] = '" & RecID & "'"
Me.Requery
Exit Sub
Error_Handler:
On Error Resume Next
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
Buttons:=vbCritical, title:="Motorhome Maintenance Records"
Resume exit_procedure
Resume

End Sub

 

[Dreamweaver]

Move This before the Update

intcompanykey = !idsCompanyInfoID And This inttest = !idsPartsInfoID

Nothing else shouted at me.

 

[ramblinwreck]

Move This before the Update

intcompanykey = !idsCompanyInfoID And This inttest = !idsPartsInfoID

Nothing else shouted at me.

ok, did that. no effect.

Let me add that after doing more research, what I'm really trying to accomplish here is to have a subform on a tab of the main form, to update to the new record. Currently, I can make the main form requery and the record is present on the form (although I still can't get the main form to move to that record). But, additionally, the subform does not show the record either. The main form shows a little of the info from the new record and the subform on the tab shows the details.

can anyone help?

thanks.

 

[ramblinwreck]

Hoping to get a response...