473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

assigning the value from a select statement to a variable

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)
  3.  
Jan 13 '09 #1
25 5169
ChipR
1,287 Expert 1GB
That SQL statement is not being executed anywhere. You are taking the Val() of that string.
Jan 13 '09 #2
ramprat
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
ChipR
1,287 Expert 1GB
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
ramprat
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
NeoPa
32,556 Expert Mod 16PB
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
  3.  
  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
ramprat
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 
  4.  
  5.  
  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])" 
  9.  
  10. 'Records the pre_05_growth_factor from the old count to populate into the new count record 
  11.  
  12. pre_05 = DLookup("[pre_05_growth_factor]", "traffic", "[pcid] = " & Forms![count_stations_form]![PCID]) 
  13.  
  14. MsgBox "the growth is " & pre_05 
  15. Me.[TRAFFICID] = DMax("[TRAFFICID]", "traffic") + 1 
  16.  
  17.  
  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 
  22.  
  23. 'Sets the pre_05_growth_factor for the new traffic record from the value looked up above 
  24.  
  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
ChipR
1,287 Expert 1GB
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
ramprat
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.

ramprat
Jan 14 '09 #9
ChipR
1,287 Expert 1GB
Yes, I'm sure the messed up Where clause is causing you problems.
Jan 14 '09 #10
ramprat
60
Thanks I'll try it and let you know.
Jan 14 '09 #11
ramprat
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
ChipR
1,287 Expert 1GB
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
ramprat
60
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..
Jan 14 '09 #14
ramprat
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]));"
  3.  
Jan 14 '09 #15
ChipR
1,287 Expert 1GB
Ok, you can also just do
msgbox strSQL
before the
DoCmd.RunSQL
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
?strSQL
or
?1+1
and it will print the result.
Jan 14 '09 #16
ramprat
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
ChipR
1,287 Expert 1GB
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
ramprat
60
ChipR,

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
  1.  
  2. strSQL = "UPDATE Traffic SET Traffic.PRE_05_GROWTH_FACTOR = 0.05 " & _
  3.                       "WHERE (((Traffic.PCID)= " & [Forms]![count_stations_form]![PCID] & " ))"
  4.  
  5.  
Jan 14 '09 #19
ChipR
1,287 Expert 1GB
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
ramprat
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
ChipR
1,287 Expert 1GB
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
NeoPa
32,556 Expert Mod 16PB
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
NeoPa
32,556 Expert Mod 16PB
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
ramprat
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!

Ramprat
Jan 15 '09 #25
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: KathyB | last post by:
Hi, figured out where I was going wrong in my post just prior, but is there ANY way I can assign several variables to then use them in an Update statement, for example (this does not work): ...
2
by: Hans-Michael Rupp | last post by:
Hello! I need to a assign a node(-set) conditionally to a varialbe like: <xsl:variable name="foo"> <xsl:if test="$bar=''"> <!-- how can I access a node here ?--> </xsl:if> <xsl:if...
2
by: Ian Rutgers | last post by:
In transforming the <towing> element (boolean) I want the xlst document test the <towing> element for the "true" text and if found output "available", otherwise output "not available". While the...
4
by: dmiller23462 | last post by:
So here's my problem.....I need to set up different email distributions based on which option in the following Select form has been chosen....For instance if "Putaway" is chosen it needs to email...
25
by: Sourav | last post by:
Suppose I have a code like this, #include <stdio.h> int *p; void foo(int); int main(void){ foo(3); printf("%p %d\n",p,*p);
1
by: mail2sakthi2003 | last post by:
Hi, In this program, i have established the connection and it even works. i get a value from the table and checked it with the given value . if both r same, i have to assign that value to a...
1
by: thanawala27 | last post by:
Hi, I am a beginner here. I was not knowing how to assign the value of a Dropdown menu to a Hidden type variable. Code:...
20
by: teddysnips | last post by:
Weird. I have taken over responsibility for a legacy application, Access 2k3, split FE/BE. The client has reported a problem and I'm investigating. I didn't write the application. The...
7
by: jodleren | last post by:
Hi I have been looking into php.net, but could not find any proper description. There is a way of assigning variables from functions, while at the same time using them in e.g. an if. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.