Thread: how to enter serial number and lookup serial number at same time

I have a form to enter serial number and service tag number. user uses scanner to enter data into form. I also have a form with a lookup box to search if serial number is in a table for trashed parts. so i have two tbl1 and tbl2.
now i am being asked to speed up operation-
is there a way to enter serial number one time and do both functions.

if serial number is in tbl2 then print report and still enter number in tbl1
if serial number is not in tbl2 - show message box "part not found" and just enter number in tbl1 ( no report)

or is there another way
thank you

The AfterUpdate event of the control for the SerialNumber will easily do both operations if you wish. You could also use the BeforeUpdate event of the FORM, if that is the way you wanted to go.

thank you- still wondering how to code- if serial number is in tbl2 - print report- and show message box "item is in tbl2" then run an insert into line to input into table1- this may be too big a question - thank you

Is your form that contains the ComboBox bound to one of the tables you want to update?

actually to complicate things:
now i have both functions on one form
top of form is button - click on it and it asks user to input serial number
( this action searches tbl2- to see if serial number is listed- right now it just previews report- so if serial number is there- it shows all records that it is listed in- if not- the report is empty)
half way down form is only combo box on form- it is bound to nothing- and has a list of service tag numbers)
user select list number- it is automatically entered in serial tag number field- and user then proceeds to scan in serial numbers -and check a box if the serial number was found in tbl2.
the service tag number and serial number and checkbox are entered into tbl1
I am trying to enter the serial number only once instead of 2times like now
thank you

Why do you have the serial number in more than one table? It sounds like you may have a normalization issue as well.

yes the serial number is both tables- tbl1 is table to input numbers- tbl2 is a table that comes to me that has list of bad parts- I do not input this data- this data is updated everyday elsewhere- -
first process- enter a serial number and see if its a bad part
second process- enter parts in table -
user doing the project does not know if part is bad
-
but i need to speed up process and cannot have user entering serial number two times
thank you

Originally Posted by arloguth

actually to complicate things:
now i have both functions on one form
top of form is button - click on it and it asks user to input serial number
( this action searches tbl2- to see if serial number is listed- right now it just previews report- so if serial number is there- it shows all records that it is listed in- if not- the report is empty)
half way down form is only combo box on form- it is bound to nothing- and has a list of service tag numbers)
user select list number- it is automatically entered in serial tag number field- and user then proceeds to scan in serial numbers -and check a box if the serial number was found in tbl2.
the service tag number and serial number and checkbox are entered into tbl1
I am trying to enter the serial number only once instead of 2times like now
thank you

All of this can be done automatically when you scan in the Serial Number. It would be done in the AfterUpdate event of the control used to scan in the SN.

We will need some additional information to accomplish the requested tasks. Is the RecordSource of your form the table/query of tbl1? Are some of your controls bound to fields in this table (tbl1)? Is there any reason to display the report if we go ahead and check the "Found in other table" checkbox? Does the ComboBox pick a "service tag number" and then remove it from the list? You could even Compact and Repair your db and zip it up and attach it to a post so we could have most of the answers we need. Just a thought. Remove any sensitive data first, of course.

i am hoping i attached file correctly- your right might be easier to show you-
I need report printed - because user needs hard copy
if I did not attach correctly can you let me know
thank you