Autopop multiple fields from one ComboColumn?

  • Thread starter Thread starter James T.
  • Start date Start date
J

James T.

Hi,

I am running Access 2000.

Using this code to autopop to a form. The problem is that
the form has 5 places for the same name, 8 places for the
same address etc. Is there a way to autopop from on column
of the combo box to multiple fields on a form?

Private Sub Combo161_AfterUpdate()

ApplicantName = Combo161.Column(1)
CoApplicantName = Combo161.Column(2)
PropertyAddress = Combo161.Column(3)
ZipCode = Combo161.Column(4)
APPSNN = Combo161.Column(5)
CoAppSSN = Combo161.Column(6)
Contractor20 = Combo161.Column(7)

End Sub

Thanks for any help.

James
 
Hi James,
Have you tried: ApplicantName1 = Combo161.Column(1)
ApplicantName2 = Combo161.Column(1)
...etc...

The Easy Day
 
Yes, and that works, but with over 40+ fields (this is a
HUD contract, need I say more ;-) ) I was looking for a
faster way then writing all of that code.

Thanks,
James
 
Try this:

Add a value to the tag of each field. This value should correspond to the
column number on the combo box.

The try this:

Private Sub Cbo1_AfterUpdate()
On Error Resume Next
Dim ctl As Control
Dim ctlNo As Long

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
ctlNo = ctl.Tag
ctl.Value = cbo1.Column(ctlNo)
End If
Next ctl
Me.Requery
End Sub

If you have multiple combos, and fields on the same forms then just add the
name of the combo to the tag and parse it out.

Private Sub Cbo1_AfterUpdate()
On Error Resume Next
Dim ctl As Control
dim strTag as string
dim cboName as string
Dim ctlNo As Long

' this works for tags like cbo001-1 where cbo001 is the combo box where the
value comes from and 1 is the column that holds the value

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
strTag = ctl.Tag
cboName = left(strTag,6)
ctlNo = right(strtag,len(strtag)-7)
if me.ActiveControl.name = cboName then
ctl.Value = me.controls(cboname).Column(ctlNo)
end if
End If
Next ctl
Me.Requery
End Sub

Rodrigo.
 
Back
Top