Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem synch-ing combo-box (2003)

    I have a main table (film) Titles linked to a company and then to a contact. Each title has an associated company and a specific contact. So both the company and contact need to appear in the main Titles table.
    Using frmTitles I can add a new company via this combo-box. I also want to add a new contact via the contacts combo. I've got it to create the new contact but it doesn't store the correct ContactId in the Titles table. (There are then other issues connected to this..). How can I get the form to create the new contact and then pick up and store the correct ContactId number in the Titles table? Hope this makes sense, Andy.

  2. #2
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem synch-ing combo-box (2003)

    Struggling to attach the zipped database, so below is relevant code which I hope helps:

    Private Sub cboContact_NotInList(NewData As String, Response As Integer)
    Dim ctl As Control
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set ctl = cboContact
    If MsgBox("Add to contact list?", vbOKCancel) = vbCancel Then
    Response = acDataErrContinue
    ctl.Undo
    Else
    ' add new Contact to table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Contacts", dbOpenDynaset)
    rst.AddNew
    rst.Fields("CompanyId") = cboCompany
    rst.Fields("Contact") = NewData
    rst.Update
    rst.Close

    Set rst = Nothing
    Set db = Nothing
    Response = acDataErrAdded

    End If
    End Sub

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

    Re: Problem synch-ing combo-box (2003)

    Set a breakpoint at the beginning of the code (click in the line and press F9).
    When you open the form and enter a non-existent contact, the code will pause at the line with the breakpoint.
    You can single-step through the code by pressing F8.
    Hover the mouse pointer over a variable to see its value.
    Hopefully this will give you a clue of what's going wrong.

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem synch-ing combo-box (2003)

    I think I need to describe the problem in stages to help solve it. I have table Titles linked to Companies and then to Contacts (relevant to the particular company) using autonumbers for CompanyId and ContactId. When I open the form for titles and move between records it shows the correct company and contact, but when I display the contacts combo-box items it displays ALL contacts even though they should only be specific to the selected company. I think I need to solve this problem firstly before I worry about synchronising the combos for new records. What SQL statement should I use to ensure that initially the contacts combo only displays contacts for the already chosen company? Does this make sense? Thanks, Andy.

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

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem synch-ing combo-box (2003)

    Thank you, but this link relates to the AfterUpdate event. I need first to resolve the issue that, as I move between records, the second combo-box does not reflect the selection already made in the first combo-box. Andy.

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

    Re: Problem synch-ing combo-box (2003)

    You can update the row source of the combo box in the same way in the On Current event of the form.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem synch-ing combo-box (2003)

    Hello again. I'm approaching this synch problem in a different way. I'm leaving the row-source of cboContacts permantly set to those for cboCompany, and just requerying cboContacts when necessary. However, I have a similar problem as before in that I'm having to use OnCurrent to requery this combo-box all the time. Shouldn't the combo-box be requeried by default anyway, as I move from record to record? It seems cumbersome (slow) to have to do this programmatically? Thanks, Andy.

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

    Re: Problem synch-ing combo-box (2003)

    Access doesn't automatically requery combo boxes and list boxes as you move from record to record, so you need to do so in the On Current event. It's only one line...

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem synch-ing combo-box (2003)

    Thank you. I suppose it's reasonable as otherwise all forms would be very slow by default.. Andy.

Posting Permissions

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