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]"

[Code] ....

View Replies


ADVERTISEMENT

Modules & VBA :: Syntax Error In INSERT INTO Statement

Jan 22, 2014

I am having a problem with below and getting a run-time error 3134

Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)

CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"

The field 'Order' in Model_types is a Long Integer.

View 4 Replies View Related

Modules & VBA :: Syntax Error With INSERT Statement And Subform

Mar 13, 2015

I am getting a syntax error on my SQL statement.

On a form I have a sub form containing the field txtGuestID - whose control source is GuestID.

On the main form I have a button that fires the code below.

I am sure I am not referring to the control txtGuestID correctly.

Code:
Private Sub cmdInbound_Transport_Click()
Dim iProductID As Integer
Dim sSQL As String
On Error GoTo cmdInbound_Transport_Err

[Code] ....

View 6 Replies View Related

Modules & VBA :: Insert Into Statement Using Data From Form Control?

Jun 29, 2014

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)

View 10 Replies View Related

Syntax Error In Insert Into Statement

Feb 1, 2007

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

View 2 Replies View Related

General :: Syntax Error In INSERT INTO Statement

Jul 31, 2012

I'm trying to run the following query

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);

and i'm receiving the error stated above

what im doing wrong?

View 12 Replies View Related

Modules & VBA :: SQL INSERT INTO VALUES Statement

Jun 7, 2013

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:

Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"

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:

Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...

View 6 Replies View Related

Modules & VBA :: DLookup In INSERT Statement

Jul 10, 2013

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:

Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"

DoCmd.RunSQL SQLtext4

Note: My DLookup works fine by itself for other uses.

View 5 Replies View Related

Modules & VBA :: INSERT INTO SELECT Statement

Mar 4, 2014

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.

View 4 Replies View Related

Run Time Error 3134 - SQL Statement

Mar 16, 2005

Good day,

I was wondering if I can get some help with my sql statement, b/c I am a run-time error. I have attached my db but the statement is as follows:

SqlStr = "INSERT INTO tblpcomaward (recempid, reclname, recfname, recdate, givempid, givelname, givefname, desc, value, rebill, comments) Values" _
& "('" & Forms!frmpcom!txtempid & "', '" & Forms!frmpcom!txtlastname & "','" & Forms!frmpcom!txtfirstname & "', #" & Forms!frmpcom!txtdategiven & "#," _
& "'" & Forms!frmpcom!txtgiverempid & "', '" & Forms!frmpcom!txtgivelname & "', '" & Forms!frmpcom!txtgivefname & "', '" & Forms!frmpcom!cbodesc & "', '" & Forms!frmpcom!txtvalue & "', '" & Forms!frmpcom!cborebill & "', '" & Forms!frmpcom!txtcomments & "')"


Thanks for any help.

View 4 Replies View Related

Run Time Error 3134 - SQL Statement

Mar 21, 2005

Good day,

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:

SqlStr = "INSERT INTO tblec (cbo1c, cbo2c, cbo3c, cbo4c, cbo5c, cbo6c, cbo7c, cbo8c, cbo9c, cbo10c, cbo11c, cbo12c, cbo13c, cbo14c, cbo15c, cbo16c cbo17c, cbo18c, cbo19c, submdate, ectl) Values" _
& "('" & Forms!frmenrollcenter!cbo1c & "', '" & Forms!frmenrollcenter!cbo2c & "','" & Forms!frmenrollcenter!cbo3c & "', '" & Forms!frmenrollcenter!cbo4c & "'," _
& "'" & Forms!frmenrollcenter!cbo5c & "', '" & Forms!frmenrollcenter!cbo6c & "', '" & Forms!frmenrollcenter!cbo7c & "', '" & Forms!frmenrollcenter!cbo8c & "'," _
& "'" & Forms!frmenrollcenter!cbo9c & "', '" & Forms!frmenrollcenter!cbo10c & "', '" & Forms!frmenrollcenter!cbo11c & "', '" & Forms!frmenrollcenter!cbo12c & "'," _
& "'" & Forms!frmenrollcenter!cbo13c & "', '" & Forms!frmenrollcenter!cbo14c & "', '" & Forms!frmenrollcenter!cbo15c & "', '" & Forms!frmenrollcenter!cbo16c & "', '" & Forms!frmenrollcenter!cbo17c & "', '" & Forms!frmenrollcenter!cbo18c & "', '" & Forms!frmenrollcenter!cbo19c & "', #" & Forms!frmenrollcenter!txtsubmdate & "#, '" & Forms!frmenrollcenter!txtectl & "')"

View 4 Replies View Related

Modules & VBA :: Multiple Variables For Insert INTO SQL Statement

Sep 28, 2013

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'

Too few parameters. Expected 1.

View 5 Replies View Related

Modules & VBA :: Insert Statement Not Saving Data To Tables

Jul 16, 2013

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

[code]...

View 11 Replies View Related

Modules & VBA :: Allow User To Select Record From Subform And Add It To Table - Insert Statement

Jun 18, 2013

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, "")

[Code] ....

View 1 Replies View Related

Modules & VBA :: Dlookup Resulting In Null Value

Aug 15, 2015

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?

View 5 Replies View Related

"INSERT/UPDATE" Statement Error

Sep 19, 2004

I try to do an INSERT statement throught my ASP to add records to my Access DB. However I get an error when I execute this. The code in ASP is -

Code: strSQL = "INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('" &cint(intLocid)& "', '" &cint(sWeek)& "', '" &cint(sMonth)& "', '" &cint(sYear)& "', '" &cint(sRating)& "', '" &sRemark& "', '" &sAction& "', '" &formatdatetime(date,vbshortdate)& "');" objConn.Execute strSQL

I get the error page -



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 -

Code: INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('50', '2', '9', '2004', '3', 'asd', 'asdasd', '9/20/2004');

I can't understand if it works in Access, who not work through ASP?? Is there somethings wrong somewhere? I cannot see it..

Please help..

View 2 Replies View Related

Modules & VBA :: INSERT INTO Runtime / Syntax Error?

Jun 11, 2013

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] ....

View 6 Replies View Related

Modules & VBA :: Syntax Error In Sql Statement

Dec 9, 2014

sort this error out:

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

View 2 Replies View Related

Modules & VBA :: Update Query End Of Statement Error

Aug 10, 2015

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.

View 2 Replies View Related

Modules & VBA :: Access 2003 - Compile Error Expected / End Of Statement

Apr 19, 2014

I'm using MS Access 2003 and having difficulty with VBA.

Code:
strSql = "Insert into TBLCONDUTOR (Codigo, pretensao, ncarta, nlicenca, nome, apelido, dnascimento, contacto1, contacto2, email, nbicc, nnif, morada, localidade, cidade, cpostal, _
imagemcondutor, catAM, dataemissaoAM, datavalidadeAM, catA1, dataemissaoA1, datavalidadeA1, catA2, dataemissaoA2, datavalidadeA2, catA, dataemissaoA, datavalidadeA, _
catB1, dataemissaoB1, datavalidadeB1, catB, dataemissaoB, datavalidadeB, catBE, dataemissaoBE, datavalidadeBE,

[Code] ....

View 3 Replies View Related

Modules & VBA :: Runtime Error 3071 When Used With IF Statement To Highlight Incorrect Entry

Jun 23, 2014

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:

Code:
DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview

Which works perfectly.

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:

DoCmd.OpenReport "RepStatisticsAllJobs", acViewPreview

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.

View 10 Replies View Related

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

Jun 18, 2013

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?

Code:

sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "

CurrentDb.Execute sSQL, dbFailOnError

View 5 Replies View Related

Forms :: Hide Form Controls And Resulting White Space

Sep 20, 2013

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.

View 4 Replies View Related

Modules & VBA :: Run-time Error 438 In 2010

Mar 1, 2014

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.

View 3 Replies View Related

Modules & VBA :: Run Time Error 91 - Print To PDF

Aug 3, 2013

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)

[Code] ....

View 5 Replies View Related

Modules & VBA :: Run Time Error 287 When Sending Emails

Dec 20, 2013

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)

[Code] ....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved