Save two fields from Combo Box.

[upsman]

Hi,

I have a Combo Box that contains two fields, the key field (StudentId) and another field (StudentName). As recommended by the Combo Box wizard, I'm hiding the key field so only the StudentName displays in the box. The wizard also allows me to save only one of those fields in my table so I chose the key field (StudentId). Problem is, I need to store both the fields in my table. How
do I store the two fields from the Combo Box in my table? I'm new to Access so I don't really know VBA that well.

Thanks,
Rod

 

[RuralGuy]

In the AfterUpdate event of the ComboBox use something like:

Me.txtNameTextBox = Me.cboName.Column(1)

Replace my psudo names with your real names.

 

[upsman]

Sorry to be so dense. Based on your suggestion, here's what I entered:

Me.StudentName = Me.Combo229.Column(2)

Combo229 is the name of my Combo Box and StudentName is the name of the field in my table I'm wanting to store. When I make a selection in the Combo Box, I get the error "Method or data member not found" and the ".StudentName"
portion of the expression is highlighted.

StudentName is in the Schedules table so I've also tried "Me.Schedules.StudentName" but get the same error. What do I put in for the
"Me.txtNameTextBox" portion of your example?

 

[RuralGuy]

Does the form where the ComboBox is located have a Record Source? It is on the data tab of the properties sheet of the form. Is it the "Schedules" table/Query? If so then put a TextBox on the form and name it txtStudentName with a Control Source of StudentName. Then your code to load it becomes:

Me.txtStudentName = Me.Combo229.Column(1)

ComboBox indexes are 0 based so column 1 is index 0, column 2 is index 1, etc.

 

[upsman]

Everything you assumed about my form/field was correct. I added the text box and changed the code and it works perfectly. Thanks!

Rod

 

[RuralGuy]

You're welcome. Glad I could be of assistance.

 

[phmurphy]

I have been looking for this - Thanks -more questions

Greetings,
Thanks, I have spent the whole weekend trying to figure this out. I actually was trying to fill in text boxes with two fields in the same record as the lookup value. They wouldn't fill in until I moved to the next record and entered something in the same box. Crazy. This works great, but how do I hide the two items in the combobox that I don't want to show. I just made the column widths so big that they were out of the window, but is there a better way.

Here is the code I was trying to use to get the two text boxes to fill in, but it failed.
Dim mysql As String
mysql = "Update WetForm a Inner Join COLO05 b ON a.Sp1 = b.SPENAME Set a.Sp1Strat = b.STRATUM, a.Sp1Indicator= REG5WET where a.Sp1 = b.SPENAME;"
CurrentDb.Execute mysql

Any Idea how I could have made this work right?

Pat

 

[RuralGuy]

...but how do I hide the two items in the combobox that I don't want to show. I just made the column widths so big that they were out of the window, but is there a better way.

Set their Column Width to 0";1.5";0";0"... etc.! Sorry but I can't help you with the SQL.