Event Procedures in Data Access Pages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon
I've decided to make my application into a DAP for data entry only. Once the DAP is filled out, I want to run an event procedure/script that will add the data from the DAP into my data table. The crux of this is that I can't seem to use VBA in the Script Editor. I'm merely looking to run some SQL Inserts and Updates, really. Here's the code from the Access form version of the same DAP

Sub Done_Click(
DoCmd.RunSQL "INSERT INTO tblrequests ([UserName], [UserDiv], [Dept], [Function], [Supervisor], [Reason], [Current], [Desc], [Notes], [Flagged], [ReqStatus], [Aisles]) VALUES (Forms!frmRequest.UserName, Forms!frmRequest.UserDiv, Forms!frmRequest.DeptName, Forms!frmRequest.FunctName, Forms!frmRequest.Super, Forms!frmRequest.Reason, Forms!frmRequest.CurrentStd, Forms!frmRequest.TaskDesc, Forms!frmRequest.Notes, True, 'Requested', Forms!frmRequest.Level)
DoCmd.SendObject acSendQuery, "qryNewReqs", acFormatXLS, "Derek Wittman", fOSUserName() & "; " & Forms!frmRequest.Super, , "A New Request for Engineered Labor Standards from " & UserDiv, "Attached, you will find a request for Engineered Labor Standards", Fals
DoCmd.RunSQL "Update tblRequests set Flagged = False where UserName = dlookup('[UserName]','tblUserList','UserID = fOSUserName()')
DoCmd.Clos
End Su

Any and all help would be appreciated (as always, watch out for the word wrap)

Thank you
Derek
 
i haven't used data access pages, but i'd be surprised if you can get away with that metho

rather than: DoCmd.RunSQL "INSERT INTO tblrequests ([UserName], [UserDiv], [Dept], [Function], [Supervisor], [Reason], [Current], [Desc], [Notes], [Flagged], [ReqStatus], [Aisles]) VALUES (Forms!frmRequest.UserName, Forms!frmRequest.UserDiv, Forms!frmRequest.DeptName, Forms!frmRequest.FunctName, Forms!frmRequest.Super, Forms!frmRequest.Reason, Forms

you will probably have to concatenate the string
DoCmd.RunSQL "INSERT INTO tblrequests ([UserName], [UserDiv], [Dept], [Function], [Supervisor], [Reason], [Current], [Desc], [Notes], [Flagged], [ReqStatus], [Aisles]) VALUES ('" & Forms!frmRequest.UserName & "','" & Forms!frmRequest.UserDiv & "','" & Forms!frmRequest.DeptName, Forms!frmRequest.FunctName... et

this way, the SQL string is populated with the correct values before being passed to the Jet engine (or whatever the data access pages use
make sure any text values for fields within the SQL string end up being included in quotation mark
if this code is within the form's module, replace the "Forms!frmRequest.UserName" with "Me!UserName" etc. it's easier and if you change the name of the form, the code will still work
 
Back
Top