RecordSource not refreshing as expected

[GraeagleBill]

A Form that has as its RecordSource the query posted yesterday, (ref: 3/30/21, Query post "Non-updateable query"), where user has requested that pets be added to their resident profile.

SELECT tblRegistry.RegistryID, tblRegistry.LastName, tblRegistry.FirstName, [lastname] & ", " & [firstname] AS Name, tblRegistry.SalID, tblRegistry.Title, tblRegistry.Gender, tblRegistry.RegAs, tblRegistry.Unit, tblRegistry.Parking, tblRegistry.LandLine, tblRegistry.Ext, tblRegistry.Cell, tblRegistry.EMA, tblRegistry.ImageName, tblRegistry.GroupIDs, tblRegistry.Notes, tblRegistry.Selected, tblRegistry.PubCell, tblRegistry.PubEMA, tblRegistry.TxtOkay, tblRegistry.EMOkay, tblRegistry.VoiceOkay, tblRegistry.PrivStaff, tblRegistry.DirPDF, tblPets.AptNum, tblPets.Pet1, tblPets.Sp1, tblPets.Pet2, tblPets.Sp2, tblPrkgStalls.StallNum, tblAutoMfg.MFG, tblAutoModels.Model, tblAutoColors.Color, tblPrkgStalls.LiNum
FROM ((((tblRegistry LEFT JOIN tblPets ON tblRegistry.Unit = tblPets.AptNum) LEFT JOIN tblPrkgStalls ON tblRegistry.Parking = tblPrkgStalls.StallID) LEFT JOIN tblAutoMfg ON tblPrkgStalls.MfgID = tblAutoMfg.MfgID) LEFT JOIN tblAutoModels ON tblPrkgStalls.ModID = tblAutoModels.ModelID) LEFT JOIN tblAutoColors ON tblPrkgStalls.ColorID = tblAutoColors.ColorID;

User clicks the option to add pets, the following event code runs:

Private Sub lblAddPets_Click()
If Nz(DLookup("AptNum", "QPets", "AptNum = " & Me.tbUnit)) = 0 Then
    InitPets (Me.tbUnit)
    Me.Requery
End If

Me.lblAddPets.Caption = "Pet(s)"

Me.tbsp1.Visible = True
Me.tbPet1.Visible = True
Me.tbSp2.Visible = True
Me.tbPet2.Visible = True

End Sub
Public Function InitPets(AptNo)
Dim I As Integer
Dim rsPets As DAO.Recordset
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

Set rsPets = DBEngine(0)(0).OpenRecordset("QPets")

With rsPets
    .AddNew
    !AptNum = AptNo
    !Sp1 = "Dog:"
    !Sp2 = "D/C:"
    .Update
End With
If Me.Dirty Then Me.Dirty = False

rsPets.Close
Set rsPets = Nothing
End Function

The new record in the pets table is in fact created correctly, but when the user attempts to toggle the species field they are greeted with the following error:



If I exit the form and re-open, the problem goes away. I obviously need something else, but I can't figure out what?

[ranman256]

you should just run an append query , rather than writing all this code.

[GraeagleBill]

Would you care to elaborate? I assume you mean append a new record to the pets table, and if so, how would that effect the issue here? The Requery of the form's RecordSource should have caused the bound fields to populate per the pets table update.

[GraeagleBill]

I got rid of the error by requery of the bound controls. However, I loose "my place" in the form's RecordSource after a add a record to the pets table. The form opens having been passed the current record, but again, I loose that positioning and need to get back.

Private Sub Form_Open(Cancel As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=( OPEN OPEN OPEN OPEN OPEN )=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
lngRecID = Me.OpenArgs

Me.RecordsetClone.FindFirst "RegistryID = " & lngRecID             
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

[Micron]

Actually, you're "losing" your place. See if this one helps.
https://www.accessforums.net/showthread.php?t=83334

[GraeagleBill]

Got it,
Thanks,
Bill