New to VB code - Help with attached code

G

Guest

I posted a similar message in the Macro section and did not get a response,
possibly because a macro may not be the answer.

I have a form that allows the user to select criteria. From this criteria,
I need access to determine which report to run, and then run that report.

The criteria fields the user must enter are first name AND last name OR
category. From the information the user enters, the report will be either a
single label with the first name or last name, or a list of labels of every
name in the category.

Below is the code I have tried to work with, but it is not working.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click




Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If

'Checking for lastname
If (Form_criteria.lastname.Value <> "") Then
lastname = Form_criteria.lastname.Value
strcriteria = strcriteria & strqueryname & ".[lastname] = '" & lastname
& "'"
End If

'Checking for category
If (Form_criteria.Category.Value <> "") Then
Category = Form_criteria.Category.Value
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" & Category
& "'"

End If

'checking for empty field values
If (Form_criteria.firstname.Value <> "") Then
stDocName = Form_criteria.firstname.Value
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria


End If








exit_Print_Label_click:
Exit Sub


err_Print_Label_click:
MsgBox Err.Description

Resume exit_Print_Label_click

End Sub

Thanks for any help.
 
G

Guest

I think it may not be working because you are testing to see if your controls
are a zero length string "". Unititialized controls are (with some
exceptions) return Null.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click

Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If Not IsNull(Me.firstname) Then
<----------- I notice here that strqueryname has not been initialized
strcriteria = strqueryname & ".[firstname] = '" &
Me.firstname & "'"
End If

'Checking for lastname
If Not IsNull(Me.lastname) Then
If Len(strcriteria) > 0 Then
strcriteria = strcriteria & " AND "
End If
strcriteria = strcriteria & strqueryname & ".[lastname] = '" &
Me.lastname
& "'"
End If

'Checking for category
If Not IsNull(Me.Category) Then
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" &
Me.Category
& "'"

End If

'checking for empty field values
If (Me.firstname.Value <> "") Then
stDocName = Me.firstname
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria
End If


Risikio said:
I posted a similar message in the Macro section and did not get a response,
possibly because a macro may not be the answer.

I have a form that allows the user to select criteria. From this criteria,
I need access to determine which report to run, and then run that report.

The criteria fields the user must enter are first name AND last name OR
category. From the information the user enters, the report will be either a
single label with the first name or last name, or a list of labels of every
name in the category.

Below is the code I have tried to work with, but it is not working.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click




Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If

'Checking for lastname
If (Form_criteria.lastname.Value <> "") Then
lastname = Form_criteria.lastname.Value
strcriteria = strcriteria & strqueryname & ".[lastname] = '" & lastname
& "'"
End If

'Checking for category
If (Form_criteria.Category.Value <> "") Then
Category = Form_criteria.Category.Value
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" & Category
& "'"

End If

'checking for empty field values
If (Form_criteria.firstname.Value <> "") Then
stDocName = Form_criteria.firstname.Value
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria


End If








exit_Print_Label_click:
Exit Sub


err_Print_Label_click:
MsgBox Err.Description

Resume exit_Print_Label_click

End Sub

Thanks for any help.
Click to expand...
 
G

Guest

I am not sure I am following because I understood (perhaps incorrectly) that
by using the If <>"", the then statement would take the criteria selected and
make that the value.

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
Click to expand...
<-----Shouldn't the previous "Then" statement take the criteria selected and
make that the value?
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If
Click to expand...

Is there a way to write a macro that will do this easier since I am not all
that familiar with VB?

Klatuu said:
I think it may not be working because you are testing to see if your controls
are a zero length string "". Unititialized controls are (with some
exceptions) return Null.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click

Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If Not IsNull(Me.firstname) Then
<----------- I notice here that strqueryname has not been initialized
strcriteria = strqueryname & ".[firstname] = '" &
Me.firstname & "'"
End If

'Checking for lastname
If Not IsNull(Me.lastname) Then
If Len(strcriteria) > 0 Then
strcriteria = strcriteria & " AND "
End If
strcriteria = strcriteria & strqueryname & ".[lastname] = '" &
Me.lastname
& "'"
End If

'Checking for category
If Not IsNull(Me.Category) Then
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" &
Me.Category
& "'"

End If

'checking for empty field values
If (Me.firstname.Value <> "") Then
stDocName = Me.firstname
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria
End If


Risikio said:
I posted a similar message in the Macro section and did not get a response,
possibly because a macro may not be the answer.

I have a form that allows the user to select criteria. From this criteria,
I need access to determine which report to run, and then run that report.

The criteria fields the user must enter are first name AND last name OR
category. From the information the user enters, the report will be either a
single label with the first name or last name, or a list of labels of every
name in the category.

Below is the code I have tried to work with, but it is not working.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click




Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If

'Checking for lastname
If (Form_criteria.lastname.Value <> "") Then
lastname = Form_criteria.lastname.Value
strcriteria = strcriteria & strqueryname & ".[lastname] = '" & lastname
& "'"
End If

'Checking for category
If (Form_criteria.Category.Value <> "") Then
Category = Form_criteria.Category.Value
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" & Category
& "'"

End If

'checking for empty field values
If (Form_criteria.firstname.Value <> "") Then
stDocName = Form_criteria.firstname.Value
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria


End If








exit_Print_Label_click:
Exit Sub


err_Print_Label_click:
MsgBox Err.Description

Resume exit_Print_Label_click

End Sub

Thanks for any help.
Click to expand...
Click to expand...
 
G

Guest

If <> is a conditional test, it assigns no value.
There is a difference between Null and ""
Null has no value.
The only way to determine is a variable or control contains Null is to use
the IsNull funtion - If IsNull(SomeVariable) Then

Now, the Then part of the statement will assign a value

If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value

In the above case, you are saying that if Form_criteria.firstname has any
other value than a zero length string, assign that value to the variable
firstname. "Any other value" would include Null. Null and a zero length
string a different values. Don't feel alone, that is a hard concept to grasp.
Now, if Form_criteria.firstname is not initialized, meaning nothing has been
entered, it will contain Null. So in the test above, it would not be = ""
and the code would try to put Null in firstname. If you don't use Option
Explicit and declare variables and data types, it would not be a problem
because all undeclared variables are Variant. If firstname were Dimmed as
String, you would get an error because only Variant data types can accept a
Null.

If I have not, by this time, totally confused you, I will keep trying :)
Risikio said:
I am not sure I am following because I understood (perhaps incorrectly) that
by using the If <>"", the then statement would take the criteria selected and
make that the value.

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
Click to expand...
<-----Shouldn't the previous "Then" statement take the criteria selected and
make that the value?
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If
Click to expand...

Is there a way to write a macro that will do this easier since I am not all
that familiar with VB?

Klatuu said:
I think it may not be working because you are testing to see if your controls
are a zero length string "". Unititialized controls are (with some
exceptions) return Null.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click

Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If Not IsNull(Me.firstname) Then
<----------- I notice here that strqueryname has not been initialized
strcriteria = strqueryname & ".[firstname] = '" &
Me.firstname & "'"
End If

'Checking for lastname
If Not IsNull(Me.lastname) Then
If Len(strcriteria) > 0 Then
strcriteria = strcriteria & " AND "
End If
strcriteria = strcriteria & strqueryname & ".[lastname] = '" &
Me.lastname
& "'"
End If

'Checking for category
If Not IsNull(Me.Category) Then
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" &
Me.Category
& "'"

End If

'checking for empty field values
If (Me.firstname.Value <> "") Then
stDocName = Me.firstname
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria
End If


Risikio said:
I posted a similar message in the Macro section and did not get a response,
possibly because a macro may not be the answer.

I have a form that allows the user to select criteria. From this criteria,
I need access to determine which report to run, and then run that report.

The criteria fields the user must enter are first name AND last name OR
category. From the information the user enters, the report will be either a
single label with the first name or last name, or a list of labels of every
name in the category.

Below is the code I have tried to work with, but it is not working.

Private Sub Print_Label_Click()

On Error GoTo err_Print_Label_click




Dim stDocName As String
Dim strcriteria As String
Dim strqueryname As String
Dim firstname, lastname, Category As String

'Checking for firstname
If (Form_criteria.firstname.Value <> "") Then
firstname = Form_criteria.firstname.Value
strcriteria = strcriteria & strqueryname & ".[firstname] = '" &
firstname & "'"
End If

'Checking for lastname
If (Form_criteria.lastname.Value <> "") Then
lastname = Form_criteria.lastname.Value
strcriteria = strcriteria & strqueryname & ".[lastname] = '" & lastname
& "'"
End If

'Checking for category
If (Form_criteria.Category.Value <> "") Then
Category = Form_criteria.Category.Value
'Add AND keyword if previous field value is not empty or else ignore the
AND
If (strcriteria <> "") Then
strcriteria = strcriteria & " AND "
End If

strcriteria = strcriteria & strqueryname & ".[category] = '" & Category
& "'"

End If

'checking for empty field values
If (Form_criteria.firstname.Value <> "") Then
stDocName = Form_criteria.firstname.Value
'build the query criteria base on field value
DoCmd.OpenReport stDocName, acPreview, , strcriteria


End If








exit_Print_Label_click:
Exit Sub


err_Print_Label_click:
MsgBox Err.Description

Resume exit_Print_Label_click

End Sub

Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 

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

data type mismatch 4
Run delete query instead of report... 4
Multiple Criteria in Listbox 3
strCriteria declaration 2
bookmark - runtime error "3420" 2
Double Click Code Help Please 2
Multiple Criteria in Code 13
Problem with Tree View 2

Top