Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2012
    Posts
    22

    Unanswered: Autofill based on Account Number

    Hi guys, I have a form where you are required to enter a clients details, what I would like to do is when the user types in the clients account number (The unique identifier for the account) it will fill in all the other fields such as; client name, address line 1, address line 2, address line 3 etc etc I have tried a combo box but I don't want a drop down box I want to be able to type and have it appear when I click out of the box
    I have tried this in the control source;
    Tables!tblClientDetails!Account Number.column(5)
    where column 5 is address line 1

    Can anyone shed some light?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With:
    - Txt_ClientAccountNumber being the name of the unbound TextBox control where the user types in the clients account number,
    - ClientAccountNumber being the name of the Field that holds the client account number (from the table or query),
    - ClientAccountNumber being numeric,
    - the Form being bound,
    you can use:
    Code:
    Private Sub Txt_ClientAccountNumber_AfterUpdate
    
        Dim rst as DAO.Database
    
        Set rst = Me.RecordsetClone
        With rst
            .FindFirst "ClientAccountNumber = " & Me.Txt_ClientAccountNumber.Value
            If .NoMatch = False Then
                Me.Bookmark = .Bookmark
            Else
                Msgbox "There is no client whith the account number " & Me.Txt_ClientAccountNumber.Value, vbInformation, "Not found"
            End If
            .Close
        End With
        Set rst = Nothing
    
    End Sub
    If ClientAccountNumber is defined as Text, replace:
    Code:
            .FindFirst "ClientAccountNumber = " & Me.Txt_ClientAccountNumber.Value
    with
    Code:
            .FindFirst "ClientAccountNumber = '" & Me.Txt_ClientAccountNumber.Value & "'"
    Note: You should refrain from using spaces or other non alphanumeric characters in the names of your objects (Tables!tblClientDetails!Account Number.column(5) )
    Have a nice day!

  3. #3
    Join Date
    Sep 2012
    Posts
    22
    I have removed the spaces from the objects, however when I type an account number in the field it comes with with an error message stating
    "Compile Error:
    Method or Data member not found"
    Then highlights ".FindFirst" in the VBA editor

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops, sorry ! It should be:
    Code:
    Dim rst as DAO.RecordSet
    Have a nice day!

  5. #5
    Join Date
    Sep 2012
    Posts
    22
    I have done that however when I type in an account number it comes up with the error message, no matter what account number I use the account number field is ClientAccountNumber in both tables it appears in as a primary key and a foreign key

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the error code and its associated message? On which line does the error occur?
    Have a nice day!

  7. #7
    Join Date
    Sep 2012
    Posts
    22
    The error message is the one that was set in the code the;
    "There is no client with the account number xxx"

    I have a table filled with clients and none of them will work

    Also sorry about the late reply. I was out of office yesterday pretty much all day

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Is the name of the field ClientAccountNumber?
    2. What does Me.Txt_ClientAccountNumber.Value returns (set a breakpoint on the line and use the Quick Watch feature [Ctrl+F9] when the code stops executing)?
    Have a nice day!

  9. #9
    Join Date
    Sep 2012
    Posts
    22
    Yeah, the field in the table is ClientAccountNumber
    it says
    <Expression not defined in context>
    Last edited by Kisageru; 11-02-12 at 08:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •