Incorrect Values Populating My Combo Boxes

[charlyzaingel]

I am creating a form with fields that auto populate. I've read numerous postings and threads, but can't get it to populate with accurate data.

[Instructor]
[LineNumber]
[Status]

Private Sub cmbInstructor_AfterUpdate()
'Initialize LineNumber and Status or each Instructor change'
If Not IsNull (Me![cmbInstructor]) Then
Me![cmbLineNumber] = (Me![cmbInstructor])


Me![cmbStatus] = (Me![cmbInstructor])

The Instructor field is populated using a qry...
SELECT [ID], [Instructor] FROM qryInstructorsExtended ORDER BY [Instructor];

The LineNumber field is populated using a qry...
SELECT [ID], [LineNumber] FROM qryLineNumberExtended ORDER BY [LineNumber];


The Status field is populated using this qry...
SELECT [Duty Status_ID] FROM tblInstructors GROUP BY [Duty Status_ID] ORDER BY [Duty Status_ID]

The values from the cmbStatus field when I run the query return the correct data (Active, Leave, Retired etc.)...but when I look at the values based on the cmbStatus dropdown in form view it reads, 1, 2, 3, 4... I am assuming it is reading based on my Lookup to the tblDutyStatus from the tblInstructors.

When I change my cmbInstructor, the cmbLineNumber populates correctly, but the cmbStatus does not populate with the correct data. It populates with the [ID] field for each Instructor.

Please help me resolve this issue. I greatly appreciate the work you guys/gals do. I love this forum.

[boblarson]

Make sure the Instructor combo has the Column Count property set to 2. And the Column Widths set to something like 0";2"

[charlyzaingel]

Double checked those values. Any other suggestions.

[boblarson]

When you select the drop down of the combo, what do you see? Do you see both fields or do you see only the ID field? The bound field should also be the ID field and not the Instructor name field. Is it?

[charlyzaingel]

When I look at the values based on the cmbStatus dropdown in form view it reads, 1, 2, 6...only the values that are currently being used. It doesn't see the two values.

When I choose Instructor it is suppose to populate the LineNumber (it does) and the Status. When I select the Instructor, the status is populated with the [ID] number of the Instructor, not the value 1, 2... 6 or the active, leave, retired.

[charlyzaingel]

In the table that it is directed back to. The values Active, Leave, Retired and so on are populated in that table.

Two issues then...

1. how can I see Active, Status etc...does it have to do with the data type leading back to the tblInstructor. lookup to tblDutyStatus. The Lookup to Duty Status is showing Number as it's datatype. However, the values that appear in table view is text...showing Bound Column 1, Column Count 3, 0";0.0047";1" on width. This shows perfectly. But I do wonder if the datatype is driving the issue for the Form.

2. Why is the Instructor table not populating the value for the Status. I do not have a qry like I'm using for cmbInstructors and cmbLineNumbers. Again, the cmbInstructors populates the Status with the [ID].

[boblarson]

Can you upload a copy of the database (make sure no sensitive information is in it)?

[charlyzaingel]

I will upload it tomorrow. Thanks for your help today. I'm exhausted. :-)

[charlyzaingel]

Hi Bob,

Can I share this dbase via email with you?

[charlyzaingel]

I had Compact/Repair...it brought it to 512kb...but didn't think to Compress/Zip. Thank you for your help.

[charlyzaingel]

Hi Bob, Have you had any luck with that form?

[boblarson]

Why in the Instructor After Update event are you setting the Status to the Instructor? That is what is happening with:

Me![Status] = (Me![Instructor])

Also, in the Instructor ID combo box you don't have anything in its after update event to be able to set the instructor combo to the right value based on it.

[charlyzaingel]

I had changed that to [Instructor], [InstructorID], [Status] and it does not populate with the correct information.

[boblarson]

Post the CODE you now have on the Instructor_AfterUpdate() event.

[charlyzaingel]

It is the same code that it is the dbase you currently have. I haven't changed it since. When I was testing the values, I tried it many different ways.