Data Sheet Sub Form with Combo Boxes 2

[cparralesl]

Hi everyone,

I have a datasheet sub form with a couple of combo boxes, one depending on the value of the other one. The mian combo box is unbounded, but it has a field on the table where to store the its value (DataSource control), and the other one takes its values from a table and depending on the value of the main combo box.

The problem I'm having is about refreshment, I think. I can see the whole list in the datasheet, with the values selected in the main combo box, but the dependant combo box does not shows nothing, it's empty. The datasource table of the dependant combo box has a field that refres to the main combo box too.

If the user select one option in the main combo box, the dependant combo box has to show certains groups of values and the use have to select one, and so on.

To solve that i used the following code at the AfterUpdate Event:

    Me.ID_ITEMSERVICIO = Null
    Me.ID_ITEMSERVICIO.Requery
    If (Me.ID_ITEMSERVICIO.ListIndex = -1) Or (Me.ID_ITEMSERVICIO.ListCount = 0) Then
        Me.ID_ITEMSERVICIO = Me.ID_ITEMSERVICIO.ItemData(0)
    Else
        Me.ID_ITEMSERVICIO = Me.ID_ITEMSERVICIO.ItemsSelected(Me.ID_ITEMSERVICIO.ListIndex)
    End If

But it doesn't has the behavior i want. I wan to see in the DataSheet Sub Form the whole list with the values selected by the user in the unbound combo box and the dependendat one. And if the user add a new record or change the value of one that exists already always shows the values.

After use the code above, it refrsh som values, not the whole list of the datasheet. And when the form is open some values of the dependant combo box appears blank.

I really apprecite the help you can give me about it.

Regards,


Cesar Humberto Parrales
Application Support

 

[dhookom]

Can you provide the actual combo box names as well as their Row Sources and any code that might modify any of their properties?

Are you sure your main combo box is unbound? This would suggest that it is unbound. If it is unbound, every record in the datasheet will display exactly the same.

Duane
Hook'D on Access
MS Access MVP

 

[cparralesl]

The subform have to store 3 values only: The field link from the main form, tHE service category and the lastone have to store the service name. There values have to be from one specific group of options, so I use combo boxes. The main and unbound combo box name is ID_SERVICIO and the the dependant field name is ID_ITEMSERVICIO. As I said, the other field is the link child field and its name is ID_SUBINDICADOR. Those names are the same in the tables of the subform

I say the main combo box is unbound because I typed the list of values it has. The second combo box takes its values from a query.

Row Sources:

ID_SERVICE = 1;"Training";2;"Medial Care";3;"Denounces";4;"Call Center"

ID_ITEMSERVICE = SELECT Temas.ID_TEMA, Temas.NOMBRE_TEMA FROM Temas INNER JOIN Estrategias ON Temas.ID_ESTRATEGIA=Estrategias.ID_ESTRATEGIA WHERE (((Estrategias.ID_SERVICE)=[Forms]![Componentes]![SFIndicadoresxComponentes].[Form]![SFIndicadoresSubxComponentes].[Form]![SFServxSubIndicadores].[Form]![ID_SERVICE]));

The code I use currently to refesh te options selected is

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If (Len(Me.ID_SERVICIO) = 0) Or (IsNull(Me.ID_SERVICIO)) And _
       (Len(Me.ID_ITEMSERVICIO) = 0) Or (IsNull(Me.ID_ITEMSERVICIO)) Then
       MsgBox "No se permiten registro incompletos o errados.", vbOKOnly + vbCritical, "SICEP"
        Cancel = True
    End If
End Sub

Private Sub Form_Current()
    If Not (Me.NewRecord) Then
        Me.ID_SERVICIO = Me.ID_SERVICIO.ItemData(0)
        Me.ID_ITEMSERVICIO.Requery
    Else
        Me.ID_SERVICIO.Requery
        Me.ID_ITEMSERVICIO.Requery
    End If
End Sub

Private Sub ID_SERVICIO_AfterUpdate()
    Call ActualizaItemdeServicio
End Sub

Private Sub ID_SERVICIO_Change()
    Call ActualizaItemdeServicio
End Sub
Private Sub ActualizaItemdeServicio()
    Me.ID_ITEMSERVICIO = Null
    Me.ID_ITEMSERVICIO.Requery
    If (Me.ID_ITEMSERVICIO.ListIndex = -1) Or (Me.ID_ITEMSERVICIO.ListCount = 0) Then
        Me.ID_ITEMSERVICIO = Me.ID_ITEMSERVICIO.ItemData(0)
    Else
        Me.ID_ITEMSERVICIO = Me.ID_ITEMSERVICIO.ItemsSelected(Me.ID_ITEMSERVICIO.ListIndex)
    End If
    'Me.Requery
End Sub

The combo box ID_ITEMSERVICIO shwos nothing whe the main form is open or when select an option in the main combo box different of those already exists en the same sub form.

I will appreciate your help about it,

Regards,

Cesar Humberto Parrales
Application Support

 

[cparralesl]

I forgot to tell. From time to time show up a pop up window asking for the value for ID_SERVICIO.


Cesar Humberto Parrales
Application Support

 

[dhookom]

You have a criteria in the second combo box linked to the first combo box. You will never see values in the second box when the first combo box value is not the same as the current record in the subform.

If this describes your issue, I typically don't totally remove the non-matching records. I set the Row Source of the second box to move the un-matching records to the bottom of the list.

Duane
Hook'D on Access
MS Access MVP

 

[cparralesl]

Hi dhookom!

You are right! That describes my issue!

I'll appreciate the sample code you can give me to apply it in my form.

I give you a star 'cause I never thought that way, so your advice is helpful for me.

Best Regards,

Cesar Humberto Parrales
Application Support

 

[MajP]

You can fake this pretty well by placing a textbox over the second combo box and cover everything but the drop down arrow. Then use the combo to select, and textbox to display. It will look like a combo. This only works if you limit the user to the list. Here is a simple demo. There is a textbox stacked on the second combo

http://www.4shared.com/document/lcZzWZ51/CascadingComboContinousForm.html

 

[dhookom]

MajP,
That's a good solution if the OP is willing to switch from "datasheet sub form" to continuous.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Yeah, I did not notice the datasheet.

 

[cparralesl]

MajP:

That was very useful form my continous forms that i already have. I give you a star.

Best Regards,

Cesar Humberto Parrales
Application Support