Opening Forms based on results of a union all

[Dan25e]

Hi peeps,

I have some code in my database that will open a form based on the result of , I think, a union query.

Private Sub resultbox_DblClick(Cancel As Integer)

'Open report based on the ID from resultbox listbox

DoCmd.OpenReport "ResultsStan", acViewReport, , "[ID] = " & Me.resultbox, , acDialog
'End If

End sub

What I need to do is open one of a number of reports. The report that needs to be opened depends on another value that is passed from the union query - DOCCAT

Being still a bit of a novice I tried...

'If "[DOCCAT]= " & Me.resultbox Like "Standards" Then
DoCmd.OpenReport "ResultsStan", acViewReport, , "[ID] = " & Me.resultbox, , acDialog
'End If

Needless to say it didn't work.

Am I close or way off the mark? Pointers or code would be very much appreciated.

Thanks in advance
Dan

 

[pbaldy]

If I understand correctly more like

If Me.[DOCCAT]= "Standards" Then

 

[Dan25e]

Thanks Paul but that didn't work.
Here's some of the code that precedes it...

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

...

strSQL6 = "SELECT WIregSeachAll.ID, WIregSeachAll.DOCCAT, WIregSeachAll.DOCUMENTNo, WIregSeachAll.ISSUE, WIregSeachAll.COPY, WIregSeachAll.IR, WIregSeachAll.DESCRIPTION " & _
"FROM WIregSeachAll"

strWhere6 = "WHERE"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txttype) Then '<--If the textbox txttype contains no data THEN do nothing
strWhere6 = strWhere6 & " (WIregSeachAll.COMMENTS) Like '*" & Me.txttype & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

'Remove the last AND from the SQL statment
strWhere6 = Mid(strWhere6, 1, Len(strWhere6) - 5)

'Pass the SQL to the RowSource of the listbox

strOrder = "ORDER BY documentno asc, description asc;"

Me.resultbox.RowSource = strSQL1 & " " & strWhere1 & " UNION ALL " & strSQL2 & " " & strWhere2 & " UNION ALL " & strSQL3 & " " & strWhere3 & " UNION ALL " & strSQL4 & " " & strWhere4 & " UNION ALL " & strSQL5 & " " & strWhere5 & " UNION ALL " & strSQL6 & " " & strWhere6 & strOrder

End Sub

I think I understand how the code gets the ID for the report but I need to get the DOCCAT out first to tell it which report to open to pass the ID to (if that makes sense).

 

[pbaldy]

Perhaps

If Me.resultbox.Column(1) = "Standards" Then

 

[Dan25e]

Paul, you are indeed the man! Does exactly what's on the tin.

Thanks for your help.

 

[pbaldy]

Happy to help Dan!

 

[Dan25e]

Remembering, of course, that Columns start at 0!!!