Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill the data from another table (2003)

    Hi,

    I have a form with data fields ( First Name, Last Name, DOB and other fields) and the data source is tblInfo. I have another table named MasterCustomer is link from a SQL Server table. Since the customer table in SQL server is more accurate, so I would like create a button call "Fill" on the form. So after users enter first name & last name, then click "Fill" button. Database will try to search for the same customer from MasterCustomer table and update "DOB" field in the form.

    I don't know how to put the codes to have this happen in the form.

    Thanks in advance.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill the data from another table (2003)

    Try this. You must substitute the appropriate names.
    <code>
    Private Sub cmdFill_Click()
    Dim varDOB As Variant

    If IsNull(Me.[FirstName]) Then
    MsgBox "Please enter a first name.", vbExclamation
    Me.[FirstName].SetFocus
    Exit Sub
    End If

    If IsNull(Me.[LastName]) Then
    MsgBox "Please enter a last name.", vbExclamation
    Me.[LastName].SetFocus
    Exit Sub
    End If

    varDOB = DLookup("[DOB]", "MasterCustomer", "[FirstName] = " & Chr(34) & Me.[FirstName] & _
    Chr(34) & " AND [LastName] = " & Chr(34) & Me.[LastName] & Chr(34))
    If Not IsNull(varDOB) Then
    Me.[DOB] = varDOB
    End If
    End Sub</code>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill the data from another table (2003)

    Thanks, Hans. I will try it.

    Regards

  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill the data from another table (2003)

    Hi Hans,

    The codes are works. But when I try to change search First name by matching Last Name & DOB, I get the runtime error '3464' data type mismatch in criteria expression message. Please see below:

    varDOB = DLookup("[First Name]", "MasterCustomer", "[DOB] = " & Chr(34) & Me.[DPB] & _
    Chr(34) & " AND [LastName] = " & Chr(34) & Me.[LastName] & Chr(34))
    If Not IsNull(varDOB) Then
    Me.[FirstName] = varDOB
    End If

    Also what codes should I add if I want systems to pops a message if the critrias can't find the MasterCustomer table?

    Thank you so much.

    Regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill the data from another table (2003)

    Chr(34) is the double quote character " - it is used around text values.
    The date of birth is a date/time field - you need to use # around text values.
    Try this:
    <code>
    varFirstName = DLookup("[First Name]", "MasterCustomer", "[DOB] = " & Chr(34) & Me.[DPB] & _
    Chr(34) & " AND [LastName] = #" & Me.[LastName] & "#")
    If Not IsNull(varDOB) Then
    Me.[FirstName] = varDOB
    Else
    MsgBox "There is no customer with this date of birth and last name!", vbExclamation
    End If
    </code>
    Note: you should always use variables with meaningful names. It's not a good idea to use a variable named varDOB for a first name.
    And did you really mean Me.[DPB] ?

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill the data from another table (2003)

    Thanks, Hans. The codes works.

    If I want to look up [First Name] & [Middle Name], should I use the following codes:

    varFirstName = DLookup("[First Name]", "MasterCustomer", "[DOB] = " & Chr(34) & Me.[DOB] & _
    Chr(34) & " AND [LastName] = #" & Me.[LastName] & "#")

    varMiddleName = DLookup("[Middle Name]", "MasterCustomer", "[DOB] = " & Chr(34) & Me.[DOB] & _
    Chr(34) & " AND [LastName] = #" & Me.[LastName] & "#")

    If Not IsNull(varFirstName) Then
    Me.[FirstName] = varFirstName
    Else
    MsgBox "There is no customer with this date of birth and last name!", vbExclamation
    End If

    If Not IsNull(varMiddleName) Then
    Me.[MiddleName] = varMiddleName
    Else
    MsgBox "There is no customer with this date of birth and last name!", vbExclamation
    End If

    Thanks

    Regards

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill the data from another table (2003)

    There is no need to display the message twice - I'd only show it when looking up the first name.

    You have mixed up Chr(34) and #, it should be

    varFirstName = DLookup("[First Name]", "MasterCustomer", "[DOB] = #" & Me.[DOB] & _
    "# AND [LastName] = " & Chr(34) & Me.[LastName] & Chr(34))

    and similar for Middle Name. Chr(34) should be used with text values such as a name, and # with date/time values such as date of birth.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill the data from another table (2003)

    Thank you so much, Hans.

Posting Permissions

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