assigning the value from a select statement to a variable

[ramprat]

Hi All,

I'm essentially trying to extract a value from the pre_05_growth_f actor column of my traffic table and assign it to a variable to be used later.

Does anyone know why the code below returns "The growth is 0" when I run this code as part of an on-click event of a button on a form but if I isolate the Select statement and create a query with it in SQL View and then run it I get the correct result showing up from my table? This tells me my syntax is correct so why can I not assign the value from a select query to a variable? Thanks for any and all help.

num_pre_05_growth = "SELECT traffic.PRE_05_GROWTH_FACTOR FROM traffic WHERE (((traffic.PCID)=[forms]![count_stations_form]![pcid]) AND ((traffic.COUNT)>0) AND ((traffic.TRAFFICID)=(SELECT Min(Traffic.TRAFFICID) AS MinOfTRAFFICID FROM Traffic where traffic.pcid = forms![count_stations_form]![pcid])))"
MsgBox "The growth is " & Val(num_pre_05_growth)

[ChipR]

That SQL statement is not being executed anywhere. You are taking the Val() of that string.

[ramprat]

Thanks Chip. So are you saying that my SQL is not being executed? What would be the proper format to have it execute and populate my variable with the value of the query? I appreciate you taking the time to help me with this Chip.I thought I was structuring this correctly but apparently I'm not.

[ChipR]

Normally I use a DLookup to grab a value and store it in a control or a string. I'm not sure if you would be able to nest all that in the where condition of the DLookup or whether you would have to use multiple ones, or just base it on a query, depending on how many times you have to run this.

[ramprat]

ChipR I think I can simplify the where criteria so that I might be able to use the DLookup. I'll give it a try. Thanks for your advice

[NeoPa]

Not too straightforward as it happens.

Try the following. It may be that this code is within the module for the form so Me. can be used instead of the Forms(...) version :

Dim strResult As String, strWhere As String
Dim strPCID As String, strTrafficID As String

strPCID = Forms("count_stations_form").pcid
strWhere = Replace("[PCID]=%P AND [Count]>0", %P, strPCID)
strTrafficID = DMin("[TrafficID]", "[Traffic]", strWhere)
strWhere = Replace("[PCID]=%P AND [TrafficID]=%T", "%P", strPCID)
strWhere = Replace(strWhere, "%T", strTrafficID)
strResult = DLookup("[PRE_05_GROWTH_FACTOR]", "[Traffic]", strWhere)

NB. Your SQL also allowed the possibility of the TrafficID matching a record whose [COUNT] value was not >0. This version doesn't.

PS. This assumes that TrafficID & PCID together make a unique reference.

[ramprat]

Thanks NeoPa,

I was able to get the Dlookup to work but now the last 3 lines of code below cause my database to crash. The funny thing is they do populate my traffic table with the necessary value but after that the database crashes. If I remove the line "docmd.runs ql strsql" the database will not crash. it won't update the traffic table as per the last 3 lines of code but it won't crash. Why do these last 3 lines of code cause it to crash. It has to be these laast 3 lines that make it crash. I'm simply trying to take the value I looked up with the DLookup and update a record in the traffic table with it. This seems simple enough. Why is it crashing?!?

'If the count station has an existing record in the traffic table and there are 2 traffic records on the traffic form 
'because someone has just entered a new count then move the oldest traffic count to the traffic_history table 
'and keep the most recent count in the traffic table 


DoCmd.RunSQL "insert into traffic_history select * from traffic where traffic.pcid = forms![count_stations_form]![pcid] " & _ 
"and traffic.count >0 and traffic.trafficid = (SELECT Min(Traffic.TRAFFICID) AS MinOfTRAFFICID FROM Traffic " & _ 
"where traffic.pcid = forms![count_stations_form]![pcid])" 

'Records the pre_05_growth_factor from the old count to populate into the new count record 

pre_05 = DLookup("[pre_05_growth_factor]", "traffic", "[pcid] = " & Forms![count_stations_form]![PCID]) 

MsgBox "the growth is " & pre_05 
Me.[TRAFFICID] = DMax("[TRAFFICID]", "traffic") + 1 


DoCmd.RunSQL "Delete * from traffic where traffic.pcid = forms![count_stations_form]![pcid] " & _ 
"and traffic.trafficid = (SELECT Min(Traffic.TRAFFICID) AS MinOfTRAFFICID FROM Traffic " & _ 
"where traffic.pcid = forms![count_stations_form]![pcid])" 
Forms!count_stations_form.Refresh 

'Sets the pre_05_growth_factor for the new traffic record from the value looked up above 

strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
"WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));" 
DoCmd.RunSQL strsql

[ChipR]

If you actually have

strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
"WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));"

You need to change it to

 strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
"WHERE (Traffic.PCID= [B]" & [forms]![count_stations_form]![pcid] & "[/B]);"

Assuming pcid is a number, because you want forms!count_sta tions_form!pcid to be evaluated, not part of the text string.

Looks like the Delete statement needs to be fixed also.

[ramprat]

Hi ChipR but the code works as is except for the database crashing. Are your suggestions designed to stop the crashing? Thanks again for helping.

ramprat

[ChipR]

Yes, I'm sure the messed up Where clause is causing you problems.

[ramprat]

Thanks I'll try it and let you know.

[ramprat]

No ChipR I'm afraid it still crashes after executing those last 3 lines of code even with your changes.

Would I be better off (although I think this is an ugly way to do it) to create a temp table to store the value and then use that table in my update query and then delete the temp table when finished?

[ChipR]

Try this. Do a
Debug.Print strSQL
right before
DoCmd.RunSQL strsql
and let's see what it looks like if you copy it from the immediates window in the vba ediotor (Alt+F11).

[ramprat]

ChipR,

Being rather new to VBA I'm not sure what you mean when you say "let's see what it looks like if you copy it from the immediates window in the vba ediotor (Alt+F11)" Is this something that I do before I try to run the code through the form because once I run the code as normal the database crashes and that's all she wrote. I have noticed that if the value of Pre_05 turns out to be null (in other words the previous traffic record has no value for this field) then the database doesn't crash. It only crashes if the Pre_05 has a value. Strange..

[ramprat]

I have to retract that last statement. It appears to be crashing any time even though I've hard coded a number into the statement in place of the Pre_05 variable just as a test.

strSQL = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = .05 " & _
                      "WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));"