VBA Coding

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

I have a line that in my code that I need to have broken up depending on the
info selected. On the form there are three drop down menus that allow the
user to select info. Depending if the user selects all 3 or 2 or 1 I would
the code to be able to handle this. Here is the current code:

Private Sub DoPrintOut(nMethod As Integer)
On Error GoTo Err_DoPrintOut

If IsNull(Me.Admin_Name) Then
MsgBox "Please select a Admin Name before trying to print."
Else
If IsNull(Me.Prof_Name) Then
MsgBox "Please select a Professor Name before trying to print."
Else
If IsNull(Me.Doc_Type) Then
MsgBox "Please select a Document type before trying to print."

Else
****DoCmd.OpenForm strReport, nMethod, , "( AdminID = " &
Me.Admin_Name & " and Professor = " & Me.Prof_Name & " and DocTypeID = " &
Me.Doc_Type & ") "****
Me.SetFocus
DoCmd.Close
End If
End If
End If

Exit_DoPrintOut:
Exit Sub

Err_DoPrintOut:
MsgBox Err.Description
Resume Exit_DoPrintOut

End Sub

I have put *** where I think the altered code needs to be placed, i.e.
something like an If Then statement, however I can not figure out how to
place it into that line. If you can offer some help that would be greatly
appreciated.

Thanks,
Tony
 
Tony said:
I have a line that in my code that I need to have broken up depending on the
info selected. On the form there are three drop down menus that allow the
user to select info. Depending if the user selects all 3 or 2 or 1 I would
the code to be able to handle this. Here is the current code:

Private Sub DoPrintOut(nMethod As Integer)
On Error GoTo Err_DoPrintOut

If IsNull(Me.Admin_Name) Then
MsgBox "Please select a Admin Name before trying to print."
Else
If IsNull(Me.Prof_Name) Then
MsgBox "Please select a Professor Name before trying to print."
Else
If IsNull(Me.Doc_Type) Then
MsgBox "Please select a Document type before trying to print."

Else
****DoCmd.OpenForm strReport, nMethod, , "( AdminID = " &
Me.Admin_Name & " and Professor = " & Me.Prof_Name & " and DocTypeID = " &
Me.Doc_Type & ") "****
Me.SetFocus
DoCmd.Close
End If
End If
End If

Exit_DoPrintOut:
Exit Sub

Err_DoPrintOut:
MsgBox Err.Description
Resume Exit_DoPrintOu
End Sub
Click to expand...


You don't really want to do it all on one line. Try
something more like:

Private Sub DoPrintOut(nMethod As Integer)
Dim strWC As String
On Error GoTo Err_DoPrintOut

If Not IsNull(Me.Admin_Name) Then
strWC = " And AdminID = """ & Me.Admin_Name & """"
End If
If Not IsNull(Me.Prof_Name) Then
strWC = " And Professor = """ & Me.Prof_Name & """"
End If
If Not IsNull(Me.Doc_Type) Then
strWC = " And DocTypeID = """ & Me.Doc_Type & """"
End If
DoCmd.OpenForm strReport, nMethod, , Mid(strWC, 6)
DoCmd.Close acForm, Me.Name

Exit_DoPrintOut:
Exit Sub

Err_DoPrintOut:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_DoPrintOut
End Sub
 
Thanks for the suggestion, however I would still like to be able to to
select all 3 options or just one option. Your code only allows for me to
choose one options because the strWC is over written enverytime the
statement is true. Is this what you intended??

Thanks,

Tony
 
Sorry I figured that all I needed to do was to use & and that would pull all
the strWC together. Now when I run the code it gives me a 2501 error and
says the openForm Action was cancelled. Any ideas??

Thanks,

Tony
 
Hi Tony,

It looks as if the problem is that you haven't got quote marks round
your text criteria. If Me.AdminName contains
Peter Jones
and Me.Prof_Name contains
John Lewis
and Me.Doc_Type contains
Report
, your WHERE condition will resolve to:

( AdminID = Peter Jones and Professor = John Lewis
and DocTypeID = Report )

and the system won't understand that "Jones" actually goes with "Peter".
You have to ensure that the text values are properly quoted, to produce

( AdminID = "Peter Jones" AND Professor = "John Lewis" ...

To get this you need to double up all the quotes that appear within the
literal string in the code, something like

Dim strWhere As String

...
strWhere = "(AdminID =""" & Me.Admin_Name _
& """ AND Professor=""" & Me.Professor _
& """ AND DocTypeID=""" & Me.Doc_Type """)"

DoCmd.OpenForm ... , strWhere
 
Here is the new updated code that is still broken.

Private Sub DoPrintOut(nMethod As Integer)
Dim strWC As String
On Error GoTo Err_DoPrintOut

If Not IsNull(Me.Admin_Name) Then
strWC = " AdminID = """ & Me.Admin_Name & """"
End If
If Not IsNull(Me.Prof_Name) Then
strWC = strWC & " And Professor = """ & Me.Prof_Name & """"
End If
If Not IsNull(Me.Doc_Type) Then
strWC = strWC & " And DocTypeID = """ & Me.Doc_Type & """"
End If



DoCmd.OpenForm strReport, nMethod, , strWC
DoCmd.Close acForm, Me.Name

Exit_DoPrintOut:
Exit Sub

Err_DoPrintOut:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_DoPrintOut
End Sub



Tony said:
Sorry I figured that all I needed to do was to use & and that would pull
all the strWC together. Now when I run the code it gives me a 2501 error
and says the openForm Action was cancelled. Any ideas??

Thanks,

Tony
Click to expand...
 
Tony said:
Here is the new updated code that is still broken.

Private Sub DoPrintOut(nMethod As Integer)
Dim strWC As String
On Error GoTo Err_DoPrintOut

If Not IsNull(Me.Admin_Name) Then
strWC = " AdminID = """ & Me.Admin_Name & """"
End If
If Not IsNull(Me.Prof_Name) Then
strWC = strWC & " And Professor = """ & Me.Prof_Name & """"
End If
If Not IsNull(Me.Doc_Type) Then
strWC = strWC & " And DocTypeID = """ & Me.Doc_Type & """"
End If

DoCmd.OpenForm strReport, nMethod, , strWC
DoCmd.Close acForm, Me.Name
Click to expand...


I was interupted and left out that part, but then you didn't
quite make up for my omissions:

Private Sub DoPrintOut(nMethod As Integer)
Dim strWC As String
On Error GoTo Err_DoPrintOut

If Not IsNull(Me.Admin_Name) Then
strWC =strWC & " And AdminID = """ & Me.Admin_Name &
""""
End If
If Not IsNull(Me.Prof_Name) Then
strWC = strWC & " And Professor = """ & Me.Prof_Name
& """"
End If
If Not IsNull(Me.Doc_Type) Then
strWC = strWC & " And DocTypeID = """ & Me.Doc_Type
& """"
End If

DoCmd.OpenForm strReport, nMethod, , Mid(strWC, 6)
DoCmd.Close acForm, Me.Name

You need the " And " at the front of all the conditions so
that you are guaranteed to be able to remove the first one
using the Mid function (note: the first And may not be with
the AdminID field).

Not that all those quotes are because I assumed that your
fields were type Text. if, for example, the Doc_Type field
is a numeric type then that line would be:
strWC = strWC & " And DocTypeID = " & Me.Doc_Type

I don't see anything here that would cause a 2501 error,
unless your report is cancelling itself when it has no data.
 
Back
Top