Displaying all query fields in separate text boxes on form

Yet another novice here... :-)

I'm doing a select query in a combo box on a form. The query turns
up 3 fields (like First Name, Last Name, Phone number), but only
displays the first one in the combo box once the selection is
made. How can I display the remaining data on
the form, either in a single control or multiple controls?

Thanks for any help...

Hi SecuritasMatt,

In a combobox each column has an unique count. The first column is 0, the second is 1 and the third is 2 and so forth. So you can use the count of the columns to extract the data.

First method,

singleControlField = Combobox.Column(0) & " " & Combobox.Column(1) & " " & Combobox.Column(2)

Second Method,

FirstNameField = Combobox.Column(0)
LastNameField = Combobox.Column(1)
PhoneNumberField = Combobox.Column(2)

You can also concantenate the fields in your query like the first method and then your combobox will display all the fields when selected in the combobox, not just the first column.

lwells

Thanks, that almost works :-)

If I put this in "After Update" for the combobox, everything compiles OK, but I still only get the first column to display. Interestingly, in the "Bound Column" property of the combo box, it doesn't seem to matter which column I specify, it still only displays the first column (0) .

If I try to assign the selected data to another field, like "TextBoxX = ComboBox.Column(1)" under the "Combobox After Update", VB complains that I can't assign the data to that control, although the error pop-up indicates the correct data I want assigned (hence, something is working). If I put the assignment under "TextBox After Update", it compiles OK, but nothing shows up (the text box says, "TextBox?") .

Should I put these assignments somewhere else than "After Update"? If so, how will it know which combo box selection I have made?

Thanks again!

Well depending on your error message, try setting it to the value of the text box

textbox.value = combobox.column(1)

The combo box will only display one field, the first column that isn't set to 0" in your widths property. And the After Update event is where you would put your code.

You can also use the control source of an unbound text box to use the pull method vs the push method shown above

=combobox.column(1)

Either way will work. Might post what your error message was. It is quite possible that you didn't use the correct name of the field in your syntax.
lwells

Thanks again!

Although, I'm starting to think this can't be done the way I want it done.
Here's the setup - I'm using this query to winnow out a list of employees who have a bit
set indicating they are allowed to authorize a transaction:

SELECT [SitePersonnel].[FirstName], [SitePersonnel].[LastName], [SitePersonnel].[Extension], [SitePersonnel].[RecordNumber]
FROM SitePersonnel
WHERE ((([SitePersonnel]![PPAuthorizerBit])=Yes))
ORDER BY [SitePersonnel].[FirstName];

I then dump this query result into a combobox called "AuthorizerRecord", which I just built with the Wizard.
The drop list displays all the data the SELECT query filters out, but apparently only one of the columns can be
dumped into the table (the one associated with this form, not the SitePersonnel table the query filters)
and onto the combobox field.

I was hoping to find a way to get the data from each column of the selected record onto the
form at least, and ideally onto the table, all in one step without having to re-run the query (or
something like that) for each column. But, maybe that's the only way to do it. The error I
get no matter what I try to get another column to display in an unbound text box is:

Run-time error '2448':
You can't assign a value to this object.

MS says this error relates to a space in a field name when using the BuildCriteria method,
which I'm not using, and there are no spaces in any of my field or control names.

Well, I'm giving up and going to try another approach, but if anyone has any bright ideas, I'd
love to hear them!

Thanks again for the help!

Why don't you post the code from your After Update from your combobox so I can see where you are getting your error from.

Secondly your combobox should display the following fields in the drop down state based on your sql,

FirstName LastName Extension RecordNumber

However it will only commit the first column that is not set to zero. Example, if the column count is set to 4 for the four above fields and the widths are set 0";1";1";1" then the combobox will display only LastName Extension RecordNumber in the drop down state, but when selected the only column that will commit is LastName because it is the first column that is not set to zero.

You might want to take a look at the column count and make sure you have the correct number of columns shown. If you have only 2 shown, then trying to use combobox.column(2) will fail because you only have columns 0 & 1 to work with.

Adding the combobox column value to your underlying table on your form can be done by setting the textbox control source to the field that you want the data stored in.

If your textbox control name is txtLastName and it is bound to the underlying table field name LastName, then the code in the After Update event of the combobox would look like this. This will assume that your combobox has four columns and the column count is four with the widths set at 1";1";1";1" and the LastName is in the second column.

txtLastName.Value = combobox.column(1)

We use (1) because the first column always starts at (0).

If you still are having trouble, post the code from the after update event of your combobox.

lwells