Cascading Combo Box BIG issue

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

Hi,

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

Petitesouris wrote:
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

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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!


"AccessVandal via AccessMonster.com" wrote:

Hi,

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

Petitesouris wrote:
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

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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

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.

Petitesouris wrote:
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

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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 wrote:
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

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

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

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 wrote:
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.

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

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

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