Update listbox.

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

Guest

I am trying to update a listbox based on another listbox. The first listbox
is linked to a table called Region with one field (named Region).

The second listbox is linked to a query with the following code:
Code: 
"SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE [IPB query].State = "IL" Or [IPB query].State = "MO"
Or [IPB query].State = "WI" Or [IPB query].State = "IN" ORDER BY [NAME],
[STATE], [IPB_ID];"
End Sub [code\]

I want to be able to click on a state in the first listbox and have the
second listbox list only choices related to the state chosen.  Can anyone
help me with this problem?  Thanks.
 
You can create a reference from the second list box to the first one

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State = Forms![MainFormName]![FirstListBoxName]
ORDER BY [NAME], [IPB_ID]

========================
On the AfterUpdate event of the first ListBox run the code
Me.[SecondListBoxName].Requery
To refresh the data
========================
Note: You need to change the names of the objects in the example to your
objects names
FirstListBoxName, SecondListBoxName, MainFormName

========================
Also, for future reference you can use "In ( "IL","MO" ,"WI" ,"IN")" instead
of "Or" and =\

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State In ( "IL","MO" ,"WI" ,"IN")
ORDER BY [NAME], [STATE], [IPB_ID]
 
Thanks for your help and also for the link.
Ofer Cohen said:
You can create a reference from the second list box to the first one

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State = Forms![MainFormName]![FirstListBoxName]
ORDER BY [NAME], [IPB_ID]

========================
On the AfterUpdate event of the first ListBox run the code
Me.[SecondListBoxName].Requery
To refresh the data
========================
Note: You need to change the names of the objects in the example to your
objects names
FirstListBoxName, SecondListBoxName, MainFormName

========================
Also, for future reference you can use "In ( "IL","MO" ,"WI" ,"IN")" instead
of "Or" and =\

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State In ( "IL","MO" ,"WI" ,"IN")
ORDER BY [NAME], [STATE], [IPB_ID]


--
Good Luck
BS"D


Yepp said:
I am trying to update a listbox based on another listbox. The first listbox
is linked to a table called Region with one field (named Region).

The second listbox is linked to a query with the following code:
Code: 
"SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE [IPB query].State = "IL" Or [IPB query].State = "MO"
Or [IPB query].State = "WI" Or [IPB query].State = "IN" ORDER BY [NAME],
[STATE], [IPB_ID];"
End Sub [code\]

I want to be able to click on a state in the first listbox and have the
second listbox list only choices related to the state chosen.  Can anyone
help me with this problem?  Thanks.[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Just had a follow up question to your original post. When you said to create
a reference, did you mean putting the code below in the Row Source box of the
second list box? I cannot figure out why the form is not working.


Ofer Cohen said:
You can create a reference from the second list box to the first one

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State = Forms![MainFormName]![FirstListBoxName]
ORDER BY [NAME], [IPB_ID]

========================
On the AfterUpdate event of the first ListBox run the code
Me.[SecondListBoxName].Requery
To refresh the data
========================
Note: You need to change the names of the objects in the example to your
objects names
FirstListBoxName, SecondListBoxName, MainFormName

========================
Also, for future reference you can use "In ( "IL","MO" ,"WI" ,"IN")" instead
of "Or" and =\

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State In ( "IL","MO" ,"WI" ,"IN")
ORDER BY [NAME], [STATE], [IPB_ID]


--
Good Luck
BS"D


Yepp said:
I am trying to update a listbox based on another listbox. The first listbox
is linked to a table called Region with one field (named Region).

The second listbox is linked to a query with the following code:
Code: 
"SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE [IPB query].State = "IL" Or [IPB query].State = "MO"
Or [IPB query].State = "WI" Or [IPB query].State = "IN" ORDER BY [NAME],
[STATE], [IPB_ID];"
End Sub [code\]

I want to be able to click on a state in the first listbox and have the
second listbox list only choices related to the state chosen.  Can anyone
help me with this problem?  Thanks.[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Yes.

But, did you change the names of the objects (form and list to what you have)?
Anf did you use the requery?

--
Good Luck
BS"D


Yepp said:
Just had a follow up question to your original post. When you said to create
a reference, did you mean putting the code below in the Row Source box of the
second list box? I cannot figure out why the form is not working.


Ofer Cohen said:
You can create a reference from the second list box to the first one

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State = Forms![MainFormName]![FirstListBoxName]
ORDER BY [NAME], [IPB_ID]

========================
On the AfterUpdate event of the first ListBox run the code
Me.[SecondListBoxName].Requery
To refresh the data
========================
Note: You need to change the names of the objects in the example to your
objects names
FirstListBoxName, SecondListBoxName, MainFormName

========================
Also, for future reference you can use "In ( "IL","MO" ,"WI" ,"IN")" instead
of "Or" and =\

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State In ( "IL","MO" ,"WI" ,"IN")
ORDER BY [NAME], [STATE], [IPB_ID]


--
Good Luck
BS"D


Yepp said:
I am trying to update a listbox based on another listbox. The first listbox
is linked to a table called Region with one field (named Region).

The second listbox is linked to a query with the following code:
Code: 
"SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE [IPB query].State = "IL" Or [IPB query].State = "MO"
Or [IPB query].State = "WI" Or [IPB query].State = "IN" ORDER BY [NAME],
[STATE], [IPB_ID];"
End Sub [code\]

I want to be able to click on a state in the first listbox and have the
second listbox list only choices related to the state chosen.  Can anyone
help me with this problem?  Thanks.[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Yes, I did make those name changes. I also used the requery. For some
strange reason, though, I kept getting this pop up that is asking me for the
form name. Everything seems to be working now, though. Thanks again.

Ofer Cohen said:
Yes.

But, did you change the names of the objects (form and list to what you have)?
Anf did you use the requery?

--
Good Luck
BS"D


Yepp said:
Just had a follow up question to your original post. When you said to create
a reference, did you mean putting the code below in the Row Source box of the
second list box? I cannot figure out why the form is not working.


Ofer Cohen said:
You can create a reference from the second list box to the first one

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State = Forms![MainFormName]![FirstListBoxName]
ORDER BY [NAME], [IPB_ID]

========================
On the AfterUpdate event of the first ListBox run the code
Me.[SecondListBoxName].Requery
To refresh the data
========================
Note: You need to change the names of the objects in the example to your
objects names
FirstListBoxName, SecondListBoxName, MainFormName

========================
Also, for future reference you can use "In ( "IL","MO" ,"WI" ,"IN")" instead
of "Or" and =\

SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE
[IPB query].State In ( "IL","MO" ,"WI" ,"IN")
ORDER BY [NAME], [STATE], [IPB_ID]


--
Good Luck
BS"D


:

I am trying to update a listbox based on another listbox. The first listbox
is linked to a table called Region with one field (named Region).

The second listbox is linked to a query with the following code:
Code: 
"SELECT [IPB query].IPB_ID, [IPB query].NAME, [IPB query].STATE
FROM [IPB query] WHERE [IPB query].State = "IL" Or [IPB query].State = "MO"
Or [IPB query].State = "WI" Or [IPB query].State = "IN" ORDER BY [NAME],
[STATE], [IPB_ID];"
End Sub [code\]

I want to be able to click on a state in the first listbox and have the
second listbox list only choices related to the state chosen.  Can anyone
help me with this problem?  Thanks.[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top