search a table using two fields as the key

  • Thread starter Thread starter Karen Skipper
  • Start date Start date
K

Karen Skipper

I am working on the design of a new database that will be used to track
shipment of parts with serial numbers. One problem is that different types
of parts can have the exact same serial number. This leaves me with the
problem of not being able to use the serial number as the search key, but
having to use a combination of the part number and the serial number. I can
do this with a query with [Enter the part number] and [enter the serial
number] as criteria but...

I want to have a form for data entry where the clerk would type in the part
number and the serial number and the program would search the table for that
combination and display that record on the screen. If there isn't a find, I
want to append a blank record and allow data entry.

Any ideas? I'm stumped.

(I'm using Access 2002 on a Win 2000 pro computer and I'm self taught in
programming.)

Karen Skipper
 
Put two textboxes (unbound) in the form's header. Also put a command button
in the form's header.

Let the user type a part number in one textbox (you also could use a combo
box or list box if you wish), and the serial number in the other textbox.
Set the command button's caption to "SEARCH".

The query that you've already set up (with the parameters for [Enter the
part number], etc.) needs to be changed slightly. Replace
[Enter the part number]
with
Forms!FormName!TextBox1Name

Replace
[Enter the serial number]
with
Forms!FormName!TextBox2Name

(Replace my generic names with your actual names).

Bind the form's recordsource to this query.

Put this code in the OnClick event of the command button:

Private Sub cmdButtonSearch_Click()
Me.Requery
If Me.RecordsetClone.RecordCount = 0 Then _
Me.Recordset.AddNew
End Sub
 
This is working great !!!

I've got this to work and am working on fine tuning the form now.

Thanks Ken.

Karen


Ken Snell said:
Put two textboxes (unbound) in the form's header. Also put a command button
in the form's header.

Let the user type a part number in one textbox (you also could use a combo
box or list box if you wish), and the serial number in the other textbox.
Set the command button's caption to "SEARCH".

The query that you've already set up (with the parameters for [Enter the
part number], etc.) needs to be changed slightly. Replace
[Enter the part number]
with
Forms!FormName!TextBox1Name

Replace
[Enter the serial number]
with
Forms!FormName!TextBox2Name

(Replace my generic names with your actual names).

Bind the form's recordsource to this query.

Put this code in the OnClick event of the command button:

Private Sub cmdButtonSearch_Click()
Me.Requery
If Me.RecordsetClone.RecordCount = 0 Then _
Me.Recordset.AddNew
End Sub


--
Ken Snell
<MS ACCESS MVP>

Karen Skipper said:
I am working on the design of a new database that will be used to track
shipment of parts with serial numbers. One problem is that different types
of parts can have the exact same serial number. This leaves me with the
problem of not being able to use the serial number as the search key, but
having to use a combination of the part number and the serial number. I can
do this with a query with [Enter the part number] and [enter the serial
number] as criteria but...

I want to have a form for data entry where the clerk would type in the part
number and the serial number and the program would search the table for that
combination and display that record on the screen. If there isn't a
Click to expand...
find,
I
want to append a blank record and allow data entry.

Any ideas? I'm stumped.

(I'm using Access 2002 on a Win 2000 pro computer and I'm self taught in
programming.)

Karen Skipper
Click to expand...
Click to expand...
 
You're welcome.

Karen Skipper said:
This is working great !!!

I've got this to work and am working on fine tuning the form now.

Thanks Ken.

Karen


Ken Snell said:
Put two textboxes (unbound) in the form's header. Also put a command button
in the form's header.

Let the user type a part number in one textbox (you also could use a combo
box or list box if you wish), and the serial number in the other textbox.
Set the command button's caption to "SEARCH".

The query that you've already set up (with the parameters for [Enter the
part number], etc.) needs to be changed slightly. Replace
[Enter the part number]
with
Forms!FormName!TextBox1Name

Replace
[Enter the serial number]
with
Forms!FormName!TextBox2Name

(Replace my generic names with your actual names).

Bind the form's recordsource to this query.

Put this code in the OnClick event of the command button:

Private Sub cmdButtonSearch_Click()
Me.Requery
If Me.RecordsetClone.RecordCount = 0 Then _
Me.Recordset.AddNew
End Sub


--
Ken Snell
<MS ACCESS MVP>

Karen Skipper said:
I am working on the design of a new database that will be used to track
shipment of parts with serial numbers. One problem is that different types
of parts can have the exact same serial number. This leaves me with the
problem of not being able to use the serial number as the search key, but
having to use a combination of the part number and the serial number.
Click to expand...
I
can
do this with a query with [Enter the part number] and [enter the serial
number] as criteria but...

I want to have a form for data entry where the clerk would type in the part
number and the serial number and the program would search the table
Click to expand...
for
that
combination and display that record on the screen. If there isn't a
Click to expand...
find,
I
want to append a blank record and allow data entry.

Any ideas? I'm stumped.

(I'm using Access 2002 on a Win 2000 pro computer and I'm self taught in
programming.)

Karen Skipper
Click to expand...
Click to expand...
Click to expand...
 
Back
Top