Results 1 to 10 of 10
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    recordset not reading

    Hi Guys, i don't know if anyone has come across a recordset not reading ?
    I can have the same code in another part of the database that works totally fine and occasionally the following won't read ? the way i normally test test it is re-write rs.EOF , when i press the stop key (.) it is not listing the functions, yet if i itest another part it does!!!!!

    mDealer is a string
    rs is a dao.recordset
    shipment date as date

    all the Dim's are set

    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo = '" & mDealer & "'" & " And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "# ORDER BY Product;")
    Do Until rs.EOF



    many thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    "Select * From tblEdit WHERE DelTo = '" & mDealer & "' And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "# ORDER BY Product"

    you dont need the ampersand next to AND, but you do need to put a stop point here, and make sure mDealer has a value. (hover cursor over mDealer when it stops)

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also make sure your date field doesn't include the time. It will if you use Now() to populate it (use Date() instead).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    a recordset not reading
    What does this mean? Maybe there are no records so you're already at EOF? IMHO you ought to check for no records before trying anything else:

    If Not(rs.EOF And rs.BOF) Then <<< if both are true, there are no records
    rs.MoveFirst <<< rs.MoveLast before if you want a record count. I would moveFirst before Do, otherwise you could start somewhere other than what you think is the beginning.
    Do While Not rs.EOF
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guys, i cn see a couple of mistakes pointed out from your reply, much appreciated, will be having a play around with it this morning..

    "Select * From tblEdit WHERE DelTo = '" & mDealer & "' And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "# ORDER BY Product" I will close off criterias before next criteria

    "Select * From tblEdit WHERE DelTo = '" & mDealer & "'
    " & "
    And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "#
    "& "
    ORDER BY Product"

    The annoying part is it did work and going back to your point about testing for an empty recordset, the shipmentdate is based on a combo that must have records as is mDealer from that shipment date, the records have got to be there as these are not dates and dealers are from the tblEdit ! ? please correct me if I am wrong ??

    It may be wise I put a MsgBox after declaring recordset ie: MsgBox(rs.RecordCount) etc....

    much appreciated guys because sometimes I can't see the woods because the trees ae in the way

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the records have got to be there
    That is really besides the point, which is that there are good practices and not so good ones. Yours is nsg.
    By getting in the good habit, you will avoid raising errors because of empty recordsets caused by
    - changes to underlying tables, queries, etc. that you forget about
    - code mods that cause unexpected code flow
    - sql/queries that contain no records because you didn't ensure a form control had a value
    - etc.
    If your combos are bound and you are using them to search on, that doesn't sound right. As for the message box, OK for interim testing I guess, but might become annoying if it's not useful information to the user as opposed to just the developer.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Use a variable for the SQL and this to see/test the finished SQL:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi guys, thenk you for help on this one, when i want to loop a recordset, there is 2 way I usually do this so tried the other way would normally do and it works fine so shows the records were there in the 1st place, however, I do find it bizarre that either way usually works for me!!

    1st method below wouldn't read (usually does on this method)!!!

    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo = '" & mDealer & "'" & " And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "# ORDER BY Product;")
    Do Until rs.EOF
    rs.move first
    do stuff
    rs.movenext
    Loop

    My other way I have used before works as below

    Dim L as long
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo = '" & mDealer & "'" & " And ShipmentDate = #" & Me.cboShipmentDateIndex1 & "# ORDER BY Product;")
    For L = 1 to rs.RecordCount

    rs.MoveFirst
    do stuff
    next L

    Wonder why 1st one didn't work when I use that method regular!

    Thanks again guys


  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have MoveFirst inside the loop, so it will probably keep working on the first record in an endless loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    For L = 1 to rs.RecordCount
    rs.MoveFirst
    Hard to know whether or not to take your code literally. RecordsetCount can't be more than one if you don't MoveLast beforehand. However, I don't see the point in using such a counter when you can just write
    Do While Not rs.EOF or Do Until as you have it in the first example. Also, the MoveFirst of the 2nd example will raise an error if the rs is empty as you don't check if the rs has records. I've adopted Allen Browne's method

    Code:
    If Not (rs.EOF And rs.BOF) Then
      Do While Not rs.EOF
        ...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reading in table
    By DevState in forum Programming
    Replies: 30
    Last Post: 07-30-2018, 12:16 PM
  2. Reading XML
    By ricksil in forum Programming
    Replies: 1
    Last Post: 05-17-2016, 08:47 AM
  3. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  4. reading a web page
    By Norman Castle in forum Programming
    Replies: 0
    Last Post: 03-14-2013, 08:51 PM
  5. Replies: 2
    Last Post: 03-08-2012, 12:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums