PLEASE HELP!! Creating report, need to add fields in report from a table based on combo box value i

[buddycheck240]

I'm developing a database that will inevitably be used to print part
numbers and other information onto a 4x6 label for one of our
manufacturing facilities.

There will be a table with all of the part information to be printed on
the label. This is "tblRM". In this table are the fields "SKU",
"Description", "UOM", "Qty", and "Location", where SKU is the primary
key. This table will be filled with information before the users touch
the database.

I have started a simple form with a drop-down box for SKU (which is all
the end-users will need to identify each part) and a text box asking
how many labels the user wants to print. Once the user selects a SKU
from the drop-down list and types in how many labels need to be
printed, they will press the PRINT button and a report will be printed
X number of times.

The following information needs to be on the report: SKU, Description,
UOM, Qty, Location....and all of this information is pre-defined in
tblRM.

***I can easily put the selected SKU on the report, but I need to
figure out how I can pull the rest of the information from tblRM based
on the SKU selected in the combo box found in the form.***

I really really appreciate any and all help. Thanks in advance!

-Bud

 

[buddycheck240]

Is what I'm trying to do here even possible, or am I going about this
the wrong way altogether? Thanks again for any insight,
Bud

 

[Duane Hookom]

Isn't tblRM in your report's record source? If so, you should have all the
fields you are asking to display in your report.

 

[buddycheck240]

Duane,

Sorry it took so long to respond - I've been away for a few days. If I
add tblRM to the report's record source, it gives me the information
for EVERY part.

When the user selects a part number from the combo box, I need the
report to contain only the information for that one part found in
tblRM. I'm not sure how to make the report pull the rest of the fields
from tblRM based on the combo box selection. I'm having trouble since
the combo box will only store one piece of information (the part
number).

Thanks again,
Buddy

 

[Duane Hookom]

I'm lost. What is the record source of your report? Why would adding a table
give you information for "EVERY part"? What kind of a join are you using in
your report's record source?

 

[buddycheck240]

I'm going to start over - my earlier explanations probably weren't very
clear. I think I've had enough coffee this morning to make more sense
;o). This database is being created to print labels.

I have a single table, "tblRM", which includes the fields SKU,
Description, UOM, stdQty, Location. The primary key of tblRM is SKU.
There is one form with a combo box and print button. The combo box
drops down a list of SKU's, of which the user will select one. Upon
selecting this one SKU, the user will then click the print button which
will print an Access report which is designed as a 4 x 6 label.

I need the report to include all of the information from the selected
SKU's record (including the SKU, description, UOM, stdQty, Location).
I can easily add the SKU to the report since it is the selected field
in the combo box. I'm having difficulty adding description, UOM,
stdQty, and Location. If I add tblRM to the record source of the
report, it retrieves ALL of the records in that table, so that when the
print button is clicked, it will print one of each SKU's label. I need
it just to print a single label based on the user's combo box
selection.

Any and all help is appreciated once again. I take it tblRM does need
to be in the report's record source. Maybe something just needs to be
added to the criteria of some of the fields. Like I said, and as
simple as this sounds, I've never had to create a report that pulls
information like this. Thanks once again,

Bud

 

[Duane Hookom]

Apparently you want to limit the report to the currently selected SKU from
the combo box. I would use code like:

Dim strWhere as String
If Not IsNull(Me.cboSKU) Then
strWhere = "[SKU] = """ & Me.cboSKU & """"
End If
DoCmd.OpenReport "rptYourReportName", acPreview, , strWhere

You can also base your report on a query with a criteria of:
WHERE SKU = Forms!frmYourFormName!cboSKU

 

[buddycheck240]

Thanks again Duane - I'll give that a shot in the morning.

Apparently you want to limit the report to the currently selected SKU from
the combo box. I would use code like:

Dim strWhere as String
If Not IsNull(Me.cboSKU) Then
strWhere = "[SKU] = """ & Me.cboSKU & """"
End If
DoCmd.OpenReport "rptYourReportName", acPreview, , strWhere

You can also base your report on a query with a criteria of:
WHERE SKU = Forms!frmYourFormName!cboSKU
--
Duane Hookom
MS Access MVP
--

I'm going to start over - my earlier explanations probably weren't very
clear. I think I've had enough coffee this morning to make more sense
;o). This database is being created to print labels.

I have a single table, "tblRM", which includes the fields SKU,
Description, UOM, stdQty, Location. The primary key of tblRM is SKU.
There is one form with a combo box and print button. The combo box
drops down a list of SKU's, of which the user will select one. Upon
selecting this one SKU, the user will then click the print button which
will print an Access report which is designed as a 4 x 6 label.

I need the report to include all of the information from the selected
SKU's record (including the SKU, description, UOM, stdQty, Location).
I can easily add the SKU to the report since it is the selected field
in the combo box. I'm having difficulty adding description, UOM,
stdQty, and Location. If I add tblRM to the record source of the
report, it retrieves ALL of the records in that table, so that when the
print button is clicked, it will print one of each SKU's label. I need
it just to print a single label based on the user's combo box
selection.

Any and all help is appreciated once again. I take it tblRM does need
to be in the report's record source. Maybe something just needs to be
added to the criteria of some of the fields. Like I said, and as
simple as this sounds, I've never had to create a report that pulls
information like this. Thanks once again,

Bud

 

[buddycheck240]

Duane - I know this will sound stupid. Where would the code go? In
the report code behind? If I use a query with the above criteria, do I
enter that criteria under the fields SKU, description, UOM, stdQty, and
Location? or just under one of those? I tried adding the criteria and
the report ends up with blank fields behind the text boxes when the
report is generated. I've never had to use the code behind before in
Access, so this is all new to me. Thanks again - I know I'll get this
soon enough!!

Take care,
Bud

 

[Duane Hookom]

You stated "so that when the print button is clicked, it will print one of
each SKU's label. I need it just to print a single label based on the
user's combo box selection."

I assume you have a "button" on a form that is clicked to open the report.
The code I provided with be in the On Click event of the button on your
form.

 

[buddycheck240]

Duane - you are my hero. Thank you again.