Problems populating a textbox from a combo box

[Elleh]

I'm really hoping someone can help me, as I'm about ready to tear my hair out at the moment!!!

After a few problems, I've managed to set up a subform with three cascading combo boxes (i.e. "Family", "Order", "Common species name"). However I want a fourth field (a textbox: "Latin species name") to update automatically when the value of common species name is selected, so that the appropriate Latin name appears in the textbox at the same time.

Subsequently, in the afterupdate event of the "Common species name" combo box, I put the code:

Me.Latin_Name = Me.Common_Name.Column(2)

(Where Common_Name.Column(2) is obviously the field containing the value for Latin species name.)

Which seemed to work fine.... for the first record at least!

The problem comes when I try to add a new record to the subform - and instead of resetting, the "Latin species name" value entered in the first record also carries over to all subsequent records.... and changing the value in any later record simply also changes it in the first (and all other) records.

Can anyone work out what I'm doing wrong here? Any help will be much appreciated!

 

[boblarson]

Your text box is likely "unbound" and as such will just display the most currently set value. Bind the text box to a field and then it will display what has been set for that record. When you go to a new record it will not display anything because a value has yet to be set for it.

 

[Elleh]

Thanks for your quick reply!

It was indeed unbound as I don't actually have an equivalent field in the underlying table.... (the underlying table is set up so that most of the fields look up their values in other tables, and I have it set up to look up both the common and latin name values in the "Common species name" field, if you see what I mean).

I've had a go at binding the text box to the appropriate field in another table but this then generated a 'run-time error', saying that the I can't assign a value to this object....

Am I doing something really silly here?! Unfortunately I've only had basic training in Access but as thats more than anyone else in my group, I've been left trying to build this database... sorry if I'm being stupid!

 

[boblarson]

It has to be bound to a field that is within the recordsource of the form.

 

[CraigDolphin]

Not to derail things, but be careful here. For one, latin names are always unique but common names are not always unique. Therefore, depending on the array of critters you're dealing with, there is the potential to get the wrong latin name if you're solely using common name to look up the latin name because Dlookup will only return the latin name of the first record that matches the common name. With that proviso...

Reading your original post, it looks as if you're not interested in storing the latin name but rather just displaying it when the common name is selected?

If so, then use the On_current event of your subform to lookup the latin name from the table if there is a value in your common name combo box, otherwise set the value of the unbound latin name box to null.

Do the same in the after_update event of your common name control.

Something like...

If Me.cmboCommonName & "" <> "" Then
Me.txtLatinName = Dlookup("LatinNameField","tblNames","[CommonNameField]=" & Me.cmboCommonName)
Else
Me.txtLatinName = ""
End if

 

[Rich]

If I'm reading this correctly you don't need any code at all, just set the control source of an unbound textbox to
=[YourComboName].[Column](2)

 

[Elleh]

Thanks so much for your help everyone - I've managed to get it working now (fingers crossed!)