Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33

    Unanswered: Find a particular field of a particular record and trace back value

    Hi.

    On frmNewBooking, i have a new customer button which loads up frmNewCustomer. It has several textboxes that, once filled, are added to tblCustomers via an append query. This also works fine. Here's what I have at the moment...

    Code:
    Private Sub cmdOK_Click()
    
    If IsNull(txtName) Then
        MsgBox "Customer's name is required.", vbInformation, "Missing Detail"
        Me!txtName.SetFocus
    ElseIf IsNull(txtDateOfBirth) Then
        MsgBox "Customer's date of birth is required.", vbInformation, "Missing Detail"
        Me!txtDateOfBirth.SetFocus
    ElseIf IsNull(txtAddress) Then
        MsgBox "Customer's address is required.", vbInformation, "Missing Detail"
        Me!txtAddress.SetFocus
    ElseIf IsNull(txtCity) Then
        MsgBox "Customer's city is required.", vbInformation, "Missing Detail"
        Me!txtCity.SetFocus
    ElseIf IsNull(txtCounty) Then
        MsgBox "Customer's county is required.", vbInformation, "Missing Detail"
        Me!txtCounty.SetFocus
    ElseIf IsNull(txtPostcode) Then
        MsgBox "Customer's postcode is required.", vbInformation, "Missing Detail"
        Me!txtPostcode.SetFocus
    ElseIf IsNull(txtTelephone) Then
        MsgBox "Customer's telephone is required.", vbInformation, "Missing Detail"
        Me!txtTelephone.SetFocus
    Else
        DoCmd.OpenQuery "qryNewCustomer", acViewNormal, acEdit
        MsgBox txtName & " has been added to the Customer table."
    
        If CurrentProject.AllForms("frmNewBooking").IsLoaded Then
            [Forms]![frmNewBooking]![txtCustomerName].Enabled = True
            [Forms]![frmNewBooking]![txtCustomerID].Enabled = True
            [Forms]![frmNewBooking]![txtCustomerName] = Me!txtName
            '***Add Query Here***
        End If
    
        txtName = ""
        txtDateOfBirth = ""
        txtAddress = ""
        txtCity = ""
        txtCounty = ""
        txtPostcode = ""
        txtTelephone = ""
        txtEmail = ""
        
    End If
    
    End Sub
    My problem is, when you click the OK button, currently it will grab the value of txtName and dump it into txtCustomerName on frmNewBooking, I would like to do the same with the CustomerID, but there's no CustomerID textbox on my NewCustomer form to get the value from. So, where I've put ***Add Query Here***, the query I would like to run to do this task would have to look through tblCustomers and find a record that matches all of the values in the textboxes in the form to their corresponding fields, before they are cleared ('cos then you can just use the value of the textboxes as the search criteria). Then, when it's found the record, it will then get the CustomerID of that particular record from a field called CustomerID in tblCustomers, and dump it into txtCustomerID on frmNewBooking.

    Any Ideas much appreiciated!

    Regards,
    Tom

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want to bump your question up the list please do so, either with <bump> message, or any ideas, duplicate positing doesn't do anyone any favours
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a gander at DLookup().

    Also, seconding healdem's request to restrict posts to one per question please. The forums had are still a scosch glitchy though, so if that wasn't intentional don't worry about it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Sorry - it wasn't intentional - I was having problems with my network and ended up posting it twice, my apologies.

    Anyway, thanks very much for the DLookup - works a treat!

    Regards,
    Tom

Posting Permissions

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