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.
Code:
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:
Code:
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?