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

CurrentDb.Execute Problem

MitchR
P: 63
Hello Folks;

I have run myself into a hole I cannot get of and need some assistance. I have a Form "Frm_ATandT_Activation_Center" that has a sub form "Frm_ATandT_Activation". Frm_ATandT_Activation_Center is standard in Form View with no Master or Child links to the Sub form. this allows me to show available SIM ID"S in inventory for activation. The Subform diplays as a datasheet that has a Query as the control source. So when the user opens the form as a whole they see the listing of available SIM ID"S and 2 textbox controls to enter a remedy ticket number and an Employee ID. I am also Capturing the date and the employee's user name that activates the SIM. I have everything working except the CurrentDb.Execute statement to matchup all the entered data to the SIM that is being activated. here is my after update event of a combo box that the user chooses that corresponds with the SIM number to be activated.
Code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text1_AfterUpdate()
  2. Dim strSim As String
  3. Dim strRemedy_Num As String
  4. Dim strEID As String
  5. Dim strUser As Variant
  6. Dim varRespond As Variant
  7. Dim strSQL As String
  8. Set Db = CurrentDb
  9. stDocName = "Frm_AtandT_Activation_Center"
  10. varSim = Me.SIM
  11. varUser = fOSUserName
  12. varEID = Forms.Frm_ATandT_Activation_Center.EID
  13. varRemedy = Forms.Frm_ATandT_Activation_Center.Remedy_num
  14. varUser = Forms.Frm_ATandT_Activation_Center.Text18
  15. Date = Forms.Frm_ATandT_Activation_Center.Used
  16.     Respond = MsgBox("Are you Ready to issue this Sim -->" & varSim & " <-- to a Hand Held Device? " & _
  17.                 "Using Remedy Number " & varRemedy & vbCrLf & _
  18.                 " Employee ID Number " & varEID & " ?", vbYesNo, "Ready to Complete Activation? ")
  19. If Respond = vbYes Then
  20.  
  21.       CurrentDb.Execute "UPDATE tbl_SimInventory SET [Used] = " & Date & " , SET [UserNam] = " & varUser & " , 
  22.  Set [Remedy_Num] = " & varRemedy & ", Set [EID] = " & varEID & " WHERE SIM = " & Me.SIM_list
  23.  
  24.  
  25.     DoCmd.Close
  26.     DoCmd.OpenForm stDocName, acNormal, , , , acWindowNormal
  27.  
  28.  
  29. If Respond = vbNo Then
  30.        MsgBox "You may complete any changes needed for this activation", , " No Worries! "
  31. Forms.Frm_ATandT_Activation_Center.Used = Null
  32. Forms.Frm_ATandT_Activation_Center.Remedy_num = Null
  33. Forms.Frm_ATandT_Activation_Center.EID = Null
  34. Forms.Frm_ATandT_Activation_Center.UserNam = Null
  35. Me.Text1 = "Inventory"
  36. End If
  37. End If
  38. End Sub
  39.  

My main goal is to fix my CurrentDb.Execute to enter the data to the corresponding SIM ID
Thank you so very much for your help.
Jun 2 '08 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
Hi, Mitch.

SQL statement you pass to Execute method contains numerous syntax errors. I suggest you to create it and test in query design to get a working one.

And, just out of curiosity, was that a bad idea to insert records via bound form?

Regards,
Fish
Jun 3 '08 #2

Post your reply

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