Need a combobox to filter sub form

[MiHutch via AccessMonster.com]

I know this has been addressed several times and I have attempted to look at
those posts and other web sites to come up with a VB code, but no luck.

I would like a Combo Box from my form called Default to sort the data from a
continues sub form called Sort_List. My combo box pulls the names from a
table called Examiner.

When I start the page I get a Compile Error: Syntax Error at line,

Sub SetFilter()

Dim LSQL As String

LSQL = "select * from Examiner"
LSQL = LSQL & " where Examiner_Name = '" & cboExaminer & "'"

Form_Sort_List.RecordSource = LSQL <---- This is red in the code page

What am I missing?


Here is the full code for what I am trying to do.

Sub SetFilter()

Dim LSQL As String

LSQL = "select * from Examiner"
LSQL = LSQL & " where Examiner_Name = '" & cboExaminer & "'"

Form_Sort_List.RecordSource = LSQL

End Sub

Private Sub cboExaminer_AfterUpdate()

SetFilter

End Sub

Private Sub cmdClose_Click()

DoCmd.Close

End Sub

Private Sub Form_Open(Cancel As Integer)

SetFilter

End Sub

 

[Ken Snell [MVP]]

Why not use the subform control's LinkMasterFields and LinkChildFields
properties to do this? Put [cboExaminer] in the LinkMasterFields property,
and put [Examiner_Name] in the LinkChildFields property.

ACCESS then will "syncronize/filter" the subform for you.

 

[MiHutch via AccessMonster.com]

I tried that, but it won't let me add a combo box. The link design menu will
only show record items from a table. If I place the name of the combo box in
the master field and the name of the link table in the child field it shows
no data. Any ideas on what I maybe missing?

MiHutch

Why not use the subform control's LinkMasterFields and LinkChildFields
properties to do this? Put [cboExaminer] in the LinkMasterFields property,
and put [Examiner_Name] in the LinkChildFields property.

ACCESS then will "syncronize/filter" the subform for you.

I know this has been addressed several times and I have attempted to look
at

[quoted text clipped - 48 lines]

End Sub

 

[Gina Whipp]

MiHutch,

You are correct the little wizard thingy (thingy is a technical term) won't
let you. You have to type in the name of the name of the combo box.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I tried that, but it won't let me add a combo box. The link design menu
will
only show record items from a table. If I place the name of the combo box
in
the master field and the name of the link table in the child field it
shows
no data. Any ideas on what I maybe missing?

MiHutch

Why not use the subform control's LinkMasterFields and LinkChildFields
properties to do this? Put [cboExaminer] in the LinkMasterFields property,
and put [Examiner_Name] in the LinkChildFields property.

ACCESS then will "syncronize/filter" the subform for you.

I know this has been addressed several times and I have attempted to look
at

[quoted text clipped - 48 lines]

End Sub

 

[Ken Snell [MVP]]

You may need to requery the subform in the combobox's AfterUpdate event,
although ACCESS should automatically update the subform when you change the
value in the combo box:

Private Sub cboExaminer_AfterUpdate()
Me.NameOfSubformControl.Form.Requery
End Sub

Be sure that you include the [ ] characters when you type [cboExaminer] in
the LinkMasterFields property box.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I tried that, but it won't let me add a combo box. The link design menu
will
only show record items from a table. If I place the name of the combo box
in
the master field and the name of the link table in the child field it
shows
no data. Any ideas on what I maybe missing?

MiHutch

Why not use the subform control's LinkMasterFields and LinkChildFields
properties to do this? Put [cboExaminer] in the LinkMasterFields property,
and put [Examiner_Name] in the LinkChildFields property.

ACCESS then will "syncronize/filter" the subform for you.

I know this has been addressed several times and I have attempted to look
at

[quoted text clipped - 48 lines]

End Sub

 

[MiHutch via AccessMonster.com]

Ok I had to switch the form up a little. I now have a blank form that does
not have a record source.

I am using a sub form that is set to datasheet with no filters and pulls from
a table called Main. I added the [cboExaminer] to the parent link and
Examiner to the child link for the sub form. The combo box is named
cboExaminer and pulls data from another table called Examiner.

When I open the form, the combo box is empty and the sub form has no entries.
When I click on the combo box it shows the user list from the Examiner table.
When I click a name it adds that user to the sub form of a *New record entry.
It does not filter and show the data from the Main table. Any ideas where I
screwed up? Or another way I can get the results I need?

MiHutch

You may need to requery the subform in the combobox's AfterUpdate event,
although ACCESS should automatically update the subform when you change the
value in the combo box:

Private Sub cboExaminer_AfterUpdate()
Me.NameOfSubformControl.Form.Requery
End Sub

Be sure that you include the [ ] characters when you type [cboExaminer] in
the LinkMasterFields property box.

I tried that, but it won't let me add a combo box. The link design menu
will

[quoted text clipped - 17 lines]

 

[Ken Snell [MVP]]

Sounds like the Data Entry property of the form that is your subform is set
to Yes. Set that property in the subform form to No.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ok I had to switch the form up a little. I now have a blank form that does
not have a record source.

I am using a sub form that is set to datasheet with no filters and pulls
from
a table called Main. I added the [cboExaminer] to the parent link and
Examiner to the child link for the sub form. The combo box is named
cboExaminer and pulls data from another table called Examiner.

When I open the form, the combo box is empty and the sub form has no
entries.
When I click on the combo box it shows the user list from the Examiner
table.
When I click a name it adds that user to the sub form of a *New record
entry.
It does not filter and show the data from the Main table. Any ideas where
I
screwed up? Or another way I can get the results I need?

MiHutch

You may need to requery the subform in the combobox's AfterUpdate event,
although ACCESS should automatically update the subform when you change
the
value in the combo box:

Private Sub cboExaminer_AfterUpdate()
Me.NameOfSubformControl.Form.Requery
End Sub

Be sure that you include the [ ] characters when you type [cboExaminer] in
the LinkMasterFields property box.

I tried that, but it won't let me add a combo box. The link design menu
will

[quoted text clipped - 17 lines]

End Sub

 

[MiHutch via AccessMonster.com]

Nope, it was set to No.

Thanks though.

MiHutch

Sounds like the Data Entry property of the form that is your subform is set
to Yes. Set that property in the subform form to No.

Ok I had to switch the form up a little. I now have a blank form that does
not have a record source.

[quoted text clipped - 34 lines]

 

[MiHutch via AccessMonster.com]

Ok, I found my problem.

The Row Source is set to, SELECT Examiner.EmployeeID, Examiner.Examiner_Name
FROM Examiner ORDER BY Examiner.Examiner_Name;

When it is set in this order the combo box show the correct employee names
when clicked on, but does not filter the sub form.

When I switched the order to, SELECT Examiner.Examiner_Name, Examiner.
EmployeeID FROM Examiner ORDER BY Examiner.Examiner_Name;

The combo box shows the employees ID, but when I select an ID the sub form
filters like it should. What am I missing here.

MiHutch

 

[Gina Whipp]

MiHutch,

What is your Link Child Fields - Link Master Fields? Sounds like it's the
ExaminerName instead of EmployeeID. OR is the bound column on your combo
box set to 2?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[MiHutch via AccessMonster.com]

The Master is set to cboExaminer and the child is set to Examiner which is in
the Main table. I have a separate table for Examiners called Examiner and the
name record is called Examiner_Name.

I had the combo box set to Unbound and to 1.

Ok, I set the Row Source to, SELECT Examiner.DID, Examiner.Examiner_Name FROM
Examiner ORDER BY Examiner.Examiner_Name;

I set the bound column to 2.
It works like a charm, thanks again Gina you rock. That's 2 for 2 you have
helped me.

Thanks to all that helped on this.
MiHutch

MiHutch,

What is your Link Child Fields - Link Master Fields? Sounds like it's the
ExaminerName instead of EmployeeID. OR is the bound column on your combo
box set to 2?

Ok, I found my problem.

[quoted text clipped - 12 lines]

MiHutch

 

[Gina Whipp]

You're welcome!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The Master is set to cboExaminer and the child is set to Examiner which is
in
the Main table. I have a separate table for Examiners called Examiner and
the
name record is called Examiner_Name.

I had the combo box set to Unbound and to 1.

Ok, I set the Row Source to, SELECT Examiner.DID, Examiner.Examiner_Name
FROM
Examiner ORDER BY Examiner.Examiner_Name;

I set the bound column to 2.
It works like a charm, thanks again Gina you rock. That's 2 for 2 you have
helped me.

Thanks to all that helped on this.
MiHutch

MiHutch,

What is your Link Child Fields - Link Master Fields? Sounds like it's the
ExaminerName instead of EmployeeID. OR is the bound column on your combo
box set to 2?

Ok, I found my problem.

[quoted text clipped - 12 lines]

MiHutch