Move to a record on a continuous form based on a value of a column 2

[BSman]

I have a continuous form with some combo boxes in the footer where the user can enter search criteria. For one of my applications, the users have requested that instead of limiting the record(s) displayed on the form to those that match the user specified value, they would like the display to just move to the record where the value matches the value they specified. That way they could use that as a starting point to move up and down to nearby records.

I can't figure out how to do this (in VBA code). The DoCmd.GotoRecord will, in effect, work the same way as if you set the filter property for the form and requery the form (which then only shows the records that match the criteria). I also found DoCmdFindRecord, but that doesn't seem to do anything.

Can you give me suggestions about how to do this?

 

[PHV]

Have a look at the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the Form/DAO.Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GShen]

Here is some code for you to edit to what you need to do.

Dim rst As ADODB.Recordset

Set rst = Me.RecordsetClone

'May need to wait for the recordset to be populated
Do While (rst.State And adStateConnecting) Or _
(rst.State And adStateFetching)
DoEvents
Loop

rst.Find "FieldOnContinuousForm = '" & Me.SearchField & "'"

If rst.EOF Then
MsgBox ("SearchFieldName " & Me.SearchField & " is not on this screen, try again"), vbOKOnly
Me.SearchField = Null
Me.SearchField.SetFocus
Else
DoCmd.GoToRecord acForm, "FormYouAreOn", acGoTo, rst.Bookmark
Me.FieldOnFormYouWantToPointTo.SetFocus
End If

Set rst = Nothing

Remember when... everything worked and there was a reason for it?

 

[BSman]

GShen,

The form's data source is based on a table (or query in some cases). I looked up recordsetclone and, as I understand it, rst should end up with a recordset that matches the data in the form. But when I tried to run the same code, at the statement

rst = me.recordsetclone

I received an "invalid argument" message.

I'm using Access 2000.

 

[PHV]

DAO.Recordset

 

[GShen]

Sorry,
I was assuming ADP, my bad. Try doing as PHV said on how to declare the recordset using DAO. I am using ACCESS 2000 as well with SQL backend.


Remember when... everything worked and there was a reason for it?

 

[BSman]

OK, I used Set rst as DAO.Recordset and was able to go further down the code.

But I keep getting wrong data type error on the line

DoCmd.GoToRecord acDataForm, "FormYouAreOn", acGoTo, rst.Bookmark

(I corrected the second set of text to say acDataForm.)

This refers, I believe, to rst.Bookmark.

 

[GShen]

You have to enter in the FORM NAME that you are using in the quotes. I used a generic FormYouAreOn. I used all Generic names in my example As Follows:

me.SearchField
FormYouAreOn
SearchFieldName
FieldOnContinuousForm


Remember when... everything worked and there was a reason for it?

 

[PHV]

Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "FieldOnContinuousForm = '" & Me.SearchField & "'"
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmak
Set rst = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GShen]

PHV,
I just saw you posted rst as ADODB.Recordset. Which is what I had originally. Which one is it for BSMAN. I don't work much with MDB's. BSMAN, this is an MDB correct?

Remember when... everything worked and there was a reason for it?

 

[PHV]

OOps, copy'n'paste too quickly. Sorry.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "FieldOnContinuousForm = '" & Me.SearchField & "'"
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmak
Set rst = Nothing

Check your references:
menu Tools -> References ... -> tick Microsoft DAO 3.x Reference Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BSman]

I did substitute my form's name in the statement as well as substituting my control name and field name. I certainly understood that the statements used generic names.

I realize that the bookmark should be the record that matches the value I'm looking for, but the problem is that the GoToRecord command doesn't work because it doesn't seem to like using me.bookmark to mark the record to go to.

 

[GShen]

BSMAN,
This is an MDB, correct? I need to be sure of this. I will test tonight and report back to you.

Remember when... everything worked and there was a reason for it?

 

[PHV]

BSMan, have you tried my suggestion ? (I never talked about GoToRecord ...)
If the field is defined as numeric, get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptpaton]

BSMan,

PHV's code shoud work perfectly. Here's almost the exact same code I use to perform exactly what you're talking about. I have buttons on my form for first, next, and prev. All you have to do is change the .FindFirst statement below to either .FindNext or .FindPrev

Also, pay close attention to the quotation marks (double and single) around the search string...they're key. Also, per PHV's advice, ensure that the DAO reference library is loaded, and unless you declare a DAO recordset (Dim rst as DAO.Recordset), make sure the DAO reference is above the ADODB reference in the list.

Dim Search As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
Search= "[YourField] = '" & Me.cboYourComboBox & "'"
With rst
.MoveFirst
.FindFirst Search
Me.Bookmark = rst.Bookmark
If .NoMatch Then
MsgBox "No Match"
End If
End With

Good luck!


-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[GShen]

BSMAN,
I tried PHV's code last night at my house and it works. Keep in mind like he said that you need the REFERENCE added in. I used 3.6 DAO OBJECTS. Also, keep in mind if you looking for exact matches or wild card matches. You can do both.

Remember when... everything worked and there was a reason for it?

 

[BSman]

Thanks. Ptpaton's code worked perfectly with no changes except that I called it strSearch, rather than Search (and of course changed the references to the column and my control). I appreciate all of your help, but have given him a thank you because his code worked immediately and the others didn't, even with tweaking.

Bob