Turning Query Results into Variables

Pezzini

Registered User.
Local time
Today, 03:33
Joined
Dec 9, 2015
Messages
20
I have a database with an input form which has many child forms. Each of the child forms should for the form add up to a certain number, for this example, 100%. If the result diviates from this number, I need to present a popup box to warn the user and force them to correct the value. This pop up I can do with little problem, what I'm struggling with is turning the query result into the variable to put into the pop up.

The query is:
SELECT [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, Sum([Tbl-InvCase].[IC%]) AS [SumOfIC%]
FROM [Tbl-InvCase]
GROUP BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway
HAVING ((([Tbl-InvCase].ProjectID)=fnProjectID()) AND (([Tbl-InvCase].Gateway)=fnGateway100()));


The return that I need to make a variable is SumOfIC%. Variable has been defined as intICcheck.

The code I have on leaving the child form is:

Private Sub Sub_InvCase_Exit(Cancel As Integer)
putProjectID Me.ProjectID.Value

putGateway100 Me.Gateway.Value
DoCmd.OpenQuery "Qry-SumIC", , acReadOnly

End Sub


which clearly defines the varibles to go into the query and runs the query, but now I need to output from the query to become fnICcheck so I can write the message box if the value returned isn't 100%.

No idea where to go from here. Can anyone help?

Thanks
 
If your query is saved a you can use a dlookup to get the result you are looking for from it.
 
Thanks Minty.


Was hopeful when I saw this, then I tried it and remembered that I've never yet been able to make a DLookup work and I appear to have retained the ability to stop them dead in their tracks. Hopefully it's something simple to someone else.


Here's the new process:
Private Sub Sub_InvCase_Exit(Cancel As Integer)

' set Project ID for summation query Qry-SumIC
putProjectID Me.ProjectID.Value
' set Gateway for summation query Qry-SumIC
putGateway100 Me.Gateway.Value

'Remove warnings for save query and the reset them
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry-SumIC", , acReadOnly
DoCmd.SetWarnings True

'Do DLookup to get variable
fniccheck = DLookup("SumOfIC%", "Tbl-SumIC", "ProjectID =" & fnProjectID And "Gateway = " & fnGateway100)

'Advise user of Total CI%
MsgBox "Total IC% : " & fniccheck, vbOKOnly

End Sub


I've tried a number of different permutations to get to this, but it won't run passed the DLookup.

Oh just in case it's needed, I've define fnICcheck as I've defined all other variables in a separate module within the same database, and I've checked all spellings aginst the table/column names, etc.


Can anyone tell me what I've done wrong? Thanks
 
Okay, you're not quite getting the methodology here... Which is understandable as it's not obvious at first.

Firstly remove that % from your query field name - it will cause you problems later.

dLookup is a Domain Lookup. In your case your Domain is the qry "Qry_SumIC" not your table. So your DLookup should be something like

Code:
fniccheck = DLookup("SumOfIC","Qry-SumIC", "[ProjectID] =" & fnProjectID & "And [Gateway] = " & fnGateway100)

This assumes bot fnProjectID and fnGateway100 are numbers not text values. You don't have to put square brackets around properly constructed field names (no spaces) - I just think it helps clarify what's being used as criteria and what's being passed in as values.

You don't need to open the query first - the dlookup will do that for you, and even if you did unless the qry updtes values you don't need to turn warning on or off.
 
Last edited:
That all makes sense, will give it a go when I'm back in work tomorrow. That said I foresee an issue - Gateway is a text feild not a number. Is that a problem?
 
Not a problem, but will need a small change to enclose the text criteria in quotes ;
Code:
fniccheck = DLookup("SumOfIC","Qry-SumIC", "[ProjectID] =" & fnProjectID & "And [Gateway] = [COLOR="Red"]'[/COLOR]" & fnGateway100 & "[COLOR="Red"]'[/COLOR]")
 
Woohoo!

It works - brilliant, thanks for all the assistance, it's really appreciated. Here, for anyone else that has similar problems, is the final code:

' set parameters for summation query Qry-SumIC
putProjectID Me.ProjectID.Value
putGateway100 Me.Gateway.Value

'Do DLookup to get variable
Dim myVar As Variant
' myVar = 123
myVar = DLookup("SumIC", "Qry-SumIC", "[ProjectID] =" & fnProjectID & "And [Gateway] = '" & fnGateway100 & "'")

'Advise user of Total CI%
MsgBox "Total IC% : " & myVar, vbOKOnly
 

Users who are viewing this thread

Back
Top Bottom