Query Using a Form and another Query

Query Using a Form and another Query

Post by RUg » Fri, 08 Jul 2005 09:06:01


My Dilema:
I have 2 tables. One of which is of mostly static records that may get
updated every so often. The other is of 5 columns that the user must update
everytime she prints labels, via a report.

The first table has a style number for the first column. There can be
repeats here as there may be multiple records for each style. The rest of
the columns here are just for printing the label and have no impact on the
query portion, just the final label report.

When running the report, I need to :
1). Ask the user what style (from the first table) they want, and list the
"List Form showing the style records, for verification purposes. (For
example if I want to print style 805, I can enter that in and push that data
to the final report)
2). After verification the user must press ok, to goto the form for the 2nd
table, or in the same form, enter in # 3 below:
3). Ask the user for the 5 columns of data in the 2nd Table, preferably by a
4). Data from both the 1st and 2nd table must be in the final address label

I can do the conditional query and make a report based on that information.
The main problem I am having is getting a form to get the 2nd tables input
from the user (in turn updating that table), and getting the result of the
Style query and the user input to be used for the report.

Note: the 2nd table will only have one record, as it will change every time
the report is printed.

Thank you in advance. Im so lost =) been reading the Microsoft sites for 2
days working on this.

Query Using a Form and another Query

Post by amw1MDA » Fri, 08 Jul 2005 12:43:02

Peculiar approach, buthere you go:

-Have your 2nd table store only one record, the form used for data entry
must not allow additions (form properties),
-Have your report use a query using your 2nd table and the expression Style:
[forms]![myform]![style] as part of the fields, to reference your style if
-After the report prints have a macro call a query to update the 5 fields to
null if the user confirms,

<a href=" http://www.yqcomputer.com/ "></a>


Query Using a Form and another Query

Post by RUg » Sat, 09 Jul 2005 03:43:02

Thank you for your reply. However I am more than likely too frustrated with
this project now to fully grasp what your saying. The macro and data entry
for the 2nd form is simple enough.

I guess I just dont understand this "Style: [forms]![myform]![style]" to put
in the query. Of course I renamed "myform", However its not accompishing
much of anything, more than likely because I'm not doing it right. Let me
explain in a bit more detail if I can.

1st table:
Columns: Style, SAM, Step, Description

2nd Table:
Columns: Group Number, QTY, Color, Size, and Thread

Basically The 1st table will have a few hundred styles in it. Style could
have more than one of the same number, and Step will be numbered as a "step"
for each of the Style Numbers, SAM, and Description will vary in each. For

Table 1:
Style Number SAM Step Description
503 $0.91 1 SEW 2-PC COLLAR W/PELLON
503 $0.15 2 TRIM CORNER
503 $2.94 4 TOPSTITCH COLLAR 1/4

These values are pretty much set until something changes months down the road.
There will be more than 503 for the style number, around 300 or so different
ones in fact, with 12-40 Steps in Each.

Table 2:
Group Number Quantity Size Thread Thread
6007 30 M WHT WHT-32001

This is only one record as all columns need to change every time they print
some labels.

As I was saying. I just cant seem to get the user to be able run a report
that will ask him/her for the info from table 2, at the same time, query the
style they want from the first table.

Basically all of the information will go into a sheet of special label
paper, each "step" of a style being printed on a separate ticket (which is
why I made that portion a separate table), and the 2nd table is repeated
information throughout all of the tickets.

Im trying to make this as user friendly as possible, else there are a few
other ways I can go about it. Basically it needs to be click and go for the
most part unless entering in new or fixing some of the Style Steps.

As always, any input is greatly appreciated or even another way of doing it.
My brain is fried at this point. Hope I'm not confusing anyone.

Query Using a Form and another Query

Post by RUg » Sat, 09 Jul 2005 05:28:10

I figured it out. The relationship I made with the 2 tables back in phase
one of this little project were making life hell for me. When I tried to use
them both on the report It didnt come up with anything.

Mainly due to the fact that I didnt put the field on the report in the first
place!!! </slapself>.

Anyhow, it came down to me forgetting to update the queries for the table
relationships and fixing the reports to actually show what I needed.

Thanks for your time