Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Updating a ComboBox

    Hi,

    In my form i have 2 combobox (Combo1 and Combo2). In combo1 i select the name of a company and in combo2 i should have all clients of this company (this combo should be updated when i select company in combo1).

    The table Company and Clients are linked.

    i'm have Access 2002. How can i do this ?? i don't know VB i was trying to setup a macro, i have a SQL query wich selects clients of a company based on a variable but i don't know how i can link this variable with combo1 ?

  2. #2
    Join Date
    Aug 2003
    Posts
    29

    Smile Re: Updating a ComboBox

    Hi Webcenter;

    Here's one way.....

    Assuming...

    the form name is frmClient_Selection.
    the combo 1 is named cboCombo1
    the combo 2 is named cboCombo2
    the table containing the Customers is named tblCustomers
    the table containing the Clients is named tblClients
    the control source for Combo1 is the Record ID in the tblCustomers.



    Open the properties for cboCombo2.

    Click the data tab

    Set Row Source Type to Table/Query.

    Click on Row Source

    Click on on elipse ...

    You're now going to build a select query.

    ===================================

    Add the Table tblClients table to the query.

    Select (Add to the query) the record ID of the tblClients
    in the first column.

    Select the field which contains the Customers ID Number (matches
    the Record ID in the tblCustomers.

    Select the field from the tblClients which contains the
    Clients Names.

    In the criteria column for the Customers ID Number enter:

    "forms!frmClient_Selection!cmdCombo1" (no quote marks).

    Click on the Red X in the Upper right corner of query form.

    Click on Yes to save.


    Click on Format tab of the properties for cboCombo2.

    Set Column Count to 2

    Set Column widths to 0;1.25

    Close the properties for cboCombo2.

    Now when you click on the arrow in cboCombo2 the result of the query
    (The Customers Clients) will display in cboCombo2

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Talking Updating a ComboBox

    Webcenter

    Yo no manejo muy bien el ingles por eso te escribo en castellano
    te adjunto un archivo con una base de datos access que tiene tu problema solucionado bastante bien.
    Espero que esto te sirva de ayuda.

    Saludos Norberto
    Attached Files Attached Files
    Saludos
    Norberto

  4. #4
    Join Date
    Aug 2003
    Posts
    3
    Thanks for your help !!

  5. #5
    Join Date
    Aug 2003
    Posts
    3
    Noberto excellent demo mdb,
    do you know how to assign the values in the list box demo in 'frmUnboundListClassDemo' to a table?

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Talking

    Este codigo de una sub-rutina te pude servir de guía.

    ***************************************

    Private Sub Confirmar_Click()
    On Error GoTo ControladorErr
    Dim dbs As Database
    Dim rst1 As Recordset
    Dim wrkPredeterminado As Workspace
    Dim Mensaje, Estilo, Título, Respuesta, MiCadena
    Dim ctlSource As Control
    Dim cadItems As String
    Dim entCurrentRow As Integer
    Dim frm As Form

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\GralRoca\U suarios.mdb")
    Set wrkPredeterminado = DBEngine.Workspaces(0)
    wrkPredeterminado.BeginTrans


    Set rst1 = dbs.OpenRecordset("Tbl Usuarios Restricciones", dbOpenTable)
    rst1.Index = "User"

    Set frm = Forms![CuadroDeDialogoMenu]
    Set ctlSource = frm!Lista

    For entCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(entCurrentRow) Then
    cadItems = ctlSource.Column(0, entCurrentRow)
    rst1.Seek "=", Me!Usuario, cadItems
    If rst1.NoMatch Then
    GoTo Alta
    Else
    GoTo Proximo
    End If
    Alta:
    rst1.LockEdits = False
    On Error GoTo ControladorErr
    With rst1
    .AddNew
    !User = Me!Usuario
    !Argument = cadItems

    .Update
    .Bookmark = .LastModified

    End With

    Proximo:
    End If
    Next entCurrentRow

    Fin:
    wrkPredeterminado.CommitTrans

    Cerrar:

    rst1.Close
    Set dbs = Nothing
    DoCmd.Requery "Subformulario Usuarios Restricciones"

    Me!Item.SetFocus

    Exit Sub


    ControladorErr:
    MsgBox "Número de error " & Err.Number & ": " & Err.Description



    Mensaje = "¿Desea Reintentar la Operacion?" ' Define el mensaje.
    Estilo = vbYesNo + vbCritical + vbDefaultButton2 ' Define los botones.
    Título = "Recuperación de Errores" ' Define el título.


    Respuesta = MsgBox(Mensaje, Estilo, Título)

    If Respuesta = vbYes Then ' El usuario eligió el botón Sí.
    MiCadena = "Sí" ' Ejecuta una acción.
    Resume
    Else ' El usuario eligió el botón No.
    wrkPredeterminado.Rollback ' Ejecuta una acción.
    GoTo Cerrar
    End If


    End Sub

    ******************************************

    Espero te sea util
    Norberto
    Saludos
    Norberto

  7. #7
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    In the Afterupdate event of the first combo:

    Code:
    Private Sub Combo1_AfterUpdate()
        Combo2 = Null
        Combo2.Requery
        Requery
    End Sub
    The first line empties the second Combo, the second line regenerates the list of the Combo2, and the third line regenerates the rest of the form, so that if the rest of the form contains a list based on the selection of the two combos, it is refreshed.

    You shoud also add a similar code to the second combo, containing only the third line.
    The only failure is not trying to do it.

Posting Permissions

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