Results 1 to 9 of 9
  1. #1
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    172

    Can not locate values in recordset

    Would someone please tell me why my code does not locate any records. There are many payments in the table received after 12/01/2012.

    Private Function getPayments()
    Dim db As Database
    Dim rst As Recordset
    Dim datePaid As Date


    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset("tblInvPayments", dbOpenDynaset)

    datePaid = #12/1/2012#

    rst.FindFirst rst![fDateReceived] >= datePaid
    If Not rst.NoMatch Then
    Debug.Print "Yes, a match!"
    Else
    Debug.Print "No match found."
    End If
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    rst.FindFirst "fDateReceived >= #" & datePaid & "#"


    If all you want to do is verify record exists and don't do anything else with recordset, try DLookup:

    Private Sub getPayments()
    If IsNull(DLookup("fDateReceived", "tblInvPayments", "fDateReceived=#" & datePaid & "#")) Then
    Debug.Print "No match found."
    Else
    Debug.Print "Yes, a match!"
    End If
    End Sub

    Use a Function when you want to return a value. This procedure does not return anything so it is really a Sub.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    172
    No, I am going to use the values.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Use what values? Do you know how to return a result from a function?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    172
    Yes, I think. I was trying to make my sample simple. The actual code if the place where I have
    Debug.Print "Yes, a match!"

    is really
    Debug.Print InvPaymentsRST![fLngInvNo]
    where 'fLngInvNo' is a field in the same table.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That's still not working like a Function, it's just performing actions, like a Sub, but not returning anything.

    Calling a function would return a value to the calling procedure. Functions can be called in queries and textboxes while Subs cannot.

    Call a function like:

    x = MyFunction()

    Then sample function:
    Function MyFunction()
    MyFunction = 5 * 11
    End Function

    This means x now has a value of 55 because 55 was returned by the function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    172
    I guess you fell like you are having to pry everything out of me. Here is what I am trying to do.
    tblInvPayments is a table linked to tblInvoices. There is another table 'tblSalesTaxRates' that contains fields 'fCityName', 'fTaxNo', 'fTaxRate', and other items.
    1. I am wanting to get a payment from the tblInvPayments. From that record, I need fInvNo, fInvAmt, fInvDate.

    2. Using the invoice number that I just got, I want to locate that invoice number in the tblInvoices (fInvAmt is the link). There I will get the fCityName, fTaxNo, and other items.

    3. Using the fCityName, I will find the tax rate (fTaxRate) along with its transit tax rate (if any) and any other taxes.
    (Sales tax is a problem when you work in may locations)

    4. I hope to put all this in a table.

    I do this now using 4 different queries. I would just like to automate the process to one button. I hope this explanation was not too long.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The procedure you have now just finds the first record in recordset that meets the date criteria. Will that give the correct data for further processing?

    How can an amount field (fInvAmt) be a reliable link?

    Certainly the process you want could all be done with VBA procedure. I don't know enough about your requirements to be specific. I just keep coming up with more questions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    172
    I put the wrong field as a link. 'fInvNo' is the linking field. As far as a function or sub, I knew a sub did not return a value, so I allways use a function. I know that I will have to locate all records in tblInvPayments and put them in another table. I re-read your replies to me and found that is missed the part where you wrote "rst.FindFirst "fDateReceived >= #" & datePaid & "#". I just tried it and it worked. I did not know how to use the "#" & datePaid & "#" part. So, I think you have solved my problem. I really appreciate your help. Thank you.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-22-2012, 03:33 PM
  2. Recordset loop & change values (kinda...)
    By opopanax666 in forum Programming
    Replies: 4
    Last Post: 05-03-2012, 04:26 AM
  3. Cannot locate VBA code for MsgBox!
    By sjl in forum Forms
    Replies: 5
    Last Post: 12-20-2011, 05:26 PM
  4. Open form and locate it on the bottom
    By Dominaz in forum Forms
    Replies: 4
    Last Post: 11-17-2011, 08:03 PM
  5. Getting Values out of a recordset
    By theracer06 in forum Programming
    Replies: 6
    Last Post: 09-24-2010, 03:44 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