List box problem (query rowsource)

[mic907]

I have a list box with a query as its row source (screenshot attached).

The query gets data from my Contracts table with information on a Consultant ID, Contract ID, and a Contract Status indicator. This effectively represents each contract with a specified status.

What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected.

A little background on my main form, the main form consists of a main page followed by several Pages, with a subform in each. Thus, my contract screen is designated as Forms![frmConsultantList]![sbfrmContracts].

So, what I'm trying to do right now is collect multiple fields from the selection the user makes (on the listbox with the query row source). To further clarify, each line has a Consultant ID and a Contract ID. How do I pull each from the user's current selection on the listbox and assign them to new variables? As of now, I am only able to select the first field in the list (Contract ID) and assign it to a variable.

How do I do this, or is there a better way?

I apologize if I'm unclear about my situation, so please feel free to ask any questions. And of course, thank you to all those who spend time reading this.

 

[ajetrumpet]

What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected

If you have a customized form you may want to use the auto-populating of controls method. Here's the Microsoft article on how to do this...http://support.microsoft.com/kb/319482/en-us
You may also want to use the "GoToRecord" command in the editor.
Cmd.GoToRecord...I think that's right...

How do I pull each from the user's current selection on the listbox and assign them to new variables?

New variables?? You mean new controls??

If you want another form to pop up when a selection is a made, the best event to write the code in is the "OnLostFocus". That way, you're form won't keep popping up and annoying the user if they've selected a choice they don't want on accident. Write a single step macro "open form" in this event property. You can also write a short SUB in the editor. Cmd.OpenForm...

 

[mic907]

If you have a customized form you may want to use the auto-populating of controls method. Here's the Microsoft article on how to do this...http://support.microsoft.com/kb/319482/en-us
You may also want to use the "GoToRecord" command in the editor.
Cmd.GoToRecord...I think that's right...New variables?? You mean new controls??

I'm not sure how to go about doing this as I don't know how to specify these records or relate them to the selection of the comboboxes. Also, when I attempt this it says I need an object name? Sorry, I'm pretty new at this so I'm not sure what to do...

 

[ajetrumpet]

So, what I'm trying to do right now is collect multiple fields from the selection the user makes (on the listbox with the query row source).

To collect these fields as one record to pull, you need an ACTION command (DoCmd)

What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected.

Mic, if you don't really understand the notation of VB language, I would use simple macros to do what you need. Below is an outline that should work for you....

On "List Box" Form...
"OnLostFocus" or "After Update" Event (of listbox control) = Macro1

***Macro1 = OpenForm (contract form or subform name)

On "Contract screen" form...
"OnOpen" Event of the Form properties = Macro2

***Macro2 = GoToRecord (offset argument ---- =Forms!["list box" form or subform]!["list box" name])

You also did mention above something about only viewing one field in the list box. If the row source of your box only SELECT's one field, that's all you will see when the dropdown appears. The "column widths" property can also affect this. To see all the fields in a dropdown from a SELECT statement, make sure there are no 0" column widths in the properties menu.

***You might also want to look up "GoToRecord" in the Access help menu. The menu lists basic syntax (code or format of the language) for every command that is used in the program.