Text Box Control Source

[crhodus]

Hi Everyone,

I came across a last minute issue just before launch time for a form that I'm working on. Why does this always happen?

Anyway, my form is populated by a query named qryPopulateRenewalForm. I have added a text box to my form named txtHiddenPayment. I want txtHiddenPayment to be populated by a different query, named qryPaymentCount. The query is a simple count query:

SELECT count(*) AS OnlinePaymentCount
FROM tblOnlineRenewalInfo
WHERE tblOnlineRenewalInfo.LIC_NUMBER=Forms![SW_form]!SLIC_NUMBER;

The text box txtHiddenPayment will not bee seen by the user and nothing will be entered into this textbox by the user. How can I populate this textbox with the number returned from qryPaymentCount?

I tried merging this query with the main query that populated data on the Form, qryPopulateRenewalForm. When I did this, it caused issues that are too long to explain in this post.

Can someone tell me how to do this?

Thanks,
Crhodus

 

[KenHigg]

Maybe you could use a dcount()?

 

[Dennisk]

use a function that contains the code to open the query as a recordset to retrieve the single return value and use that as the function return variable.

Then place the following code in the control source of the text box to recieve the value

=myfunction(anyParameters)

 

[crhodus]

Hi KenHigg,

Thanks for the quick response. It looks like this might work. I tried adding the following to the control source for txtHiddenPayment:
=DCount("tblOnlineRenewalInfo","OnlinePaymentCount","tblOnlineRenewalInfo.LIC_NUMBER=Forms![SW_form]!SLIC_NUMBER")

But I'm receiving #Error for the value in the textbox.

Crhodus

 

[KenHigg]

DCount("tblOnlineRenewalInfo","OnlinePaymentCount ","tblOnlineRenewalInfo.LIC_NUMBER= " & Forms![SW_form]!SLIC_NUMBER)

Or

DCount("tblOnlineRenewalInfo","OnlinePaymentCount ","tblOnlineRenewalInfo.LIC_NUMBER= '" & Forms![SW_form]!SLIC_NUMBER & "'")

???

 

[crhodus]

Hmm.... It still seems to be receiving the same error. How would go you about using a record set in vba?

Thanks,
Crhodus

 

[KenHigg]

Private Sub Form_Current()
Me.txtHiddenPayment = DCount("tblOnlineRenewalInfo", "OnlinePaymentCount ", "tblOnlineRenewalInfo.LIC_NUMBER= " & Forms![SW_form]!SLIC_NUMBER)
End Sub


???

 

[boblarson]

You shouldn't need the extra tblOnlineRenewalInfo part in the third argument, and the field comes first and THEN the table:

Private Sub Form_Current()
Me.txtHiddenPayment = DCount("OnlinePaymentCount",[color=red]"tblOnlineRenewalInfo"[/color], "LIC_NUMBER=" & Forms![SW_form]!SLIC_NUMBER)

End Sub

 

[crhodus]

Thanks everyone!

Bob, I ended up using your code, with one exception. I think part of my problem was the "OnlinePaymentCount" piece in the DCount. OnlinePaymentCount is not a field in the table, it is just a name that I had been assigning to the number of records. When I changed this to a field in the table, it seems to work. Here was my final solution:

Me.txtHiddenPayment = DCount("[COLOR="Red"]LIC_NUMBER[/COLOR]","tblOnlineRenewalInfo", "LIC_NUMBER=" & Forms![SW_form]!SLIC_NUMBER)

I might actually get to eat lunch today!

 

[boblarson]

glad you got it sorted.