Combo Box Question.

[Bopsgtir]

Hi, ive got a problem with a combo box, its in an assignments form, so on one side i have the tech, and the other a fuel card.

both are on drop down boxes for my techs without a card and cards that have not been assigned

My question is i want the combo box to display two fields, i can get two fields with the drop down list but i also dont want the line thats between the two field. My issue is once i select a tech i only get his first name field in the box.

 

[CBrighton]

Then change the query, instead of 2 fields ([Forename],[Surname]) have 1 field ([Forename] & " " & [Surname]).

 

[Bopsgtir]

How would that be typed in the Row Source Property??

 

[r.harrison]

Hi,

To create a new field you'll need to do something like:


SELECT [TBL_Tech].[Firstname] & " " & [TBL_Tech].[Surname] AS [Full name] FROM TBL_Address;

 

[Bopsgtir]

Thank you for that, one question, where does the TBL_Address come from?? so does this create create a virtual field somewhere for the cbo to lookup??

 

[r.harrison]

Sorry, tbl_address is a table in my db that I use for testing. Replace that with the nam of your table.

does this create create a virtual field somewhere for the cbo to lookup??

Yes, this creates a virtual field from the fields you select.

 

[Bopsgtir]

ok so i cant still user the PK for that record as my bound column

Below are my first 4 columns in TBLTechs.

The number is want to return is 1 in the ID column but show Dean Robinson in the drop down.

ID TechID FirstName Surname
1 82740 Dean Robinson

 

[r.harrison]

Ok, try this:

SELECT [TBLTechs].[ID], [TBLTechs].[Firstname] & " " & [TBLTechs].[Surname] AS [Full name] FROM TBLTechs;

in The Data properties for the combo set:

BoundColumn to 1
ColumnWidths to 0cm;3cm

This will display the [fullname] in the combo, and when selected will return the ID for that Tech.

 

[Bopsgtir]

so your putting the id column in the sql statement so you can select it from the bound column.

without that added you would just return your statement so it would be Dean Robinson??

Sorry i know im asking alot of questions but i dont like the idea of just copying a line of code and not trying to learn what it does so i dont have to keep asking the same types of questions

 

[r.harrison]

Glad you're taking interest in it rather than copy n pasting.

The RowSource property of the combo is a Query in itself. If you click the [...] button next to the Rowsource the query builder will open. I tend to use this to create my SQL statements as it generally shows you where the errors are (if any).

To add a new field in the query builder, add the table you want to use and the ID column. Then in the next field type the Variable name you want in this case FullName and follow it with a :.

In this instance your query builder would look like

Field: ID Fullname: [tblTechs].[FirstName] & " " & [tblTechs].[Surname]
Table: tblTechs
Sort:
Show:
Criteria:

You can also right click on the Field and click [Build] to open a window that helps to build the field.

It's very useful to get to know all this. I've only really started using the Virtual Fields and It's saved me so much coding!

 

[Bopsgtir]

Great explanation, i could understand why i wasnt able to pickup the ID if i had the first and last name, as i didnt understand that the cbo was a query so needed the ID in that query, but i guess its like everything with access now you have explained it, it seems so obvious that thats how it would work.