Modules & VBA :: INSERT Statement From Form Resulting In Run-time Error
Dec 4, 2013
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " INSERT INTO [tblTicket]"
I've got a form, user inputs a whole bunch of data and the data is stored into 2 different tables (using a form and subform), however i also need to copy a few fields from the subform into another table (as a new record) the table i intend to insert this data into (tblAdjustments) has four fields (that im interested in) Type, Reason, Quantity and Product (not the exact names) Type and reason are both just straight strings and will be the same each time, regardless of what the other two variables are, so i need type to equal "Finished Goods" and Reason to equal Produced further to that, the subfrom has 2 fields, batchPackedQTY (which will be quantity in the new table) and fgID (product in the new table
so i need an insert into statement that does this, ive tried INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY) ("Finished Goods", "Produced", Forms!SubFormBatches.fgID, Forms!SubformBatches.batchPackedQTY)
What is the syntax error in this Insert Into statement ?
INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace ='customer.demo' AND RunID =256
INSERT INTO Enrolled_Students (Last Name, First Name, Address, town/city, county, postcode, phone number, date of birth, age) SELECT Last Name, First Name, Address, Town/City, County, Postcode, Phone Number, Date of Birth, Age From Candidate Details Where IsNumeric (Student ID);
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code: INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role ) SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role FROM tblPeople WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code: Dim strSQL As String Dim db As Database Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
I had a post last week with this same problem. Last time I was using a reserved word but I have ruled it out with this database because I changed every field name and I still get the error. I think it may have to do with the combo boxes I am using, but not really sure why I am getting the error. Any help would be great. The sql statement is:
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click() Dim strSQL As String Dim strSalesman As String Dim strContentArea As String DoCmd.SetWarnings False
[Code] ....
Error Microsoft Visual Basic popup Run-time error '3061'
I am trying to create a form in which users can save new supplier data to a database. I am using two insert statements which insert similar data in to two similar tables. The insert statements appear to have no problems and no errors are produced upon execution yet the new data doesn't save into the tables. Here's the code;
Code: Private Sub Command14_Click() Dim sqlstr As String Dim dbs As Database
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code: Private Sub Command3_Click() Dim dbs As Database Dim sqlstr As String Set dbs = CurrentDb Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "") Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code: Private Sub txtProductName_Click() Dim iProdType As Integer Dim ProductID As Integer iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value]) Note ProductID in an integer
How can I diagnose this or what could the solution be?
Microsoft JET Database Engineerror '80040e14' Syntax error in INSERT INTO statement. /PeriodicWorkScheduleRatingSubmit.asp, line 65
I tried doing a response.write onto the ASP (to get the actual SQL), got the query, copy-pasted it and ran it on MS Access - it worked fine there. The query is -
Why I get a runtime 3134 error on this piece of code.
They are all text values
Code:
Dim strUserName As String strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress Dim strIDValue As String strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1 Dim strAttribute As String
Code: If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero i = 1 Else i = DMax("ID", "tblupdate") End If Dim ssql As String Dim j, k As String j = "P" & i k = Environ("username") ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')" CurrentDb.Execute ssql, dbFailOnError
I'm trying to write an UPDATE line in VBA, but whenever I put the table name I get an "Expected End of Statement" error.This is line currently;
Code: strSQL = UPDATE "MASTER PLANNER" SET [SET 2] = TRUE WHERE "[ID] = " & Me.PlannerID
It doesn't matter whether I change the table name to square brackets, quotes or nothing at all, it always gives me the expected end of statement error.
In my database I am trying to produce a "Statistics" function. As part of this, the user will enter a "Start Date" and "End Date" in a form and then click a button which will open the requested report with the date drawn from a query. The code on clicking the "All Jobs" button is:
I am trying to stop the user from leaving the date fields blank or entering dates outside of the range of the database so I have tried the code:
Code: Private Sub AllSalesEnquiries_Click() If Me.DateFrom = "" Then Beep If MsgBox("You have not entered a start date", vbCritical, "Start Date Not Entered") Then
[Code] ....
If the user enters dates within the range of the database the report is presented correctly.
If the user does not enter a date or enters one outside of the range it produces the correct message box however if the user then corrects the mistake I receive a Runtime Error 3071 message. Clicking "Debug" highlights the final line of code:
I know that the code is correct because it works fine as stand alone code and it works if the user enters the correct dates so I am not sure where I am going wrong.
The date format works perfectly for the way dates are formatted in the database.
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078 The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
I have a checkbox that determines whether or not to display certain form controls. How can I also hide the resulting white space that comes from hiding the form controls?
Can I put all of the controls in some sort of container and hide the container? The form objects are all displayed in order, so it shouldn't cause issue.
Can I create a subform for the objects and hide it? This would make the main and subforms based off of the same table.
Occam's Razor would be the preferred philosophy in this case. Nothing too fancy is needed.
Previously my snapshot file was displaying thur this code but as 2010 Access, it produded subjected error and halt at below red color line.
Public Function LoadSnapshotFile(snpCtl As Object, strFilePath As String) As Boolean Const conSnpFinishedDownload As Integer = 4 With snpCtl .SnapshotPath = strFilePath
[Code]....
And it was runing well. But not now due to the produced error. I tried to make it pdf extension of the report file. It is updating the file at the path (where I kept the report) but problem is not displaying after click event on the form.
I have created a report in Access for around 800 uniquely identified line items. My end goal is to develop a script that will Print a PDF for each unique identifier and save them all in one folder and have each one named by unique identifier. Below is the script I have written, I cannot get through the Run-Time Error 91. The data is being derived from a SQL database.
Private Sub Command35_Click() Dim rst As ADODB.Recordset Set rst = Currentdb.OpenRecordset("SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", dbOpenSnapshot)
I have the following code in a module that sends out various email updates to me when I'm off site. On my PC it works perfectly whether I have Outlook open or not. I've installed the database on several other machines, but I'm getting a Run Rime Error 287 on the .Send line if the user has Outlook closed. Any setting somewhere in Outlook or Access that needs changing? Or is there something that can be changed in the code to ensure the email sends whether Outlook is open or not?
Code: Public Sub SendEmailNotification(strSubject as String, strMessage as String, strAddress as String) Dim olEmailApp As Object Dim olMsg As Object Dim strEmailBody As String Set olEmailApp = CreateObject("Outlook.Application") Set olMsg = olEmailApp.CreateItem(0)
