VBA in Access, reading tables in forms

[John]

I could really use some help. Any assistance would be greatly
appreciated. I don't know if this is possible, but here goes:

I have an Access 2000 MDB database with 20+ tables. One of my forms
is primarily used for one table. Within this form, I have a need for
accessing information in an unrelated table using VBA. All I need to
do is pinpoint a specific record, using the primay key, and then store
the value of one field in a variable.

Is this possible?, or am I chasing my tail, while at the same time
beating my head against the wall?

Please Help!!!

John

 

[Bill James]

This is not only possible... but done all the time!

You are probably using bound controls of one type or
another on your form. Right? All you have to do place
an unbound control (textbox?) on your form. Name the
control and reference it to assign it a value on some
event such as a user selecting the associated record.

e.g. [MyControl] = SomeValue

 

[Gerard Sayers]

You can use recordsets in VBA code but I find the simpleist way is to use a
combo box that references your other table, the bound column would be the
forign key on your form which would match the primary key of the other
table. Select out the primary key and the additional field value you want in
the combo box record source, set the number of columns to 2, then in VBA
code on the afterupdate event
reference the second column, column(1) in the combobox. i.e.

Private Sub cboField_AfterUpdate()
myValue = cboField.column(1)
End Sub

Column 0 is the primary key value and potentially the bound value.

Good luck, I hope this is clear enough.