problems referencing a subform control?

[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 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 (I can post John’s if you need to see it):

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

' Me!FamilyID = iFamilyID - original line

' 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

 

[Steve Sanford]

Hi Kathy,

I don't know if you have solved this, but I modified you code and it seems
to do what you want.

-----Watch for line wrap-----

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
Dim lname As String
Dim FName As String

iAns = MsgBox("Create a new Family and move this person to it?", vbYesNo)

If iAns = vbYes Then
'get the first/last name from the subform
FName = Me.FirstName
lname = Me.LastName

'open a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFamily", dbOpenDynaset)
rs.AddNew
rs!FamLastName = lname
rs.Update
'can't rely that the last added record is the last in the recordset or
table
'move to last modified record
rs.Bookmark = rs.LastModified
'get the PK
iFamilyID = rs!FamID
rs.Close


'comment out the following if you do not want
' to add the name to the subform
'----------------------------------------------
'open a recordset to the individuals table
Set rs = db.OpenRecordset("tblIndividual", dbOpenDynaset)
rs.AddNew
'add the FK to the family name
rs.Fields("InFamID") = iFamilyID
'add the persons name
rs.Fields("FirstName") = FName
rs.Fields("LastName") = lname
'save it
rs.Update
rs.Close
'----------------------------------------------


'refresh the main form recordsource
Parent.Requery
Set rs = Parent.RecordsetClone
'find the last added family name
rs.FindFirst "[FamID] = " & iFamilyID
If Not rs.NoMatch Then
Parent.Bookmark = rs.Bookmark
End If

Set rs = Nothing
Set db = Nothing
End If
End Sub



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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 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 (I can post John’s if you need to see it):

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

' Me!FamilyID = iFamilyID - original line

' 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
.