using a form to search database and print report

[rkg]

Could someone suggest a way to do the following:

Open form
Type Name
Lookup name
Print all records matching that name from
query or table.
I've created the report for the complete query(All names in table). I
would just like to limit the scope to whatever name I need for an
individual report.
I've done it years ago in dBase but haven't done it in Access.
I'm using Access 2003.

Thanks,
Ron

 

[Guest]

Ron:

Add a button to the form with the following in its Click event procedure:

Dim strCriteria As String

strCriteria = "ContactName = """ & Me.txtName & """"

If Not IsNull(DLookup("ContactName", "YourQueryNameGoesHere",
strCriteria)) Then
DoCmd.OpenReport "YourReportNameGoesHere",
WhereCondtition:=strCriteria
Else
MsgBox "Name not found.", vbInformation, "Warning"
End If

where ContactName is the name of the field in the report's underlying
recordset and txtName is the name of the text box on the form into which you
enter the name. Better still would be a combo box which lists the names so
you can simply select one from the list.

Remember that if object names include spaces or other special characters you
need to wrap the name in square brackets, e.g. [Contact Name].

Ken Sheridan
Stafford, England

 

[rkg]

I've tried it and got an error message. Where exactly do I place the
code? When I chose the event procedure I selected code builder.
Thanks for any help you can give me.

Ron

Ron:

Add a button to the form with the following in its Click event procedure:

Dim strCriteria As String

strCriteria = "ContactName = """ & Me.txtName & """"

If Not IsNull(DLookup("ContactName", "YourQueryNameGoesHere",
strCriteria)) Then
DoCmd.OpenReport "YourReportNameGoesHere",
WhereCondtition:=strCriteria
Else
MsgBox "Name not found.", vbInformation, "Warning"
End If

where ContactName is the name of the field in the report's underlying
recordset and txtName is the name of the text box on the form into which you
enter the name. Better still would be a combo box which lists the names so
you can simply select one from the list.

Remember that if object names include spaces or other special characters you
need to wrap the name in square brackets, e.g. [Contact Name].

Ken Sheridan
Stafford, England

Could someone suggest a way to do the following:

Open form
Type Name
Lookup name
Print all records matching that name from
query or table.
I've created the report for the complete query(All names in table). I
would just like to limit the scope to whatever name I need for an
individual report.
I've done it years ago in dBase but haven't done it in Access.
I'm using Access 2003.

Thanks,
Ron

 

[Guest]

The error might be due to one or two lines in my first reply having wrapped
over two lines. One should have been:

DoCmd.OpenReport "YourReportNameGoesHere", WhereCondtition:=strCriteria

or if written over two lines to improve readability, with the underscore
continuation character:

DoCmd.OpenReport "YourReportNameGoesHere", _
WhereCondtition:=strCriteria

The same is true for the following single line:

If Not IsNull(DLookup("ContactName", YourQueryNameGoesHere",strCriteria)) Then

To put the code in a button's Click event procedure select the event in the
button's properties sheet, then click on the build button (the one on the
right with 3 dots), choose Code Builder and put the code in the procedure in
the VBA window between the two lines already in place.

If you still get an error post back with the actual code you've used.

Ken Sheridan
Stafford, England

I've tried it and got an error message. Where exactly do I place the
code? When I chose the event procedure I selected code builder.
Thanks for any help you can give me.

Ron

Ron:

Add a button to the form with the following in its Click event procedure:

Dim strCriteria As String

strCriteria = "ContactName = """ & Me.txtName & """"

If Not IsNull(DLookup("ContactName", "YourQueryNameGoesHere",
strCriteria)) Then
DoCmd.OpenReport "YourReportNameGoesHere",
WhereCondtition:=strCriteria
Else
MsgBox "Name not found.", vbInformation, "Warning"
End If

where ContactName is the name of the field in the report's underlying
recordset and txtName is the name of the text box on the form into which you
enter the name. Better still would be a combo box which lists the names so
you can simply select one from the list.

Remember that if object names include spaces or other special characters you
need to wrap the name in square brackets, e.g. [Contact Name].

Ken Sheridan
Stafford, England

Could someone suggest a way to do the following:

Open form
Type Name
Lookup name
Print all records matching that name from
query or table.
I've created the report for the complete query(All names in table). I
would just like to limit the scope to whatever name I need for an
individual report.
I've done it years ago in dBase but haven't done it in Access.
I'm using Access 2003.

Thanks,
Ron

 

[rkg]

The error might be due to one or two lines in my first reply having wrapped
over two lines. One should have been:

DoCmd.OpenReport "YourReportNameGoesHere", WhereCondtition:=strCriteria

or if written over two lines to improve readability, with the underscore
continuation character:

DoCmd.OpenReport "YourReportNameGoesHere", _
WhereCondtition:=strCriteria

The same is true for the following single line:

If Not IsNull(DLookup("ContactName", YourQueryNameGoesHere",strCriteria)) Then

To put the code in a button's Click event procedure select the event in the
button's properties sheet, then click on the build button (the one on the
right with 3 dots), choose Code Builder and put the code in the procedure in
the VBA window between the two lines already in place.

If you still get an error post back with the actual code you've used.

Ken Sheridan
Stafford, England

I've tried it and got an error message. Where exactly do I place the
code? When I chose the event procedure I selected code builder.
Thanks for any help you can give me.

Ron

Ron:

Add a button to the form with the following in its Click event procedure:

Dim strCriteria As String

strCriteria = "ContactName = """ & Me.txtName & """"

If Not IsNull(DLookup("ContactName", "YourQueryNameGoesHere",
strCriteria)) Then
DoCmd.OpenReport "YourReportNameGoesHere",
WhereCondtition:=strCriteria
Else
MsgBox "Name not found.", vbInformation, "Warning"
End If

where ContactName is the name of the field in the report's underlying
recordset and txtName is the name of the text box on the form into which you
enter the name. Better still would be a combo box which lists the names so
you can simply select one from the list.

Remember that if object names include spaces or other special characters you
need to wrap the name in square brackets, e.g. [Contact Name].

Ken Sheridan
Stafford, England

:

Could someone suggest a way to do the following:

Open form
Type Name
Lookup name
Print all records matching that name from
query or table.
I've created the report for the complete query(All names in table). I
would just like to limit the scope to whatever name I need for an
individual report.
I've done it years ago in dBase but haven't done it in Access.
I'm using Access 2003.

Thanks,
Ron

Ken, Here is what I did. I added a combo box that brings up all the
names then added this code to the click event procedure:
Dim strCriteria As String
strCriteria = "name" = """&Me.txtName&"""""
If Not IsNull(DLookup("name", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I got no error messages but when I try to choose a name from the combo
box I can't.

Thanks for any help you could offer.

Ron Greenbank

 

[Guest]

I'm rather surprised that no error occurred. A few problems here:

1. The = sign should be within the literal string expression.

2. Using Name as field name is not a good idea as it can conflict with the
Name property of an object. Something like ContactName, CompanytName etc, is
better. If you do use Name then wrap it in square brackets when referring to
it in the code.

3. Put the code in the combo box's AfterUpdate event procedure, not its
Click event procedure.

4. You have too many quotes characters in places. You should have one set
of quotes characters around each literal string, and pairs of adjacent quotes
characters within the literal strings to represent a quotes character within
a literal string itself delimited by quotes.

5. Not essential, but breaking up the code with blank lines helps
readability. Similarly leaving a space between an operator (e.g. = or &) and
its operands improves readability.

So the code would

Dim strCriteria As String

strCriteria = "[name] = """ & Me.txtName & """"

If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

 

[rkg]

Ken, Here is the code I'm using:
Dim strCriteria As String
strCriteria = "[name]" = "&Me.txtName&"
If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I cut and pasted it. For some reason I get the Name not found message
every time I open the form. I also notice that the only two names I can
show in the combo box are the first and last record. I can click on a
name but nothing happens. I can highlight the name and hit enter and
get the Name no found message.
Do you have any other ideas.
Thanks,
Ron Greenbank

I'm rather surprised that no error occurred. A few problems here:

1. The = sign should be within the literal string expression.

2. Using Name as field name is not a good idea as it can conflict with the
Name property of an object. Something like ContactName, CompanytName etc, is
better. If you do use Name then wrap it in square brackets when referring to
it in the code.

3. Put the code in the combo box's AfterUpdate event procedure, not its
Click event procedure.

4. You have too many quotes characters in places. You should have one set
of quotes characters around each literal string, and pairs of adjacent quotes
characters within the literal strings to represent a quotes character within
a literal string itself delimited by quotes.

5. Not essential, but breaking up the code with blank lines helps
readability. Similarly leaving a space between an operator (e.g. = or &) and
its operands improves readability.

So the code would

Dim strCriteria As String

strCriteria = "[name] = """ & Me.txtName & """"

If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

Ken, Here is what I did. I added a combo box that brings up all the
names then added this code to the click event procedure:
Dim strCriteria As String
strCriteria = "name" = """&Me.txtName&"""""
If Not IsNull(DLookup("name", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I got no error messages but when I try to choose a name from the combo
box I can't.

Thanks for any help you could offer.

Ron Greenbank

 

[Guest]

Your expression is still not right; it should be:

strCriteria = "[name] = """ & Me.txtName & """"

The two pairs of quotes characters within the strings delimited by quotes
characters are interpreted as literal quotes characters when the expression
evaluates, so if txtName was Ken Sheridan the expression would evaluate to
[name] = "Ken Sheridan", which is how it should be.

The reason it didn't find anything is because the expression as you wrote it
would always evaluate to a Boolean FALSE, so the DLookup function would
always return a NULL.

As regards the combo box's list you should be able to show all names by
having a RowSource property such as:

SELECT [name] FROM YourTable ORDER BY [name];

where YourTable is the name of the table containing the [name] column.

I'm on the road for a few days now, but will be back Monday.

Ken Sheridan
Stafford, England

Ken, Here is the code I'm using:
Dim strCriteria As String
strCriteria = "[name]" = "&Me.txtName&"
If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I cut and pasted it. For some reason I get the Name not found message
every time I open the form. I also notice that the only two names I can
show in the combo box are the first and last record. I can click on a
name but nothing happens. I can highlight the name and hit enter and
get the Name no found message.
Do you have any other ideas.
Thanks,
Ron Greenbank

I'm rather surprised that no error occurred. A few problems here:

1. The = sign should be within the literal string expression.

2. Using Name as field name is not a good idea as it can conflict with the
Name property of an object. Something like ContactName, CompanytName etc, is
better. If you do use Name then wrap it in square brackets when referring to
it in the code.

3. Put the code in the combo box's AfterUpdate event procedure, not its
Click event procedure.

4. You have too many quotes characters in places. You should have one set
of quotes characters around each literal string, and pairs of adjacent quotes
characters within the literal strings to represent a quotes character within
a literal string itself delimited by quotes.

5. Not essential, but breaking up the code with blank lines helps
readability. Similarly leaving a space between an operator (e.g. = or &) and
its operands improves readability.

So the code would

Dim strCriteria As String

strCriteria = "[name] = """ & Me.txtName & """"

If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

Ken, Here is what I did. I added a combo box that brings up all the
names then added this code to the click event procedure:
Dim strCriteria As String
strCriteria = "name" = """&Me.txtName&"""""
If Not IsNull(DLookup("name", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I got no error messages but when I try to choose a name from the combo
box I can't.

Thanks for any help you could offer.

Ron Greenbank

 

[rkg]

Ken,

I got everything to work. I needed to put my combo box name in the
Me.txtName spot and everything worked great. I really appreciate the
help.

Ron

Your expression is still not right; it should be:

strCriteria = "[name] = """ & Me.txtName & """"

The two pairs of quotes characters within the strings delimited by quotes
characters are interpreted as literal quotes characters when the expression
evaluates, so if txtName was Ken Sheridan the expression would evaluate to
[name] = "Ken Sheridan", which is how it should be.

The reason it didn't find anything is because the expression as you wrote it
would always evaluate to a Boolean FALSE, so the DLookup function would
always return a NULL.

As regards the combo box's list you should be able to show all names by
having a RowSource property such as:

SELECT [name] FROM YourTable ORDER BY [name];

where YourTable is the name of the table containing the [name] column.

I'm on the road for a few days now, but will be back Monday.

Ken Sheridan
Stafford, England

Ken, Here is the code I'm using:
Dim strCriteria As String
strCriteria = "[name]" = "&Me.txtName&"
If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I cut and pasted it. For some reason I get the Name not found message
every time I open the form. I also notice that the only two names I can
show in the combo box are the first and last record. I can click on a
name but nothing happens. I can highlight the name and hit enter and
get the Name no found message.
Do you have any other ideas.
Thanks,
Ron Greenbank

I'm rather surprised that no error occurred. A few problems here:

1. The = sign should be within the literal string expression.

2. Using Name as field name is not a good idea as it can conflict with the
Name property of an object. Something like ContactName, CompanytName etc, is
better. If you do use Name then wrap it in square brackets when referring to
it in the code.

3. Put the code in the combo box's AfterUpdate event procedure, not its
Click event procedure.

4. You have too many quotes characters in places. You should have one set
of quotes characters around each literal string, and pairs of adjacent quotes
characters within the literal strings to represent a quotes character within
a literal string itself delimited by quotes.

5. Not essential, but breaking up the code with blank lines helps
readability. Similarly leaving a space between an operator (e.g. = or &) and
its operands improves readability.

So the code would

Dim strCriteria As String

strCriteria = "[name] = """ & Me.txtName & """"

If Not IsNull(DLookup("[name]", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

:

Ken, Here is what I did. I added a combo box that brings up all the
names then added this code to the click event procedure:
Dim strCriteria As String
strCriteria = "name" = """&Me.txtName&"""""
If Not IsNull(DLookup("name", "qscale06", strCriteria)) Then
DoCmd.OpenReport "rscaletickets06", WhereCondition:=strCriteria
Else
MsgBox "Name not found", vbInformation, "Warning"
End If
I got no error messages but when I try to choose a name from the combo
box I can't.

Thanks for any help you could offer.

Ron Greenbank