Cascade comboboxes again... :)

[gostap]

Cascade comboboxes again;




Hi!


For a week ago I got some help with comboboxes; grateful for that..
But now I have a new problem/issue that I don’t know to ”attack”


What I want my form and cascade comboboxes to do is the following:


There are 3 comboboxes where I select ”brukare”, ”Assistent” and ”Datum”. These works excellent.

However, in the second combobox, ”Assistent” I want to ”AfterUpdate” have the oppurtunity to print a report based on the same RecordSource as in the subform but I am not really sure how to do that. Tested some variants but It will not work. I get a error message that says ”fault 2465...can’t fiend the Field ”rptIncidSamlat” which is the name of the report(?)



What do I wrong?



Bg//Gostap

[Bob Fitz]

If the report is based on the same table/query that the form is based upon you just need the one line of code:
DoCmd.OpenReport "rptIncidSamlat", acViewPreview, "",Enter the same criteria here as that used on the form

The following link has an example of the criteria required for the WhereCondition argument of DoCmd.OpenReport
https://www.codevba.com/msaccess/docmd_openreport.htm

[gostap]

Thanks for answer, however, the main form has no recordsource. The comboboxes are in the mainform and the results is in the subform

[Bob Fitz]

Thanks for answer, however, the main form has no recordsource. The comboboxes are in the mainform and the results is in the subform

Can you post a copy of the db

[orange]

I agree with Bob we need to see your database with sufficient data and instructions to
-repeat the issue (2465) you are seeing, and
-show us/describe to us WHAT should be happening for a correct situation.

Please avoid jargon, and do provide specifics.

[ssanfu]

You cannot change the report record source while the report is CLOSED.



1) I would set the report record source to "SELECT * FROM qryFiltCombo",
2) then use the WHERE parameter of the DoCmd.OpenReport.
DoCmd.OpenReport rptIncidSamlat, acViewPreview, , strWhere

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

    cboDate = Null

    'the WHERE clause
    strWhere = " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And"
    strWhere = strWhere & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"

    'cboDate row source
    strSQL = "SELECT DISTINCT qryFiltCombo.incDatum FROM qryFiltCombo"
    strSQL = strSQL & strWhere
    strSQL = strSQL & " ORDER BY qryFiltCombo.incDatum;"
    Debug.Print strSQL
    Me.cboDate.RowSource = strSQL

    'set sub form record source
    strSQLSF = "SELECT * FROM qryFiltCombo"
    strSQLSF = strSQLSF & strWhere
    Debug.Print strSQLSF

    Me!frmSubtblIncident.Form.RecordSource = strSQLSF
    Me.Requery


    'Preview report
    DoCmd.OpenReport "rptIncidSamlat", acViewPreview, , strWhere

End Sub

(BLUE text = Edited or added)

[gostap]

Can you post a copy of the db

Sure! by a PM?

[gostap]

Unfortunately iv'e got this message...
error 2497 You must specify the Report Name argument for the action/method (Google translate)

[ssanfu]

If this is directed towards me:

error 2497 You must specify the Report Name argument for the action/method (Google translate)

Sorry, I forgot the quotes around the report name. Corrected now.

Is there actually a report named "rptIncidSamlat"?

[gostap]

Yes, I actually notice the missing qoutes but still the same result...

[gostap]

Says, "
driving error 3075.: Syntax error(operaror missing) Query expression 'Where qryFiltCombo.kund = 'Johan Persson' And qryfiltCombo.Assistant = 'Gösta Persson'"

[ssanfu]

Try adding a Debug.Print line to make sure the SQL for strWhere is properly formed

    'the WHERE clause
    strWhere = " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And"
    strWhere = strWhere & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    Debug.Print strWhere

It would be helpful to have a copy of the current dB (with the report and a few records) for testing...

[Welshgasman]

There is no single quote after Personn?, just a double quote?

[gostap]

Try adding a Debug.Print line to make sure the SQL for strWhere is properly formed

    'the WHERE clause
    strWhere = " WHERE qryFiltCombo.Kund = '" & Me.cboBrukare & "' And"
    strWhere = strWhere & " qryFiltCombo.Assistent = '" & Me.cboAssist & "'"
    Debug.Print strWhere

It would be helpful to have a copy of the current dB (with the report and a few records) for testing...

Here is the stripped databasestripped.zip

[Micron]

There is no single quote after Personn?, just a double quote?

Methinks that is the single quote in the sql which is surrounded by 2 singles, one at the beginning, one at the end.

Gostap, did you test this before uploading? You want it to open a report but it isn't there?
I get a prompt for kund, which is generated by code that tries to set linkmaster and linkchild properties, but there is no bound form to link with the subform. Are you trying to build a search form and open a form or report based on the inputs? If so, why bother with the subform?

EDIT - if you need to see the subform records, then I think you're applying the recordsource and the requery to the wrong form. In cboBrukare event, if I leave in the code to set the linked fields to 'kund' I get an error. If I rem out the problem lines, the subform doesn't show the proper records. However, if I set the subform recordsource as in

Me.frmSubtblIncident.Form.RecordSource = strSQLSF

then that part works. I don't see the point in assigning a recordsource to your search form and requerying it (you're using Me, which refers to the main form in this case, not the subform).