Go to a record

[Giordano]

I have a form (frmSchools) with a command button (cmdFind) on it which
when clicked opens up a dialog box (frmLookUpSchool - a 3 column
datasheet with all the records from the main table listed) which allows
me to quickly scroll through the records, select the one I want. I
highlight the desired row, press OK, the dialog box closes, and I find
myself positioned at the record I selected.

Here is the simple code I am using:

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

Dim stDocname As String
Dim stLinkCriteria As String

stDocname = "frmSchools"

stLinkCriteria = "[fldSchoolName]=" & "'" & Me![fldSchoolName] &
"'"
DoCmd.OpenForm stDocname, , , stLinkCriteria
DoCmd.Close acForm, "frmLookUpSchool"

The problem is that the main form is being filtered with only the one
record showing. I want all the records to be available. How do I
achieve this?

 

[Al Campagna]

Giordano,
If you already have a form (form view) that displays the individual schools in your
School recordset, there's really no reason to open frmSchools (datasheet view), select a
record, and then go back to the form view. Your method is filtering the recordset, so
only one school is returned.
On my website (below) I have a sample file (V97 and/or V2003) called Quick Find Combo.
It shows how to place a combo box on your form view form, allow the user to select a
record by name, and "Find" that record, by the key field value in the recordset. A Find
only goes to the record desired, and does not filter out other records.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Giordano]

Thanks Al,

Your solution is a much neater.


G