Modules & VBA :: SQL Statement DELETE With Variable As Table
Aug 14, 2014
I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :
Private Sub cmdRunCheck_Click()
Dim strSQL As String
Dim strTempTbl As String
strTempTbl = "tblCheckDoubles"
strSQL = "DELETE * FROM " & "'" & strTempTbl & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
What is clear is that if I use this line the Sub is working smoothly
strSQL = "DELETE * FROM tblCheckDoubles"
Please do not tell me that it is not possible to use a variable as Table parameter....
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _ & " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _ & " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _ "Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
Basically I'm trying to filter records from a view in my listbox based on a value in a text box. As such...
Private Sub Form_Open(Cancel As Integer)
Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = " & Form_frmMain.txtName
End Sub
**Note that this the value I'm trying to pull is from a seperate form. My statement works fine as long as I don't use a variable ie. Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = 'Agnew' "
I'm sure it may be a simple syntax problem but I fairly new at this. Any help would be appreciated.
Is there a way to use a variable in an SQL statement?
Something like this:
Code:
dim mysql1, newname newname = inputbox("direction on what to do go here?",,) mysql1 = "INSERT INTO FinalAll SELECT newname.* FROM newname;" DoCmd.RunSQL mysql1
Where newname would be a variable assigned from an input box.
I want to populate a single table from several different tables but have the user specify which tables to use.
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code: Public Sub TRANSPOSE() Dim rs As DAO.Recordset Dim rsNew As DAO.Recordset Dim varItm As Variant Dim I As Integer Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
I wanna create a button in form which can allow me to import my data base file with some tables , i would select just the variable that i need in the table not all of them .
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] ) SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], * FROM TestBank WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I have a process that reads a list of files sequentially and then processes them .... As each file is processed, its name (strfile) gets written to a Table (Vault.[Processed_file])
I want to verify if the file exists in the table prior to processing it - if it exists, I won't process it again ...
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code: Private Const strNomeTblFonte as string = _ "SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;" Private Sub Class_Initialize() Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
I have a very simple 2010 Access database with only one table which contains a few fields (username, email, phone....)
I have a combobox with unbound controls to search/find users (by the lastname field). The combobox is in the header section which populates the fields (controls?) in subform below.
I have a delete button with some VBA code which allows the user to delete the currently displayed record however when the form refreshes, I end up with two issues:
1: I see #deleted# in the combobox dropdown until the dbase is closed/reopened (it's only typically used by one person at a time.) Compact/Repair doesn't seem necessary, especially since i think it's pretty difficult to do this programatically (?)
2: There are blank rows in the combobox dropdown from where the data used to reside after deleting the record.
I haven't been able to figure out how to remove those blank rows and refresh the combobox to display the remaining records (with the blank rows removed). So I have dozens of blank rows. Me.Requery doesn't seem to work.
My assumption is that the blank rows exist because I'm deleting the content from the combobox's initial creation?
Is there a better way to allow user's to see the list of user's and then be able to select that record and delete it?
If I use a simple search box on a form where people have to type a (last name for example), if it's spelled incorrectly, then nothing would be found.
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code: For Each nd In Me.tree.Nodes If nd = level1 Then nd.Child.EnsureVisible Exit For End If Next nd Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
Hy again! Can someone please tell me why the following code doesn't work?
DELETE tblHolliday.RACF AS EXP1, tblHolliday.Date FROM tblHolliday INNER JOIN tblPlanner ON (tblHolliday.RACF = tblPlanner.RACF) AND (tblHolliday.Date = tblPlanner.Date) WHERE (((tblHolliday.Date) Like [forms]![frmtest].[txtday1])) WITH OWNERACCESS OPTION;
When I run it, it comes with the following error:
Specify the table containing the records you want to delete
my database just records end of day sales figures which are entered though a form, after they are entered the total for the day is archived. However the problem i have is that the i need some way of clearing the input table with a command in the form.
I have a sub form that allows users to add staff to a project team, once added it populates a table which updates the subform showing the selected employee. I am trying to enable a delete function that allows users to remove an employee from the project team in the subform showing selected staff. Here is the code I have so far, but it doesn't work;
Code: Private Sub Command4_Click() Dim dbs As Database Dim rs As Recordset Dim sqlstr As String Set dbs = CurrentDb sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = Forms!frm_Switchboard.CAP_Live" dbs.Execute (sqlstr) End Sub
Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:
Code: Private Sub Del_TsCorrection_tbox_Click() Dim lngID As Long Dim strSQL As String
[code]...
But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.
I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-
Code: Private Sub cmdDeleteEmployee_Click() On Error GoTo Err_cmdDeleteEmployee_Click
DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_cmdDeleteEmployee_Click: Exit Sub Err_cmdDeleteEmployee_Click: MsgBox Err.Description Resume Exit_cmdDeleteEmployee_Click End Sub
But when I click on the button I get a message saying it would create duplicate data in the table.
