VBA code returns no records hwenKey ID is added

Discussion in 'Access Forms Coding' started by LightByrd, Jan 24, 2013.

  1. LightByrd

    LightByrd Guest

    I need some help with a curious error.
    I have a form with a subform. The form prompts the user to enter the
    partspec of a product/service.
    The subform then returns all the businesses which match the partspec, using
    an on_click event in the main form.
    This runs VBA code which injects an SQL statement with the FROM pointing to
    a main table query [qryFindProduct] into the recordsource of the subform.

    What I am trying to do is add the Key field (ID) from the main table to the
    query and I have modified the subform to have that field display on the
    But as soon as I add that field to the query and run the program it cannot
    find any records that match the SQL statement. I debugged the VBA code and
    saw that the SQL is properly formed. Here is the line in the on_click VBA
    it stumbles over:

    If Me![Find Product Subform].Form.RecordsetClone.RecordCount = 0 Then
    Found = False (then displays to msgBox)

    With the ID key field removed from the query, that line returns the proper #
    of records.
    With it in the query, that line returns 0.

    The frustrating thing is that I copied all this code from another database I
    wrote and it works perfectly there.
    Any ideas from those more knowledgable than I?
    LightByrd, Jan 24, 2013
    1. Advertisements

  2. LightByrd

    Bob Quintal Guest

    Without the SQL of the query, nobody can help you.
    Bob Quintal, Jan 26, 2013
    1. Advertisements

  3. LightByrd

    LightByrd Guest

    Thank you!
    Assuming you mean the SQL that is formed by the VBA and then injected into
    the Recordsource property of the sub form:

    SELECT * FROM qryFindProduct WHERE [ProductService] Like 'aut*';
    (This case assumes the partspec to be aut as in "auto parts or auto repair"

    If you mean the SQL of the actual query [gryFindProduct] itself, the SQL is:

    SELECT tblPhoneRecs.PhoneRecsID, tblPhoneRecs.ProductService,
    tblPhoneRecs.NameLine1, tblPhoneRecs.NameLine2, tblPhoneRecs.StreetNumber,
    tblPhoneRecs.StreetAddress, tblPhoneRecs.City, tblPhoneRecs.StateOrProv,
    tblPhoneRecs.BusinessPhone, tblPhoneRecs.Fax, tblPhoneRecs.Email
    FROM tblPhoneRecs ORDER BY tblPhoneRecs.ProductService,

    The frustration comes from the fact that I lifted the VBA code and
    form/subform structure from another similar program I wrote where it works
    (Main form unbound -- subform recordsource property bound to the generated
    SQL code (as above))

    Without the Key ID field [PhoneRecsID] in [qryFindProduct] it works but only
    if I bind the main form to the underlying table.
    Here is a review:
    main form unbound & no Key ID field in [qryFindProduct] -- repeating
    parameter query boxes
    main form unbound & Key ID field in [qryFindProduct] -- repeating parameter
    boxes. Search eventually fails as described in the original post.
    main form bound to main table & Key Id in [qryFindProduct] -- search
    fails -- find no records
    main form bound to main table & no Key ID in [qryFindProduct] -- works (but
    defeats my original purpose)

    Thanks again Bob, this is about as clear as I can make it!
    LightByrd, Jan 28, 2013
  4. LightByrd

    Bob Quintal Guest

    1) Does the [qryFindProduct] query return a valid recordset with and
    without the tblPhoneRecs.PhoneRecsID field when opened in datasheet
    mode as a query?

    2) what are the properties of link parent fields and link child
    fields in the subform?.- There should not be any.

    3) Are there filters set in the properties of the form or subform?
    Bob Quintal, Jan 28, 2013
  5. LightByrd

    LightByrd Guest

    Yes -- perfectly
    None -- nothing related -- the database is flat file.
    No. They were "allowed" but none applied in either form
    Thank you, Bob
    Answers to your questions noted above

    -- Richard
    LightByrd, Jan 29, 2013
  6. LightByrd

    Bob Quintal Guest

    Must be something in your code.
    Bob Quintal, Jan 29, 2013
  7. LightByrd

    LightByrd Guest

    That's the most frustrating part!
    The code is exactly the same as in the program that works.
    I've stepped through the code and the line that's odd is this one:

    'If no records match criteria, set Found to False
    If Me![Find Product Subform].Form.RecordsetClone.RecordCount = 0 Then
    (dsp. "no recs match criteria" msg)

    With the KeyID field in the underlying query, that line returns 0.
    With the KeyID field removed, the line returns the correct # of records
    I've checked for spelling errors, etc. so far haven't found any
    Go figger!
    I know USENET protocol frowns on attachments, etc., but would it help if I
    sent you a dummy version by some other means.
    In the meantime, well, I'll keep looking!
    LightByrd, Jan 30, 2013
  8. LightByrd

    Bob Quintal Guest

    The If Me!... line is working correctly..
    We need to see the code that sets the recordset.and probably the
    rest of the code in that procedure
    Bob Quintal, Jan 30, 2013
  9. LightByrd

    LightByrd Guest

    Many thanks in advance!!
    Here goes:
    Look For Product and Look For City are the names of the text input controls
    btnFindProduct is the command button that triggers the search

    Private Sub AddToWhere(FieldValue As Variant, FieldName As String,
    MyCriteria As String, ArgCount As Integer)
    'Create criteria for WHERE clause.
    If FieldValue <> "" Then 'Add "and" if other criterion exists.
    If ArgCount > 0 Then
    MyCriteria = MyCriteria & " and "
    End If

    'Append criterion to existing criteria. Enclose FieldValue and * in
    MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue &
    Chr(42) & Chr(39))
    ArgCount = ArgCount + 1 'Increase Argument Count
    End If
    End Sub
    Private Sub btnFindProduct_Click()
    'Create a WHERE clause using search criteria entered by user and
    'Set RecordSource property of FindProduct Subform to SQL WHERE Statement.

    Dim MySQL As String, MyCriteria As String, MyRecordSource As String
    Dim ArgCount As Integer, ReTry As Integer, Found As Integer
    Dim ChkCity As Variant, Tmp As Variant

    Found = False 'Initialize Variable

    Me![Find Product Subform].Form.RowHeight = 250

    'Initialize SELECT Statement
    MySQL = "SELECT * FROM [qryFindProduct] WHERE "

    'Use data entered in form header text boxes to create criteria for WHERE
    ArgCount = 0: MyCriteria = ""
    AddToWhere [Look For Product], "[ProductService]", MyCriteria, ArgCount
    AddToWhere [Look For City], "[City]", MyCriteria, ArgCount

    'If no criterion specifed, return all records.
    If MyCriteria = "" Then
    MyCriteria = "True"
    End If

    'Create SELECT stmnt. for subform Recordsource property
    MyRecordSource = MySQL & MyCriteria

    'Set RecordSource property-FindProduct Subform.
    Me![Find Product Subform].Form.RecordSource = MyRecordSource

    'Lookup City From qryFindProduct--message if not found
    ChkCity = DLookup("[City]", "qryFindProduct", "[City] Like [Look For
    If IsNull(ChkCity) Then
    MsgBox "Unknown City! Please Re-Enter..."
    Me![Look For City].Value = Null
    Me![Look For City].SetFocus
    Exit Sub
    End If

    'If no records match criteria, set Found to False return focus to text
    input control
    If Me![Find Product Subform].Form.RecordsetClone.RecordCount = 0 Then
    Found = False
    Tmp = EnableControls("Detail", True) 'Enable control in detail section
    of sub form
    Me![Look For Product].SetFocus 'Move insert point to Product
    Found = True
    End If

    If Not Found Then 'reset Look For Product text input window
    MsgBox "No Records Match Specified Criteria.", 48, "No Records Found"
    Me![Look For Product].Value = Null
    Me![Look For Product].SetFocus
    End If
    End Sub
    LightByrd, Jan 31, 2013
  10. LightByrd

    Bob Quintal Guest

    Bob Quintal, Jan 31, 2013
  11. LightByrd

    LightByrd Guest

    LightByrd, Feb 1, 2013
  12. LightByrd

    Bob Quintal Guest

    Yeah, it was me clutching at straws after being told that my
    suspicion of link parent/child propewrties was not the problem.

    However my suggestion about the dlookup still holds. I've seen
    several cases where dlookups fail in Access 2010 that worked in
    older versions.
    No apologies required. To err is human.
    Yes, Access does lots of things without warning the user. Some
    things it does are beneficial, but many are not.

    You are welcome. I enjoy debugging code when the weather is too cold
    for me to bask in the sun..
    Bob Quintal, Feb 2, 2013
  13. LightByrd

    LightByrd Guest

    I didn't want to mention it, since I knew it wasn't the problem,
    but your code suggestion:

    ChkCity = DLookup("[City]", "qryFindProduct", _
    "[City] Like " & chr(39) & [Look For City] & chr(39) &'*'")

    according to Access 2003, has a syntax error in it.
    The exact message is "compile error -- expected expression"
    It highlights the single quote mark just before the wildcard star.
    Crazy -- looks OK to me
    Now that's a drag!! I Wonder how many more of those are kicking around!
    And I, for one, am so grateful for your hobby!!

    Hi Bob....
    Three comments above
    Thanks again -- so much
    LightByrd, Feb 2, 2013
  14. ChkCity = DLookup("[City]", "qryFindProduct", _
    "[City] Like " & chr(39) & [Look For City] &"*" & chr(39))

    "LightByrd" wrote in message

    I didn't want to mention it, since I knew it wasn't the problem,
    but your code suggestion:

    ChkCity = DLookup("[City]", "qryFindProduct", _
    "[City] Like " & chr(39) & [Look For City] & chr(39) &'*'")

    according to Access 2003, has a syntax error in it.
    The exact message is "compile error -- expected expression"
    It highlights the single quote mark just before the wildcard star.
    Crazy -- looks OK to me
    Douglas J Steele, Feb 3, 2013
  15. LightByrd

    LightByrd Guest

    Yes Doug, that works fine!
    I'm sure Bob Q. appreciates it as well
    I think we have all tried to learn over the years just how scary syntax can
    get, and
    that use of string literals in double quotes can become unexpectedly

    I am still somewhat unnerved by Bob's observation that certain syntaxes that
    work in Access 2003 crash in 2010 (and probably 2007 as well.)
    Many of the databases I have written were done for use with 2003, but the
    organizations that use them may have crawled into bed with later versions.
    (I'm afraid to answer the phone!) ;<}
    LightByrd, Feb 3, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.