If statement to open in different forms

[UT227]

I have a form [frmSupervisors]. On it, I have a list box, [lstUnapprovedRpts]. Here's the SQL to it: SELECT [ReportID], [ReportTypeID], [ReportType], [OccDate], [BldgName], [FullName] FROM qryRPTUnapproved ORDER BY [OccDate], [FullName];

I'm trying to double click the list box and have it open them in a particular form. If [ReportTypeID]=2, I need it to open it up in [frmRptOR], If [ReportTypeID]=3, I need it to open it up in [frmRptSecurity]. If [ReportTypeID]=4, I need it to open it up in [frmRptAlarm]. All them need to go to the record, [ReportID].

I figure that I need an if statement. I just can't figure it out.

[pbaldy]

You could use If/ElseIf, I'd probably use Select/Case. In each test, set a variable to the desired form name, then after use that variable with OpenForm. You could also have a table that related the ID to the name.

[UT227]

I started off with just an if statement

Private Sub lstUnapprovedRpts_DblClick(Cancel As Integer)
If [ReportTypeID] = 4 Then
DoCmd.OpenForm "frmRptAlarm", , , "[ReportID] = [ReportID]"
End If

End Sub

I got this error:



Figure I should fix it from the beginning

[pbaldy]

You have to refer to the listbox, perhaps including column, not a field name returned by the listbox.

[June7]

Refer to the listbox to get the report type. What column has the type number? Also, must concatenate the ReportID parameter.

If Me.lstUnapprovedRpts = 4 Then
     DoCmd.OpenForm "frmRptAlarm", , , "ReportID=" & Me.ReportID
EndIf

[pbaldy]

Oh, and the wherecondition syntax needs tweaking:

http://www.baldyweb.com/wherecondition.htm

[UT227]

It got past the first step. But it won't open the form. I'm getting an error:

[pbaldy]

The form has that field in its record source? While in debug mode, hover over the form reference and see what value it contains.

[isladogs]

Two questions
1. Is ReportID a number field? If not the code needs tweaking
2. Are you aware that the first column in a listbox is column(0) so column(1) is the 2nd column

[pbaldy]

If you're trying to get report ID from the listbox selection, you need to refer to the listbox there too.

[UT227]

Yes. It is a number field. I did remember that the first column is 0.
I completely forgot that I had to reference the list box again. That worked. Thanks. This is what I did.

Private Sub lstUnapprovedRpts_DblClick(Cancel As Integer)
If [lstUnapprovedRpts].Column(1) = 4 Then
DoCmd.OpenForm "frmAlarmReport", , , "[ReportID] = " & Forms![frmSupervisors]![lstUnapprovedRpts]

ElseIf [lstUnapprovedRpts].Column(1) = 3 Then
DoCmd.OpenForm "frmRptSecurity", , , "[ReportID] = " & Forms![frmSupervisors]![lstUnapprovedRpts]

End If

End Sub

[pbaldy]

No problem, glad you got it working.