What have I done Wrong? form to report adventures.

[tassiegal]

Using advice from the very helpful people on this forum and some other sources I have managed to get this far with my form to report adventures. I have written this bit of VB code which SHOULD run when I hit the button that says generate report. The report should generate based on the answers to drop down boxes in the form. It works fine if the form is blank. However as soon as I try to limit it I get a "Data type mismatch in criteria expression" on the last line before the End If End Sub lines. Anything with frm in it is the dropdown value from the form. I THINK the problem is the WhereCondition:=Mid(strWhere, 6) but I dont know how to fix it. Any suggestions? THANKS! Zelda

Guess I should add there are 6 tables - the one I am trying to generate the report from basically calls on all the other tables to get filled out. So I have Investigator, Site, Length, Discipline, StudentParticipation, and Project. The only two unique columns in project (besides its identifier) are Project_Name and Project_Description. The Investigators Column in project is based on a join query to join first and last name from the Investigator table.

Private Sub CmdRunReport_Click()
Dim strWhere As String 'String variable to store where clause

If Len(Me.Frmsite & "") > 0 Then
strWhere = strWhere & " And Project.location = '" & Me.Frmsite & "'"
End If

If Len(Me.StPFrm & "") > 0 Then
strWhere = strWhere & " And project.Student_Participation = '" & Me.StPFrm & "'"
End If

If Len(Me.FrmName & "") > 0 Then
strWhere = strWhere & " And project.Investigators = '" & Me.FrmName & "'"
End If

If Len(Me.FrmDiscp & "") > 0 Then
strWhere = strWhere & " And project.Discipline = '" & Me.FrmDiscp & "'"
End If

If Len(Me.FrmLength & "") > 0 Then
strWhere = strWhere & " And Project.length = '" & Me.FrmLength & "'"
End If

'If Me.chkFinal = True Then
' strWhere = strWhere & " AND startedFrm=true"
' End If

If Len(strWhere & "") = 0 Then
' no options selected. Open report with no where condition
DoCmd.OpenReport "ReportProject", acViewPreview
Else
' remove first "AND" from where condition and pass across to report
DoCmd.OpenReport "ReportProject", acViewPreview, WhereCondition:=Mid(strWhere, 6)
End If

End Sub

 

[Galaxiom]

As you have posted i, all the values in the Where would have to be text strings.

Remove the single quotes from any that are numbers.

 

[vbaInet]

... and here's how you should have written the first two blocks of code:

If Len(Me.Frmsite & "") > 0 Then
    strWhere = "Project.location = '" & Me.Frmsite & "'"
End If

If Len(Me.StPFrm & "") > 0 Then
    if len(strWhere) <> 0 Then
        strWhere = strWhere & " And project.Student_Participation = '" & Me.StPFrm & "'"
    else
        strWhere = "project.Student_Participation = '" & Me.StPFrm & "'"
    end if
End If

Use the second block of code as a template for the others.

 

[tassiegal]

As you have posted i, all the values in the Where would have to be text strings.

Remove the single quotes from any that are numbers.

They are strings in the drop down boxes, BUT their unique identifiers are numbers....so should I be searching by the ID numbers NOT the strings?

 

[vbaInet]

In Galaxiom's absence, yes you should.

 

[tassiegal]

In Galaxiom's absence, yes you should.

Hmmmm - this means a bit of tweaking I think.....I will need to mask the drop down boxes so that the user sees the words not the numbers, and then feed that into a query and then into the report.
Is this thinking right? Or have I gone and overcomplicated it again?

 

[vbaInet]

You're on the right path. You can hide the first column of a combo box (or listbox) by setting the Column Widths property of that column to be 0cm. So let's say you have two fields, ProjectID and ProjectName where ProjectID is the field you wish to hide, the Column Width will be set to:

0cm;2cm

2cm can be anything you wish. The Column Count property will also need to be set to 2 which indicates two columns.

 

[Galaxiom]

... and here's how you should have written the first two blocks of code:

If Len(Me.Frmsite & "") > 0 Then
    strWhere = "Project.location = '" & Me.Frmsite & "'"
End If
 
If Len(Me.StPFrm & "") > 0 Then
    if len(strWhere) <> 0 Then
        strWhere = strWhere & " And project.Student_Participation = '" & Me.StPFrm & "'"
    else
        strWhere = "project.Student_Participation = '" & Me.StPFrm & "'"
    end if
End If

Use the second block of code as a template for the others.

I am not so sure. This code and the original code are just two variants of the plethora of ways that a where clause can be concatenated with coordinators (AND/OR).

Personally I prefer the OPs approach of including the coordinator in every step and removing the superfluous one at the end. Another variant includes them at the end of each subclause and removes the last one.

It makes for much tidier code than the clutter of checking the length each time and deciding if a coordinator is required. It would be several nanoseconds faster too.

 

[tassiegal]

You're on the right path. You can hide the first column of a combo box (or listbox) by setting the Column Widths property of that column to be 0cm. So let's say you have two fields, ProjectID and ProjectName where ProjectID is the field you wish to hide, the Column Width will be set to:

0cm;2cm

2cm can be anything you wish. The Column Count property will also need to be set to 2 which indicates two columns.

Ok....so I set up my form - setting the unbound combo boxes to display the strings but not the identifiers from a query NOT the table?.

From that I pass the unseen identifier back to that bit of VBA coding I have above to generate my report? Or do I have to pass the identifier back to a query and the query answer to the report?

The table I am basing my report off only has 2 columns which are not called from other tables or queries. All the others are from tables EXCEPT the investigator column which is a query based column to give first and last names together as opposed to separate.

Thanks for being so patient with me. I am slowing starting to get this.

 

[Galaxiom]

The Bound Column of the combo needs to tbe the ID number column. Then the references to the combo will return that number rather than the text as displayed.

Your existing code references to the combos will then get that number. Just remove the quotes for the ones that refer to an ID number rather than text.

 

[tassiegal]

The Bound Column of the combo needs to be the ID number column. Then the references to the combo will return that number rather than the text as displayed.

Your existing code references to the combos will then get that number. Just remove the quotes for the ones that refer to an ID number rather than text.

I am getting slightly confused again. The code as I have it (even though the reports don't utilise the ID number) should generate the report I want even though I don't have those ID's in the report just the words?

 

[Galaxiom]

The changes to the selector code only affects the Where condition and hence the records that are chosen. it won't affect the design of your report at all.

BTW There are two ways to put the text into the report via the RecordSource query. One returns the ID number and uses a combo on the report to display the name exactly as you have now done for the selector combos. The other uses a join to return the text to be dispayed in the textbox. I get te impression you are using the latter.

 

[vbaInet]

I am not so sure. This code and the original code are just two variants of the plethora of ways that a where clause can be concatenated with coordinators (AND/OR).

Personally I prefer the OPs approach of including the coordinator in every step and removing the superfluous one at the end. Another variant includes them at the end of each subclause and removes the last one.

It makes for much tidier code than the clutter of checking the length each time and deciding if a coordinator is required. It would be several nanoseconds faster too.

I didn't even notice tassiegal's good coding skills indicated below. Thanks for pointing that out. I do that sometimes too.

If Len(strWhere & "") = 0 Then
' no options selected. Open report with no where condition
DoCmd.OpenReport "ReportProject", acViewPreview
Else
' remove first "AND" from where condition and pass across to report
DoCmd.OpenReport "ReportProject", acViewPreview, WhereCondition:=Mid(strWhere, 6)
End If

End Sub

 

[tassiegal]

Not my good coding skills, my good cut and paste skills. But coding has helped me work out WHAT each line does.

 

[vbaInet]

Not my good coding skills, my good cut and paste skills.

A good skill all the same, except that most of the time it doesn't compile.

Regarding your OP, do you now understand what we're talking about?