Will G said:
Hey Carl,
thanks for your help, i guess it does work, only that, i got a little
comfuse with what i am doing and i would love for you to clarify it for
me....
i have the main form "MSM"...this form contains a search text field and i
want to enter text here, a string, and when click the search button, i
want
to openup a different for that's named "frmSelectMSM" ...this form
contains a
list box with ModelNumber(string and could be for example A99000M), should
be
same field as the one entered in the search area txtbox.......this list
box
is the only item on this form....i want to filter this list if the user
enters "A" or if the user enters the entire ModelNumber. And when i select
one of this filtered ModelNumber, i like to populate the fields on the
main
form "MSM", that contains ModelNumber, and other fields. .....if you
understand this, can you help me with it?
<snipped>
Let's try this again. There are two steps to this process: passing the value
from your search text field to the second form so that the listbox can be
filtered, and returning the listbox selection to the first form so it can be
used to "populate the fields" (more on this later).
For the first step, pass the value from the search text box to the second
form in the OpenArgs parameter of the FormOpen method:
DoCmd.OpenForm "frmSelectMSM",,,,,acDialog,txtSearchBox
Be sure to use your own text box control name here. We open the second form
in Dialog mode so that the first form "pauses" until the second form closes.
In the Load event of the second form, "pick up" the value from the OpenArgs
parameter and use it to filter your listbox. I can't really give an example
here, because I don't know how you are populating the listbox with the model
number strings. If the listbox is bound to a table, you can just modify the
RowSource of the listbox to incorporate the value passed in OpenArgs:
lstListBox.RowSource = "SELECT ... FROM ... WHERE [ModelNumber] LIKE '" &
OpenArgs & "*'"
I made up the name of the listbox control here, so be sure to use your
actual control name. If you are populating the listbox by some other method,
you'll need to adjust it as needed to incorporate the value in OpenArgs.
For the second step (returning the listbox selection back to the first
form), you need to first create a module to define an application-level
("global") variable that can be used from both forms. Create a new module
(or you can use an existing module if you have one) and add the variable
declaration:
Public gblSelection As String
In the second form, in the DblClick event of the listbox (or whatever other
method you use to exit the form), store the listbox selection in the global
variable:
gblSelection = lstListBox
(be sure to use your own variable and control names here). Back in the MSM
form, immediately following the OpenForm call that opened the second form,
pick up the value from the global variable and use it to "filter" the MSM
form:
Me.RecordSource = "SELECT ... FROM ... WHERE [ModelNumber]='" & gblSelection
& "'"
This assumes you're using an SQL statement for the form's RecordSource
(which is what I prefer, for just this reason). If you're using a table or
query, you may want to use the form's Filter and FilterOn properties instead
(like in your previous question). If your MSM form is unbound, you can fetch
the record from the table yourself (with a Recordset) and populate the
controls on the form.
I don't know if there's more I can add to this without actually writing the
code for you. Study these examples, look in Access and VB Help, and search
these newsgroups for specifics. What you're trying to do is fairly common
and has been addressed many times in these newsgroups.
Good luck,
Carl Rapson
