G
Guest
I have a problem with linked combo boxes, which I am certain is due to how
things are linked, but I can't figure out the problem(s)
I have two forms, "SampleForm" and "BlendForm", which are linked to two
tables "SampleData" and "BlendData". Each SampleData record has a
StyleNumber and BlendID associated with it; the latter links the two tables.
Each StyleNumber is associated with only one BlendID (many to one
relationship).
Here's what I'm trying to do:
The SampleForm has a combo box to select a StyleNumber, with the data source
a query of the SampleData table, filtered for No Duplicate Values. Once a
StyleNumber is selected from the list, I want to look up the BlendID
associated with it and display it in the BlendID combo box. If it is a new
style number, I want the user to be able to select a BlendID from the combo
box (data source = BlendData BlendID) or double-click to enter a new blend
(opening the BlendForm). Lastly, if a new blend is entered on the BlendForm
(generating a new BlendID), I want the new BlendID to appear in the BlendID
combo box on the SampleForm. In every case, I want the record associated
with the BlendID in the SampleForm BlendID combo box to be displayed in the
BlendForm.
Here's what I've tried:
1) to link the StyleNumber combo box to the BlendID combo box on the
SampleForm
Me.Blend_ID = Null
Me.Blend_ID.Requery
Me.Blend_ID = Me.Blend_ID.ItemData(0)
this worked fine by itself... then I added
2) a bit of code pilfered from an example off the MS Access website
'If the BlendID is blank, then exit the Sub.
If IsNull(Me.BlendID) Then
Exit Sub
End If
'Dimension variables.
Dim FormName As String, SyncCriteria As String
Dim F As Form, rs As Object
'Set the formname to "BlendForm," the form that will be
'synchronized.
FormName = "BlendForm"
'Check to see if BlendForm is open. If it
'is not open, open it.
If Not fIsLoaded("BlendForm") Then
DoCmd.OpenForm FormName
End If
'Define the form object and Recordset object for
'the Products form.
Set F = Forms(FormName)
Set rs = F.Recordset.Clone
'Define the criteria used for the synchronization.
SyncCriteria = "[BlendID] =" & Me![BlendID]
'Synchronize the corresponding record in BlendForm to
'the current record in the subform.
rs.FindFirst SyncCriteria
'If a record exists in Blendform, find the
'matching record.
If rs.EOF Then
MsgBox "No match exists!", 64, FormName
Else
F.Bookmark = rs.Bookmark
End If
The second portion worked okay for a few minutes, then quickly collapsed
everything. Now, neither portion of the code is working. Selecting a
StyleNumber does not result in the associated BlendID appearing in the
BlendID combo box on the SampleForm. Selecting a BlendID from the combo box
on the SampleForm is not pulling the desired record. I get an error saying
the record does not exist. And I can't get anything to work to send a new
BlendID back from the BlendForm to the SampleForm.
any thoughts?
your assistance is GREATLY appreciated by this newbie!
ElizCat
things are linked, but I can't figure out the problem(s)
I have two forms, "SampleForm" and "BlendForm", which are linked to two
tables "SampleData" and "BlendData". Each SampleData record has a
StyleNumber and BlendID associated with it; the latter links the two tables.
Each StyleNumber is associated with only one BlendID (many to one
relationship).
Here's what I'm trying to do:
The SampleForm has a combo box to select a StyleNumber, with the data source
a query of the SampleData table, filtered for No Duplicate Values. Once a
StyleNumber is selected from the list, I want to look up the BlendID
associated with it and display it in the BlendID combo box. If it is a new
style number, I want the user to be able to select a BlendID from the combo
box (data source = BlendData BlendID) or double-click to enter a new blend
(opening the BlendForm). Lastly, if a new blend is entered on the BlendForm
(generating a new BlendID), I want the new BlendID to appear in the BlendID
combo box on the SampleForm. In every case, I want the record associated
with the BlendID in the SampleForm BlendID combo box to be displayed in the
BlendForm.
Here's what I've tried:
1) to link the StyleNumber combo box to the BlendID combo box on the
SampleForm
Me.Blend_ID = Null
Me.Blend_ID.Requery
Me.Blend_ID = Me.Blend_ID.ItemData(0)
this worked fine by itself... then I added
2) a bit of code pilfered from an example off the MS Access website
'If the BlendID is blank, then exit the Sub.
If IsNull(Me.BlendID) Then
Exit Sub
End If
'Dimension variables.
Dim FormName As String, SyncCriteria As String
Dim F As Form, rs As Object
'Set the formname to "BlendForm," the form that will be
'synchronized.
FormName = "BlendForm"
'Check to see if BlendForm is open. If it
'is not open, open it.
If Not fIsLoaded("BlendForm") Then
DoCmd.OpenForm FormName
End If
'Define the form object and Recordset object for
'the Products form.
Set F = Forms(FormName)
Set rs = F.Recordset.Clone
'Define the criteria used for the synchronization.
SyncCriteria = "[BlendID] =" & Me![BlendID]
'Synchronize the corresponding record in BlendForm to
'the current record in the subform.
rs.FindFirst SyncCriteria
'If a record exists in Blendform, find the
'matching record.
If rs.EOF Then
MsgBox "No match exists!", 64, FormName
Else
F.Bookmark = rs.Bookmark
End If
The second portion worked okay for a few minutes, then quickly collapsed
everything. Now, neither portion of the code is working. Selecting a
StyleNumber does not result in the associated BlendID appearing in the
BlendID combo box on the SampleForm. Selecting a BlendID from the combo box
on the SampleForm is not pulling the desired record. I get an error saying
the record does not exist. And I can't get anything to work to send a new
BlendID back from the BlendForm to the SampleForm.
any thoughts?
your assistance is GREATLY appreciated by this newbie!
ElizCat