Print value from query on form

[elisharae]

I am trying to print the value from a query on my form.

So, I have a text box on the ClaimClient_MainForm form called ClaimCount.

My query goes like this:

dim sql as string
sql=SELECT Claim.ClientID, Count(Claim.ClaimID) AS CountOfClaimID
FROM Claim
GROUP BY Claim.ClientID
HAVING (((Claim.ClientID)=[forms]![ClaimClient_MainForm]![ClientID]))

I would like the value of ClaimID to write to the ClaimCount text box on my form.

How do I execute this query and make this little write statement happen?

Thanks,
Julie

 

[Laois]

I would forget the query and use a Dlookup

Put this in the Control Source of the text box.

DlookUp("[ClaimID]","Claim","[ClientID]="&)=[Forms]![ClaimClient_MainForm]![ClientID])

Hope this helps

Dermot

 

[Laois]

My previous post had an error, sorry.

Put this in the Control Source of the text box.

DlookUp("[ClaimID]","Claim","[ClientID]="&[Forms]![ClaimClient_MainForm]![ClientID])

Dermot

 

[Steve101]

A minor correction to the above post. You must put an equal sign to the left of the Dlookup; ie:

=DlookUp("[ClaimID]","Claim","[ClientID]="&[Forms]![ClaimClient_MainForm]![ClientID])

There is also an assumption that the ClientId is defined as a numeric field - which it probably is; However, if its
not, then you'll need to make some minor changes, as illustrated below:

=DlookUp("[ClaimID]","Claim","[ClientID]='"&[Forms]![ClaimClient_MainForm]![ClientID] & "'" )

What this does is "surround" the ClientId with single quotes, to allow it to match up with a non numeric ClientId. As stated, you wont need or want to do this, unless the client Id is defined as non numeric.




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[JulieS]

I'm not familiar with the DLookup function (help was of no use when looking it up) - how does this count the number of claims for the client?
I also tried to set the control source of the text box to my count query (=[ClaimCount]![CountOfClaimID]) where countOFClaimID is the count function in the query, but it just resulted in #Name.

Thanks for your help,
Julie