By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

assigning the value from a select statement to a variable

P: 60
Hi All,

I'm essentially trying to extract a value from the pre_05_growth_factor 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.

Expand|Select|Wrap|Line Numbers
  1. 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])))"
  2. MsgBox "The growth is " & Val(num_pre_05_growth)
Jan 13 '09 #1
Share this Question
Share on Google+
25 Replies

Expert 100+
P: 1,287
That SQL statement is not being executed anywhere. You are taking the Val() of that string.
Jan 13 '09 #2

P: 60
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.
Jan 13 '09 #3

Expert 100+
P: 1,287
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.
Jan 13 '09 #4

P: 60
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
Jan 13 '09 #5

Expert Mod 15k+
P: 31,273
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strResult As String, strWhere As String
  2. Dim strPCID As String, strTrafficID As String
  4. strPCID = Forms("count_stations_form").pcid
  5. strWhere = Replace("[PCID]=%P AND [Count]>0", %P, strPCID)
  6. strTrafficID = DMin("[TrafficID]", "[Traffic]", strWhere)
  7. strWhere = Replace("[PCID]=%P AND [TrafficID]=%T", "%P", strPCID)
  8. strWhere = Replace(strWhere, "%T", strTrafficID)
  9. 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.
Jan 14 '09 #6

P: 60
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.runsql 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?!?

Expand|Select|Wrap|Line Numbers
  1. 'If the count station has an existing record in the traffic table and there are 2 traffic records on the traffic form 
  2. 'because someone has just entered a new count then move the oldest traffic count to the traffic_history table 
  3. 'and keep the most recent count in the traffic table 
  6. DoCmd.RunSQL "insert into traffic_history select * from traffic where traffic.pcid = forms![count_stations_form]![pcid] " & _ 
  7. "and traffic.count >0 and traffic.trafficid = (SELECT Min(Traffic.TRAFFICID) AS MinOfTRAFFICID FROM Traffic " & _ 
  8. "where traffic.pcid = forms![count_stations_form]![pcid])" 
  10. 'Records the pre_05_growth_factor from the old count to populate into the new count record 
  12. pre_05 = DLookup("[pre_05_growth_factor]", "traffic", "[pcid] = " & Forms![count_stations_form]![PCID]) 
  14. MsgBox "the growth is " & pre_05 
  15. Me.[TRAFFICID] = DMax("[TRAFFICID]", "traffic") + 1 
  18. DoCmd.RunSQL "Delete * from traffic where traffic.pcid = forms![count_stations_form]![pcid] " & _ 
  19. "and traffic.trafficid = (SELECT Min(Traffic.TRAFFICID) AS MinOfTRAFFICID FROM Traffic " & _ 
  20. "where traffic.pcid = forms![count_stations_form]![pcid])" 
  21. Forms!count_stations_form.Refresh 
  23. 'Sets the pre_05_growth_factor for the new traffic record from the value looked up above 
  25. strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
  26. "WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));" 
  27. DoCmd.RunSQL strsql
Jan 14 '09 #7

Expert 100+
P: 1,287
If you actually have
Expand|Select|Wrap|Line Numbers
  1. strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
  2. "WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));"
You need to change it to
Expand|Select|Wrap|Line Numbers
  1.  strsql = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR ='" & pre_05 & "'" & _ 
  2. "WHERE (Traffic.PCID= " & [forms]![count_stations_form]![pcid] & ");"
Assuming pcid is a number, because you want forms!count_stations_form!pcid to be evaluated, not part of the text string.

Looks like the Delete statement needs to be fixed also.
Jan 14 '09 #8

P: 60
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.

Jan 14 '09 #9

Expert 100+
P: 1,287
Yes, I'm sure the messed up Where clause is causing you problems.
Jan 14 '09 #10

P: 60
Thanks I'll try it and let you know.
Jan 14 '09 #11

P: 60
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?
Jan 14 '09 #12

Expert 100+
P: 1,287
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).
Jan 14 '09 #13

P: 60

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..
Jan 14 '09 #14

P: 60
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.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = .05 " & _
  2.                       "WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));"
Jan 14 '09 #15

Expert 100+
P: 1,287
Ok, you can also just do
msgbox strSQL
before the
and see what the statement looks like.

It is easier though to use the debugging feature. If you put a breakpoint in by clicking on the left margin next to the DoCmd.RunSQL, then your code will stop there. You can show the Immediate window under View->Immediate Window, or Ctrl+G, and you can type stuff in there and see what it evaluates to during run time. That window is where the output of Debug.Print goes, but you can also type straight into the window
and it will print the result.
Jan 14 '09 #16

P: 60
This is what comes up in the immediate window when I use the debug.print strsql

UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = .05 WHERE (((Traffic.PCID)=[forms]![count_stations_form]![pcid]));
Jan 14 '09 #17

Expert 100+
P: 1,287
Exactly. [forms]![count_stations_form]![pcid] is a variable, and needs to be evaluated and replaced by a number before the command can be run.
This is why you do
Expand|Select|Wrap|Line Numbers
  1.  ..." & [forms]![count_stations_form]![pcid] & "...
Jan 14 '09 #18

P: 60

I may be making progress. Modifying the code to what you said gives me the following in the immediates window:

UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = 0.05 WHERE (((Traffic.PCID)= 3012594 )) but alas it still crashes if I let it run the docmd.runsql strsql

Expand|Select|Wrap|Line Numbers
  2. strSQL = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = 0.05 " & _
  3.                       "WHERE (((Traffic.PCID)= " & [Forms]![count_stations_form]![PCID] & " ))"
Jan 14 '09 #19

Expert 100+
P: 1,287
Just in case, did you fix the Where clause of the Delete * ... statement also? What kind of crash are we talking about? Any message or just an option to send an error report and close?
Is that the end of the function? If not, put a breakpoint (or msgbox) on the next line after that and see if it gets there.
Jan 14 '09 #20

P: 60
I did not fix the where clause of the delete query because that part of the code is old and has been running fine for over a month and a half. Only the pre_05 variable part is new. Anyway, I put in a msgbox after the docmd.runsql and it doesn't get to it. Basically the continuous form (which is based on my traffic table ) goes white and then I get the option to send an error report and close.
Jan 14 '09 #21

Expert 100+
P: 1,287
Maybe try running that Update with hardcoded values and without the form open and see if it works. You can run it from a separate empty form or just call a function with a macro or create a query. Most of the crashes I get are caused by forms, subforms, and tab controls. I don't even dare switch from Form view to Design view anymore, I close and reopen in the other view. It's really frustrating.
Jan 14 '09 #22

Expert Mod 15k+
P: 31,273
A lot seems to have gone on in here since I was last here (only yesterday :S).

Give me a few minutes to catch up.
Jan 15 '09 #23

Expert Mod 15k+
P: 31,273
After tidying up the layout of your code to have a look at it, I have a couple of comments :
  1. The DoCmd.RunSQL line (#6) will copy any record (if there) to the Traffic_History table. Fine, but it doesn't then remove it from the Traffic table. Also, there doesn't seem to be a reliable method of determining whether or not there is more than one matching record.
  2. Traffic.Count (I'm assuming) is what you use to determine if there are multiple records or not. This is not updated when you delete a record and is stored (rather than determined at the time) so I can't imagine it is reliable. Possibly I'm missing something here - but this looks all wrong to me.
  3. Your DLookup() on line #12 does not specify which record it should get its result from. If there were multiple matching records to start with, then there are still, and the DLookup() will select one at random (Not exactly random, but not predicatble either which is the important thing).
  4. The DoCmd.RunSQL line (#18) will delete a matching entry, but regardless of whether or not there is more than one. Min() will return the only value if there is only one.
  5. The DoCmd.RunSQL line (#25) treats Traffic.PRE_05_GROWTH_FACTOR as a string field (by passing the value in pre_05 as a string literal).
Although there are some problems with some of your code, I wouldn't expect line #25 to crash the database on its own. Invalid SQL should be handled quite adequately generally. It's possible though, that there is some corruption in your database that causes it to crash under certain circumstances.

I would suggest doing a Compact/Repair on your database as the first step (but DON'T delete the original until you're happy that the new version is working). Be sure to close the database first before executing this or it will automatically do it over itself - thereby losing the original.

When that has been done I'd consider looking again at your code and resolving the issues I've drawn attention to.

If after all that you still experience the same problem then we can look at things again.
Jan 15 '09 #24

P: 60
Hi ChipR and NeoPa,

I am happy to report that it appears that the problem has been solved. I created a workaround (sometimes we don't always see the simpler solution). I simply added the following line of code to the click event of the button on my form.
Expand|Select|Wrap|Line Numbers
  1. Me.Pre_05_growth_factor = DLookup("[pre_05_growth_factor]", "traffic", "[pcid] = " & Forms![count_stations_form]![PCID])
I put it in before the code that moves the old record into the traffic_history folder. In essence I'm (on my continuous form) updating the new traffic record's pre_05_growth_factor with the value stored in the record that is about to be moved to the traffic_history table. This is at the point when the new record has not yet been added to the traffic table so there is only one record in the traffic table for that particular pcid at the time that I'm doing this.

I must say that I could not have solved this without all the help from both of you. Thank you. You are certainly two of the reasons why this site is such a great source of advice!

Jan 15 '09 #25

Expert Mod 15k+
P: 31,273
Thanks for the vote of confidence. It's always nice to be appreciated :)

Good luck in taking your project even further.
Jan 15 '09 #26

Post your reply

Sign in to post your reply or Sign up for a free account.