Help with Syntax error in string in query expression (1 Viewer)

[lookforsmt]

HI!
i am trying to open another form from main form on double click but getting below error

Syntax error in string in query expression 'MICR_ndt = 'xxxxx'

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_1stResults_DblClick
    Dim IDnr As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    IDnr = Me.MICR_m
    stDocName = "frm_Correction"
    stLinkCriteria = "MICR_ndt = '" & IDnr
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_1stResults_DblClick:
    Exit Sub
Err_1stResults_DblClick:
    MsgBox Err.Description
    Resume Exit_1stResults_DblClick
End Sub

i am not sure if i have put the correct quotation mark
thanks

 

[lookforsmt]

Attaching the error snapshot

 

[vba_php]

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_1stResults_DblClick
    Dim IDnr As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    IDnr = Me.MICR_m
    stDocName = "frm_Correction"
    stLinkCriteria = "MICR_ndt = '" & IDnr
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_1stResults_DblClick:
    Exit Sub
Err_1stResults_DblClick:
    MsgBox Err.Description
    Resume Exit_1stResults_DblClick
End Sub

use:

    stLinkCriteria = IDnr

and it should work.

correction, try:

    stLinkCriteria = "[MICR_ndt] = '" & IDnr & "'"

 

[lookforsmt]

message edited
Thanks, it opens the form but it gives me popup msg
"Enter Parameter Value" MICR_ndt

and then opens the form, with no details.

 

[vba_php]

Thanks, it opens the form but it gives me all the details instead of that particular detail.

did you see my correction I added a few minutes I originally posted an answer? I misread ur question initially.

 

[lookforsmt]

yes, i updated your below code and after that it gave me message

stLinkCriteria = "[MICR_ndt] = '" & IDnr & "'"

 

[vba_php]

yes, i updated your below code and after that it gave me message

stLinkCriteria = "[MICR_ndt] = '" & IDnr & "'"

really!? wow...maybe this is a versioning issue. try:

"MICR_ndt = '" & IDnr & "'"

are you sure "Me.MICR_m" is a string?

 

[lookforsmt]

i tried the other code but still same result.
i have attached my db

 

[lookforsmt]

If you can have a look at it. I am not sure if the string is Me.MICR_m

 

[vba_php]

in your popup form, your field is named "MICR" and has a source name the same. your criteria is checking for a field named "MICRid"!

change it and see if it works. perhaps you've been working a little too long today?

 

[lookforsmt]

i have changed the string to MICR
Now it opens the blank form without any error message.

 

[isladogs]

I haven't looked at your app.
However, just for info you could remove several lines of superfluous code
If its a text field then

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_1stResults_DblClick

    DoCmd.OpenForm "frm_Correction", , , "[MICR_ndt] = '" & Me.MICR_m & "'"

Exit_1stResults_DblClick:
    Exit Sub
Err_1stResults_DblClick:
    MsgBox Err.Description
    Resume Exit_1stResults_DblClick
End Sub

or if its a number field

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_1stResults_DblClick

    DoCmd.OpenForm "frm_Correction", , , "[MICR_ndt] = " & Me.MICR_m

Exit_1stResults_DblClick:
    Exit Sub
Err_1stResults_DblClick:
    MsgBox Err.Description
    Resume Exit_1stResults_DblClick
End Sub

 

[vba_php]

that's because there are no records that match your criteria field. I just tried it and the main form you have, in the first record, your criteria field is holding a value that is not found in table "tbl_Master_MICR". perhaps your data setup is wrong?

you're trying to use the hidden field as criteria, right?

 

[lookforsmt]

haha, time difference GMT +4 hours.
i did change to MICR same result in my post #11.

i changed to MICRId but gets error "Data Type mismatch in criteria expression"

 

[vba_php]

haha, time difference GMT +4 hours.
i did change to MICR same result in my post #11.

i changed to MICRId but gets error "Data Type mismatch in criteria expression"

take a look at my last post. the code i gave you works. there are no records that match the criteria. I think u need to look at this db a little more closely. it's possible that it's my fault, but when I looked up in the table as to whether there was a value that matched that hidden field, there wasn't. so, from my test, everything works fine.

 

[isladogs]

I've just had a quick look. Perhaps it should be

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_1stResults_DblClick

      DoCmd.OpenForm "frm_Correction", , , "MICRid = " & Me.MICR_m

Exit_1stResults_DblClick:
    Exit Sub
Err_1stResults_DblClick:
    MsgBox Err.Description
    Resume Exit_1stResults_DblClick
End Sub

 

[lookforsmt]

Thanks isladogs, i tried your last code. it gave me blank records.

vba_php, The code provided by you gets me the result. Yes, the first details is not in the table so gives a blank record. Did not realize this.

Thank you both for the quick help.
God bless

 

[isladogs]

Sorry. Posted wrong code. This works

DoCmd.OpenForm "frm_Correction", , , "MICR = '" & Me.lstResults & "'"

 

[lookforsmt]

Thanks isladogs for the updated db.

I just realized, i will not be able to amend the details which don't match as only match items get populated on double click.

Is there a way to bring the unmatch item on double click

 

[lookforsmt]

same way the 1stResult has ChqNos; RoutNos & DebitNos displays the possible match