Trying to populate a text box from the choice of a combo box

[nrypka]

I've been working on this database for about a week now and it is my only experience with Access so far so I don't know too much VBA yet. My problem currently is that I am attempting to Add a record to a database on a split form, and I want it so that when I choose the department via combobox that it populates a textbox with the departmentID. I have tried using the code below on the on change of the combobox

Private Sub Department_Change()
Me.DepartmentID.Value = Me.Department.Column(1)
End Sub

Any help with this would be greatly appreciated. Thank you

 

[pbaldy]

You haven't said what the problem is. The code looks okay. It should pull the second column of the combo's row source.

 

[nrypka]

Sorry I didn't state the problem, which is that it is not populating the textbox. Are there any other settings that would need to be changed to go along with this?

 

[pbaldy]

Make sure the column count property of the combo is at least 2 (should be the number of columns in the row source). I'd also use the after update event rather than the change event, which fires with every keystroke.

 

[nrypka]

I do have the column count set to 2, and I took your advice and changed it to after update, but still no result

 

[pbaldy]

Can you attach the db here?

 

[nrypka]

View attachment Calibration.zip Here is the database.

 

[pbaldy]

The row source of the combo is:

SELECT Department.Department
FROM Department;

There is no second column of data to pull. Sounds like you want

SELECT Department.Department, Department.ID
FROM Department;

 

[nrypka]

Now I have both of the fields showing up in the Department combobox, but it still isn't populating the DepartmentID textbox

 

[MarkK]

In a typical scenario you bind the combo directly to the record source of the form. So say you have a field called DepartmentID which is the foreign key in the current record, linking to the department. You can bind the combo directly by setting these properties . . .

RowSource: SELECT DepartmentID, Department, FROM tblDepartment ORDER BY Department;
Bound Column: 1
Control Source: DepartmentID
Column Count: 2
Column Widths: 0";2"

In this situation, when you change the Department in the combo, the DepartmentID is changed directly, and the user never sees or deals the with foreign key ID, and there is no need to update a second field on the form.

Hope this helps,

 

[pbaldy]

Now I have both of the fields showing up in the Department combobox, but it still isn't populating the DepartmentID textbox

Works for me, though I had to add code to save the record.

 

[nrypka]

I'm just not sure what I am doing wrong. I appreciate all of the help.

 

[nrypka]

After I removed the Control Source for the DepartmentID the textbox populates from the combobox, but the problem with that is it takes away the DepartmentID in datasheet leaving all of the DepartmentID's blank

 

[nrypka]

The row source of the combo is:

SELECT Department.Department
FROM Department;

There is no second column of data to pull. Sounds like you want

SELECT Department.Department, Department.ID
FROM Department;
__________________

Was this the only code you changed? I have been doing everything I can to make it work and just can't seem to get it.

 

[pbaldy]

You missed

though I had to add code to save the record.

The code I used was:

If Me.Dirty Then Me.Dirty = False

 

[nrypka]

I added that but it still throws a run-time error '3341' code.

 

[nrypka]

Here are screenshots of my properties for the combobox. Would you mind telling me if I am wrong somewhere.

 

[nrypka]

I have got it working now following Markk's instruction. But it still has error saying 'Object Required', though if I just hit OK it all works just fine as if there was no error

 

[MarkK]

When you get the Object Required error, is there a debug option? If so, click debug, and post the code, indicating the line that causes the error. Maybe we can track that down . . .

 

[nrypka]

this is the code that shows with the debug

Me.Department.Column(1) = Me.DepartmentID.Value