Using a passed variable to change the viewed record in a form

[Wally0]

There are several disscussion on using cobo boxes to change information viewed in a form. In my case, I'm using a pop-up form (with a cobo box) to retrieve a record number. I can successfully pass this value to the form. However, I'm stumped on how to change the current record to a different record based on the "record number." Here are some of the methods that I've tried:

Me.Filter = "[RecordNumber] = varIndex'"
Me.FilterOn = True

also

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT * FROM [tblMain] WHERE RecordNumber = " & varIndex & ""

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

This has to be something simple -- I'm just missing what it is. Thanks for any assistance

Wally

 

[nickjar2]

I can't think of the code off hand. But look up the recordset.clone in the help. I think that's how I normally do it. Something like:

Nick

 

[nickjar2]

I can't think of the code off hand. But look up the recordset.clone in the help. I think that's how I normally do it.

Nick

 

[Wally0]

Thanks Nick for the tip.. I tried using the RecordSource property and modified the code a little and it worked:

Dim strSQL As String

strSQL = "SELECT * FROM [tblMain] WHERE RecordNumber = " & varIndex & ""

Dim strNewRecord As String
strNewRecord = (strSQL)
Me.RecordSource = strNewRecord



Wally

 

[nickjar2]

Wally,

That's good. Another way is like:
dim rs as object
dim sRef as string

sRef = me.openargs

Set rs = Me.Recordset.Clone
rs.FindFirst "[ref_number] = '" & sRef & "'"
Me.Bookmark = rs.Bookmark

Nick