Can I make a form goto a specific record using SQL?

[Regnab]

The record's primary key is the report number. The form in which data
is entered or modified has this Report Number at the top. I would like
to know if I could use SQL in the VB code so that on Exit of the
'Report Number' text box, the code can scan through to see if that
report number exists and if so, jump to it. I was looking through and
thought maybe docmd.GoToRecord, but that seem to only move a specific
number of records - not via parameter.

Thanks for your help,

Cheers

Reg

[Larry Linson]

"Regnab" wrote

The record's primary key is the report number. The form in which data
is entered or modified has this Report Number at the top. I would like
to know if I could use SQL in the VB code so that on Exit of the
'Report Number' text box, the code can scan through to see if that
report number exists and if so, jump to it. I was looking through and
thought maybe docmd.GoToRecord, but that seem to only move a specific
number of records - not via parameter.

Two ways to accomplish this:

(1) If you have the entire recordset open as the RecordSource of the Form,
you can execute VBA code like (the following aircode)

Me.RecordsetClone.FindFirst "[ReportNumber] = " & Me.txtReportNumber

if ReportNumber is a numeric field, or if it is alphanumeric,

Me.RecordsetClone.FindFirst "[ReportNumber] = """ & Me.txtReportNumber &
""""

followed by

Me.Bookmark = Me.RecordsetClone.Bookmark

(2) you could take the value in the Report Number text box, build SQL, and
replace the Form's RecordSource.

I would suggest that, instead of a TextBox, you use a ComboBox... you can
limit to list, it will scroll as the user types, and they will not be able
to make a mistake and type in the wrong value. Once they get close, they
could stop typing and click to choose the proper ReportNumber from the
ComboBox' drop-down list.

Larry Linson
Microsoft Access MVP