filling form from table lookup

[dtm]

I have a form i am trying to fill based on another text box...

I have a table named devices. I have a text box serialnumber. i want to fill
in the serialnumber and click a button and have it prefill in the customer
name from the table. how can i do this?

thanks.
dm.

 

[John Vinson]

I have a form i am trying to fill based on another text box...

I have a table named devices. I have a text box serialnumber. i want to fill
in the serialnumber and click a button and have it prefill in the customer
name from the table. how can i do this?

thanks.
dm.

If the customer name can be derived by looking it up using the
serialnumber - then it SHOULD be derived by looking up the
serialnumber. Storing it redundantly in a second table is a bad idea;
not only does it waste space, but if someone changes their name, you
now have to track down all the instances.

Can you explain why you feel you need to do so?

John W. Vinson[MVP]

 

[dtm]

Because one table is a linked table from our SQL database and one table is a
log table for loging trouble tickets. We just want to auto fill the data so
we dont have to manually type it in when a customer calls in.

 

[John Vinson]

Because one table is a linked table from our SQL database and one table is a
log table for loging trouble tickets. We just want to auto fill the data so
we dont have to manually type it in when a customer calls in.

I take it the link to SQL is not available when the customer calls in,
then? Ok, this is one case where it may be appropriate.

Use the Form's BeforeUpdate event. Have two textboxes: one with the
lookup, the other bound to the local table. In the Form's BeforeUpdate
event set the value of the bound textbox to the value of the unbound
one.

John W. Vinson[MVP]

 

[dtm]

Can you be a little more detailed?

thanks.

I take it the link to SQL is not available when the customer calls in,
then? Ok, this is one case where it may be appropriate.

Use the Form's BeforeUpdate event. Have two textboxes: one with the
lookup, the other bound to the local table. In the Form's BeforeUpdate
event set the value of the bound textbox to the value of the unbound
one.

John W. Vinson[MVP]

 

[John Vinson]

Can you be a little more detailed?

Sure, if you will do so yourself.

What is the name of the form control wherein you're looking up the SQL
data?

What is that control's Control Source (or how are you "looking up" the
data)?

Where do you want the looked-up value put - is it into a field in the
Form's recordsource query, or elsewhere?


John W. Vinson[MVP]

 

[dtm]

I have a linked table "devices" with the exported sql data in it. I have
another table "rmalog" with the trouble ticket info in it.

I have a serialnumber box with the control source of rmalog.serial number.
and a customer box with the control source of rmalog.customer

i want to be able to enter data into the serialnumber box and after that,
look that number up in the "devices" table and fill in the customer box with
the customer name. I would like this to be done with a command button.
"cmdlookup" This way when the submit button is clicked, all the data is
entered into the "rmalog" table.

I previously had this done by making the customer name have a query behind
it "SELECT customer
FROM devices
WHERE serialnumber=serialnumber"
and had this activated on after update.

THe problem was that i had a refresh on form load and this made the form way
to slow on startup. (i had several lookups, ie: customer address, name,
location, etc.)

thanks.