cascade comboboxes

**gostap** · 08-24-2021, 04:53 AM

Help!

Please need some help! Hope you can understand my english!

I have a form with 3 cascade comboboxes. Something seems to wrong in the code. If I select first one it works properly, iI do have the selected record in second box.
After selection in second box, I can see the right records in third box, But,..in the subform list ii will not show, just viewing ”new record”

Someone have ideas what I have done wrong?

Gratefully//Gostap

Private Sub cboAssist_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboDate = Null

strSQL = " SELECT DISTINCT qryFiltCombo.Datum FROM qryFiltCombo "
strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & cboBrukare & "' And "
strSQL = strSQL & " qryFiltCombo.Assistent = '" & cboAssist & "'"
strSQL = strSQL & " ORDER BY qryFiltCombo.Datum;"

cboDate.RowSource = strSQL

strSQLSF = " SELECT * FROM qryFiltCombo "
strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & cboBrukare & "' And "
strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & cboAssist & "'"

Me!frmSubtblIncident.LinkChildFields = ""
Me!frmSubtblIncident.LinkMasterFields = ""

Me!frmSubtblIncident.LinkChildFields = "Kund;Assistent"
Me!frmSubtblIncident.LinkMasterFields = "Kund;Assistent"
Me.RecordSource = strSQLSF
Me.Requery
End Sub

Private Sub cboBrukare_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboAssist = Null
cboDate = Null

strSQL = "SELECT DISTINCT qryFiltCombo.Assistent FROM qryFiltCombo "
strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & cboBrukare & "'"
strSQL = strSQL & " ORDER BY qryFiltCombo.Assistent;"

cboAssist.RowSource = strSQL

strSQLSF = "SELECT * FROM qryFiltCombo "
strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & cboBrukare & "'"

Me!frmSubtblIncident.LinkChildFields = "Kund"
Me!frmSubtblIncident.LinkMasterFields = "Kund"
Me.RecordSource = strSQLSF
Me.Requery
End Sub

Private Sub cboDate_AfterUpdate()
Dim strSQLSF As String

strSQLSF = " SELECT * FROM qryFiltCombo "
strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & cboBrukare & "' And "
strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & cboAssist & "' And "
strSQLSF = strSQLSF & " qryFiltCombo.Datum = " & cboDate

Me!frmSubtblIncident.LinkChildFields = ""
Me!frmSubtblIncident.LinkMasterFields = ""

Me!frmSubtblIncident.LinkChildFields = "Kund;Assistent;Datum"
Me!frmSubtblIncident.LinkMasterFields = "Kund;Assistent;Datum"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Click image for larger version.

Name: overview.JPG
Views: 34
Size: 57.8 KB
ID: 46042

Click image for larger version.

Name: 1stselec.JPG
Views: 37
Size: 42.7 KB
ID: 46043

Click image for larger version.

Name: lastselect.JPG
Views: 37
Size: 44.5 KB
ID: 46044

Click image for larger version.

Name: designCboAndSubform.JPG
Views: 35
Size: 62.8 KB
ID: 46045

**Gicu** · 08-24-2021, 09:01 AM

Hard to say without seeing your form. You are assigning the strSQLSF variable to the main form recordsource but I think the main form should be unbound and the variable should be used as the recordsource for the subform (and leave out the LinkChildFields\LinkMasterFields everywhere).

Cheers,

**Welshgasman** · 08-24-2021, 10:09 AM

Why not Debug.Print strSQLSF and see what you actually have.?
Might need # around the date.?

**gostap** · 08-24-2021, 12:06 PM

Originally Posted by Gicu

Hard to say without seeing your form. You are assigning the strSQLSF variable to the main form recordsource but I think the main form should be unbound and the variable should be used as the recordsource for the subform (and leave out the LinkChildFields\LinkMasterFields everywhere).

Cheers,

Thanks for answer, I will try that...been thinking about myself..

**gostap** · 08-24-2021, 03:06 PM

Originally Posted by Gicu

Hard to say without seeing your form. You are assigning the strSQLSF variable to the main form recordsource but I think the main form should be unbound and the variable should be used as the recordsource for the subform (and leave out the LinkChildFields\LinkMasterFields everywhere).

Cheers,

I tried to use unbound form...that works but however I need the Indexkey? (To commad further actions such openreport)

**ssanfu** · 08-24-2021, 04:22 PM

It looks like you have a field named "Datum" (Date)? "Date" is a reserved word in Access and a built in function and shouldn't be used for object names.

If the Main form is unbound, you don't set the Master/Child link fields. Just change the sub form record source.

I would like to have a copy of your dB for testing, but maybe this code will work.

Code:

Private Sub cboBrukare_AfterUpdate()  'user

    Dim strSQL As String
    Dim strSQLSF As String

    cboAssist = Null
    cboDate = Null

    ' set cboAssist combo box row source
    strSQL = "SELECT DISTINCT qryFiltCombo.Assistent FROM qryFiltCombo "
    strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "'"
    strSQL = strSQL & " ORDER BY qryFiltCombo.Assistent;"

    Me.cboAssist.RowSource = strSQL

    ' set sub form record source
    strSQLSF = "SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "'"
    Debug.Print "cboBrukare = " & strSQLSF
    
    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery
    
End Sub
'------------------------------------------------------------------------------------

Private Sub cboAssist_AfterUpdate()   'assistant
    Dim strSQL As String
    Dim strSQLSF As String

    cboDate = Null

    ' set cboDate combo box row source
    strSQL = " SELECT DISTINCT qryFiltCombo.Datum FROM qryFiltCombo "
    strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQL = strSQL & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    strSQL = strSQL & " ORDER BY qryFiltCombo.Datum;"
    
    Me.cboDate.RowSource = strSQL

    ' set sub form record source
    strSQLSF = " SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    Debug.Print "cboAssist = " & strSQLSF

    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery
    
End Sub
'------------------------------------------------------------------------------------

Private Sub cboDate_AfterUpdate()    ' Date
    Dim strSQLSF As String

    ' set sub form record source
    strSQLSF = " SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & Me.cboAssist & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Datum = #" & Me.cboDate & "#"
    Debug.Print "cboDate = " & strSQLSF
    
    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery

End Sub

Originally Posted by gostap

I tried to use unbound form...that works but however I need the Indexkey? (To commad further actions such openreport)

Can't you reference the sub form to get the PK field (IncID)?

**gostap** · 08-25-2021, 04:21 AM

Originally Posted by ssanfu

It looks like you have a field named "Datum" (Date)? "Date" is a reserved word in Access and a built in function and shouldn't be used for object names.

If the Main form is unbound, you don't set the Master/Child link fields. Just change the sub form record source.

I would like to have a copy of your dB for testing, but maybe this code will work.

Code:

Private Sub cboBrukare_AfterUpdate()  'user

    Dim strSQL As String
    Dim strSQLSF As String

    cboAssist = Null
    cboDate = Null

    ' set cboAssist combo box row source
    strSQL = "SELECT DISTINCT qryFiltCombo.Assistent FROM qryFiltCombo "
    strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "'"
    strSQL = strSQL & " ORDER BY qryFiltCombo.Assistent;"

    Me.cboAssist.RowSource = strSQL

    ' set sub form record source
    strSQLSF = "SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "'"
    Debug.Print "cboBrukare = " & strSQLSF
    
    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery
    
End Sub
'------------------------------------------------------------------------------------

Private Sub cboAssist_AfterUpdate()   'assistant
    Dim strSQL As String
    Dim strSQLSF As String

    cboDate = Null

    ' set cboDate combo box row source
    strSQL = " SELECT DISTINCT qryFiltCombo.Datum FROM qryFiltCombo "
    strSQL = strSQL & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQL = strSQL & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    strSQL = strSQL & " ORDER BY qryFiltCombo.Datum;"
    
    Me.cboDate.RowSource = strSQL

    ' set sub form record source
    strSQLSF = " SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    Debug.Print "cboAssist = " & strSQLSF

    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery
    
End Sub
'------------------------------------------------------------------------------------

Private Sub cboDate_AfterUpdate()    ' Date
    Dim strSQLSF As String

    ' set sub form record source
    strSQLSF = " SELECT * FROM qryFiltCombo "
    strSQLSF = strSQLSF & " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Assistent = '" & Me.cboAssist & "' And "
    strSQLSF = strSQLSF & " qryFiltCombo.Datum = #" & Me.cboDate & "#"
    Debug.Print "cboDate = " & strSQLSF
    
    Me!frmSubtblIncident.RecordSource = strSQLSF
    Me.Requery

End Sub

Can't you reference the sub form to get the PK field (IncID)?

Hi, and thanks a lof for your help

Unfortunately I gey this messages:

Click image for larger version.

Name: code438.JPG
Views: 27
Size: 54.3 KB
ID: 46054

Click image for larger version.

Name: object doesn't support propertyORmethod.JPG
Views: 28
Size: 66.5 KB
ID: 46055

Maybe I could send you the database??

Thansk in advance//Gostap

**Welshgasman** · 08-25-2021, 05:22 AM

Well for a start you are not spelling Brukar/Brukare consistently? :-(

DO you have Option Explicit in all your modules.?

What does the Debug.Print show.?

Note, most of us do NOT speak your language, so would would need to translate the errors messages?

This is what I have for one of my subforms, code is in the mainform.

Code:

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qryEmails"
    Me.sfrmEmails.SourceObject = "cfrmEmails"
    Set Me.sfrmEmails.Form.Recordset = Me.Recordset
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.Maximize
    
End Sub

Notice I am using the Form.recordset of the subform control?
Notice I had to use Set also ?

**gostap** · 08-25-2021, 06:31 AM

Originally Posted by Welshgasman

Well for a start you are not spelling Brukar/Brukare consistently? :-(

DO you have Option Explicit in all your modules.?

What does the Debug.Print show.?

Note, most of us do NOT speak your language, so would would need to translate the errors messages?

This is what I have for one of my subforms, code is in the mainform.

Code:

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qryEmails"
    Me.sfrmEmails.SourceObject = "cfrmEmails"
    Set Me.sfrmEmails.Form.Recordset = Me.Recordset
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.Maximize
    
End Sub

Notice I am using the Form.recordset of the subform control?

Notice I had to use Set also ?

Sorry, Brukare are spelling the same. The attached picture is just not wide enough..

The message say's "The object doesn't support the property or the method"

**Welshgasman** · 08-25-2021, 06:44 AM

Yes, I had a guess at 438.

So try my method?
The error is telling you whatever frmSubTblIncident is, it does not have a recordsource property? If that is the subform control name, then use my syntax.?

Would help if you also copied and pasted the result of the Debug.Print for strSQLSF ?

**Gicu** · 08-25-2021, 09:32 AM

Also try this please:

Code:

Me!frmSubtblIncident.Form.RecordSource = strSQLSF

Cheers,

**ssanfu** · 08-25-2021, 01:32 PM

Maybe you would post your dB for testing?

**gostap** · 08-25-2021, 01:54 PM

Originally Posted by ssanfu

Maybe you would post your dB for testing?

Absolutely! Will just restore it it was from beginning

**gostap** · 08-25-2021, 03:35 PM

Here is the database. Whar doesn't work is a cascade combobox in "huvuudmeny" in tag "rapporter och utskrifter"//Filtrerade Incidenter. When I select in the comboboxes all works fine, but in the last combox "datum", when I select a Date then the subform goes to "new record"? I can't find out what I have done wrong?

Hmmm, seems that I cant upload the file? How do I manage that? When I attach file I cant see them??

**gostap** · 08-25-2021, 03:37 PM

Originally Posted by ssanfu

Maybe you would post your dB for testing?

how can I do that??

cascade comboboxes

Thread Tools

cascade comboboxes

Similar Threads

Cascade Forms

Revese a cascade comboboxes

3 Comboboxes each need to update form, 2 Comboboxes also need to be able to filter by other combobox

Relationship Cascade help

Cascade of 3 or more ComboBoxes

Posting Permissions