Populate combo box from another combo box 1

[dom24]

Hello,

I have a combo box with a list of surnames and I want the first name combo box to update depending on what is selected in the surname box.
It works the first time but then the whole form has to be reset before it will work again.

I'd appreciate any help.

 

[PHV]

Any chance you could post the RowSource property of your 2 combo and how (and when) the first name combo is supposed to be updated ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

Yeah sure.

Rowsource for cboLName is:

SELECT DISTINCT dbo_Staff.LName
FROM dbo_Staff
ORDER BY dbo_Staff.LName;

Rowsource for cboFName is:
SELECT dbo_Staff.FName
FROM dbo_Staff
WHERE (((dbo_Staff.LName)=[forms]![frmpassword]![cboLName]));

The first combo box (cboLName) is a drop down list of surnames for the user to select one.
Once select I want the corresponding first names to appear in the second combo box.

 

[PHV]

In the AfterUpdate event procedure of cboLName:
Me!cboFName.RowSource = "SELECT FName FROM dbo_Staff WHERE LName='" & Me!cboLName & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

That works great, but can I get it to appear in cboFName straight away rather than always having to use the drop down? It's just that sometimes there will only be one first name so they shouldn't need to use the drop down.

 

[PHV]

You may try to add this:
Me!cboFName = Me!cboFName.Column(0, 0)
Or this:
Me!cboFName.ListIndex = 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

I'm gettin an error message when I change the FName combo box saying:

Microsoft Access can't find the macro '.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved. etc....

It's fine on the first name in the list but when I change it this error pops up. Any ideas???
This happens without adding
Me!cboFName = Me!cboFName.Column(0, 0)
Or this:
Me!cboFName.ListIndex = 0

 

[PHV]

Seems you're trying to write VBA code in an OnXXXX property instead of click the ellipsis (...) near <event procedure>

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

No I haven't wrote any code in the Onxxxx property. It's all inside an event procedure. The only code I've got is inside the rowsource of the combo box LName.

 

[dom24]

Never mind, I've just deleted it and created a new one and it seems to be working now. Must've been something but I couldn't see anything! Thanks for your help.

 

[PHV]

Check ALL the OnXxxxx properties of the FName combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244