-----Original Message-----
Steve,
I must be doing something wrong, because I get the
following error after selecting something in the Make combo
box..."The expression After Update you entered as the event
property setting produced the following error: Procedure
declaration does not match description of event or
procedure having the same name.
*The expression may not result in the name of a macro, the
name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function,
event, or macro."
If I understood you correctly, the Update SubForm should go
after the "me.cboModel.Requery line.
Below is what I have for the first combo box, could you
look at it for me and tell me where I've gone wrong?
Thank you ever so much,
Ginger
Private Sub cboMake_AfterUpdate()
'Clear the text boxes
cboModel = ""
cboYear = ""
cboPartName = ""
cboPartNumber = ""
cboRefNumber = ""
'Requery the combo boxes
Me.cboRefNumber.Requery
Me.cboPartNumber.Requery
Me.cboPartName.Requery
Me.cboYear.Requery
Me.cboModel.Requery
'update the subform
Dim strSQL As String
strSQL = "Select [Make], [Model], [Year], [PartName],
[PartNumber], [RefNumber], [Condition], [PartDescription1],
[PartDescription2], [New], [Quantity], [Comment1],
[Comment2], [Comment3], [Sold], [DateSold], [From],
[Location], [3], [4], [5], [6], [7], [8], [9], [10] from
tblProduct where "
If Not IsNull(cboMake) Then
strSQL = strSQL & "[Make] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) Then
strSQL = strSQL & " and [Model] = '" & cboModel & "' "
End If
If Not IsNull(cboYear) Then
strSQL = strSQL & " and [Year] = '" & cboYear & "' "
End If
If Not IsNull(cboPartName) Then
strSQL = strSQL & " and [PartName] = '" &
cboPartName & "' "
End If
If Not IsNull(cboPartNumber) Then
strSQL = strSQL & " and [PartNumber] = '" &
cboPartNumber & "' "
End If
If Not IsNull(cboRefNumber) Then
strSQL = strSQL & " and [RefNumber] = '" &
cboRefNumber & "' "
End If
'set the subform to strSQL
Child8.Form.RecordSource = strSQL
Child8.Requery
End Sub
-----Original Message-----
You're very welcome
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Thank you ever so much, Steve! That will work for me!
Ginger
-----Original Message-----
One way would be to:
1) set the record source for the subform to select all
records
2) get the cascading (unbound) combo boxes working. In
the
AfterUpdate event of each combo box you will have to
clear
the following combo boxes and requery each. ie, in the
AfterUpdate event of cboMake there would be
..
..
..
cboModel = Null
cboYear = Null
cboPartName = Null
cboPartNumber = Null
cboReferenceNumber = Null
Me.cboReferenceNumber.Requery
Me.cboPartNumber.Requery
Me.cboPartName.Requery
Me.cboYear.Requery
Me.cboModel.Requery
UpdateSubForm '** see #3 below
End Sub
The next combo box (cboModel) would have the above
minus
the cboModel references, cobYear minus the cboYear
lines,
etc.
3) while in the VBE, add a sub (I named it UpdateSubForm
n
the snippet above) that will create SQL 'on the fly'
using
the cascading combo boxes as parameters and set the
subform's recordset the this SQL statement, then requery
the subform.
To build the SQL, you could use IF statements: (AIR CODE)
Private Sub UpdateSubForm
Dim strSQL as String
strSQL = "Select [fields] from tblTable where "
If Not IsNull(cboMake) then
strSQL = strSQL & "[FieldMake] = '" & cboMake & "'"
End If
If Not IsNull(cboModel) then
strSQL = strSQL & " and [FieldModel] = '" &
cboModel
& "' "
End If
.
.
.
(six If statements)
NOTE: if any of the comboboxes are numeric and not text,
don't use the single quote delimiters.
Then
(set the subform record source to strSQL)
(requery the subform)
End Sub
The last combo box, cboReferenceNnumber, would only have
to requery the subform, not rebuild the SQL recordsource.
HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
I have 6 combo boxes on a form with one subform. I would
like to have all 6 combo boxes linked and dependent on
the
previous one. After a combo box has a selected item, I
would like the subform to update (not sure whether to
requery or filter).
The ideal situation is this: select 1 from cboMake,
subform
changes to filter to that selection only and the
contents
of the next combo box would be filered depending on what
was selected in the first combo box. After cboModel was
selected, the subform data should change to reflect this
and the next combo box record source should only include
items that are based on the first and 2nd combo box's
selection.
So do I filter, or do I requery? What is the best
approach
for this. I tried separate queries for each of the combo
boxes, and that worked fine for 3 of them.
The combo boxes for this inventory program would be:
make,
model, year, partname, partnumber, reference number. The
subform/datasheet should change after any selection of
the
6 combo boxes.
Any help in this matter would be appreciated.
Sincerely,
Ginger
.
.
.
.
.
