Thru VBA, reference a field that is in query twice

[BakerUSMC]

Hello to all,

I have a form (frm_paymentform) that has a subform (subfrm_paymentform_payment) on it. On the Form_Load event of frm_paymentform I have the following code:

    If DCount("[Sponsorship ID]", "qry_paymentform", "[Sponsorship ID]=" & Me.[sponsoring.Sponsorship ID]) <> "0" Then
    
        Me.Number_of_Payments_Made = DCount("[Sponsorship ID]", "qry_paymentform", "[Sponsorship ID]=" & Me.[sponsoring.Sponsorship ID])

    Else
    
        Me.Number_of_Payments_Made = "0"
    
    End If

In the query (qry_paymentform) that frm_paymentform is based off of, there are 2 tables being referenced (sponsoring & sponsoring_options). Each of these tables has the field (Sponsorship ID) and they are both referenced in the query.

When I open frm_paymentform and after I enter the Sponsor ID for whose records I am looking for, I get an error stating: "The Specified field '[Sponsorship ID]' could refer to more than one table listed in the FROM clause of your SQL statement".

Here is the SQL of the qry_paymentform:

SELECT sponsoring.[Sponsorship ID], sponsoring.[Orphan ID], sponsoring.[Sponsor ID], sponsoring.[Term of Sponsorship], sponsoring.[Start of Sponsorship], sponsoring.[End fo Sponsorship], sponsoring.[Payment Type], sponsoring.[Payment Schedule], sponsoring.[Total Number of Payments Due], sponsoring.[Number of Payments Made], sponsoring.[Total Amount Due], sponsoring.[Total Amount Paid], sponsoring.[Total Overdue Payments], sponsoring.[Record Active?], sponsoring.[Date Last Updated], sponsoring_options.[Needs Monthly Reminder], sponsoring_options.[Needs Semi-Annual Update], sponsoring_options.[Date last SAU sent], sponsoring_options.[Correspondence Language], sponsoring_options.[Sponsoring Status], sponsoring_options.Notes, sponsoring_options.[Correspondence Type], sponsoring_options.[Sponsorship ID]
FROM sponsoring LEFT JOIN sponsoring_options ON sponsoring.[Sponsorship ID] = sponsoring_options.[Sponsorship ID]
WHERE (((sponsoring.[Sponsorship ID]) Is Not Null) AND ((sponsoring.[Sponsor ID])=[Please enter Sponsor ID]));

How can I reference only one of the Sponsorship ID fields from VBA?

Thanks for your help!!!!

BakerUSMC

 

[lupins46]

Do you need to include both fields?

 

[BakerUSMC]

No, I only need the (Sponsorship ID) from the (sponsoring) table to do that calculation that I need...

THanks

 

[lupins46]

So remove the other one from the Select then.

 

[BakerUSMC]

I can't because its needed to update the fields in its table and both tables are linked by the Sponsorship ID field....

 

[PHV]

And what about simply this ?
Me.Number_of_Payments_Made = DCount("*", "qry_paymentform", "sponsoring.[Sponsorship ID]=" & Me.[Sponsorship ID control name])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BakerUSMC]

PHV,

Thanks for your post...

I tried what you suggested and also tried to incorporate that into my If/then clause but when I ran the form, I got the following error:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation Object 'Please enter Sponsor ID."

Not sure what to do...

 

[PHV]

Seems that your query is a parametized query ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BakerUSMC]

I believe so...

I am requesting the user to enter a Sponsor ID and the query will find all records that have a Sponsorship ID from the Sponsoring table and any records with a matching Sponsorship Id from the Sponsoring_options table...