Form Lookup

[Starcraft1535]

Ok so I'm creating a crew change over form for people to fill out. My main goal is to have one form for each crew instead of having mutiple forms. So I have a query for each crew. Alpha, Bravo, and Charlie. I want the user to select a a crew from a dropdown box and it will populate the other dropdown boxes with the crew member names. Any ideas?


Also does anyone know if its possible to merge 2 rows from a record and combine them in a dropdown box?

 

[Micron]

Try setting the form's record source to "" on form opening, then to a query or sql statement that uses the combo as criteria, driven by the combo's AfterUpdate event. Then refresh the form. Not sure what you mean by the second question. A record is a row, so you cannot have 2 rows and 1 record. You can have two records (or 2 rows, but that is not the proper name for a record) in a recordset, however. Perhaps the answer is to make the control source equal to a query that returns the records that belong in the combo list.

 

[xenou]

You can google this topic under the keywords "msaccess cascading comboboxes" or "msaccess dependent comboboxes" (I'm sure there are several different ways to accomplish that goal).

Some links:
Access Tips: Cascading Lists for Access Forms
Dependent Combo Box in Access
Microsoft Access Forms with Cascading Combo Boxes and List Boxes

 

[Starcraft1535]

Try setting the form's record source to "" on form opening, then to a query or sql statement that uses the combo as criteria, driven by the combo's AfterUpdate event. Then refresh the form. Not sure what you mean by the second question. A record is a row, so you cannot have 2 rows and 1 record. You can have two records (or 2 rows, but that is not the proper name for a record) in a recordset, however. Perhaps the answer is to make the control source equal to a query that returns the records that belong in the combo list.

So i meant is a possible to have two columns in one row combined in one drop down box. So like i have one box as First name other as Last name so when a user is picking a name from the drop down to show as First Last.

 

[Starcraft1535]

You can google this topic under the keywords "msaccess cascading comboboxes" or "msaccess dependent comboboxes" (I'm sure there are several different ways to accomplish that goal).

Some links:
Access Tips: Cascading Lists for Access Forms
Dependent Combo Box in Access
Microsoft Access Forms with Cascading Combo Boxes and List Boxes

So i have tried the cascading combo and i dont know what im doing wrong.

Everything i have.
Tables
tbl_Personnel

Forms
form_ChangeOver

Qureys
All Crews
Alpha Crews
Bravo Crew
Charlie Crew
Delta Crew
Echo Crew

 

[Micron]

So i meant is a possible to have two columns in one row combined in one drop down box. So like i have one box as First name other as Last name so when a user is picking a name from the drop down to show as First Last.

If I understand your question, yes, you can have more than one field (column) displayed in a combo box, but only when it is dropped down. On the combo box property sheet you set the number of columns and their widths. Those that you do not want to see are set to 0 (that would often be an ID field for the record, which has no meaning to the user). The first field that is not zero will be the one displayed when the selection is made. The field that you want to 'bind' the record to is called the bound field, and you have to choose that from the property sheet. That means that regardless of what is showing in the combo after a selection, it is the bound field value that is being passed to whatever is using it. The bound field is a zero based number, so to bind the first field, you set it to 0. As for the data that will be shown in the combo's list, that would be a query or sql statement that retrieves the Last Name, First Name or whatever it is you want to display.

 

[xenou]

I know it's hard to explain but Micron's instructions are spot on. You can think of what you "see" in a drop down as being as many columns as you want -- you decide how much space to give to each field by setting the widths (even zero - which means you don't see it). The "bound field" is the one that the drop down sees as the "real value", the rest is just for show. Typically, the bound field is something like an ID, and the displayed field is something like a user-friendly name or description.

See further with some pictures:
MS Access 2003: Bind combo box to primary key but display a description field

 

[Starcraft1535]

If I understand your question, yes, you can have more than one field (column) displayed in a combo box, but only when it is dropped down. On the combo box property sheet you set the number of columns and their widths. Those that you do not want to see are set to 0 (that would often be an ID field for the record, which has no meaning to the user). The first field that is not zero will be the one displayed when the selection is made. The field that you want to 'bind' the record to is called the bound field, and you have to choose that from the property sheet. That means that regardless of what is showing in the combo after a selection, it is the bound field value that is being passed to whatever is using it. The bound field is a zero based number, so to bind the first field, you set it to 0. As for the data that will be shown in the combo's list, that would be a query or sql statement that retrieves the Last Name, First Name or whatever it is you want to display.

So what i have so far that working. I still don't know how im going to have two fields in the dropdown box.

[COLOR=#000099]Private[/COLOR][COLOR=#0000CC] [/COLOR][COLOR=#000099]Sub[/COLOR] cboCrew_AfterUpdate()
[COLOR=#0000CC]   [/COLOR][COLOR=#000099]On Error Resume Next[/COLOR]
[COLOR=#0000CC]   [/COLOR][COLOR=#000099]Select[/COLOR][COLOR=#0000CC] [/COLOR][COLOR=#000099]Case[/COLOR] cboCrew.Value
[COLOR=#0000CC]      [/COLOR][COLOR=#000099]Case[/COLOR] "Alpha"
         cboCMD.RowSource = "QAlphaCrew"
         cboDep.RowSource = "QAlphaCrew"
[COLOR=#0000CC]      [/COLOR][COLOR=#000099]Case[/COLOR] "Bravo"
         cboCMD.RowSource = "QBravoCrew"
         cboDep.RowSource = "QBravoCrew"
[COLOR=#0000CC]   [/COLOR][COLOR=#000099]End Select
End Sub[/COLOR]

 

[Starcraft1535]

If its not possiable that way can i just use a Dlookup in another feild based on the value in one of the cboCMD or cboDep

 

[Starcraft1535]

Found my solution I'm using another textbox with the following AfterUpdate command

me.<textboxname>=DLookup("[costfieldname]", "table", "drugname='" & Me.<comboname> & "'")