K
Kathy R.
I’ve borrowed the following code from John Vinson, whose form/subform
was set up the same as mine (as near as I can tell). There is a “move
out” button on the subform that moves an individual to a new family
unit. This is the control that I am trying to replicate. I’ve replaced
the form and control names in the code with mine, but keep getting an error.
A new record is created in tblFamily, but the code causes an error at
the line where the InFamID is set to update to the new FamID. As you
can see I’ve tried this a couple of different ways and noted the errors
I received.
John has used the field name “FamilyID” in both the tblFamily and the
tblIndividual tables so I may have interpreted this line (or others) wrong.
Any help sorting this out would be very much appreciated.
Kathy R.
tblFamily
FamID (primary key, autonumber)
FamLastName
tblIndividual
IndID (primary key, autonumber)
InFamID (foreign key to tblFamily)
FirstName
LastName
frmFamMoveOut
FamID
FamLastName
sfrIndMoveOut
IndID
InFamID
FirstName
LastName
MoveOut_cmd
Parent/Child = FamID/InFamID
My Code:
Private Sub MoveOut_cmd_Click()
' Move the current Person record to a newly created Family
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iAns As Integer
Dim iFamilyID As Long
iAns = MsgBox("Create a new Family and move this person to it?", vbYesNo)
If iAns = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFamily", dbOpenDynaset)
rs.AddNew
rs!FamLastName = Me.LastName
rs.Update
rs.MoveLast
iFamilyID = rs!FamID
' ---ERROR HERE---
' Me!FamilyID = iFamilyID - original line in John's code
' Me!IndFamID = iFamilyID 'can't find the field 'IndFamID'
' Me![sfrIndMoveOut].Form.[IndFamID] = iFamilyID 'can't find the
field 'sfrIndMoveOut'
' Forms!frmFamMoveOut!sfrIndMoveOut.Form.IndFamID
'application-defined or object-defined error
DoCmd.RunCommand acCmdSaveRecord
rs.Close
Set rs = Nothing
Parent.Requery
Set rs = Parent.RecordsetClone
rs.FindFirst "[FamID] = " & iFamilyID
If Not rs.NoMatch Then
Parent.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
was set up the same as mine (as near as I can tell). There is a “move
out” button on the subform that moves an individual to a new family
unit. This is the control that I am trying to replicate. I’ve replaced
the form and control names in the code with mine, but keep getting an error.
A new record is created in tblFamily, but the code causes an error at
the line where the InFamID is set to update to the new FamID. As you
can see I’ve tried this a couple of different ways and noted the errors
I received.
John has used the field name “FamilyID” in both the tblFamily and the
tblIndividual tables so I may have interpreted this line (or others) wrong.
Any help sorting this out would be very much appreciated.
Kathy R.
tblFamily
FamID (primary key, autonumber)
FamLastName
tblIndividual
IndID (primary key, autonumber)
InFamID (foreign key to tblFamily)
FirstName
LastName
frmFamMoveOut
FamID
FamLastName
sfrIndMoveOut
IndID
InFamID
FirstName
LastName
MoveOut_cmd
Parent/Child = FamID/InFamID
My Code:
Private Sub MoveOut_cmd_Click()
' Move the current Person record to a newly created Family
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iAns As Integer
Dim iFamilyID As Long
iAns = MsgBox("Create a new Family and move this person to it?", vbYesNo)
If iAns = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFamily", dbOpenDynaset)
rs.AddNew
rs!FamLastName = Me.LastName
rs.Update
rs.MoveLast
iFamilyID = rs!FamID
' ---ERROR HERE---
' Me!FamilyID = iFamilyID - original line in John's code
' Me!IndFamID = iFamilyID 'can't find the field 'IndFamID'
' Me![sfrIndMoveOut].Form.[IndFamID] = iFamilyID 'can't find the
field 'sfrIndMoveOut'
' Forms!frmFamMoveOut!sfrIndMoveOut.Form.IndFamID
'application-defined or object-defined error
DoCmd.RunCommand acCmdSaveRecord
rs.Close
Set rs = Nothing
Parent.Requery
Set rs = Parent.RecordsetClone
rs.FindFirst "[FamID] = " & iFamilyID
If Not rs.NoMatch Then
Parent.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub