Results 1 to 9 of 9
  1. #1
    dopeyjh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    5

    Querying a Linked table - not searching all records

    Hi,

    I am wondering if there is a limit to how many records a query on a linked table will search?

    I have a form that when a user clicks on a button it will look up the information based on the account number and populate portions of the form with the information from the linked table. The below code works. I've looked up several accounts and it populates, however some it will not find, when i know they exist. I can manually find them in the linked table and query them directly in the table.

    Its almost as it it's not searching the entire table (which is rather large) (2 500 000 records) - it seems to search the first half of the table. When I query records at the beginning of the table it finds them and works as expected, but if i pick a record further down the table it doesn't find it.

    Is there a limit? If there is anyway around it? Or is there something else I should be doing?


    Any suggestions, help is appreciated

    Code:
    Private Sub bttnLoadData_Click()
     
    Dim rs As DAO.Recordset
    Dim sSQL As String
     
    If Me.txtNumber <> "" Then
    sSQL = "SELECT * FROM linkedTable WHERE STR(linkedTable.Number)  = '" & Me.txtNumber & "'"
    
    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
     
            If rs.RecordCount = 0 Then
     
            MsgBox "Unable to locate the account ", vbExclamation + vbOKOnly
     
            Else
     
            Me.txtCloseDate = rs.Fields("DATE_CLOS").Value
            Me.txtLN1 = rs.Fields("LN1").Value
     
            rs.Close
            Set rs = Nothing
           
            End If
          
    Else
        MsgBox "Please enter account number", vbExclamation + vbOKOnly
     
    End If
     
     
    End Sub
    
    
    
    
    
    
    
    
    
    

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Change the text box to a combobox with a query that contains DATE_CLOS and LN1. Then populate those two fields from the combobox. Or else use a filter on the form (if bound to the table).

  3. #3
    dopeyjh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    5
    I should have added that there are an additional 20+ fields that I am populating on the form, for simplicity reasons I removed them from the code above.
    Does that mean that I should change each one of them to a combo box and update the source to the query?

    I don't see how that's going to change anything? The form is populating as it should.

    For some reason the SQL query doesn't seem to be searching the entire table. If i type in an account number where the record is at the beginning of the table, lets say the 30th record, the query will work, it will look up the 20+ fields and populate them accordingly. But if i type in an account number that's closer to the end of the table, the SQL statement can't find it.

    However, If I go into the Query builder and run the exact same thing(an account closer to the bottom of the table), it will find the record no problem. ???

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do you really need to populate the fields using VBA? If you added the table/query as the Record Source for the form, then clicking the button will be able to filter:
    Me.Filter="keyfieldname=" & me!formfieldname (with quotes if text)
    Me.FilterOn=True
    Me.Requery

    That will populate all the fields on the form.

    (Is the field on your table REALLY called "Number"?)

  5. #5
    dopeyjh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    5
    No, I suppose it doesn't need to, the idea was that the user would open the form enter the acctNum (actual name ) click the button to look up some of the system information, which is only 1/3 of the form contents and the proceed to complete the remaining fields on the form.

  6. #6
    dopeyjh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    5
    Apologies on the delay in responding to my question.
    I need to be able to edit and manipulate the data and then store the information into a new table. Essentially just want to lookup the information to save the users from having to retype the information in.

    I was able to narrow the original issue down a little more, with the same code as originally posted, it will produce results when the Account Number is 6 digits, if the account number is 7 digits it won't find it.

    Any ideas?

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No real reason from what you have given as to why it finds some but not others. So if the first record has 7 digits then it won't find it? That must be something to do with the way things are defined, impossible for me to take a guess.

    Another method would be to run an append query seeing that you are adding a new record to a table. Once they enter the account let the append query include the fields required, the form is bound to the new table and you do the filter method.

  8. #8
    dopeyjh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    5
    FIXED IT!!!
    changed the Select statement..this is the winner
    "Select * From [linkedTable] Where [linkedTable.Number] = " & txtAcctNum
    and works like a charm!

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Good job, well done!

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

Similar Threads

  1. Multiple users querying linked tables
    By vadnagra in forum Import/Export Data
    Replies: 6
    Last Post: 09-21-2016, 01:31 PM
  2. Replies: 2
    Last Post: 08-24-2015, 02:56 PM
  3. Replies: 1
    Last Post: 07-19-2015, 09:00 AM
  4. Replies: 4
    Last Post: 03-02-2015, 07:54 PM
  5. Replies: 7
    Last Post: 03-20-2014, 06:20 AM

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