472,121 Members | 1,511 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,121 software developers and data experts.

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


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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScannedUPC_AfterUpdate()
  2.     If (ScannedUPC & vbNullString) = vbNullString Then Exit Sub
  3.     Dim rs As DAO.Recordset
  4.     Set rs = Me.RecordsetClone
  5.     rs.FindFirst "[SKU]=""" & ScannedUPC & """"
  6.     If rs.NoMatch Then
  7.         MsgBox "Sorry, no such record '" & ScannedUPC & "' was found.", _
  8.                vbOKOnly + vbInformation
  9.     Else
  10.         Me.Recordset.Bookmark = rs.Bookmark
  11.     End If
  12.     rs.Close
  13.     ScannedUPC = Null
  14. 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
Dec 21 '13 #1
4 1260
5,501 Expert Mod 4TB
Post 1 Code-Block Lines 3 thru 5 currently read:
Expand|Select|Wrap|Line Numbers
  1.   Dim rs As DAO.Recordset
  2.     Set rs = Me.RecordsetClone
  3.     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

Expand|Select|Wrap|Line Numbers
  2.    Dim rs As DAO.Recordset
  3.    Dim zstrSQL As String
  4.    '
  5.    Set rs = Me.RecordsetClone
  6.    '
  7.    zstrSQL = "[SKU]=""" & me.ScannedUPC & """"
  8.    '
  9.    Debug.Print "zstrSQL= " & zstrSQL
  10.    '
  11.    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.
Dec 21 '13 #2
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 :-)
Dec 21 '13 #3
5,501 Expert Mod 4TB
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 (^_^)

Dec 21 '13 #4
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.
Dec 21 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by none | last post: by
121 posts views Thread by David Pendrey | last post: by
7 posts views Thread by Ulrich Wisser | last post: by
13 posts views Thread by Edwin Smith | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.