By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,962 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,962 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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
Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.