A
Andy G
I have a combo box that I want to add and look up records with. I have the
lookup part down but when I try to add record it adds the record with the 2
ID's it needs (Type_ID and Contact_ID) but then I go to type info into the
address field and it inserts another record with only the Contact_ID. This
is a subform that gets the main Contact_ID from the parent form. It has a
combo box w/ 3 different address types that displays the contacts address
information in fields in the same sub-form. Here is my code. I think I may
have my record insert in the wrong place.
Private Sub cmbAddressType_AfterUpdate()
Dim rs As Recordset
'Find the record that matches the control
If Not IsNull(Me.cmbAddressType) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ADDR_TYPE_ID] = " & Me.cmbAddressType
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Dim rsAddress As Recordset
Dim myID As Integer
myID = Me.txtTypeID
Set rsAddress = CurrentDb().OpenRecordset("tblADDR",
dbOpenDynaset)
rsAddress.AddNew
rsAddress![CONTACT_Contact_ID] =
Forms![frmDetail]![CONTACT_Contact_ID]
rsAddress![ADDR_TYPE_ID] = myID
rsAddress![ADDR_Address] = ""
rsAddress![ADDR_City] = ""
rsAddress![ADDR_State] = ""
rsAddress![ADDR_Zip] = ""
rsAddress.Update
MsgBox "record added"
rsAddress.Close
Me.lblAddrDisplay.Caption = Me.cmbAddressType.Column(1)
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
Me.lblAddrDisplay.Caption = Me.cmbAddressType.Column(1)
End If
Set rs = Nothing
End If
End Sub
lookup part down but when I try to add record it adds the record with the 2
ID's it needs (Type_ID and Contact_ID) but then I go to type info into the
address field and it inserts another record with only the Contact_ID. This
is a subform that gets the main Contact_ID from the parent form. It has a
combo box w/ 3 different address types that displays the contacts address
information in fields in the same sub-form. Here is my code. I think I may
have my record insert in the wrong place.
Private Sub cmbAddressType_AfterUpdate()
Dim rs As Recordset
'Find the record that matches the control
If Not IsNull(Me.cmbAddressType) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ADDR_TYPE_ID] = " & Me.cmbAddressType
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Dim rsAddress As Recordset
Dim myID As Integer
myID = Me.txtTypeID
Set rsAddress = CurrentDb().OpenRecordset("tblADDR",
dbOpenDynaset)
rsAddress.AddNew
rsAddress![CONTACT_Contact_ID] =
Forms![frmDetail]![CONTACT_Contact_ID]
rsAddress![ADDR_TYPE_ID] = myID
rsAddress![ADDR_Address] = ""
rsAddress![ADDR_City] = ""
rsAddress![ADDR_State] = ""
rsAddress![ADDR_Zip] = ""
rsAddress.Update
MsgBox "record added"
rsAddress.Close
Me.lblAddrDisplay.Caption = Me.cmbAddressType.Column(1)
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
Me.lblAddrDisplay.Caption = Me.cmbAddressType.Column(1)
End If
Set rs = Nothing
End If
End Sub