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
    subform.
    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?
    Thanks
    --
    Best Wishes,
    Richard Harison
     
    LightByrd, Jan 24, 2013
    #1
    1. Advertisements

  2. LightByrd

    Bob Quintal Guest

    "LightByrd" <> wrote in
    news::

    > 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 subform.
    > 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?
    > Thanks


    Without the SQL of the query, nobody can help you.

    --
    Bob Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jan 26, 2013
    #2
    1. Advertisements

  3. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA1545C3140BQuintal@69.16.185.252...
    > "LightByrd" <> wrote in
    > news::
    >
    >> 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 subform.
    >> 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?
    >> Thanks

    >
    > Without the SQL of the query, nobody can help you.
    >
    > --
    > Bob Q.
    > PA is y I've altered my address.


    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,
    tblPhoneRecs.NameLine1;

    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
    fine.
    (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!

    --

    Best Wishes,
    Richard Harison
     
    LightByrd, Jan 28, 2013
    #3
  4. LightByrd

    Bob Quintal Guest

    Re: VBA code returns no records when Key ID is added

    "LightByrd" <> wrote in
    news::

    >
    > "Bob Quintal" <> wrote in message
    > news:XnsA1545C3140BQuintal@69.16.185.252...
    >> "LightByrd" <> wrote in
    >> news::
    >>
    >>> 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 subform.
    >>> 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?
    >>> Thanks

    >>
    >> Without the SQL of the query, nobody can help you.
    >>
    >> --
    >> Bob Q.
    >> PA is y I've altered my address.

    >
    > 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,
    > tblPhoneRecs.NameLine1;
    >
    > 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 fine.
    > (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!
    >
    > --
    >
    > Best Wishes,
    > Richard Harison
    >


    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 Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jan 28, 2013
    #4
  5. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA156AB7F56C6EBQuintal@69.16.185.252...
    > "LightByrd" <> wrote in
    > news::
    >
    >>
    >> "Bob Quintal" <> wrote in message
    >> news:XnsA1545C3140BQuintal@69.16.185.252...
    >>> "LightByrd" <> wrote in
    >>> news::
    >>>
    >>>> 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 subform.
    >>>> 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?
    >>>> Thanks
    >>>
    >>> Without the SQL of the query, nobody can help you.
    >>>
    >>> --
    >>> Bob Q.
    >>> PA is y I've altered my address.

    >>
    >> 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,
    >> tblPhoneRecs.NameLine1;
    >>
    >> 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 fine.
    >> (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!
    >>
    >> --
    >>
    >> Best Wishes,
    >> Richard Harison
    >>

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


    Yes -- perfectly

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


    None -- nothing related -- the database is flat file.

    > 3) Are there filters set in the properties of the form or subform?


    No. They were "allowed" but none applied in either form
    >
    >
    > --
    > Bob Q.
    > PA is y I've altered my address.


    Thank you, Bob
    Answers to your questions noted above

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

    Bob Quintal Guest

    Re: VBA code returns no records when Key ID is added

    "LightByrd" <> wrote in
    news::

    >>>
    >>> 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!
    >>>
    >>> --
    >>>
    >>> Best Wishes,
    >>> Richard Harison
    >>>

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

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

    >
    > None -- nothing related -- the database is flat file.
    >
    >> 3) Are there filters set in the properties of the form or
    >> subform?

    >
    > No. They were "allowed" but none applied in either form
    >>
    >>
    >> --
    >> Bob Q.
    >> PA is y I've altered my address.

    >
    > Thank you, Bob
    > Answers to your questions noted above
    >
    > -- Richard
    >

    Must be something in your code.



    --
    Bob Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jan 29, 2013
    #6
  7. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA157A55776568BQuintal@69.16.185.252...
    > "LightByrd" <> wrote in
    > news::
    >
    >>>>
    >>>> 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!
    >>>>
    >>>> --
    >>>>
    >>>> Best Wishes,
    >>>> Richard Harison
    >>>>
    >>>
    >>> 1) Does the [qryFindProduct] query return a valid recordset with
    >>> and without the tblPhoneRecs.PhoneRecsID field when opened in
    >>> datasheet mode as a query?

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

    >>
    >> None -- nothing related -- the database is flat file.
    >>
    >>> 3) Are there filters set in the properties of the form or
    >>> subform?

    >>
    >> No. They were "allowed" but none applied in either form
    >>>
    >>>
    >>> --
    >>> Bob Q.
    >>> PA is y I've altered my address.

    >>
    >> Thank you, Bob
    >> Answers to your questions noted above
    >>
    >> -- Richard
    >>

    > Must be something in your code.
    >
    >
    > --
    > Bob Q.
    > PA is y I've altered my address.


    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!
    Thanks

    --
    Richard
     
    LightByrd, Jan 30, 2013
    #7
  8. LightByrd

    Bob Quintal Guest

    Re: VBA code returns no records when Key ID is added

    "LightByrd" <> wrote in
    news::

    >
    > "Bob Quintal" <> wrote in message
    > news:XnsA157A55776568BQuintal@69.16.185.252...
    >> "LightByrd" <> wrote in
    >> news::
    >>
    >>>>>
    >>>>> 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!
    >>>>>
    >>>>> --
    >>>>>
    >>>>> Best Wishes,
    >>>>> Richard Harison
    >>>>>
    >>>>
    >>>> 1) Does the [qryFindProduct] query return a valid recordset
    >>>> with and without the tblPhoneRecs.PhoneRecsID field when opened
    >>>> in datasheet mode as a query?
    >>>
    >>> Yes -- perfectly
    >>>
    >>>> 2) what are the properties of link parent fields and link child
    >>>> fields in the subform?.- There should not be any.
    >>>
    >>> None -- nothing related -- the database is flat file.
    >>>
    >>>> 3) Are there filters set in the properties of the form or
    >>>> subform?
    >>>
    >>> No. They were "allowed" but none applied in either form
    >>>>
    >>>>
    >>>> --
    >>>> Bob Q.
    >>>> PA is y I've altered my address.
    >>>
    >>> Thank you, Bob
    >>> Answers to your questions noted above
    >>>
    >>> -- Richard
    >>>

    >> Must be something in your code.
    >>
    >>
    >> --
    >> Bob Q.
    >> PA is y I've altered my address.

    >
    > 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!
    > Thanks
    >
    > --
    > Richard
    >

    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 Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jan 30, 2013
    #8
  9. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA158AC81DC75BQuintal@69.16.185.252...
    > "LightByrd" <> wrote in
    > news::
    >
    >>
    >> "Bob Quintal" <> wrote in message
    >> news:XnsA157A55776568BQuintal@69.16.185.252...
    >>> "LightByrd" <> wrote in
    >>> news::
    >>>
    >>>>>>
    >>>>>> 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!
    >>>>>>
    >>>>>> --
    >>>>>>
    >>>>>> Best Wishes,
    >>>>>> Richard Harison
    >>>>>>
    >>>>>
    >>>>> 1) Does the [qryFindProduct] query return a valid recordset
    >>>>> with and without the tblPhoneRecs.PhoneRecsID field when opened
    >>>>> in datasheet mode as a query?
    >>>>
    >>>> Yes -- perfectly
    >>>>
    >>>>> 2) what are the properties of link parent fields and link child
    >>>>> fields in the subform?.- There should not be any.
    >>>>
    >>>> None -- nothing related -- the database is flat file.
    >>>>
    >>>>> 3) Are there filters set in the properties of the form or
    >>>>> subform?
    >>>>
    >>>> No. They were "allowed" but none applied in either form
    >>>>>
    >>>>>
    >>>>> --
    >>>>> Bob Q.
    >>>>> PA is y I've altered my address.
    >>>>
    >>>> Thank you, Bob
    >>>> Answers to your questions noted above
    >>>>
    >>>> -- Richard
    >>>>
    >>> Must be something in your code.
    >>>
    >>>
    >>> --
    >>> Bob Q.
    >>> PA is y I've altered my address.

    >>
    >> 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!
    >> Thanks
    >>
    >> --
    >> Richard
    >>

    > 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 Q.
    > PA is y I've altered my address.


    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
    quotes.
    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
    clause.
    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
    City]&'*'")
    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
    Else
    Tmp = EnableControls("Detail", True) 'Enable control in detail section
    of sub form
    Me![Look For Product].SetFocus 'Move insert point to Product
    Control
    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
    ----------------------------------------------------------------------------



    --
    Best Wishes,
    Richard
     
    LightByrd, Jan 31, 2013
    #9
  10. LightByrd

    Bob Quintal Guest

    Re: VBA code returns no records when Key ID is added

    "LightByrd" <> wrote in
    news::

    >
    > "Bob Quintal" <> wrote in message
    > news:XnsA158AC81DC75BQuintal@69.16.185.252...
    >> "LightByrd" <> wrote in
    >> news::
    >>
    >>>
    >>> "Bob Quintal" <> wrote in message
    >>> news:XnsA157A55776568BQuintal@69.16.185.252...
    >>>> "LightByrd" <> wrote in
    >>>> news::
    >>>>
    >>>>>>>
    >>>>>>> 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!
    >>>>>>>
    >>>>>>> --
    >>>>>>>
    >>>>>>> Best Wishes,
    >>>>>>> Richard Harison
    >>>>>>>
    >>>>>>
    >>>>>> 1) Does the [qryFindProduct] query return a valid recordset
    >>>>>> with and without the tblPhoneRecs.PhoneRecsID field when
    >>>>>> opened in datasheet mode as a query?
    >>>>>
    >>>>> Yes -- perfectly
    >>>>>
    >>>>>> 2) what are the properties of link parent fields and link
    >>>>>> child fields in the subform?.- There should not be any.
    >>>>>
    >>>>> None -- nothing related -- the database is flat file.
    >>>>>
    >>>>>> 3) Are there filters set in the properties of the form or
    >>>>>> subform?
    >>>>>
    >>>>> No. They were "allowed" but none applied in either form
    >>>>>>
    >>>>>>
    >>>>>> --
    >>>>>> Bob Q.
    >>>>>> PA is y I've altered my address.
    >>>>>
    >>>>> Thank you, Bob
    >>>>> Answers to your questions noted above
    >>>>>
    >>>>> -- Richard
    >>>>>
    >>>> Must be something in your code.
    >>>>
    >>>>
    >>>> --
    >>>> Bob Q.
    >>>> PA is y I've altered my address.
    >>>
    >>> 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!
    >>> Thanks
    >>>
    >>> --
    >>> Richard
    >>>

    >> 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 Q.
    >> PA is y I've altered my address.

    >


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

    would be much better as

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

    It may be getting confused by the reference to [look for city]
    instead of converting it ti its value.



    --
    Bob Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jan 31, 2013
    #10
  11. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA159AB5AD2727BQuintal@69.16.185.252...
    > "LightByrd" <> wrote in
    > news::
    >
    >>
    >> "Bob Quintal" <> wrote in message
    >> news:XnsA158AC81DC75BQuintal@69.16.185.252...
    >>> "LightByrd" <> wrote in
    >>> news::
    >>>
    >>>>
    >>>> "Bob Quintal" <> wrote in message
    >>>> news:XnsA157A55776568BQuintal@69.16.185.252...
    >>>>> "LightByrd" <> wrote in
    >>>>> news::
    >>>>>
    >>>>>>>>
    >>>>>>>> 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!
    >>>>>>>>
    >>>>>>>> --
    >>>>>>>>
    >>>>>>>> Best Wishes,
    >>>>>>>> Richard Harison
    >>>>>>>>
    >>>>>>>
    >>>>>>> 1) Does the [qryFindProduct] query return a valid recordset
    >>>>>>> with and without the tblPhoneRecs.PhoneRecsID field when
    >>>>>>> opened in datasheet mode as a query?
    >>>>>>
    >>>>>> Yes -- perfectly
    >>>>>>
    >>>>>>> 2) what are the properties of link parent fields and link
    >>>>>>> child fields in the subform?.- There should not be any.
    >>>>>>
    >>>>>> None -- nothing related -- the database is flat file.
    >>>>>>
    >>>>>>> 3) Are there filters set in the properties of the form or
    >>>>>>> subform?
    >>>>>>
    >>>>>> No. They were "allowed" but none applied in either form
    >>>>>>>
    >>>>>>>
    >>>>>>> --
    >>>>>>> Bob Q.
    >>>>>>> PA is y I've altered my address.
    >>>>>>
    >>>>>> Thank you, Bob
    >>>>>> Answers to your questions noted above
    >>>>>>
    >>>>>> -- Richard
    >>>>>>
    >>>>> Must be something in your code.
    >>>>>
    >>>>>
    >>>>> --
    >>>>> Bob Q.
    >>>>> PA is y I've altered my address.
    >>>>
    >>>> 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!
    >>>> Thanks
    >>>>
    >>>> --
    >>>> Richard
    >>>>
    >>> 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 Q.
    >>> PA is y I've altered my address.

    >>

    >
    > ChkCity = DLookup("[City]", "qryFindProduct", _
    > "[City] Like [Look For City]&'*'")
    >
    > would be much better as
    >
    > ChkCity = DLookup("[City]", "qryFindProduct", _
    > "[City] Like " & chr(39) & [Look For City] & chr(39) &'*'")
    >
    > It may be getting confused by the reference to [look for city]
    > instead of converting it ti its value.
    >
    >
    >
    > --
    > Bob Q.
    > PA is y I've altered my address.


    Hello Bob,
    First of all - GOT IT!
    Your [Look for City] code suggestion wasn't the answer, since that DLookup
    is only there to verify that such a partspec actually exists in the
    database.
    But it was enough to get me going again, and, after a couple of false
    starts, I stumbled upon the link parent/child fields suggestion you had
    already made.
    Sure enough...there actually were links which I had not seen before. I
    removed those, added the code to the on click event of the PhoneRedsID text
    control in the subform and BANG!

    So, second of all, I owe you an apology for having missed that. Here's how
    I figure my foolishness created the problem:
    The main form was unbound, but in the program from which I swiped my code,
    the subform already had the RecID in the underlying query. Thus the main
    form could remain unbound. (It is in reality only an input vehicle anyway)
    But when I imported the code, there was no RecID in the original query.
    Thus the main form cried out for an ID. To silence that I bound the main
    form to the main table and the parameter query boxes disappeared.
    What I am guessing is that the instant I bound the main form, Access
    automatically generated the parent/child dependencies.
    So when I answered your question about such, it must have been in a phase
    when I was experimenting with the main form unbound.

    So, thirdly, please accept my heartfelt thanks for your willingness, your
    time, your perseverance, and your patience with me which resulted in
    success.
    You are a fine credit to this news group!!!


    --
    Best Wishes,
    Richard Harison
     
    LightByrd, Feb 1, 2013
    #11
  12. LightByrd

    Bob Quintal Guest

    Re: VBA code returns no records when Key ID is added

    "LightByrd" <> wrote in
    news::

    ..
    >>>

    >>
    >> ChkCity = DLookup("[City]", "qryFindProduct", _
    >> "[City] Like [Look For City]&'*'")
    >>
    >> would be much better as
    >>
    >> ChkCity = DLookup("[City]", "qryFindProduct", _
    >> "[City] Like " & chr(39) & [Look For City] & chr(39) &'*'")
    >>
    >> It may be getting confused by the reference to [look for city]
    >> instead of converting it ti its value.
    >>
    >>
    >>
    >> --
    >> Bob Q.
    >> PA is y I've altered my address.

    >
    > Hello Bob,
    > First of all - GOT IT!


    GREAT!!!

    > Your [Look for City] code suggestion wasn't the answer, since that
    > DLookup is only there to verify that such a partspec actually
    > exists in the database.


    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.

    > But it was enough to get me going again, and, after a couple of
    > false starts, I stumbled upon the link parent/child fields
    > suggestion you had already made.
    > Sure enough...there actually were links which I had not seen
    > before. I removed those, added the code to the on click event of
    > the PhoneRedsID text control in the subform and BANG!
    >
    > So, second of all, I owe you an apology for having missed that.


    No apologies required. To err is human.

    > Here's how I figure my foolishness created the problem:
    > The main form was unbound, but in the program from which I swiped
    > my code, the subform already had the RecID in the underlying
    > query. Thus the main form could remain unbound. (It is in reality
    > only an input vehicle anyway) But when I imported the code, there
    > was no RecID in the original query. Thus the main form cried out
    > for an ID. To silence that I bound the main form to the main table
    > and the parameter query boxes disappeared. What I am guessing is
    > that the instant I bound the main form, Access automatically
    > generated the parent/child dependencies. So when I answered your
    > question about such, it must have been in a phase when I was
    > experimenting with the main form unbound.
    >

    Yes, Access does lots of things without warning the user. Some
    things it does are beneficial, but many are not.


    > So, thirdly, please accept my heartfelt thanks for your
    > willingness, your time, your perseverance, and your patience with
    > me which resulted in success.
    > You are a fine credit to this news group!!!
    >

    You are welcome. I enjoy debugging code when the weather is too cold
    for me to bask in the sun..

    --
    Bob Q.
    PA is y I've altered my address.
     
    Bob Quintal, Feb 2, 2013
    #12
  13. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added

    "Bob Quintal" <> wrote in message
    news:XnsA15B9D31B4CA9BQuintal@69.16.185.252...
    >
    >
    > "LightByrd" <> wrote in
    > news::
    >
    > ..
    >>>>
    >>>
    >>> ChkCity = DLookup("[City]", "qryFindProduct", _
    >>> "[City] Like [Look For City]&'*'")
    >>>
    >>> would be much better as
    >>>
    >>> ChkCity = DLookup("[City]", "qryFindProduct", _
    >>> "[City] Like " & chr(39) & [Look For City] & chr(39) &'*'")
    >>>
    >>> It may be getting confused by the reference to [look for city]
    >>> instead of converting it ti its value.
    >>>
    >>>
    >>>
    >>> --
    >>> Bob Q.
    >>> PA is y I've altered my address.

    >>
    >> Hello Bob,
    >> First of all - GOT IT!

    >
    > GREAT!!!
    >
    >> Your [Look for City] code suggestion wasn't the answer, since that
    >> DLookup is only there to verify that such a partspec actually
    >> exists in the database.

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


    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

    > However my suggestion about the dlookup still holds. I've seen
    > several cases where dlookups fail in Access 2010 that worked in
    > older versions.


    Now that's a drag!! I Wonder how many more of those are kicking around!

    >> But it was enough to get me going again, and, after a couple of
    >> false starts, I stumbled upon the link parent/child fields
    >> suggestion you had already made.
    >> Sure enough...there actually were links which I had not seen
    >> before. I removed those, added the code to the on click event of
    >> the PhoneRedsID text control in the subform and BANG!
    >>
    >> So, second of all, I owe you an apology for having missed that.

    >
    > No apologies required. To err is human.
    >
    >> Here's how I figure my foolishness created the problem:
    >> The main form was unbound, but in the program from which I swiped
    >> my code, the subform already had the RecID in the underlying
    >> query. Thus the main form could remain unbound. (It is in reality
    >> only an input vehicle anyway) But when I imported the code, there
    >> was no RecID in the original query. Thus the main form cried out
    >> for an ID. To silence that I bound the main form to the main table
    >> and the parameter query boxes disappeared. What I am guessing is
    >> that the instant I bound the main form, Access automatically
    >> generated the parent/child dependencies. So when I answered your
    >> question about such, it must have been in a phase when I was
    >> experimenting with the main form unbound.
    >>

    > Yes, Access does lots of things without warning the user. Some
    > things it does are beneficial, but many are not.
    >
    >
    >> So, thirdly, please accept my heartfelt thanks for your
    >> willingness, your time, your perseverance, and your patience with
    >> me which resulted in success.
    >> You are a fine credit to this news group!!!
    >>

    > You are welcome. I enjoy debugging code when the weather is too cold
    > for me to bask in the sun..


    And I, for one, am so grateful for your hobby!!

    > --
    > Bob Q.
    > PA is y I've altered my address.


    Hi Bob....
    Three comments above
    Thanks again -- so much

    --
    Best Wishes,
    Richard Harison
     
    LightByrd, Feb 2, 2013
    #13
  14. Re: VBA code returns no records when Key ID is added

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


    "LightByrd" wrote in message
    news:...


    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
    #14
  15. LightByrd

    LightByrd Guest

    Re: VBA code returns no records when Key ID is added


    >> "LightByrd" wrote in message
    >> news:...



    >> 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" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
    >news:kek9ps$sdc$...
    > ChkCity = DLookup("[City]", "qryFindProduct", _
    > "[City] Like " & chr(39) & [Look For City] &"*" & chr(39))
    >
    >

    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
    dangerous!

    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!) ;<}

    --
    Best Wishes,
    Richard Harison
     
    LightByrd, Feb 3, 2013
    #15
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nick

    Blank records added

    Nick, Aug 19, 2003, in forum: Access Forms Coding
    Replies:
    1
    Views:
    63
    Gary Miller
    Aug 19, 2003
  2. Datatonic

    If query returns no records display message box

    Datatonic, Nov 16, 2003, in forum: Access Forms Coding
    Replies:
    15
    Views:
    188
    Dirk Goldgar
    Nov 20, 2003
  3. tlyczko
    Replies:
    8
    Views:
    143
    tlyczko
    Feb 8, 2006
  4. Jasonm
    Replies:
    2
    Views:
    67
    Jasonm
    Apr 23, 2006
  5. Replies:
    2
    Views:
    127
    missinglinq via AccessMonster.com
    Jun 23, 2007
  6. Melinda Chase

    Display records added since last time opened

    Melinda Chase, Mar 4, 2008, in forum: Access Forms Coding
    Replies:
    5
    Views:
    97
    Melinda Chase
    Mar 4, 2008
  7. laknight

    Limit Value List Records Added To 2nd List Box

    laknight, Jul 11, 2008, in forum: Access Forms Coding
    Replies:
    4
    Views:
    119
    laknight
    Jul 14, 2008
  8. Pete

    Check if records added to subform on a subform

    Pete, Mar 12, 2009, in forum: Access Forms Coding
    Replies:
    12
    Views:
    121
    Marshall Barton
    Mar 16, 2009
Loading...