Code:query = "update table set date='" & srs.fields("docdate") & "', page=" & srs.fields("pagenumber") &_ ", near='" & near1 & "" & drive & irfile & "', image='" & ars.fields("file_name") &_ "', notes='Image not Found on Nearline.' where file_name = '" & ars.fields("file_name") & "'" msgbox query set fixrs = aconn.execute(query)
code to open connection
Code:sub accessconn ' open a connection to access db' set aconn = CreateObject("ADODB.Connection") 'prep connection' set ars = CreateObject("ADODB.Recordset") ' prep recordset' set fixrs = CreateObject("ADODB.Recordset") ' prep recordset' aconn.open "DRIVER={Microsoft Access Driver (*.mdb)};" &_ "DBQ=" & accessfile & ";DefaultDir=;UID=;PWD=;"end sub
If anymore info is required please tell me, but basically i can insert into the database no problems , but when it comes to updating what is in there i recieve the above error
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
I have a website which uses a series oif ASP/VBscript scripts to access and update an Access database. It has run for years on a shared commercial service.I also have a replica of the wesbite on my home pc for development and testing running under XP Pro and IIS. All works fine.
Recently I have had to switch my home setup to my laptop (also XP Pro and IIS). The scripts all appear to work ok except when I try to UPDATE certain tables.
Some give "Syntax error in UPDATE statement." Some give "Data type mismatch in criteria expression." Some work without error
Yet the identical script and table on the main site and my home PC work fine and have done for years.
My issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date.
Dim DENIEDDATE1 As Date If (Not IsDate(rs.Fields("DENIED_DATE"))) Then DENIEDDATE1 = Null Else DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'" End If
update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI A B C D 1 2 2 6 4 3
1 7 5 9 1 2 2 5 8 5 etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI A B C D 1 2 & % 2 6 4 3 1 7 5 9 1 2 & % 2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click() CurrentDb.execute "UPDATE TableI" Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2 Docmd.save Docmd.close End Sub
Dim dbtmp As DAO.Database Dim tblObj As DAO.TableDef Dim rs As DAO.Recordset Set dbtmp = OpenDatabase("C:WINDOWSBureaudevcli.xls", False, True, "Excel 8.0;") DoEvents Set rs = dbtmp.OpenRecordset("select * from [DEVCLI$A1:C10]")
in the last line i would like replace DEVCLI (the Excel sheet name) by a variable to make my program works with any excel file. What is the syntaxe for that ? Thanks in advance for help. VINCENT
Hi, I wud like to know why this syntax is not working. select * from table_name where col_val like 'a%'
This returns the column values that start with 'a'. I was getting the result properly few days ago. But now suddenly this query doesnot give the result. When i tried this out in a website it was working there, but not in access.
Can anybody see what is wrong in this syntax? If the syntax is right where wud be the fault?
Can someone tell me how to insert the contents of a variable into a table? I am trying to add selected items from a listbox (Members) into a temporary table (tempMembers). The code is below. On debugging, it picks up the contents of the variable (iMemberID) OK, but adds "0" to the temp table (both are integers). I'm pretty sure the problem is in the Insert statement below (stSQL3), but I can't figure out how to write it!
Any help would be greatly appreciated! Thanks
stSQL3 = "INSERT INTO tempMembers (MemberID) VALUES & iMemberID & ;" For Each varItm In Me!Members.ItemsSelected iMemberID = ctl.ItemData(varItm) DoCmd.RunSQL stSQL3 Next varItm
Can someone help me on the syntax for the following? I have two tables: one is TapeNumber and has one field (Tape_No) autonumbered PK. The other table has information about the video and uses the Tape_No as a secondary key.
I have a form to add information about videos. I have the form bound to the first table, so it adds a new TapeNumber when I add a record. I have a subform displaying the video information for that tape (there may be several videos recorded on one tape).
When I add a new tape (or video to an existing tape), how do I save the tape number to the second table?
have this code which keeps on giving me this error...I dont know whats wrong with the code please help tried removing the quotes (rstStudents.Open "SELECT * FROM Students WHERE Regno = " & _ txtReg , _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText) but then it wont retain any records even if they exist in the table
here is the original code
rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _ txtReg & "'", _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText
ERROR MESSAGE>>>>>>> Runtime Error '-2147217913 (80040e07) Data type mismatch in criteria expression
the code is supposed to use number input by user to search in a table and fill all the other fields in the form with the table data (it is run on lost focus of the text box were value has been input)
the whole code :::::
Private Sub txtReg_LostFocus() Dim rstStudents As ADODB.Recordset Dim blnFound As Boolean Dim fldItem As ADODB.Field
blnFound = False
If Me.txtReg = "" Then Exit Sub
Set rstStudents = New ADODB.Recordset rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _ txtReg & "'", _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText
With rstStudents While Not .EOF For Each fldItem In .Fields If fldItem.Name = "Regno" Then If fldItem.Value = txtReg Then Me.txtName = .Fields("Name") Me.txtAdd = .Fields("Address") Me.txttel = .Fields("Telno") Me.txtTutor = .Fields("TutorName") Me.txtbks = .Fields("NoBooksonloan") blnFound = True End If End If Next
.MoveNext Wend End With
If blnFound = False Then
MsgBox "No student record to display" ' ... and reset the form ' cmdReset_Click End If
I am trying to open a PDF from Access. I get it to work but it requires me to hard code the path and fiel name....
The path will always be the same although the file name will change. RIght now I get the fiel name from a textbox on a form...I am trying to change the below working code with a varaible instead of the path....I think there is some systax issues that I cannto fingure out
TotPath is the variable that holds the entire path to the file "X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf"
This variable is rebuilt everytime the user selects something on the form....so the actual File name will change every time...
Does anyone have and ideas as to how to incorporate a variable instead of the hard coded path?
THank you all for your help.....
Code:WORKING CODE: 'Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf", vbMaximizedFocus ' open a txt documentNOT WORKING CODE:Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe TotPath", vbMaximizedFocus ' open a txt document
I'm familiar with SQL but not with Access97 - some wierd syntax here!
I'm creating a Query that is to Delete records from a table depending on a non-key field in a second table.
I first tried an INNER JOIN
DELETE Activity_Preferences.* FROM Activity_Preferences INNER JOIN Activity_Key Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
which was initially accepted but then complained about the INNER JOIN after I went in a second time to 'fix' the error.
I've finally got the following syntax accepted:-
DELETE Activity_Preferences.* FROM Activity_Preferences, Activity_Key WHERE Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
But when executed still gets the following error (same as for the INNER JOIN):-
Hi, In a form i use the following filter fonction : DoCmd.ApplyFilter , "CliNom = '" & RunCli & "'" The problem is that sometimes RunCli (wich is a name) contains ' symbol and then generates a syntaxe error, is there a way to prevent that ? Thanks in advance. VINCENT
On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:
Private Sub cmdLotHistory_Click()
Dim stDocName As String, stSelection As String
stDocName = "rptLotHistory"
If Me.rdoAll = True Then DoCmd.OpenReport "rptLotHistory", acViewPreview End If
If Me.cboLotLU.Value > 0 Then stSelection = "[LotID] =" & Me![cboLotLU] End If
I am using the folling code in a after update combobox event:
Dim rst As DAO.Recordset Dim sqlwhere As String
sqlwhere = " "
If IsNull(Me.comboSearchSerial) = False Then sqlwhere = "SerialNumber='" & Me.comboSearchSerial & "' and " End If
mysql = "SELECT TBL_RMA.* from [TBL_RMA] Where " mysql = mysql & sqlwhere mysql = mysql & ";" Forms!FRM_RMA.RecordSource = mysql
Exit_comboSearchSerial_AfterUpdate:
This is currently working for me with SerialNumber being a text field. I would like to use the same code substituting SerialNumber with a Date field. I know its probly an issue with the use of quotes, but I can't seem to figure it out. Also, if I wanted to use a number format, what would I use... Thanks Gregg
I have a FE/BE access database. I have split it for our business processes from the original owners who decided to keep it combined. I have in my code Set rst = CurrentDb which needs to point to the BE (where the tables are) and I am struggling with getting the proper syntax. My error msgs are as follows:
"Operation is not supported for this type of object" (most likely becuase the rst is nothing)
I then get an error msg related to the form that it cannot update the StudentID field.
The help VB help module is not able to display this information..
I have a form under development that is to assign an invoice number to a specific set of records based on the client, contractor and job type.
All the above are combo boxes and the first 2 have rowsource directly from tables, no problem.
The 3rd, JobType is dependent on the first 2 being identified first. Thus the query builds from information on the loaded form.
I have built a query that obtains the data needed for the combo-box whose SQL is as follows: SELECT [Lookup:Job_Description].Job FROM (([Lookup:Clients] INNER JOIN [Lookup:Contractor] ON [Lookup:Clients].Client_ID = [Lookup:Contractor].Client_ID) INNER JOIN Contractor_Fees ON ([Lookup:Contractor].Contractor_ID = Contractor_Fees.ContractorID) AND ([Lookup:Contractor].Client_ID = Contractor_Fees.Client_ID)) INNER JOIN [Lookup:Job_Description] ON Contractor_Fees.Job_Type = [Lookup:Job_Description].Job_Key WHERE ((([Lookup:Clients].Company_Name)=[Forms]![frmAssign_Invoice_Num]![cboCompanyName]) AND (([Lookup:Contractor].Contractor_Name)=[Forms]![frmAssign_Invoice_Num]![cboContractorName]));
--- hope you can read that!
I have attempted to format this so that it can be executed in code under the GotFocus event. My code, which does compile looks like this:
I am trying to run the query below to tell me how much stock a company has available by deducting the amount dispatched from its allocation. I am getting an error message;
Run Time error '3061' Too few parameters expect 2
The code is;
Dim db As DAO.Database, qr1 As DAO.QueryDefs Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset Dim varVal0 As Variant, varVal1 As Variant, varVal2 As Variant Dim strSQL As String Set db = DBEngine(0)(0)
VarVal0 = Me.WINENUMBER
strSQL = "SELECT Sum(tbl_Data_DispatchLineitems.Amount) AS Amount " strSQL = strSQL & "FROM tbl_data_DispatchDetails INNER JOIN tbl_Data_DispatchLineitems ON tbl_data_DispatchDetails.DispatchID = tbl_Data_DispatchLineitems.DispatchID " strSQL = strSQL & "GROUP BY tbl_data_DispatchDetails.TradingName, tbl_Data_DispatchLineitems.WineNumber " strSQL = strSQL & "HAVING ((tbl_data_DispatchDetails.TradingName)=[Forms]![frm_data_Orders]![TradingName]) AND (tbl_Data_DispatchLineitems.WineNumber = VarVal0)"
Set rs2 = db.OpenRecordset(strSQL) rs2.Edit varVal1 = rs2![Amount] rs2.Close
I assume the error lies in the final line of the query code. Any help to find the error would be appreciated.
