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
I get a runtime error that says "No value given for one or more required parameters" and I can't figure out what is wrong with the statement that it is referring to:
.Source = "select timage.*, InStr([Keywords],'" & [Forms]![search options]![searchtext] & "') AS Expr1, IIf(IsNull([forms]![search options]![combo121]),[category],[category]=[forms]![search options]![combo121]) AS Expr2" _ & " FROM timage WHERE (((InStr([Keywords],'" & [Forms]![search options]![searchtext] & "'))>0) AND ((IIf(IsNull([forms]![search options]![combo121]),[category],[category]=[forms]![search options]![combo121]))=-1));"
I'm not sure if more information is needed, but I thought perhaps something might jump out to a more experienced Access person.....
This may be a very silly question but I have the following code which is meant to take data from textboxes, checkboxes etc and insert it into a new record on a table using the INSERT INTO statment. However I am getting a Syntax error in my INSERT INTO statement which i cannot figure out.
Please Help!!?
Private Sub cmdSaveRecord_Click()
Dim SQL As String Dim Today As String Dim Ref As String Dim HK As String Dim Site As String Dim Equip As String Dim Serial As String Dim Invoice As String Dim Client As String Dim HKRef As String Dim Tested As String Dim Completed As String Dim chkHKr As String Dim Repaired As String Dim Spares As String Dim CompDate As String Dim Exp As String
This is a simple code for inserting values to database.But I'm getting syntax error in the insert statement.I'm attaching the code for reference.Do help me out Regards Ransha
Dim mail,Login,password,conn,strSql Login=Request.Form("name") password=Request.Form("password") mail=Request.Form("email") 'Creating connection Object Set conn=Server.CreateObject("ADODB.Connection") conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:/example/login.mdb" strSql = "INSERT INTO Names (UserName, Password, Email) VALUES ('"&Login&"','"&password&"','"&mail&"')" conn.Execute(strSql) conn.Close Set conn=nothing
Can anyone tell me what is wrong with this statement. I get the error "Syntax error in INSERT INTO statement". I am the worst kind of user as I did not write this database and am trying to learn on the fly. Any help is greatly appreciated. This macro is supposed to insert an amount into an "Activity" record based on the Co_Default_Rate field setup in the CompanyInfo table.
INSERT INTO [FORMS]![Activity]![Amount] SELECT (CompanyInfo.Co_Default_Rate) FROM CompanyInfo WHERE CompanyInfo.Co_Number = [FORMS]![Installs]![Installs_Co_Number];
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
I've managed to create an access-database and (fill it with some data), retrieve data from it with asp, but when I try to insert or update data with asp, I get the same error:
"Syntax-error in Insert expression" or "Syntax-error in update expression"
Do I have to "chmod" the database to write to it, or what?
(I usually work with sql-databases, and the code/syntax I believe is not wrong,as it works on another database) Conn.Execute("INSERT INTO tour(date,venue,city,link) VALUES('" & datum & "','" & venue & "','" & city & "','" & link & "')")
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 trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code: strPath = "etwork driveMy Folder" strFilename = "MyFile.doc" If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
Basically I have a form where a user has selected a couple values from a dropdown element, and entered a few other text values into the form. I am then trying to grab the values and append them to a table.
Code: Private Sub btnSubmitInputVendorPerformanceForm_Click() 'Button Is part of frmInputVendorPerformance Dim ValueCheck As Integer 'Increments to make sure no errors in entry Dim ErrorShow As String Dim YesorNoAnswerToMessageBox As String Dim QuestionToMessageBox As String Dim DeliveryRate 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 have an unbound form: frm_ReceiptSearch with some fields that, when something is input, will search a datasheet viewed form on a subform on the main form (subform name is sf_frm_Receipts). Currently, on the txt_CheckNo field's after update event, I have the following code:
Private Sub txt_CheckNo_AfterUpdate() Dim SrchVar As String SrchVar = Me.txt_CheckNo Me!sf_frm_Receipts!REFNO.SetFocus DoCmd.FindRecord SrchVar, acEntire, , acSearchAll, , acCurrent, True End Sub
I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.
VBA:
Private Sub List1_DblClick(Cancel As Integer) DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name" End Sub
Somehow it doesn't take this variable msoFileDialogSaveAs. It tells me it's unknown.
Code: Public Sub Command2_Click() On Error GoTo MyError3 Dim filelocation As Variant Dim g As Object Set g = Application.FileDialog(msoFileDialogSaveAs)
I am currently creating a form which includes a command button to update a field in a table to show Today's Date, at the time of running a query I am still yet to write.
I have the following code for my Command Button, which is returning the Runtime Error 3464 (Data type mismatch in Criteria Expression):
Private Sub cmdAddTodaysDate_Click() CurrentDb.Execute (" UPDATE tblMyTable SET TodaysDate = " & Date & " WHERE tblMyTable.ProductCode = " & Me.txtProductCode) End Sub
The query above seems to be having issues matching the text from the 'txtProductCode' textbox to the table field 'ProductCode'.
I currently have this set as the forms default recordsource (which works just fine):
Code: SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1
And I have this vba to dynamically switch around that email address.
Code: Private Sub Form_Load() Dim intPos As Integer Dim strControlName As String Dim strValue As String Dim sSQL As String If Len(Me.OpenArgs) > 0 Then ' Position of the pipe
[Code] ....
If I msgbox the sSQL - it shows identical to the default recordsource but I get the error:
The error message I get is:
Run-Time error '2580' The record source 'SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1' specified on this form or report does not exist.
I tried copying the exact working default sql into the vba and get the same result.
When I try to open a recordset based on this sql, it gives me the runtime error - which is odd since I don't have any criteria in the statement.
I think the problem may be that vba is somehow adding a line break between "fullvals" and "18", but I don't know why it would do so and it doesn't always (only if the string is long).
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 have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.
Each time I run my code I receive "run-time error 3021: No current record."
It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit" of my code. I'm suspecting that my function is running too fast, such that it did not have time to read that the table has already been populated by the timestamp in the AddNew code
Code: Private Sub Command9_Click() Dim dbs As DAO.Database Dim rstTimestamp As DAO.Recordset Dim rstAcknowledgement As DAO.Recordset Dim rstAgent As DAO.Recordset
I'm using Access 2010 and I want to export a query to Excel by clicking a button. Every time I click on it Excel opens but doesn't open my worksheet, it's just gray like you just open the program without a table. After exiting it Access tells me I got a run time error 1004: the open-method of the workbook object couldn't be executed. After opening my Excel file it says that Excel found unreadable content and asks if I want to restore the content of the workbook.
Here is my code:
Code: Private Sub Befehl62_Click() Dim cdb As DAO.Database, qdf As DAO.QueryDef, SheetName As String, xlApp As Object Set cdb = CurrentDb Forms!Export_to_Excel!txtSheetName.SetFocus SheetName = Me!txtSheetName.Text