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

Why do these 3 lines of code work but crash my database?

P: 60
I am using a Dlookup to pull a value from my traffic table for a record prior to moving that record to a traffic_history table. I want to take the value and store it in a variable and then populate a new record in the traffic table with that value based on a unique PCID. This code is triggered by the click event of a button on my form after someone enters a new traffic count into my continuous form. The code takes the old count and puts it into the history table and then deletes the old count from the traffic table leaving only the newest count. The code actually works but it crashes my database. I have isolated the cause of the crash to the last 3 lines of code. Any idea why this is happening?

If I just 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. 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
  28.  
Jan 14 '09 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,287
Answered in other thread.
Jan 14 '09 #2

Post your reply

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