Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Unanswered: VBA Code works in stand alone Form NOT when same form is used as Subform

    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

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by shawnm777 View Post

    ...I hope someone can tell me what I am doing wrong...
    The first thing that you're doing wrong is not only starting a duplicate thread, but starting it less than an hour after your original thread was posted. This is considered a violation of etiquette not only here, but on every help forum I've ever visited. Your second thread has been deleted.

    Double-posting potentially wastes the time of the very people you're asking for help! People answering questions here are volunteering their time and expertise, and have lives and responsibilities away from this forum, especially on weekends and holidays! People answering Post One don't know what's been written to Post Two, and vice versa! They don't know what's been suggested and tried, or even if the problem has already been solved. Proper etiquette is to wait until a reasonable amount of time has passed, and if you find that your post has not received any responses, and has been moved to a second page, by the intervening posts, post a response to your own post. Simply enter something like "Bump!" and this will bring your post back to the top of the queue. Eventually someone will come along and help you.

    Thank you for your attention to this matter, and welcome to dbForums!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    To be fair to the original poster his posts were flagged for moderation. I approved 'em but forgot to tidy up afterwards. Theres a lot of first time posters who get flagged for moderation who repost as they think their post is lost. My fault on not tidying up.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    because you are using it in a subform, which is linked to a parent form, you need to "find" the record in the parent form NOT the sub form

    so whether you push the code into the parent form as a public function and call it from the sub form or drive the code from the sub form is up to you
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    To be fair to the original poster his posts were flagged for moderation. I approved 'em but forgot to tidy up afterwards. Theres a lot of first time posters who get flagged for moderation who repost as they think their post is lost. My fault on not tidying up.
    I'll keep that in mind, healdem!

    My apologies to shawnm777!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •