Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Auto Populate Text Boxes based on another

    Hi All,

    I have a form where a user enters data. I have fields on the form as follows.

    Account Number
    Employer
    State
    Parent Number

    After a user enters an account number, I want the employer, state, and parent numbers to auto populate with the data that corresponds to that account number.


    I have a table called Clients. It has colums, employer, state, account number and parent number. This table is where I assume the data would be retrieved from once the user enters an account number on the form.

    I'm am quite new to this so any instructions on how I an achieve this goal would be appreciated. Thanks all!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Paul gives you two options, here:

    Autofilling Fields

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can:
    a) use a domain function to retrieve the values:
    Code:
    Private Sub Account_number_AfterUpdate()
    
        strCriteria = "[account number]=" & Me.Account_number
        Me.employer.Value = DLookup("employer", "clients", strCriteria)
        Me.state.Value = DLookup("state", "clients", strCriteria)
        Me.parent_number.Value = DLookup("[parent number]", "clients", strCriteria)
        
    End Sub
    b) Use a Recordset:
    Code:
    Private Sub Account_number_AfterUpdate()
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        strCriteria = "[account number]=" & Me.Account_number
        Set rst = CurrentDb.OpenRecordset("clients", dbOpenSnapshot)
        With rst
            .FindFirst strCriteria
            If .NoMatch = False Then
                Me.employer.Value = !employer
                Me.state.Value = !state
                Me.parent_number.Value = ![parent number]
            End If
            .Close
        End With
    
    End Sub
    c) Use a combo box to select the account number:
    1. replace the textbox by a combo box, set its RowSource property to:
    Code:
    SELECT Clients.[account number], Clients.employer, Clients.state, Clients.[parent number] FROM Clients ORDER BY Clients.[account number];
    2. set the ColumnWidths property so that only the first column is visible and set the ColumnCount property to 4.
    3. The code now becomes:
    Code:
    Private Sub Account_number_AfterUpdate()
    
        With Me.Account_number
            Me.employer.Value = .Column(1)
            Me.state.Value = .Column(2)
            Me.parent_number.Value = .Column(3)
        End With
        
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Oct 2007
    Posts
    214
    Absolutely brilliant. I used option B and worked perfect. Thanks so much it is appreciated!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Oct 2007
    Posts
    214
    This code seemed to be working but suddenly now it is not. For every account number I enter , it keeps returning the value of the first record in the table instead of going to the corresponding record. Any suggestions?
    Dim rst As DAO.Recordset
    Dim strCriteria As String

    strCriteria = "[account number]=" & Me.Account_number
    Set rst = CurrentDb.OpenRecordset("clients", dbOpenSnapshot)
    With rst
    .FindFirst strCriteria
    If .NoMatch = False Then
    Me.employer.Value = !employer
    Me.state.Value = !state
    Me.parent_number.Value = ![parent number]
    End If
    .Close
    End With

  7. #7
    Join Date
    Oct 2007
    Posts
    214
    I also keep getting a data mismatch pointing to :
    strCriteria = "[account number]=" & Me.Account_number

    I assume this has to do with this being a non numeric field. How would I delimit this?

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    [QUOTE=benz1984;6615836]

    ...I assume this has to do with this being a non numeric field.../QUOTE]

    Exactly! For Text the syntax would be:

    Code:
    strCriteria = "[account number]='" & Me.Account_number & "'"

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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