Listbox Filters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two forms with two different list boxes. frm_MachineSearch and
frm_DrawingNum.

In my form frm_MachineSearch I have a list box with the following fields in
it, MachineNum(Primary Key) and Description.

In my form frm_DrawingNum I have the following fields in it,
DrawingNum(Primary Key) and MachineNum(Foreign Key).

When I double click one of the Records in the list box from
frm_MachineSearch I want the form frm_DrawingNum to open and in the list box
only show the DrawingNum records that match the MachineNum double clicked in
the frm_MachineSearch form.

I really don't know if it is even possible to code this some how, I tried a
few things but they didn't work. If you could follow what I am trying to do
and know of a way to do it please help.

Thanks.
 
Playa,
To start you off, for your double-click event of your
listbox you want:
DoCmd.OpenForm formname:="frm_DrawingNum"

In your frm_DrawingNum form, you need to make the row
source of the listbox react to changes in the listbox in
frm_MachineSearch. To do this:
1) Make an invisible text box in frm_MachineSearch. Call
it txtMachineNum.
2) In the double-click event of the listbox in
frm_MachineSearch do:
me.txtMachineNum.value=Me.listboxMachineNum.Column(0) or
whatever column your MachineNum is in.
3) Write the query that is the row source for the listbox
in frm_DrawingNum to look like:
Select DrawingNum, MachineNum from xxx Where MachineNum =
[Forms]![frm_MachineSearch]![txtMachineNum]

Good luck!
Geof Wyght
 
All this works except for step 3. When I go to open the frm_DrawingNum a
parameter value message box appears instead of opening up to that record. I
don't see anything wrong with the criteria
[form]![frm_MachineNum]![txtMachineNum] I put in the Query....did i miss
something?

Geof Wyght said:
Playa,
To start you off, for your double-click event of your
listbox you want:
DoCmd.OpenForm formname:="frm_DrawingNum"

In your frm_DrawingNum form, you need to make the row
source of the listbox react to changes in the listbox in
frm_MachineSearch. To do this:
1) Make an invisible text box in frm_MachineSearch. Call
it txtMachineNum.
2) In the double-click event of the listbox in
frm_MachineSearch do:
me.txtMachineNum.value=Me.listboxMachineNum.Column(0) or
whatever column your MachineNum is in.
3) Write the query that is the row source for the listbox
in frm_DrawingNum to look like:
Select DrawingNum, MachineNum from xxx Where MachineNum =
[Forms]![frm_MachineSearch]![txtMachineNum]

Good luck!
Geof Wyght
-----Original Message-----
I have two forms with two different list boxes. frm_MachineSearch and
frm_DrawingNum.

In my form frm_MachineSearch I have a list box with the following fields in
it, MachineNum(Primary Key) and Description.

In my form frm_DrawingNum I have the following fields in it,
DrawingNum(Primary Key) and MachineNum(Foreign Key).

When I double click one of the Records in the list box from
frm_MachineSearch I want the form frm_DrawingNum to open and in the list box
only show the DrawingNum records that match the MachineNum double clicked in
the frm_MachineSearch form.

I really don't know if it is even possible to code this some how, I tried a
few things but they didn't work. If you could follow what I am trying to do
and know of a way to do it please help.

Thanks.
.
Click to expand...
Click to expand...
 
All this works except for step 3. When I go to open the frm_DrawingNum a
parameter value message box appears instead of opening up to that record. I
don't see anything wrong with the criteria
[form]![frm_MachineNum]![txtMachineNum] I put in the Query....did i miss
something?
Click to expand...

Just the letter "s": it should be [Forms]! rather than [form]! at the
start of the expression.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top