combo box filtering

[johnnyvino]

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?

 

[Klatuu]

Using Form as a name may cause problems. It is an Access reserved word.
To use cascading combos, you have to filter the rowsouce of the second combo
on the value of the first. For example, the rowsource for the Form combo
should be something like:

SELECT Form FROM SomeTable WHERE [TestType] = '" & Me.txtTestType & "'"

Then in the After Update event of the TestType Query, requery the Form combo.

Me.cboForm.Requery

 

[johnnyvino]

I tried it on the "Type" field combox first, but came up with a syntax error
message.

Private Sub Type_BeforeUpdate(Cancel As Integer)
SELECT Type FROM FieldTest08 WHERE [Subject] = '" & Me.txtSubject & "'

End Sub

Using Form as a name may cause problems. It is an Access reserved word.
To use cascading combos, you have to filter the rowsouce of the second combo
on the value of the first. For example, the rowsource for the Form combo
should be something like:

SELECT Form FROM SomeTable WHERE [TestType] = '" & Me.txtTestType & "'"

Then in the After Update event of the TestType Query, requery the Form combo.

Me.cboForm.Requery
--
Dave Hargis, Microsoft Access MVP

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?

 

[Klatuu]

Type is another word you should not use.
I don't think I made myself clear. The SELECt does not go in the code, It
goes in the row source of the combo box.
All you do in the code is requery the combo.
--
Dave Hargis, Microsoft Access MVP

I tried it on the "Type" field combox first, but came up with a syntax error
message.

Private Sub Type_BeforeUpdate(Cancel As Integer)
SELECT Type FROM FieldTest08 WHERE [Subject] = '" & Me.txtSubject & "'

End Sub

Using Form as a name may cause problems. It is an Access reserved word.
To use cascading combos, you have to filter the rowsouce of the second combo
on the value of the first. For example, the rowsource for the Form combo
should be something like:

SELECT Form FROM SomeTable WHERE [TestType] = '" & Me.txtTestType & "'"

Then in the After Update event of the TestType Query, requery the Form combo.

Me.cboForm.Requery
--
Dave Hargis, Microsoft Access MVP

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?

 

[johnnyvino]

oops, my bad. I renamed the Type field to CR_or_MC and plugged that
expression into the rowsource, and changed the fieldname in the AfterUpdate.

But now I'm getting a syntax error for the following specific part of the
expression:

[Subject] = '" & Me.txtSubject & "'"

 

[Klatuu]

Assuming subject is a text field in your recordset,
[Subject] = '" & Me.txtSubject & "'"
should be okay. What is the entire statement? The error may be somewhere
else.
--
Dave Hargis, Microsoft Access MVP

oops, my bad. I renamed the Type field to CR_or_MC and plugged that
expression into the rowsource, and changed the fieldname in the AfterUpdate.

But now I'm getting a syntax error for the following specific part of the
expression:

[Subject] = '" & Me.txtSubject & "'"

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?

 

[johnnyvino]

Select CR_or_MC FROM FieldTest08 WHERE [Subject] = '" & Me.txtSubject & "'"

My goal would be to select a subject (English 10) and then the CR_or_MC
combox would show both options if they exist for that subject, or only CR if
there's just CR. The next combox (FormNumber) would be narrowed to just the
CR or MC FormNumbers depending on my Subject and CR_or_MC selections. (Just
restating what I'm trying to do for my own benefit)

Assuming subject is a text field in your recordset,
[Subject] = '" & Me.txtSubject & "'"
should be okay. What is the entire statement? The error may be somewhere
else.
--
Dave Hargis, Microsoft Access MVP

oops, my bad. I renamed the Type field to CR_or_MC and plugged that
expression into the rowsource, and changed the fieldname in the AfterUpdate.

But now I'm getting a syntax error for the following specific part of the
expression:

[Subject] = '" & Me.txtSubject & "'"

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?

 

[Klatuu]

Try it this way:
Select CR_or_MC FROM FieldTest08 WHERE [Subject] = Me.txtSubject
--
Dave Hargis, Microsoft Access MVP

Select CR_or_MC FROM FieldTest08 WHERE [Subject] = '" & Me.txtSubject & "'"

My goal would be to select a subject (English 10) and then the CR_or_MC
combox would show both options if they exist for that subject, or only CR if
there's just CR. The next combox (FormNumber) would be narrowed to just the
CR or MC FormNumbers depending on my Subject and CR_or_MC selections. (Just
restating what I'm trying to do for my own benefit)

Assuming subject is a text field in your recordset,
[Subject] = '" & Me.txtSubject & "'"
should be okay. What is the entire statement? The error may be somewhere
else.
--
Dave Hargis, Microsoft Access MVP

oops, my bad. I renamed the Type field to CR_or_MC and plugged that
expression into the rowsource, and changed the fieldname in the AfterUpdate.

But now I'm getting a syntax error for the following specific part of the
expression:

[Subject] = '" & Me.txtSubject & "'"


:

I am trying to use combo boxes in a data entry form to filter three columns
of data - Subject, TestType, and Form. "Form" is the primary key.

I can use a query in the RowSource as a filter for individual comboxes, but
I don't know how to make a cascading sequence where the options in TestType
depend on the Subject selection, and the options in Form depend on the
TestType selection.

Obviously I can picture what I want it to do, but I don't have real
experience making macros, or writing SQL expressions.

Any help?