Select case Statement for a Report

G

Guest

I have a form that I am using to select a field and a corresponding value
based on that field. cboboxselectvalue and cboselectfield.

cboselectvalue uses a value list as a recordsource.
cboselectfield uses a query as a record source

The cboselectfieldvalue datatype are number,date,and text IF the user
selects a number OR date datatype, opens the report,views the data, and
closes the report and then selects a text datatype a "The value you entered
isn.t valid for this field error is generated"

If the user opens the form and selects a textdatatype first and goes trough
the same process ALL cboselectvalues can be used with no problems. WHY? My
guess is that when the repor tis closed and a new value entered the report is
still holding hte filtered value used prior to closing but that is only a
guess. My code is below for the Printpreview click event:

Private Sub CmdPreviewReport_Click()
Dim Strwhere As String
Dim blCombo2 As Boolean
'As you have differing field types, a Select Case is
'what I decided to use to ensure your "string" is properly built
'This IS
'"& Me!Number & ""
''" & Me!Text & "'"
'#" & Me!Date & "#"
Select Case Me!cboSelectField
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Employee Name"
Strwhere = "[EmployeeName] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Employee Assigned"
Strwhere = "[EmployeeAssigned] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Date Resolved"
Strwhere = "[DtResolved] = # " & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Status"
Strwhere = "[Status] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Priority"
Strwhere = "[PriorityField] = " & Me!cboSelectValue & "'"
blCombo2 = True
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
'Stop
' Case "Manager"
'Strwhere = "[Manager] = '" & Me!cboSelectValue & "'"
'blCombo2 = True

Case "Report All"
Strwhere = ""

End Select

DoCmd.OpenReport "rptTickets", acPreview, , Strwhere
DoCmd.Maximize
Me.cboSelectField = ""
Me.cboSelectValue = ""
With Me
.cboSelectValue.Value = ""
.cboSelectField.Value = ""

End With

Exit_Preview_Click:
Exit Sub

End Sub
 
S

Steve Schapel

John,

I am not sure specifically of the cause of the problem, but a few
comments about the code...

1. You have a couple of the values repeated in the Case items, e.g.
TicketID and Contact Date.

2. At the end, you twice set the value of the comboboxes to "". First
of all, you only need to do it once :) and also use Null, i.e....
Me.cboSelectField = Null
Me.cboSelectValue = Null

3. You don't appear to have the syntax for numerical fields correct.
For example, it should be...
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue
.... i.e. no trailing "s

4. You don't appear to have the syntax for Priority correct. If
Priority is numerical, it should be...
Case "Priority"
Strwhere = "[PriorityField] = " & Me!cboSelectValue
.... If Priority is text, it should be...
Case "Priority"
Strwhere = "[PriorityField] = '" & Me!cboSelectValue & "'"

Try tidying these up, and then post back with more details if it is
still misbehaving.

--
Steve Schapel, Microsoft Access MVP

I have a form that I am using to select a field and a corresponding value
based on that field. cboboxselectvalue and cboselectfield.

cboselectvalue uses a value list as a recordsource.
cboselectfield uses a query as a record source

The cboselectfieldvalue datatype are number,date,and text IF the user
selects a number OR date datatype, opens the report,views the data, and
closes the report and then selects a text datatype a "The value you entered
isn.t valid for this field error is generated"

If the user opens the form and selects a textdatatype first and goes trough
the same process ALL cboselectvalues can be used with no problems. WHY? My
guess is that when the repor tis closed and a new value entered the report is
still holding hte filtered value used prior to closing but that is only a
guess. My code is below for the Printpreview click event:

Private Sub CmdPreviewReport_Click()
Dim Strwhere As String
Dim blCombo2 As Boolean
'As you have differing field types, a Select Case is
'what I decided to use to ensure your "string" is properly built
'This IS
'"& Me!Number & ""
''" & Me!Text & "'"
'#" & Me!Date & "#"
Select Case Me!cboSelectField
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Employee Name"
Strwhere = "[EmployeeName] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Employee Assigned"
Strwhere = "[EmployeeAssigned] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Date Resolved"
Strwhere = "[DtResolved] = # " & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Status"
Strwhere = "[Status] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Priority"
Strwhere = "[PriorityField] = " & Me!cboSelectValue & "'"
blCombo2 = True
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
'Stop
' Case "Manager"
'Strwhere = "[Manager] = '" & Me!cboSelectValue & "'"
'blCombo2 = True

Case "Report All"
Strwhere = ""

End Select

DoCmd.OpenReport "rptTickets", acPreview, , Strwhere
DoCmd.Maximize
Me.cboSelectField = ""
Me.cboSelectValue = ""
With Me
.cboSelectValue.Value = ""
.cboSelectField.Value = ""

End With

Exit_Preview_Click:
Exit Sub

End Sub
Click to expand...
 
G

Guest

Steve,

Thank You for the reply I have changed (corrected the code with your
changes) and still have the same problem
1. Open form
2. Select Either a date field OR a Numeric Field
3. close the report
4. Select a Text Datatype (This is where the error occurs "The Value You
entered isn't valid for this field"

Appreciate ANY additional help you can give me on this as I have been
struggling with it for the past 4 days. I am not sure but I am thinking it
could be that the filter is not being cleared from the report after closing
and selecting another datatype. Hence the eror being generated. It still is
holding the Numeric or date datatype and cannot filter the text Datatype. I
have even tried to use Apply filter = false on the onclose Event of the
report but have the same problem.

Thank You Again!
Steve Schapel said:
John,

I am not sure specifically of the cause of the problem, but a few
comments about the code...

1. You have a couple of the values repeated in the Case items, e.g.
TicketID and Contact Date.

2. At the end, you twice set the value of the comboboxes to "". First
of all, you only need to do it once :) and also use Null, i.e....
Me.cboSelectField = Null
Me.cboSelectValue = Null

3. You don't appear to have the syntax for numerical fields correct.
For example, it should be...
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue
.... i.e. no trailing "s

4. You don't appear to have the syntax for Priority correct. If
Priority is numerical, it should be...
Case "Priority"
Strwhere = "[PriorityField] = " & Me!cboSelectValue
.... If Priority is text, it should be...
Case "Priority"
Strwhere = "[PriorityField] = '" & Me!cboSelectValue & "'"

Try tidying these up, and then post back with more details if it is
still misbehaving.

--
Steve Schapel, Microsoft Access MVP

I have a form that I am using to select a field and a corresponding value
based on that field. cboboxselectvalue and cboselectfield.

cboselectvalue uses a value list as a recordsource.
cboselectfield uses a query as a record source

The cboselectfieldvalue datatype are number,date,and text IF the user
selects a number OR date datatype, opens the report,views the data, and
closes the report and then selects a text datatype a "The value you entered
isn.t valid for this field error is generated"

If the user opens the form and selects a textdatatype first and goes trough
the same process ALL cboselectvalues can be used with no problems. WHY? My
guess is that when the repor tis closed and a new value entered the report is
still holding hte filtered value used prior to closing but that is only a
guess. My code is below for the Printpreview click event:

Private Sub CmdPreviewReport_Click()
Dim Strwhere As String
Dim blCombo2 As Boolean
'As you have differing field types, a Select Case is
'what I decided to use to ensure your "string" is properly built
'This IS
'"& Me!Number & ""
''" & Me!Text & "'"
'#" & Me!Date & "#"
Select Case Me!cboSelectField
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Employee Name"
Strwhere = "[EmployeeName] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Employee Assigned"
Strwhere = "[EmployeeAssigned] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Date Resolved"
Strwhere = "[DtResolved] = # " & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
Case "Status"
Strwhere = "[Status] = '" & Me!cboSelectValue & "'"
blCombo2 = True
'Stop
Case "Priority"
Strwhere = "[PriorityField] = " & Me!cboSelectValue & "'"
blCombo2 = True
Case "Ticket ID"
Strwhere = "[TicketID] = " & Me!cboSelectValue & ""
blCombo2 = True
'Stop
Case "Contact Date"
Strwhere = "[Contactdt] = #" & Me!cboSelectValue & "#"
blCombo2 = True
'Stop
'Stop
' Case "Manager"
'Strwhere = "[Manager] = '" & Me!cboSelectValue & "'"
'blCombo2 = True

Case "Report All"
Strwhere = ""

End Select

DoCmd.OpenReport "rptTickets", acPreview, , Strwhere
DoCmd.Maximize
Me.cboSelectField = ""
Me.cboSelectValue = ""
With Me
.cboSelectValue.Value = ""
.cboSelectField.Value = ""

End With

Exit_Preview_Click:
Exit Sub

End Sub
Click to expand...
Click to expand...
 
S

Steve Schapel

John,

On the After Update event of cboSelectField, do you have a statement
like?...
Me.cboSelectValue.Requery

At which point in the process does this error occur? Am I correct in
understandijng this is when you try to select another item in
cboSelectField?

What is the purpose of the blCombo2 variable in your code?
 
G

Guest

Steve,

Thanks again for the reply. In answer to your questions/s
Yes I do have something simular in the After Update Event below is the start
of the Module and your answer at the bottom:

Private Sub cboselectfield_AfterUpdate()
Dim strSQL As String
'Based on the field the user wants to filter on
'set the rowsource property of the Value combobox
Select Case Me!cboSelectField
Case "Ticket ID"
strSQL = "SELECT DISTINCT [TicketID] FROM qryClosedTickets ORDER BY
[TicketID]"
Case "Contact Date"
strSQL = "SELECT DISTINCT [contactDt]FROM qryClosedTickets ORDER BY
[ContactDt]"
More code……

Case "Report All"
Me!cboSelectValue.Enabled = False
Me!cboSelectValue = Null '**********************************
Me!cboSelectValue.RowSource = ""
End Select

If Me!cboSelectField <> "Report All" Then
Me!cboSelectValue.Enabled = True
Me!cboSelectValue = Null '**********************************
Me!cboSelectValue.RowSource = strSQL
Me!cboSelectValue.Requery
Me!cboSelectValue.SetFocus
End If
End Sub

"Am I correct in
understanding this is when you try to select another item in
cboSelectField?"

Yes


The error occurs AFTER the user has selected for example TicketID,opens the
report in preview,closes the report,and reenters another value for example
EmployeeName. The form is NEVER closed If the user was to select one value,
preview OR print the report and close the form there are no problems it is
when they want to view other selected field values from the selections.

"What is the purpose of the blCombo2 variable in your code?"

Really it is serving No purpose I have Commented it out and there is no
change to the problem
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Slow-Running Sub 13
Report Preview and RTF discrepancy 2
Filter Report with a Form 1
Filter on Recordset not working 2
Problem using Select Case 6
Limiting a Report to a Date Range 6
Select Current Record for Report 2
Help with one of allen browne's tips for limiting a report to dates 6

Top