Auto Fill Fields in a Form using a Combo field

[jlindquist23]

I'm using the ItemNo Combo box to update 28 other fields.
The first 4 work fine. The other 24 don't update. My column count is set to 28. Any other thoughts why the others won't fill in?
I've attached a picture.
Here's my OnChange formula for that field. No errors happen when I run it.

Private Sub cboItemNo_Change()Me.txtRC_LCL.Value = Me.cboItemNo.Column(1)
Me.txtRC_UCL.Value = Me.cboItemNo.Column(2)
Me.txtSagePotTemp.Value = Me.cboItemNo.Column(3)
Me.txtSageQTemp.Value = Me.cboItemNo.Column(4)
Me.txtSageRackQty.Value = Me.cboItemNo.Column(5)


Me.txtSageLoadTime.Value = Me.cboItemNo.Column(6)
Me.txtSageHardnessAim.Value = Me.cboItemNo.Column(7)
Me.txtSageBTPinDia.Value = Me.cboItemNo.Column(8)
Me.txtSageBTVBlock.Value = Me.cboItemNo.Column(9)
Me.txtSageBTQty.Value = Me.cboItemNo.Column(10)
Me.txtSageBTLot.Value = Me.cboItemNo.Column(11)
Me.txtSageBTMin.Value = Me.cboItemNo.Column(12)
Me.txtSageSurHdnsQty.Value = Me.cboItemNo.Column(13)
Me.txtSageSurHdnsLot.Value = Me.cboItemNo.Column(14)
Me.txtSageSurHdnsMin.Value = Me.cboItemNo.Column(15)
Me.txtSageGrindHdnsQty.Value = Me.cboItemNo.Column(16)
Me.txtSageGrindHdnsLot.Value = Me.cboItemNo.Column(17)
Me.txtSageGrindHdnsMin.Value = Me.cboItemNo.Column(18)
Me.txtSageEndSurHdnsQty.Value = Me.cboItemNo.Column(19)
Me.txtSageEndSurHdnsLot.Value = Me.cboItemNo.Column(20)
Me.txtSageEndSurHdnsMin.Value = Me.cboItemNo.Column(21)
Me.txtSageHyperlink.Value = Me.cboItemNo.Column(22)
Me.txtSageStrikeTestQty.Value = Me.cboItemNo.Column(23)
Me.txtSageStrikeTestLot.Value = Me.cboItemNo.Column(24)
Me.txtSageMicro.Value = Me.cboItemNo.Column(25)
Me.txtSageMemo.Value = Me.cboItemNo.Column(26)
Me.txtSageD4ProgramNumber.Value = Me.cboItemNo.Column(27)
Me.txtSageD4PartsPerRack.Value = Me.cboItemNo.Column(28)
End Sub

Thanks for you help!!!
Jen

[pbaldy]

Potentially stupid question, but are all the fields in the row source of the combo?

[jlindquist23]

Yes. I made sure, but appreciate the thought.

[davegri]

Just a shot in the dark, but try

Me.txtSageRackQty.Value = NZ(Me.cboItemNo.Column(5),0)

to see if null values are the problem.

[June7]

I have to wonder why all this data is duplicated and not just saving the ID?

[davegri]

I have to wonder why all this data is duplicated and not just saving the ID?

Or just have the rowsource for the combobox be the recordsource for the form.

[jlindquist23]

Just a shot in the dark, but try

Me.txtSageRackQty.Value = NZ(Me.cboItemNo.Column(5),0)

to see if null values are the problem.

Hmmm... interesting. That put zeros in all the boxes.
Sooo... that means that it thinks my data is empty?

But the data is there... see attachments.

[davegri]

Sooo... that means that it thinks my data is empty?

Indeed it does.

[pbaldy]

Can you attach the db here?

[Micron]

Also wonder why data that already exists is being duplicated. Sounds like underlying table design issue.
Regardless, will say that if you knew you'd be doing this from the outset, then you could have added the column number to the control name and shorten the code to something kind of like

Private Sub cboItemNo_Change()
Dim ctl As Control
Dim int As Integer

For Each ctl in Me.Controls
  If ctl.ControlType = acTextbox Then
    int = Right(ctl.Name,1)
    .ctl = Nz(.cboItemNo.Column(int),"")
  End If
Next

End Sub

If the first control name encountered ended in 4, the Right function would set int = 4. Then the value gets set to what's in column 4. If there's no value in it, then control is set to empty string. You could use any allowable alternate value for Nz. If you had a bunch of other textboxes, they wouldn't get a number at the end of their name.
Note: I would never start a name with a number. I also see that the absence of a number or numbers greater than 9 would have to be dealt with, but seeing as how this won't get used anyway, will leave it as is. Just thought it might be food for future thought. Upon further reflection, using the control tag would be even simpler.