Open form based on query result

[Mike Revis]

Hi Group,
Winxppro, Access2000

I'm having a problem figuring out how to write my where clause.

I have a query that returns job numbers based on entering all or part of a
sales order number.
The query is run from a command button on my "menu form". frmMainMenu.

SELECT DISTINCT tblPackageData.JobNumber
FROM tblPackageData LEFT JOIN tblRefNo ON tblPackageData.PackageID =
tblRefNo.PackageID
WHERE (((tblRefNo.SalesOrder) Like "*" & [Enter Sales Order Number ] &
"*"));

That part seems to be working ok. When I enter all or part of a sales order
number I get a list of all the jobs that meet that criteria.

Now what I would like to accomplish is to open the job form (frmJobData) to
show all the records that match the query result.

So far I have this.
......cmdFindJobBySalesOrder_Click()

Dim stDocName As String
Dim stfrmName As String
Dim stLinkCriteria As String

stDocName = "qryFindJobBySalesOrder"
stfrmName = "frmJobData"

this is where I'm stuck stLinkCriteria = "[JobNumber]='" &
JobNumber is a text data type

DoCmd.OpenQuery stDocName
DoCmd.OpenForm stfrmName, , , stLinkCriteria

Or perhaps there is a different way to accomplish this.

As always any thoughts, comments or suggestions are welcome.

Best regards
Mike

 

[Albert D. Kallal]

I like this type of approach.

When using the "where" clause, simple remove **all** parameters from the
query. (not only does this make the query more readable, but then it can be
used for reports and all kinds of other things without *any* prompt
occurring. So, remove the parameter from the query.

The, what you have is *very* close.

Now what I would like to accomplish is to open the job form (frmJobData)
to
show all the records that match the query result.

If you base the form on that query with a prompt, then the form *will* open
with all of the reords, but I think it better to build your own prompt...

this is where I'm stuck stLinkCriteria = "[JobNumber]='" &
JobNumber is a text data type

Is the above a text box on your screen that the user typed into??? It not
clear what/where/when the user is to type somting in????

Assuming you have a un-bound form in which the user types in the job number,
then the code would be:

stLinkCriteria = "[JobNumber]= '*" & me.txtJobNum & "*'"
DoCmd.OpenForm stfrmName, , , stLinkCriteria


So, you do NOT have to play/touch the query. As I said, you can do this with
no code if you leave the parameter in the query, but I do think it is
somewhat better to build a prompt on a form, remove the parameter from the
query, and then use the "where" clause, as the you don't touch or modify the
query.

 

[Mike Revis]

Thank you for your response Albert.

I'm not sure I explained myself very well. Or I'm not grasping the essence
of your response.
My goal is to open the Job Form (frmJobData) to display all of the records
that match the job numbers returned by qryFindJobBySalesOrder.

My structure is like so.

frmJobData bound to tblJobData 1~M to tblPackageData. txtJobNumber is PK/FK.
(I know, poor design)

tblPackageData 1~1 to tblRefNo. PackageID is PK/FK.
frmPackageData is bound to qryPackageData which is based on tblPackageData
and tblRefNo.

1 job(tblJobData). Many packages(tblPackageData). Each package has different
sales order number(tblRefNo).

So the only place the Sales Order Number shows up is in tblRefNo.

When I know the Sales Order Number I need to find the Job Number that Sales
Order belongs to.
There is a random chance that two unrelated jobs could have a package with
the same sales
order number.

The command button that runs qryFindJobBySalesOrder is on an unbound form.
frmMainMenu.

qryFindJobBySalesOrder prompts the user for all or part of a sales order
number.

qryFindJobBySalesOrder returns a list of job numbers where txtSalesOrder is
*like* the text the user is prompted to enter by the query.

Now I need to open frmJobData to show all the records from tblJobData
containing the job numbers returned by qryFindJobBySalesOrder.

It was really easy when each job had one sales order number but now I can
have multiple packages with different sales order numbers within one job.

I could just put a sales order field in tblPackageData but in the interest
of normalization I have put the sales order number in its own table. Only
about 25% of the packages have sales order numbers.

Again, thank you for your time and consideration.

Mike

I like this type of approach.

When using the "where" clause, simple remove **all** parameters from the
query. (not only does this make the query more readable, but then it can
be used for reports and all kinds of other things without *any* prompt
occurring. So, remove the parameter from the query.

The, what you have is *very* close.

Now what I would like to accomplish is to open the job form (frmJobData)
to
show all the records that match the query result.

If you base the form on that query with a prompt, then the form *will*
open with all of the reords, but I think it better to build your own
prompt...

this is where I'm stuck

stLinkCriteria = "[JobNumber]='" &

JobNumber is a text data type

Is the above a text box on your screen that the user typed into??? It not
clear what/where/when the user is to type somting in????

Assuming you have a un-bound form in which the user types in the job
number, then the code would be:

stLinkCriteria = "[JobNumber]= '*" & me.txtJobNum & "*'"
DoCmd.OpenForm stfrmName, , , stLinkCriteria


So, you do NOT have to play/touch the query. As I said, you can do this
with no code if you leave the parameter in the query, but I do think it is
somewhat better to build a prompt on a form, remove the parameter from the
query, and then use the "where" clause, as the you don't touch or modify
the query.

 

[Albert D. Kallal]

Thank you for your response Albert.

I'm not sure I explained myself very well. Or I'm not grasping the essence
of your response.
My goal is to open the Job Form (frmJobData) to display all of the records
that match the job numbers returned by qryFindJobBySalesOrder.

Sure, you can still use a where clause here....

strWhere = "JobNum in " & _
"(select JobNum from qryFindJobBySalesOrder " & _
"where salesOrder = '" & me.TxtSalesOrder & "')"

docmd.OpenForm frmJobData,,,strWhere


So, you can "pass" the results, or list of id's from the above. And, you can
change the salesorder to a partial match. (I suggest sticking to starting
chars + * since indexing can still be used. If you do a complete wild card
search, then indexing can not be used

eg:
"where salesOrder like '*" & me.TxtSalesOrder & "*')"
above is going to be slow, and access cannot use the index because you
searching anywhere in the string for the salesorder number.

"where salesOrder like '" & me.TxtSalesOrder & "*')"

Above is going to be fast...even with 100,000 records...it will run in an
instant....

 

[Mike Revis]

Albert,
So once I thought I had this set up correctly it would work up to a point
where I would get the hourglass wait signal. I let it run one time while I
went and made a sandwich. I did a control break after a while and got the
message "the open form action was cancelled." ??
Obviously I need to do a little more studying on this but first I'm going to
let it rest for a few days while I finish this sandwich. I will post back
once I get to thinking coherently again.

Thank you
Mike