Combo Box

[Desstro]

For example, lets say I have a table that has 4 columns

1. First name
2. Last name
3. D.O.B


4. SS#

SS# being primary key.

Question: I would like to create one combo box on a form that looks up the SS# and also shows the rest of the data associated with that SS#. I can do that. What I can't seem to figure out is when I select something from the drop down on the combo box all I am left with is the SS#. Is it possible to select something from the combo box drop down and be left with all the data that was shown in the drop down?

[RuralGuy]

When collapsed, a ComboBox *only* displays one column. You can copy the other column values to some TextBoxes if you want.

[DaveT]

If your combo box is pulling four columns (SSN, LastName, FirstName, DOB) and SSN is the bound column, you can display the other columns in text boxes (cannot edit them, just display them).

For example, if the combo box is named cboSSN, then add a text box where the Control Source is =[cboSSN].[Column](1)

Note that the columns are base zero, so SSN is column 0, LastName column 1, FirstName column 2, ...

See attached image.

[Desstro]

Thanks guys!

Thats actually perfect because I dont want the data to be able to be manipulated anyway. Perfect! Thanks again.

[RuralGuy]

You're certainly welcome, and another satisfied customer is added to the list.

[Desstro]

Ooops, .... ran into a problem.

ok so I went ahead and did just as you guys suggested. Works great and is exactly what I need, however, once I get the data in there how can the end user clear it out?

This form will be filled out twice daily, 6am and 6pm and is for info only to the client. The people filling it out will need to be able to clear out the data.

Is there some way to maybe put a button in the header they can push that will reset the form?

[RuralGuy]

Not using the formulas in the ControlSource as DaveT suggested. You will need 2 functions in your form's code module: 1 to fill the TextBoxes and another 1 to clear them. You invoke the "Fill them" procedure from the AfterUpdate event of the ComboBox and the "clear them" from your button.

[DaveT]

As always, there is more than one solution.

Assuming that cboSSN is NOT a bound control (Control Source is blank) so that you are not editing the SSN you can:

1. Add a command button with On Click code:

On Error Resume Next
cboSSN = Null
txtLastName = Null
txtFirstName = Null
dteDOB = Null

2. You can create a union query such that a blank row shows on the combo pull down. For example:

SELECT null as SSN, null as LastName, null as FirstName, null as DOB
FROM zTest
UNION
SELECT zTest.SSN, zTest.LastName, zTest.FirstName, zTest.DOB
FROM zTest
ORDER BY SSN;

[Desstro]

I am liking the union query idea. I understand the code unfortunately I have no idea where to put it. Please help.

Don't have much knowledge on union queries.

[DaveT]

First, after testing, I modified the query as follows:

SELECT zTest.SSN, zTest.LastName, zTest.FirstName, zTest.DOB
FROM zTest
UNION
SELECT null as SSN, null as LastName, null as FirstName, null as DOB
FROM zTest
ORDER BY SSN;

The "null" row SELECT is last but will sort and show as the first row.

You can either put the SQL directly as the Row Source for the combo (Fig1), or you can save the query and use that as the Row Source (Fig2).

Build the query by using the SQL command in query design (Fig3). Union queries are noted in the navigation pane as shown in Fig4 and you cannot use the design grid for union queries. You build/edit the SQL statement directly (text above).

[DaveT]

Here's a link that discusses adding N/A in combo boxes. Link has video also:

Creating Combo Boxes in Access 2007 that Allow Users to Select N/A

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[Desstro]

So that combo box already has a row source. How do I add that code at the end of the pre existing one? I tried to the way I figure it should be but no luck.

This is what is in the row source already.

SSN name etc was just a for example by the way.

SELECT [PkgsReceivedandScheduledStatus].[PackageNumber], [PkgsReceivedandScheduledStatus].[Program], [PkgsReceivedandScheduledStatus].[FacilityArea], [PkgsReceivedandScheduledStatus].[FacilityLocation], [PkgsReceivedandScheduledStatus].[UID] FROM PkgsReceivedandScheduledStatus ORDER BY [PackageNumber];

[Desstro]

Nevermind....I figured it out. For forum purposes it looks like this

SELECT [PkgsReceivedandScheduledStatus].[PackageNumber], [PkgsReceivedandScheduledStatus].[Program], [PkgsReceivedandScheduledStatus].[FacilityArea], [PkgsReceivedandScheduledStatus].[FacilityLocation], [PkgsReceivedandScheduledStatus].[UID] FROM PkgsReceivedandScheduledStatus
UNION
SELECT null as PackageNumber, null as Program, null as FacilityArea, null as FacilityLocation, null as UID
FROM PkgsReceivedandScheduledStatus
ORDER BY [PackageNumber];

Thanks Dave. Your answer was right in front of me the whole time I just needed a little more time to understand what I was being told. Thanks for your patience.

[DaveT]

Great! Glad you got it working.