1 combo box driving 2 results

[summer]

I have a combo box that after I enter the data it returns a value to another cell. The following is the code I am using and it is working fine...

Private Sub new_record_Click()
On Error GoTo Err_new_record_Click


DoCmd.GoToRecord , , acNewRec

Exit_new_record_Click:
Exit Sub

Err_new_record_Click:
MsgBox Err.Description
Resume Exit_new_record_Click

End Sub

Private Sub Combo92_AfterUpdate()
Me.LBS_GAL = Me.Combo92.Column(1)
DoCmd.RunCommand acCmdRefresh
End Sub

Private Sub ID1_AfterUpdate()
Me.ID1 = Me.ComboBox.Column(1)
End Sub

BUT,
how do I add to this so when I enter the data into the ID1 combo box and it pulls the info into the LBS_GAL field, it will also pull information into a field called LBS_VOC?

 

[David R]

Your field names are a little confusing, so I'll ask: Are you trying to store information in two fields based on a combo selection, or just display it?

If it's just displaying it, you can use something extremely similar to Me.LBS_GAL = Me.Combo92.Column(1), provided you have that information in your combo (you may need to add another column).

If you're storing it, I'd make sure you need to first. There are cases where it's wise, but if two pieces of information come from a single selection, it's probably something you can look up later when you need it with a query, right?

HTH,
David R

 

[summer]

For now, I just need to view it. The info is in my combo box now. I've tried many ways to get this to work, but I guess I'm not putting the string together right.

 

[summer]

One more thing...
I was creating this in the AfterUpdate field as an event procedure. Then went to another field and did the same thing with a different combo box and it cancelled out my first. So am I understanding that you can only do 1 "event procedure" on a form? If so, how do I create this as a macro?
F.Y.I. This programming this is VERY complicated to me. Your help is much appreciated!!

 

[David R]

Oh no, you can do as many things in any one event procedure, and just about as many event procedures as you want, on a form. If you're new to it all though it could look a little arcane right now. Let's see if I can clarify.

So your combo (Combo92?) has four columns right now: The index column, the bound column that displays after you select something, the LBS_GAL column that you're displaying in Me.LBS_GAL, and a column for LBS_VOC that you're trying to display in (let's say) Me.LBS_VOC.
Is that a correct summary?

Combo boxes can have multiple columns in the list, but they can only display one column (the "bound" column) once you've selected an item. For other columns you can do a bit of form trickery in AfterUpdate, as you figured out. However I've found that in Access 2000 at least, there's a simpler way.

Make your text boxes that you want to display values in. Then set the Properties>Data>Control Source to =Combo92.Column(x) - you may have do a bit of fiddling to get it to show the right column, but this worked for me just now. It will update when you change the combo automatically, and requires no coding.

Also as to your problem with one event procedure wiping out the other, try taking out the DoCmd Refresh statement and see what happens. You should be able to do away with those event procedures altogether if this works properly.

Good luck,
David R

 

[summer]

This is great informaiton. I'll give it a try, but something has happened. It's not letting me enter any information. Not in the form, or my query, but my table is OK.
Any ideas?

 

[summer]

This is weird. I opened the query and clicked off of the Totals button. Now I can enter data. I will now try your suggestion.

 

[summer]

David,
Your advice helped alot. Everything is working great now. Thanks alot!
Summer