populating a combo box

[newbie87]

On my main form I have a textfield (Age) and a combobox for forenames. I have a "Details" table where I have Forename, Surname, Age

when i enter an age and click go I want the combo box to populate with all of the Forenames that are that age.

for example

if i search for age 21
the combo box should populate with the following names from the details table.
peter
matthew
adam

I've tried using a DLookup and this just populates the combo box with just peter which is the first name in the table. this is my code

cmbForename("Forename", "Details", "Age = '" & Age & "')

Many thanks

 

[CBrighton]

Create a query as the recordsource for the combobox.

It should be 2 column, 1 not returned but just used as criteria (name & gae, age is criteria only).

In the criteria cell for the age column right click and click build (or click the ... to the right of the cell after clicking into it).

Use the object browser at the bottom of the build window to locate the age control, double click it to set it as the criteria.


It's good to then set the .visible property of the name combobox to false so it's not displaying as empty when the form opens (as the age control is presumably emptry to it's looking for records with no age) and set the after update event of the age control to make the combobox visible and requery it (.visible = true & .requery).

 

[newbie87]

Hi CBrighton,

I followed your steps, and now it populates the combo box with all of the Forenames from the table, but when i search for 21 the Forename peter shows in the combo box and not all of the forenames who are listed as 21?

My criteria is set as Details.Age = Details.Age

What could i be doing wrong?

Thanks for your help.

 

[CBrighton]

I would expect the criteria to be something like:

WHERE Details.Age = [Forms]![[COLOR=red][B]SearchForm[/B][/COLOR]]![[COLOR=red][B]txtAge[/B][/COLOR]]

As in, field = control.

Surely Details.Age = Details.Age will always be true?

:edit:

Guessed form & control name highlighted red to ensure no confusion. I don't know your form or control name.

 

[newbie87]

THANK YOU!!!!!

been trying to work this out all afternoon

many thanks for your help

 

[CBrighton]

No problem.

I guess my query builder advice wasn't detailed enough, I was expecting the expression builder to do the criteria for you after you browsed to, and double clicked, the correct control on the form.

See attached screen-print.

 

[newbie87]

Hi CBrighton,

What if i want to take this a little further?

When the user types in the age, and the list of names populate the combo box, i want the user to be able to click a name and this will then populate the remaining textfields on the form with their personal data?

for example, the user searches 21, choose Peter, and then his address and email address display in the textfields?

I have this at the moment, but when this is 'clicked' nothing happens

If (Me.cmbForenames = " & me.cmbForenames & ") Then
txtEmail = DLookup("EmailAdrress", "Details", "Forename = " & cmbForename & " AND Age = '" & Age & "'")

Many thanks in advance

 

[CBrighton]

I assume the table has some unique field (an autonumber or perhaps an emplyee ID / payroll number)?

It would be better to use that as criteria rather than a combination of forename & age (it's possible to have 5 Dave's all born on the same year unless you have strange hiring principles!).

This can be easily done by making the combobox a 2 column combobox and adding the field to the query in the row source. By setting the ciolumn widths to something like "5cm;0cm" to can tell it not to display the second column, but your VBA can still refer to it with "cmbForename.Column(1)" (I think column is 0 based).

This would improve your ability to load the rest of the data. However, I wouldn't do it via VLookUp.

I would advise 1 of 2 options:

1. Have the form unbound and do everything via VBA - this means virtual recordsets to load & save the data.

2. Keep the main form unbound but add a subform. In the same way that the combobox rowsource uses a control for criteria you can use the unique field value in the 2nd column of the combobox as the criteria for the recordsource of the subform. It would also be a good idea to hide it by default and have the combobox after update event set .visible = true and .requery like you did with the combobox.