recordset.find problem

[jpl458]

I've looked this up and still can't get it right. Have a recordset that is in sequential order by date and I need to get 30 account numbers into 30 different text boxes on a form. Here is the start of the code:

'Fill the recordset
rscardnos.Open sel_card_nos
'Find the start of the card sequence in the record set
rscardnos.Find (Me.tbstartcardno)

It bombs on the rscardnos.Find (Me.tbstartcardno)


rscardnos is the recordset
Me.tbstartcardno is the first text box on the form.

Just need to know whats wrong with the Find. (there is more code after this, but I've test that)


Thanks in advance


jpl

 

[jpl458]

Here is another attempt using DoCmd to locate a record in the record set, but there is something wromg with that as well

'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rscardnos As New ADODB.Recordset
rscardnos.ActiveConnection = cnndbo
rscardnos.CursorType = adOpenDynamic
'Fill the recordset
rscardnos.Open sel_card_nos
'Find the start of the card sequence in the record set

Dim coffst As Integer
coffst = 1
DoCmd.FindRecord(Me.tbstartcardno, acEntire, True, acSearchAll, , acCurrent, True}
For coffst = 1 To 30
Me("tbacno" & CStr(coffst)) = rscardnos(0)
DoCmd.FindNext
Next coffst

The line Me("tbacno" & CStr(coffst)) = rscardnos(0) moves 30 account numbers to 30 Text Boxes on a form in the order they were created.

Hope this is enough info.

Thanks jpl

 

[Golom]

What is in [red]Me.tbstartcardno[/red]?

The argument to Find needs to be something like

rscardnos.Find [red]"[SomeField] = 'ABC'"[/red]

If Me.tbstartcardno just contained (for example) ABC then the find won't work.

 

[jpl458]

Golom, in your example I am assuming that [SomeField] is a field name in the recordset. Can the 'ABC' be contained in a variable or a textbox, and how do refer to it in the rst.find statement?

thanks for the help

jpl

 

[Golom]

... [SomeField] is a field name in the recordset

Correct. You would need something like

rscardnos.Find [red]"[SomeField] = '" & Me.tbstartcardno & "'"[/red]

Assuming that [SomeField] is a text field. If not, lose the single quotes.

 

[jpl458]

Still having problems, with the find. Have tried various ways of entering the first part in (the {SomeField part):

rscardnos.Find "[SomeField] = '" & Me.tbstartcardno & "'"

Here is the SQL that fills the recordset

'SELECT dbo_Master_Accounts.Master_ID, dbo_Master_Accounts.Date_Created
'FROM dbo_Master_Accounts
'WHERE (((dbo_Master_Accounts.Date_Created) > #1/1/2010#))
'ORDER BY dbo_Master_Accounts.Date_Created

I just can't figure out how to enter the field Maser_ID in the recordset.find

rscardnos.Find "['rscardnos(0)'] = '" & Me.tbstartcardno & "'"

rscardnos.Find "['Master_ID'] = '" & Me.tbstartcardno & "'"

These and others did not work.
Thanks again

jpl

 

[Golom]

Without the quotes around Master_ID

rscardnos.Find "[Master_ID] = '" & Me.tbstartcardno & "'"

 

[jpl458]

Thanks Golom, it worked like a champ.

Jpl