Auto-populate a form

[Dave]

Is there a way to auto-populate a form using data indexed to a primary key in
a table? That is, when I enter the primary key value in a form, I want the
remaing fields to automatically pull data from a table with that primary key
and populate the remaining fields.

 

[Klatuu]

I would suggest an unbound combo with a row source based on your primary key
field. Then use the After Update event of the comb to locate the record and
make it the current record:

Private Sub MyCombo_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note, the syntax on the FindFirst line assumes [PrimeKeyField] is numeric.
It it is text:
.FindFirst "[PrimeKeyField] = """ & Me.MyCombo & """"

For A Date field
.FindFirst "[PrimeKeyField] = #" & Me.MyCombo & "#"

And, of course, use your field and control names.

 

[Al Campagna]

Dave,
You'll need to capture the key value (ex. CustID = a bound field), but
you don't want to capture the ancillary information. (Name, Address, City,
etc...)
You just want to "display" it on the form.
Once you've captured the CustID, you can always re-derive the ancillary
information... in any subsequent form, query, or report.

A popular method is to use a multi-column combo box (ex. cboCustID) to
select the CustID.
Example cboCustID column setup...
CustID CustName CustAddress
142 Jones 12 Main St

cboCustID Combobox properties...
ControlSource = CustID
No of colums = 3
Column Widths = 0"; 1.5"; 2.0"

What this does is... it allows the user to select a CustID by CustName,
the combo displays CustName, but what's really stored in the CustID field in
the table is... the CustID.
Now, given an "unbound" text control, on that form, with a ControlSource
of...
=cboCustID.Column(2)
will always "display" the CustAddress (not capture it, but just display it).
Combo colums are numbered 0, 1, 2, 3, etc..., so column 2 would be the
CustAddress value.

On my website (below) I have a 97 and 2003 sample file called "Combo
populates multiple fields", that shows how this is done.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Allie]

Can someone explain how I would do this with an input field rather than a
drop-down combo box?

Here is my problem:
I am trying to edit a db that someone else created.

There is one table of data (RUNNERS).
There is one form (BIB LOOKUP).
The goal is that on the form you would type the BIB NO into a field and all
other runner info would populate.

I am not sure how this is accomplished - but it is working.

My issue is that I have a new set of data to load - with different column
names. I have replaced the data into RUNNERS and now I need to adjust the
form to read my new data, but I cannot figure out how this is done.

In the control source of the field which need to be populated, it says:
=[BIB LOOKUP].COLUMN(2)

If BIB LOOKUP is the name of the form itself, so I do not see how that is
reference my table RUNNERS. Obviously the column numbers are different, but
what else needs to be changed to avoid the #NAME? that I am receiving as
output?

Very confused, any input would be greatly GREATLY appreciated!

 

[Allie]

Can anyone explain how I would do this with an input field instead of a combo
box?
Here is my issue:

I am trying to edit a db that someone else created.

There is one table of data (RUNNERS).
There is one form (BIB LOOKUP).
The goal is that on the form you would type the BIB NO into a field and all
other runner info would populate.

I am not sure how this is accomplished - but it is working.

My issue is that I have a new set of data to load - with different column
names. I have replaced the data into RUNNERS and now I need to adjust the
form to read my new data, but I cannot figure out how this is done.

In the control source of the field which need to be populated, it says:
=[BIB LOOKUP].COLUMN(2)

If BIB LOOKUP is the name of the form itself, so I do not see how that is
reference my table RUNNERS. Obviously the column numbers are different, but
what else needs to be changed to avoid the #NAME? that I am receiving as
output?

Very confused, any input would be greatly GREATLY appreciated!