Can I be table specific in stLinkCriteria ? (1 Viewer)

[vealeww10]

I have a button on a form that I would like to take results to a report. This all works well with a single table but when I have multiple tables with the same ID field name "ID" it does not work. The form with the button on it comes from a query that has three tables on it all having ID as key field. (know now probably should not have done that). The query only has the ID from the table I want to use on report. Error indicates it has more than one ID field. Following is portion of event procedure.

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenReport stDocName, , , stLinkCriteria

My question, is there a way to be table specific in the stLinkCriteria command? If not, any thoughts on how to get around this/ Thanks WWV

 

[plog]

You can "alias" a field name in a query. So while you have 3 ID fields, you can assign them more specific names in a query:

SELECT TableA.ID AS IDTableA, TableB.ID AS IDTableB, TableC.ID AS IDTableC
FROM  TableA, TableB, TableC

Then, the report will know them as IDTableA, IDTableB, IDTableC respectively and you can fix your stLinkCriteria as appropriate.

 

[ebs17]

Of course you can.

The criteria you put together corresponds to the WHERE part of a complete SQL statement without the WHERE keyword. So it can look very diverse.

stLinkCriteria = "tblA.ID = " & Me!ID & " AND tblB.xDate < Date() AND tblC.Num IN (SELECT Num FROM tblX)"
Debug.Print stLinkCriteria     'look at the test

 

[vealeww10]

Thank you so much but now going to show my lack of knowledge. This code makes complete sense to be but not sure where/how to put it in a query. I'm learning and this site is great.

 

[ebs17]

but not sure where/how to put it in a query

There is nothing to insert into a query.

Your report has a table/query as RecordSource.
The WHERE argument of the OpenReport call is simply added as an additional filter.
Oh right. I wrote a little more in stLinkCriteria, just for illustration. You can probably see that you simply have to write the table name in front of the field name.

 

[vealeww10]

Changed my 3 tables ID"s to IDCASE, IDCUST, IDEMP (or I think I did) See SQL below.

SELECT Cases.ID AS IDCASE, Cases.Customer, Cases.Status, Cases.[INVOICE PD], Cases.[INVOICE PD DTE], Cases.Category, Cases.[Assigned To], Cases.Description, Cases.[Resolved Date], Cases.[Opened Date], Cases.Comments, Cases.[INVOICE PD], Cases.[INVOICE PD DTE], Customers.ID AS IDCUST, Customers.[First Name], Customers.[Last Name], Customers.Address, Customers.City, Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Business Phone], Cases.[Resolved Date], Customers.PRINTED, Customers.ESTIMATE, Customers.[ESTIMATE ACCEPTED], Customers.[DATE EST ACCEPTED], Employees.ID AS IDEMP, Employees.[Last Name], Employees.[First Name], Employees.[Last Name], Cases.SVCALLAMOUNT, Cases.[MATRL AMOUNT], [SVCALLAMOUNT]+[MATRL AMOUNT] AS [SVCPLUS MATRL], ([SVCALLAMOUNT]+[MATRL AMOUNT])*0.03 AS CCRDFEE, Cases.ID AS CASESID
FROM Customers RIGHT JOIN (Employees INNER JOIN Cases ON Employees.ID = Cases.[Assigned To]) ON Customers.ID = Cases.Customer;

Then, on a button on the form generated by the query, I used the following Event Procedure. Said it could not find IDCASE. It's a challange but I sure am having fun learning more. PS, I'm a young 79 year old enjoying this. THANKS

Private Sub Command79_Click()
On Error GoTo Err_Command22_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "INVOICE TO PRINT INVLIST"

stLinkCriteria = "[IDCASE]=" & Me![IDCASE]
DoCmd.OpenReport stDocName, , , stLinkCriteria

Exit_Command22_Click:
Exit Sub

Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click
End Sub

 

[vealeww10]

More info,,,, I want the button to show the invoice for that particular record.

I hope I'm not wearing out my welcome but you folks are the experts, Thanks Again.

 

[plog]

Said it could not find IDCASE

1. Is the data source of the report this query you posted? Just a sanity check--sometimes people miss the obvious.

2. IS [IDCASE] the name of a control on your form? Me![IDCASE] has 2 parts--Me! is what object to reference. You could explicitly state the name of a report or form, but when you use Me! it means whatever form/report the user is currently using. [IDCASE] is the control (e.g. input) on that object to go get the value from. So, on the form that you clicked the button on, is there actually a control called [IDCASE] on the form and not called something else?

Lastly, you will do yourself a lot of coding and querying favors if you only use alphanumeric characters in naming things. No spaces, no special characters.

 

[Pat Hartman]

three tables on it all having ID as key field.

Pretty silly to call all the PK's "ID". Give each one a unique name that reflects the table it defines and then use that same PK name as the FK. Oh so much more rational and makes relationships obvious as you look at tables without having to look at the relationship window.

 

[Gasman]

I must admit, I did the same when I started with Access, mainly I think, as that what Access generated when creating tables from external sources. ?

 

[vealeww10]

1. Is the data source of the report this query you posted? Just a sanity check--sometimes people miss the obvious.

2. IS [IDCASE] the name of a control on your form? Me![IDCASE] has 2 parts--Me! is what object to reference. You could explicitly state the name of a report or form, but when you use Me! it means whatever form/report the user is currently using. [IDCASE] is the control (e.g. input) on that object to go get the value from. So, on the form that you clicked the button on, is there actually a control called [IDCASE] on the form and not called something else?

Lastly, you will do yourself a lot of coding and querying favors if you only use alphanumeric characters in naming things. No spaces, no special characters.

The information was great... All working now. Thanks again.

 

[Pat Hartman]

When I create tables, I always specify the PK because I know I need one. When you let Access generate it, it has no idea what a good name might be so you get "ID" and then have to fix it later. Why? when you know you need the PK to begin with just start every table with one?