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

    Unanswered: Setting a combo box's value to a specific column

    Hi.

    I have a combo box in frmExistingCustomers that populates with 3 pieces of data from tblCustomers... CustomerID, Name, DateOfBirth. I currently have them in this order and would like to keep them in this order. This form loads from frmNewBooking. This works fine.

    I would like it so the value of the combo box is set to column1, (Name), instead of the default column0, (CustomerID). Is this possible?

    The main idea of what I am trying to acheive is to get the value of Name from the combo box and then dump it into a field called txtCustomerName on frmNewBooking.

    If not, once the record has been selected, is there a way to relate to that record's column index number (1) and grab tht value, and then go on to dump it into the other form's field?

    Obviously, I would like the first method as it would then display the Name in the combobox rather than the CustomerID.

    Regards,
    Tom

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your combo box has a property called "Bound column" in the data tab. Set this to the column that contains the values you need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Tried it, it's default was on 1, so I tried changing it to 2, but still had no effect.

    I have no control source set as I am using a query, is this why?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Tom
    If you are prepared to place some code behind the combo box then you could consider soemthing similar to

    if you are "pushing" the value from frmExistingCustomer (ie you spot a change in the combo box and send the new value to frmNEwBooking
    forms!frmNewBooking!txtCustomername=<mycombobox>.c olumn(1)

    if you are "pulling" the value from frmExistingCustomer (IE you want to drag accross the current value from frmExistingCustomer
    txtCustomername=forms!frmExistingCustomer<mycombob ox>.column(1)

    for this use you are probably better off with the "push" variant, placing the code in the controls on click event

    I'm not on a Microsoft OS, so no Access at present so I can't test it. You will undoubtably need to tinker with the exact expression. you will need to limit to list (so soembody can't put in a partial match and drag that accross). you may need to look at the items selected property of the combo

    Is there anything stopping you putting the combo with taht data on your new booking form as an unbound combo - makes the user interface far tidier - the user doesn't have to toggle between two screens, could also be a lot faster in use.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    Nice bit of code healdem. Much appreiciated. However, this has now created a new problem...

    On frmNewBooking, i also have a new customer button which loads up frmNewCustomer. It has several forms 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 field on my NewCustomer form. 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 criteria. Then, when it's found the record, it will then get the CustomerID of that particular record and dump it into txtCustomerID on frmNewBooking.

    Any Ideas much appreiciated!

    Regards,
    Tom

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry mate, I haven't got the time or the access to look at your code at present

    I mayget some time later on, but not certain

    Having had a brief look, can I make a comment on coding style. On a personal level Ihate the design paradigm that checks an element and ssays in effect who have a rpoblme fix this, when potentially there are other problems in the same data stream.

    So id be tempted to validate all fields in one session, find a means of identifying the problems to a user, hoistorically I've toggled the background colour, or set it to red, and then set a tooltip to indicate what the eror is in each field. I'm not claiming that is the right way todo it, its just as a user I'm annoyed at being fed an item at a time, I want ot find what the problems are fix em & move on. Potentailly you users may end up hitting the 'OK' button 8 or more times to get a clean record.

    it may make sense to move your validation code into the forms before update event, that way round you will catch any cnning users who try to close the form, or move to another record. Access has a helpfull habit of autosaving records. if you haven't built in the validation then you can get dirty records appearing n the db.

    have a look in the help system for info on events

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    OK, I'm not worried about blank records or anything at the moment, im still building my code up at present.

    Any other ideas ppl? desperately needed! Any query experts out there?

    Regards,
    Tom
    Last edited by stibily; 01-25-06 at 15:45.

Posting Permissions

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