Opening a form using criteria from a startup form in ADO

[Jimmy211]

Hi. I’m fairly new to using SQL server with Access and have a question about opening forms. I have two forms, one that is used to set criteria for opening the second form which views the data. The startup form is called frmGaugeStartup, and the main form is called frmGaugeLog. I want to use two criteria from frmGaugeStartup to open frmGaugeLog: Date and Location.
The frmGaugeLog’s data source is based on an SQL statement that uses two tables, TransGauge and zInfoTanks. TransGauge holds the transactional information and zInfoTanks has the location information.
The SQL record source for frmGaugeLog looks like this:

SELECT TransGauge.*, zInfoTanks.T_LOC_ABBR
FROM TransGauge INNER JOIN
zInfoTanks ON TransGauge.GA_Tank = zInfoTanks.T_ID
ORDER BY zInfoTanks.T_INDEX

The problem I seem to be having is that I can’t open the form referencing the two criteria fields from frmGaugeStartup using a traditional docmd.openform command. When I use the following I get an error:

Dim stLinkCriteria As String
stLinkCriteria = "TransGauge.[GA_Date] = '" & Date1 & "' _
AND zInfoTanks.[T_LOC_ABBR] = '" & Ref1 & "'"
DoCmd.OpenForm "frmGaugeLog", , , stLinkCriteria, acFormEdit, acWindowNormal

The error I get says the following:

Run-time error ‘107’:
The column prefix ‘zInfoTanks’ does not match with the table name or alias name used in the query.

Am I going about this wrong? If I don’t use linked tables as the source for frmGaugeLog and only use the table TransGauge then I’m able to open ok, but without being able to use the Location criteria, as that is stored on the zInfoTanks table.

Any help or pointers would be greatly appreciated and I can stop pulling out my hair. Thanks in advance.

James

 

[Norris68]

It sounds like you are not INNER JOINing the zInfoTanks table in your second form - therefore there is no field to apply the criteria to. There are two ways round it. 1) JOIN the second table to your form record source - this may cause problems with updates if you try to alter data from both tables at once. 2) Massage your criteria a little ...

Assuming PK field of TransGauge is called TGID, make your criteria ...

stLinkCriteria = "TGID IN (SELECT TransGauge.TGID FROM TransGauge INNER JOIN
zInfoTanks ON TransGauge.GA_Tank = zInfoTanks.T_ID WHERE TransGauge.[GA_Date] = '" & Date1 & "' _
AND zInfoTanks.[T_LOC_ABBR] = '" & Ref1 & "')"

This will limit the TGID to those matching your initial criteria, but without the need to JOIN the second table in your form record source.

 

[Jimmy211]

Thanks for responding Norris68. I tried using the stLinkCriteria you suggested and got the same error message of the query not being able to find the zInfoTanks reference. Here is a copy of what I used (my PK for the TransGauge is actually GA_ID):

stLinkCriteria = "GA_ID IN (SELECT TransGauge.GA_ID FROM TransGauge INNER JOIN zInfoTanks ON TransGauge.GA_Tank = zInfoTanks.T_ID WHERE TransGauge.[GA_Date] = '" & Date1 & "' AND zInfoTanks.[T_LOC_ABBR] = '" & Ref1 & "')"

So, modifying your suggestion a little I also tried using this statement:

stLinkCriteria = "GA_DATE, T_LOC_ABBR IN (SELECT TransGauge.*, zInfoTanks.T_LOC_ABBR FROM TransGauge INNER JOIN zInfoTanks ON TransGauge.GA_Tank = zInfoTanks.T_ID WHERE TransGauge.[GA_Date] = '" & Date1 & "' AND zInfoTanks.[T_LOC_ABBR] = '" & Ref1 & "')"

I got an invalid SQL statement message using this select statement.

As for your suggestion #1, I was under the impression I already had joined the two tables for the second form record source as show above in the SQL statement which goes like this:

SELECT TransGauge.*, zInfoTanks.T_LOC_ABBR
FROM TransGauge INNER JOIN
zInfoTanks ON TransGauge.GA_Tank = zInfoTanks.T_ID
ORDER BY zInfoTanks.T_INDEX


Any more suggestions? Thanks in advance.

James

 

[Jimmy211]

I found something that works. Actually Norris68, your stLinkCriteria worked under the following conditions:
1) I changed the record source for the frmGaugeLog form to only reference the transaction table TransGauge, taking off the reference to the lookup table zInfoTanks.
2) I added a GA_ID autonumber field to my TransGauge table. I had forgotten earlier that my PK was actually a combination of GA_DATE and GA_Tank. Sorry.

Works pretty good now. Thanks for the help.

James