Code works in stand alone form not when form is used as a subform.

[smcfadden]

Hello,

I hope someone can tell me what I am doing wrong.

I have two forms. The first one, which I later put into the other form as an unrelated subform is called UPCSubfrm. It’s record source is Tbl: Product Information. This form has a bound text field for the ProductID and an Unbound text field called ScannedUPC into which I scan in the UPC for various products.
For the After Update event I use the code:

Private Sub ScannedUPC_AfterUpdate()
    If (ScannedUPC & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[SKU]=""" & ScannedUPC & """"
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & ScannedUPC & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    ScannedUPC = Null
End Sub

To return the ProductID related to the UPC that is scanned in.

This works great. I scan in the UPC and the ProductID is displayed.

If I then put this form, UPCLookup, in the second form (as a subform) with no attached master/child relationship (I will send the ProductID to a field in the other form later) and enter a scanned UPC (SKU, I got these terms mixed up at the start), it no longer works. It gives me the “no such record message”.

In short, why does the code work in the form when it is used separately but fails to work when it is used as a subform?

Thank you
Window 7
Access 2007

[zmbd]

Post 1 Code-Block Lines 3 thru 5 currently read:

  Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[SKU]=""" & ScannedUPC & """"
 

The error is in line 5, why it worked as a stand alone form is interesting

For troubleshooting please change as follows

 
   Dim rs As DAO.Recordset
   Dim zstrSQL As String
   '
   Set rs = Me.RecordsetClone
   '
   zstrSQL = "[SKU]=""" & me.ScannedUPC & """"
   '
   Debug.Print "zstrSQL= " & zstrSQL
   '
   rs.FindFirst zstrSQL
 

Load and run your form as normal.
Once done, press <ctrl><g>

The immediates window will open in the VBE.
It should contain the resolved string. Please check how this resolves both as a stand alone and as a subform.

if you would, please post back if this fixed

The debug.print is only there for debuging :)

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving (as I did above with the debug.print); thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

[smcfadden]

zmbd Thank you for your insight. I cannot work on it at the moment but will let you know how it comes out. Sorry to poke at your pet peeve :-)

[zmbd]

MS poked it 1st (^_^)
You'd be surprised how many times the issue is in the string and yet impossible to check with the way they teach to use the functions/calls in their examples.

Just me I suppose (^_^)

bol

[smcfadden]

I could not wait.

I made the suggested code changes in the stand alone form and t worked fine... gave me the UPC. When I use it as a subform it still gves me the

"Sorry, no such record '024000525462' was found."

The immediate window has the same result for both as a form and as a subform:

zstrSQL= [SKU]="024000525462"

Which is the UPC. I would have thought these would be be different.