Cascading Combo Box BIG issue

[Petitesouris]

Hello,
I have a table for some products
tblErmax
Mark
Type
Designation


I have a form with cascading combos to be able to sort out my records
Mark
Type
Designation and below it brings the record that meet those criterias ...All
good until I select the Designation combo box...It bug and says it is missing
an operator I have tried everything and I am lost at tone stage before
mentioning the missing operator it was mentioning it had a problem with
distinct row on that last combo box designation

Thanks for the help!!!!!!!!!!!!!!!!!!!!!!!!!!!

Here is the code after update

Option Compare Database
Option Explicit
Private Sub cboDesignation_AfterUpdate()

Dim strSQLSF As String

strSQLSF = " SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQLSF = strSQLSF & " tblErmax.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tblErmax.Designation = " & cboDesignation

Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Private Sub cboMark_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboType = Null
cboDesignation = Null

strSQL = "SELECT DISTINCT tblErmax.Type FROM tblErmax "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "'"
strSQL = strSQL & " ORDER BY tblErmax.Type;"

cboType.RowSource = strSQL

strSQLSF = "SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "'"

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Private Sub cboType_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboDesignation = Null

strSQL = " SELECT DISTINCT tblErmax.Designation FROM tblDemo "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQL = strSQL & " tblErmax.Type = '" & cboType & "'"
strSQL = strSQL & " ORDER BY tblErmax.Designation;"

cboDesignation.RowSource = strSQL

strSQLSF = " SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQLSF = strSQLSF & " tblErmax.Type = '" & cboType & "'"


Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type"
Me!ermaxForm.LinkMasterFields = "Mark;Type"
Me.RecordSource = strSQLSF
Me.Requery
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

cboMark = Null
cboType = Null
cboDesignation = Null
Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""
Me.RecordSource = "tblErmax"
Me.Requery

exit_cmdShowAll_Click:
Exit Sub

err_cmdShowAll_Click:
MsgBox Err.Description
Resume exit_cmdShowAll_Click
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = " SELECT DISTINCT tblErmax.Mark FROM tblErmax ORDER BY
tblErmax.Mark;"
cboMark.RowSource = strSQL
End Sub

 

[AccessVandal via AccessMonster.com]

Hi,

Is "Designation" a Text or Number? You'll need the include the quotes if it
is Text.

 

[Petitesouris]

Thank you it is Text so wher should I put the quote?

The other problem I am having is that I fixed the one but it does show me
only one item in designation when it should be more and sometimes it does but
with sintax error in the SQL query which might be something to do with
distinc I think... not sure

and sometimes it says that there is an issue with childlink and
masterlink.Thanks A LOT as I am starting to change thing but it still does
not do what I whant the first two combo should be distinct but not the last
one desingation!

 

[Petitesouris]

New code...
It works fine until I reach the designation field the code seems to big...
and deos not display by dersignation sorted by eg: Mark Honda Type Belly pan
Designation it should have all the belly pan for Honda!!!!!!!!!
ARGGGGGGGGGGGGGGGGGGGGGGGGGGG

Option Compare Database
Option Explicit
Private Sub cboDesignation_AfterUpdate()

Dim strSQLSF As String

strSQLSF = " SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQLSF = strSQLSF & " tblErmax.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tblErmax.Designation = '" & cboDesignation & "'"

Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Private Sub cboMark_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboType = Null
cboDesignation = Null

strSQL = "SELECT DISTINCT tblErmax.Type FROM tblErmax "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "'"
strSQL = strSQL & " ORDER BY tblErmax.Type;"

cboType.RowSource = strSQL

strSQLSF = "SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "'"

Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Private Sub cboType_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboDesignation = Null

strSQL = " SELECT DISTINCT tblErmax.Designation FROM tblErmax "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQL = strSQL & " tblErmax.Type = '" & cboType & "'"
strSQL = strSQL & " SELECT BY tblErmax.Designation;"

cboDesignation.RowSource = strSQL

strSQLSF = " SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQLSF = strSQLSF & " tblErmax.Type = '" & cboType & "'"


Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery
End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

cboMark = Null
cboType = Null
cboDesignation = Null
Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""
Me.RecordSource = "tblErmax"
Me.Requery

exit_cmdShowAll_Click:
Exit Sub

err_cmdShowAll_Click:
MsgBox Err.Description
Resume exit_cmdShowAll_Click
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = " SELECT DISTINCT tblErmax.Mark FROM tblErmax ORDER BY
tblErmax.Mark;"
cboMark.RowSource = strSQL
End Sub

 

[AccessVandal via AccessMonster.com]

Hi,

First, the form, is it a "Single" or "Continuous".

Second, what the idea on this code?

Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""

Me!ermaxForm.LinkChildFields = "Mark;Type;Designation"
Me!ermaxForm.LinkMasterFields = "Mark;Type;Designation"
Me.RecordSource = strSQLSF
Me.Requery

The above code does not filter the combo boxes, what happen here is that you
have replace the form's Record Source SQL string to filter out by the first
and second combo boxes then the last combo box.

On the first combo box, does it work?

On the second combo box, does it work?

If the first and second combo box works, does it filter out the records?

As for the LinkChildFields and LinkMasterFields, I don't think you need them
if they are a Single Form.

 

[AccessVandal via AccessMonster.com]

Hi,

I forgot to include one more thing.

You're not doing a Cascading Combo Box! You're filtering the form's record
source.

What are the steps you wish to filter the records?

First combo, select the "Mark" then,
second combo, select "Type" then,
Third combo, select "Designation" then
filter the form's record source?

 

[Petitesouris]

Thank you so much for all your help.
The idea is I have a table of product
I have imported the data from my supplier’s excel file.

Mark: ( Hobda, Suzuki etc)
Type: (Belly Pain, Screen)
Designation: when the two above are slected will give me the range of item
fitting the description above.

Like all the Belly Pan for Honda will bring few records in designation.

It is right, I want to be able to filter...in the header of my Single
Form!!! And bring in a sub form or datasheet view all the info for those
products.

So far it does work

If I select a Mark it will bring in type all the type available for that
brand...

And after that when I want to go to designation it $#$#%W$# does not
work...it brings a syntax error

Thank you so much I very new to access and good with simple things...

Cheers

 

[AccessVandal via AccessMonster.com]

Hi,

Assuming the combo boxes are unbound from the main form and it is also
unbound to any source.

You'll need to insert a subform (Data Sheet) into the main form Detail
section.

Below is the modified code.

Option Compare Database
Option Explicit

Private Sub cboDesignation_AfterUpdate()

Dim strSQLSF As String

strSQLSF = " SELECT * FROM tblErmax "
strSQLSF = strSQLSF & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQLSF = strSQLSF & " tblErmax.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tblErmax.Designation = '" & cboDesignation & "'"

Me!YourSubFormName.Form.RecordSource = strSQLSF
Me!YourSubFormName.Form.Requery

End Sub

Private Sub cboMark_AfterUpdate()
Dim strSQL As String

cboType = “â€
cboDesignation = “â€

strSQL = "SELECT DISTINCT tblErmax.Type FROM tblErmax "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "'"
strSQL = strSQL & " ORDER BY tblErmax.Type;"

cboType.RowSource = strSQL

Me.cboType.Requery ‘requery the combo to filter Type

End Sub

Private Sub cboType_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT DISTINCT tblErmax.Designation FROM tblErmax "
strSQL = strSQL & " WHERE tblErmax.Mark = '" & cboMark & "' And "
strSQL = strSQL & " tblErmax.Type = '" & cboType & "'"
strSQL = strSQL & " ORDER BY tblErmax.Designation;"

cboDesignation.RowSource = strSQL

Me.cboDesignation.Requery ‘requery combo to filter out Designation

End Sub

Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click

cboMark = Null
cboType = Null
cboDesignation = Null
Me!ermaxForm.LinkChildFields = ""
Me!ermaxForm.LinkMasterFields = ""
Me.RecordSource = "tblErmax"
Me.Requery

exit_cmdShowAll_Click:
Exit Sub

err_cmdShowAll_Click:
MsgBox Err.Description
Resume exit_cmdShowAll_Click
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = " SELECT DISTINCT tblErmax.Mark FROM tblErmax ORDER BY
tblErmax.Mark;"
cboMark.RowSource = strSQL
End Sub

 

[Petitesouris]

Hello Thank you for your help, I had to adjust few thing before it could
workfirst of course my subform name which I already did have in my previous
model.

But Me.cboDesignation.Requery ‘requery combo to filter out Designation
that would not work so I had to remove it and it seems to work

cboType = “â€
cboDesignation = “â€
And to remove that as it would bug...

It seems to work I am going to test it

Thanks and will keep you posted if It does not work

Petitesouris from Australia

 

[Petitesouris]

It still does not work...surely missing something it is the morning here I
guess you might be in the USA.

I will try to verify the code and will post you the latest with the error
message if I cannot figure out to have it working as I want.

Cheers

 

[AccessVandal via AccessMonster.com]

Hi,

I realized that you was using Candace Tripp's Cascading Combo box.
Tripp's method requires two tables to do this, but in your case, you have
only one table.

You can safely remove the linkChildField and LinkMasterField in your code.

 

[Petitesouris]

Thank you for your help, I did indeed remove that after getting some error
messages... then remove something you put too as it was having a opo in
windows asking for parameter. It now does what I want... Thanks to the web
and your help.

I am not an expert but can manage a bit...with lots of help but trying to
learn !!

Thanks agian wishing you a nice weekend