Searching in a SQL DB using a form

[Ana]

Hi,

I would like to do search in a SQL2k DB of a license plate number using a
command button in an Access 2k form field. The latter is already linked to
the DB.

The license number is composed (here in Spain) by letter and numbers
(ABC1234). However, a numeric 'LIKE 1234' search will satisfy my needs.

HOWTO accomplish it?

TIA

Ana

 

[SteveS]

Hi,

I would like to do search in a SQL2k DB of a license plate number using a
command button in an Access 2k form field. The latter is already linked to
the DB.

The license number is composed (here in Spain) by letter and numbers
(ABC1234). However, a numeric 'LIKE 1234' search will satisfy my needs.

HOWTO accomplish it?

TIA

Ana

Hi, Ana

Try this:

In the form header create a text box (I named it txtWhatLicense) and a button
(I named it btnFilter).

If "License" is not the name of the field that holds the License plate number,
you will need to change "[License]" to the name of your field.

The code behind the button is:

'***begin code ***
Private Sub btnFilter_Click()
On Error GoTo Err_btnFilter_Click

DoCmd.ApplyFilter , "[License] Like '*" & Me.txtWhatLicense & "*'"

Exit_btnFilter_Click:
Exit Sub

Err_btnFilter_Click:
MsgBox Err.Description
Resume Exit_btnFilter_Click

End Sub
'***end code ***

You can enter any part or all of the License number, then click the button.

If you leave the text box empty, all records are returned.

HTH

 

[Ana]

Thank you.
But where will the output go?

Hi,

I would like to do search in a SQL2k DB of a license plate number using a
command button in an Access 2k form field. The latter is already linked
to the DB.

The license number is composed (here in Spain) by letter and numbers
(ABC1234). However, a numeric 'LIKE 1234' search will satisfy my needs.

HOWTO accomplish it?

TIA

Ana

Hi, Ana

Try this:

In the form header create a text box (I named it txtWhatLicense) and a
button (I named it btnFilter).

If "License" is not the name of the field that holds the License plate
number, you will need to change "[License]" to the name of your field.

The code behind the button is:

'***begin code ***
Private Sub btnFilter_Click()
On Error GoTo Err_btnFilter_Click

DoCmd.ApplyFilter , "[License] Like '*" & Me.txtWhatLicense & "*'"

Exit_btnFilter_Click:
Exit Sub

Err_btnFilter_Click:
MsgBox Err.Description
Resume Exit_btnFilter_Click

End Sub
'***end code ***

You can enter any part or all of the License number, then click the
button.

If you leave the text box empty, all records are returned.

HTH

 

[SteveS]

Hi,

I would like to do search in a SQL2k DB of a license plate number using a
command button in an Access 2k form field. The latter is already linked
to the DB.

The license number is composed (here in Spain) by letter and numbers
(ABC1234). However, a numeric 'LIKE 1234' search will satisfy my needs.

HOWTO accomplish it?

TIA

Ana

Hi, Ana

Try this:

In the form header create a text box (I named it txtWhatLicense) and a
button (I named it btnFilter).

If "License" is not the name of the field that holds the License plate
number, you will need to change "[License]" to the name of your field.

The code behind the button is:

'***begin code ***
Private Sub btnFilter_Click()
On Error GoTo Err_btnFilter_Click

DoCmd.ApplyFilter , "[License] Like '*" & Me.txtWhatLicense & "*'"

Exit_btnFilter_Click:
Exit Sub

Err_btnFilter_Click:
MsgBox Err.Description
Resume Exit_btnFilter_Click

End Sub
'***end code ***

You can enter any part or all of the License number, then click the
button.

If you leave the text box empty, all records are returned.

HTH

Thank you.
But where will the output go?

You said the form was bound to a table (or query). You should have controls
(bound to fields) in the Detail section of the form.

The code filters the table/query; only those records that match (in whole or
part) the entry in the text box will be displayed in the detail section of the
form.