help with rsClone

[ramblinwreck]

I have a command button that loads a form and I want the loaded form to display a certain record, based on the variable passed to the form load procedure (called strOrderNumberFromParts).

I cannot get the rsclone code below to work resulting in a run time error.

Appreciate the help.

Private Sub Form_Load()
On Error GoTo Error_Handler:
'Dim db As Database
'Dim rst As DAO.Recordset
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone
With rsClone
    .MoveFirst
    .FindFirst "[chrordernumberfromparts] =" & strOrderNumberFromParts
    If Not .NoMatch Then
        Me.Bookmark = rsClone.Bookmark
    Else
        .AddNew
        .MoveLast
    End If
    Close
End With
exit_procedure:
Close
Set rst = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
On Error Resume Next
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
Buttons:=vbCritical, title:="Motorhome Maintenance Records"
Resume exit_procedure
Resume
End Sub

 

[RuralGuy]

How are you passing the data to this procedure? I see typo's in this code. Is it a direct copy and does it compile?

 

[ramblinwreck]

How are you passing the data to this procedure? I see typo's in this code. Is it a direct copy and does it compile?

yes. direct copy. does compile.

The only data passed is the value of the variable strOrderNumberFromParts


When code execution gets to this line:

.FindFirst "[chrordernumberfromparts] =" & strOrderNumberFromParts

A run time error results.

 

[RuralGuy]

Is strOrderNumberFromParts a global string variable or do you pass it in the OpenArgs argument? I would question those Close statements.

 

[ramblinwreck]

It's a global variable.

I changed this code :

.FindFirst "[chrordernumberfromparts] =" & strOrderNumberFromParts

to this:

.FindFirst "[chrordernumberfromparts] =""" & strOrderNumberFromParts & """"

and it works. I don't understand enough about quotation marks to know why it makes a difference but it does.

thanks for your help

 

[RuralGuy]

Glad you got it resolved. Strings need to be enclosed in quotes in VBA. Key the same into the immediate window preceded with a question mark and see what comes back.
?"[chrordernumberfromparts] =""" & "strOrderNumberFromParts" & """"

 

[ramblinwreck]

Interestingly, this is what I get when I paste into the immediate window:

?"[chrordernumberfromparts] =""" & strOrderNumberFromParts"""
[chrordernumberfromparts] ="s1543cg31119865"
?"[chrordernumberfromparts] =" & strOrderNumberFromParts
[chrordernumberfromparts] =s1543cg31119865

I can see the obvious difference on the right side of the equal sign. What I can't understand is why three sets of double quotes are on either side of the string variable.

Logically, it would seem that the first of the three double quotes immediately following the equal sign would close the quotation around [chrordernumberfromparts] but testing in the immediate window does not confirm.

Can you help me understand the significance of the three double quotes?

thanks.

 

[RuralGuy]

The first quote terminates the string. The second quote is an escape character that makes the third quote part of the string.

 

[RuralGuy]

There were actually 4 double quotes after the string variable to make it leave 1 of them in the string.

 

[ramblinwreck]

There were actually 4 double quotes after the string variable to make it leave 1 of them in the string.

I know. I played around with that as well in the immediate window. It didn't seem to have any effect and I took it out of the code (at least in the immediate window code).