Forms, Queries, Reports, oh my!

[tmoore4748]

I've been trying to get a report on one of my tables, but to get it
specific. It's a table with personnel data listed, and I want to bring up a
report with only one person at a time, selected by me. I tried to
accomplish this by creating a form with a combo box (as some people have
hard-to-spell names), and then referenced the query back to the form, and
put in a command button to preview the report. All the right stuff. One
problem, though: even after naming all the objects, and referencing to the
correct ones, I still only bring up a blank report. Why is this? Can
anyone help?

 

[Ken Snell [MVP]]

Likely something still isn't right. Are you keeping the form open when you
open the report? Are you sure that the data format of the form's control is
the same as what the query's criterion expression expects? and the query's
field expects? Can you post info re: the properties of the form's control
and the query's SQL statement?

 

[John Vinson]

I've been trying to get a report on one of my tables, but to get it
specific. It's a table with personnel data listed, and I want to bring up a
report with only one person at a time, selected by me. I tried to
accomplish this by creating a form with a combo box (as some people have
hard-to-spell names), and then referenced the query back to the form, and
put in a command button to preview the report. All the right stuff. One
problem, though: even after naming all the objects, and referencing to the
correct ones, I still only bring up a blank report. Why is this? Can
anyone help?

Because you've done something wrong with the query, causing it to
retrieve no records. Hard to tell WHAT you've done wrong of course!

Could you please open the Query in SQL view and post the SQL text
here? That should give someone a chance to figure out what went wrong.

 

[tmoore4748]

Yep, keeping the form open. Using following statement from the table (labeled "Technician") and in the query (as well as the combo box) to get the output to the query I want: Technician: [Last] & ", " & [First]. After that I use the following in the criteria of the query for Technician: Forms![frmTechDataCard]![cboTechName]. After all of this, I use a command button to selct "Preview Report", having selected a name from the combo box, and the report is sopposed to come up. I'm sure that this should work, but the problem is that I've never used criteria from combined fields, and even when they're not combined (i.e. using last name only), it still doesn't bring anything up. I really need help on this

 

[Mike Painter]

Yep, keeping the form open. Using following statement from the table
(labeled "Technician") and in the query (as well as the combo box) to
get the output to the query I want: Technician: [Last] & ", " &
[First]. After that I use the following in the criteria of the query
for Technician: Forms![frmTechDataCard]![cboTechName].

Unless cboTechName contains the name of the person in the "Last, First "
format, something it probably does not (or you would have no need to
concatanate) you will never get an answer.

You could bind a field to the ID of the tech and use that as a criteria in
teh query.

 

[Ken Snell [MVP]]

I agree with Mike. It would be better to use a combo box that shows the
names but is bound to the ID (primary key) field for the person. Then do the
filtering of the query based on the primary key field.

--

Ken Snell
<MS ACCESS MVP>

Yep, keeping the form open. Using following statement from the table
(labeled "Technician") and in the query (as well as the combo box) to
get the output to the query I want: Technician: [Last] & ", " &
[First]. After that I use the following in the criteria of the query
for Technician: Forms![frmTechDataCard]![cboTechName].

Unless cboTechName contains the name of the person in the "Last, First "
format, something it probably does not (or you would have no need to
concatanate) you will never get an answer.

You could bind a field to the ID of the tech and use that as a criteria in
teh query.

 

[tmoore4748]

Ya know, I haven't thought of that, so I tried it out. I cannot express the
absolute joy I had when it worked. I've been working on this specific
object for almost three days now. I'm used to being able to build a
database in about a week, on average with over 300 objects. Some are pretty
cookie-sutter, but ones like this one that I've made (very detail-oriented),
just make the process that much more difficult. If you can imagine the
thought of a 110 lb, blong, blue-eyed nerd jumping around, halfway
destroying his home office over adulation, you might get a picture of what
happened. It was as simple as moving the crieteria statement over by two
fields. Thanks for all the help.

I agree with Mike. It would be better to use a combo box that shows the
names but is bound to the ID (primary key) field for the person. Then do the
filtering of the query based on the primary key field.

--

Ken Snell
<MS ACCESS MVP>

Yep, keeping the form open. Using following statement from the table
(labeled "Technician") and in the query (as well as the combo box) to
get the output to the query I want: Technician: [Last] & ", " &
[First]. After that I use the following in the criteria of the query
for Technician: Forms![frmTechDataCard]![cboTechName].

Unless cboTechName contains the name of the person in the "Last, First "
format, something it probably does not (or you would have no need to
concatanate) you will never get an answer.

You could bind a field to the ID of the tech and use that as a criteria in
teh query.

 

[Ken Snell [MVP]]

I love it when a plan comes together! Good luck!

--

Ken Snell
<MS ACCESS MVP>

Ya know, I haven't thought of that, so I tried it out. I cannot express the
absolute joy I had when it worked. I've been working on this specific
object for almost three days now. I'm used to being able to build a
database in about a week, on average with over 300 objects. Some are pretty
cookie-sutter, but ones like this one that I've made (very detail-oriented),
just make the process that much more difficult. If you can imagine the
thought of a 110 lb, blong, blue-eyed nerd jumping around, halfway
destroying his home office over adulation, you might get a picture of what
happened. It was as simple as moving the crieteria statement over by two
fields. Thanks for all the help.

I agree with Mike. It would be better to use a combo box that shows the
names but is bound to the ID (primary key) field for the person. Then do the
filtering of the query based on the primary key field.

--

Ken Snell
<MS ACCESS MVP>

tmoore4748 wrote:
Yep, keeping the form open. Using following statement from the table
(labeled "Technician") and in the query (as well as the combo box) to
get the output to the query I want: Technician: [Last] & ", " &
[First]. After that I use the following in the criteria of the query
for Technician: Forms![frmTechDataCard]![cboTechName].

Unless cboTechName contains the name of the person in the "Last, First "
format, something it probably does not (or you would have no need to
concatanate) you will never get an answer.

You could bind a field to the ID of the tech and use that as a

criteria

in