Problem with an Autofilling Form

[HopeResearch]

Hello All,

I'm relatively new to Access so bear with me.

I have a form I created in the past that autofills some of its fields based on the user's selection of a record from a combobox linked to another table. The form has been working perfectly however I recently tried to add a couple of more fields to the form that need to be autofilled. I added the new fields to the source table and added columns to the combobox that should allow my VB code to fill those boxes. However only one of the new fields is filling correctly. After trying a few different things I notice that the problem seems to be with whatever data field becomes the 21st column in the combobox. Is there some sort of limit on the number of columns contained in the data source for such a box? Do anyone have any idea how to fix this?

I've pasted my VB code below

Option Compare Database

Private Sub EmpIDAutofill_Change()
Me.EmployerCatAutofill.Value = Me.EmpIDAutofill.Column(1)

Me.StreetNumAutofill.Value = Me.EmpIDAutofill.Column(2)

Me.JobCityAutofill.Value = Me.EmpIDAutofill.Column(3)

Me.JobZipAutofill.Value = Me.EmpIDAutofill.Column(4)

Me.ComOnExactAddresss.Value = Me.EmpIDAutofill.Column(5)

Me.ExactAddressAutofill.Value = Me.EmpIDAutofill.Column(6)

Me.LatitudeAutofill.Value = Me.EmpIDAutofill.Column(7)

Me.LongitudeAutofill.Value = Me.EmpIDAutofill.Column(8)

Me.PositionAutofill.Value = Me.EmpIDAutofill.Column(9)

Me.JobCatAutofill.Value = Me.EmpIDAutofill.Column(10)


Me.PT_FTAutofill.Value = Me.EmpIDAutofill.Column(11)

Me.HoursAutofill.Value = Me.EmpIDAutofill.Column(12)

Me.EqualOppEmployerAutofill.Value = Me.EmpIDAutofill.Column(13)

Me.CrimHisAutofill.Value = Me.EmpIDAutofill.Column(14)

Me.RefAutofill.Value = Me.EmpIDAutofill.Column(15)


Me.RaceQAutofill.Value = Me.EmpIDAutofill.Column(16)

Me.SkillsTestAutofill.Value = Me.EmpIDAutofill.Column(17)

Me.EmpEmailAutofill.Value = Me.EmpIDAutofill.Column(18)

Me.CommOnEmpAutofill.Value = Me.EmpIDAutofill.Column(19)

Me.TimePostedAutoFill.Value = Me.EmpIDAutofill.Column(20)

Me.DatePostedAutofill.Value = Me.EmpIDAutofill.Column(21)​

 

[GinaWhipp]

Umm, why not just have one Combo Box and let it look up the Record, as in a typical COmbo Box?

 

[HopeResearch]

That's basically what I have if I understand what you're saying. To originally set up the form I followed the method in a youtube video entitled, "Access 2010: Auto populate fields using a combo box in forms," to originally set it up.

On the form I have one combobox "EmpIDAutofill" that pulls information from another table to create a drop-down. From the drop-down the user selects the record entry. I have an on change event on this combo box using the VB code in my previous post that fills the contents of different form fields based on the content of the combo box. My problem is anytime I have more then 20 columns of data in the combo box the 21st column will not be auto filled into the correct form field. Are you suggesting a different way of setting this up?

 

[GinaWhipp]

Umm, never saw that video, so...

The *standard* way of doing it is make a selection from a Cmob Box and it fills the fields with a few lines of code, using one of these methods...

http://allenbrowne.com/ser-03.html

https://www.youtube.com/watch?v=x0lX3q5dhhU

http://ms-access-tips.blogspot.com/2011/05/using-combo-box-to-search-for-record.html