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 Replies


ADVERTISEMENT

Modules & VBA :: How To Use Variables In Update Statement

Apr 10, 2014

What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"

[code]....

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

View 3 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

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 :: 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

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 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 :: 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 4 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

Access - SQL Statement That Uses Variables

Nov 13, 2014

I have a form that has multiple toggle buttons. I would like to be able to do the following when the toggle button is clicked:

Set the dateset the userlock both fields so that the information is not lost

Because there are multiple toggle buttons I would like to be able to use variables.

Here is a sample of what i have.

Private Sub CONTRACTTgl_Click()
CONTRACTDATE = Now()
CONTRACTEMP = CurrentUser
CONTRACT = "Collected"
Me.CONTRACTDATE.Locked = True

[Code] .....

View 8 Replies View Related

Modules & VBA :: Multiple And In If Then Statement

Oct 1, 2013

I have the following code on an After Update event:

If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then
Me.ProgPriceTxt.Value = "85"
End If

This works fine. When I add another "And" to the statement, however, it no longer functions:If

Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then
Me.ProgPriceTxt.Value = "85"
End If

Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).

I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.

View 11 Replies View Related

Modules & VBA :: If Statement With Multiple Criteria

Apr 14, 2015

I'm trying to create a report that does the following:

If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.

If Not IsNull(Me.Time1) Then
If Me.Time1 = "Other" Then
Me.Time1.Visible = False
Else
Me.Time1.Visible = True

[Code] ......

View 6 Replies View Related

Modules & VBA :: Multiple Criteria For Where Statement?

Feb 25, 2015

I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:

Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3

You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.

Is there a simple way to build this string based on the number of search strings that are not null?

View 2 Replies View Related

Modules & VBA :: Syntax On Multiple Criteria In Where Statement

Dec 10, 2014

I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.

[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.

Dim maxFlag As String
Dim flagCriteriaWarranty As String
Dim flagCriteriaEstimate As String

[Code] .....

View 13 Replies View Related

Modules & VBA :: Trying To Get Case Statement To Recognize Multiple Conditions

Sep 8, 2014

I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?

Code:

Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

[code]...

View 1 Replies View Related

Multiple Variables In Query

Mar 21, 2006

I've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.

For this particular report I've broken down the AFSCs down to three different formats. They are as follows:

2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)

The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.

Here's the SQL for it.

SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);


Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.

View 3 Replies View Related

INSERT INTO Statement Help..

Mar 17, 2005

hello all

after many hours of reading access help, for my update command button,
not sure what i did wrong...

when I on the update button, its give me blank space on the table where all my contacts are stored,

I trying to updated existing client contact info, without changing contact name,
I have contact form with drop down menu, where I select clients name and view and clients information,

Update
"'" & Me!TbxComp & "', " & _
"'" & Me!TbxStreet & "', " & _
"'" & Me!TbxFloor & "', " & _
"'" & Me!TbxCityStateZip & "', " & _
"'" & Me!Telephone & "', " & _
"'" & Me!TbxFax & "', " & _
"'" & Me!TbxAcctMgr & "', " & _
"'" & Me!TbxEmail & "')"
-----------------------------------------
here is what my form code looks like,
Private Sub cboContact_AfterUpdate()

Dim ContactID As String
Dim rst As DAO.Recordset
ContactID = "SELECT * FROM Contacts " & " WHERE ContactName = '" _
& Me!cboContact.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactID, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!Telephone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!Email
Me!TbxAcctMgr = rst!Manager
End If

Set rst = Nothing

End Sub
Private Sub Close_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()

'Set Record Source for Forms as Contacts Table
Forms!Contacts.RecordSource = "Contacts"

End Sub

Private Sub Update_Contact_Click()
Dim QrySQL As String

QrySQL = "INSERT INTO [Contacts] " & _
"(ContactName, Company, Street, Floor, CityStateZip, Telephone, Fax, Manager, Email) " & _
"VALUES(" & _
"'" & Me!TbxContactName & "', " & _
"'" & Me!TbxComp & "', " & _
"'" & Me!TbxStreet & "', " & _
"'" & Me!TbxFloor & "', " & _
"'" & Me!TbxCityStateZip & "', " & _
"'" & Me!Telephone & "', " & _
"'" & Me!TbxFax & "', " & _
"'" & Me!TbxAcctMgr & "', " & _
"'" & Me!TbxEmail & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub


please note: i have very little programming skills
Not sure if I explained this correctly..

Thank you
Your help is greatly appreciated

AA

View 5 Replies View Related

Tables :: Multiple Variables Table

Nov 26, 2012

I have a store database containing multiple values about a single product, and separate table on color that this product comes in and in another table are quantities this product is available in. How can I make relationships between the tables so it works and I can make queries from it?

View 2 Replies View Related

OVerflow On INSERT Statement

Mar 7, 2006

Hi there,

I am trying to perform an INSERT into a linked Oracle Table, using the following code:

INSERT INTO JTCS_OWNER_VISITS
SELECT *
FROM Visits;

Seems simple enough, there are 10,000 records in the visits table, but when i try to run this query i get an error message that simply says 'OverFlow' but no error code. This query worked earlier when i only tried to insert 1,800 records, can anyone tell me if there is a limit to the number of records i can insert. I have linked the oracle tables using ODBC drivers (SQORA32.dll) and this has worked fine against all of the other tables except this one...

Thanks

Dan

View 2 Replies View Related

Insert Statement ...order By

Jun 23, 2006

I've created a new table to hold information about female customers and it would be nice if I could glance through it in alphabetical order of their surnames.

INSERT INTO TBL_FEMALE_CUSTOMERS
SELECT *
FROM TBL_CUSTOMERS
WHERE GENDER='F'
ORDER BY SURNAME ;

In MS Access the resulting table data is NOT sorted by surname, but it works fine when I'm using my trusty SQL Server database. Can this be done in Access?
...and yes, I know I just need to use a query to sort the data, but I'm more interested to find out why it works in SQL Server and not Access.
Cheers,
Sam

View 4 Replies View Related

Insert And Update In Same Sql Statement

Feb 20, 2008

Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:

View 2 Replies View Related

Insert Into Sql Statement Not Working.. Help!

Dec 5, 2006

hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.

I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.

this is my code:

strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")


but i get the error message


You cannot or change a record because a related record is required in table tblPricing


any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?

View 8 Replies View Related

Still Having Trouble With Insert Statement

Jan 19, 2005

this is what I have with all the syntax problems on the insert statement

Private Sub Save_Click()
'On Error GoTo Err_Save_Click


DoCmd.SetWarnings False


Dim EntryTime As String
Dim lsupervisor As String
Dim sSemester As String

Dim selectRequirementid1 As String
Dim selectRequirementid2 As String
Dim selectRequirementid3 As String
Dim selectRequirementid4 As String
Dim selectRequirementid5 As String
Dim selectRequirementid6 As String
Dim selectRequirementid7 As String
Dim selectRequirementid8 As String
Dim selectRequirementid9 As String
Dim selectRequirementid10 As String
Dim selectRequirementid11 As String
Dim selectRequirementid12 As String

Dim sRequirement1 As String
Dim sRequirement2 As String
Dim sRequirement3 As String
Dim sRequirement4 As String
Dim sRequirement5 As String
Dim sRequirement6 As String
Dim sRequirement7 As String
Dim sRequirement8 As String
Dim sRequirement9 As String
Dim sRequirement10 As String
Dim sRequirement11 As String
Dim sRequirement12 As String


lsupervisor = Me.Supervisor1.Column(0)
sSemester = Me.Semester1.value
EntryTime = Format(Now, "dddd, mmmm d, yyyy, hh:mm:ss AMPM")


sRequirement1 = Me.category1.value & ">" & Me.subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement2 = category1.value & ">" & subcategory1.value & ">" & Me!Label2.Caption
selectRequirementid2 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement2] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement3 = category1.value & ">" & subcategory1.value & ">" & Me!Label3.Caption
selectRequirementid3 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement3] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement4 = category1.value & ">" & subcategory1.value & ">" & Me!Label4.Caption
selectRequirementid4 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement4] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement5 = category1.value & ">" & subcategory1.value & ">" & Me!Label5.Caption
selectRequirementid5 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement5] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement6 = category1.value & ">" & subcategory1.value & ">" & Me!Label6.Caption
selectRequirementid6 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement6] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement7 = category1.value & ">" & subcategory1.value & ">" & Me!Label7.Caption
selectRequirementid7 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement7] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement8 = category1.value & ">" & subcategory1.value & ">" & Me!Label8.Caption
selectRequirementid8 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement8] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement9 = category1.value & ">" & subcategory1.value & ">" & Me!Label9.Caption
selectRequirementid9 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement9] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement10 = category1.value & ">" & subcategory1.value & ">" & Me!Label10.Caption
selectRequirementid10 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement10] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement11 = category1.value
selectRequirementid11 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement11] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "

sRequirement12 = category1.value & ">" & subcategory1.value
selectRequirementid12 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement12] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "


DoCmd.RunSQL "INSERT INTO Hours ([UniversityID], [LicenseNumber], [SiteName], [RequirementID1], [RequirementID2], [RequirementID3], [RequirementID4], [RequirementID5], [RequirementID6], [RequirementID7], [RequirementID8], [RequirementID9], [RequirementID10], [RequirementID11], [RequirementID12], [Semester], [HoursCompleted1], [HoursCompleted2], [HoursCompleted3], [HoursCompleted4], [HoursCompleted5], [HoursCompleted6], [HoursCompleted7], [HoursCompleted8], [HoursCompleted9], [HoursCompleted10], [HoursCompleted11], [HoursCompleted12], [EntryTime], [Comments]) " & _
" VALUES('" & UniversityID1 & "', '" + [lsupervisor] + "', '" & SiteName1 & "', '" + [selectRequirementid1] + "', '" + [selectRequirementid2] + "', '" + [selectRequirementid3] + "', '" + [selectRequirementid4] + "', '" + [selectRequirementid5] + "', '" + [selectRequirementid6] + "', '" + [selectRequirementid7] + "', '" + [selectRequirementid8] + "', '" + [selectRequirementid9] + "', '" + [selectRequirementid10] + "', '" + [selectRequirementid11] + "', '" + [selectRequirementid12] + "', '" + [sSemester] + "', '" & hours1 & "', '" & hours2 & "', '" & hours3 & "', '" & hours4 & "', '" & hours5 & "', '" & hours6 & "', '" & hours7 & "', '" & hours8 & "', '" & hours9 & "', '" & hours10 & "', '" & categoryhours & "', '" & subcategoryhours & "', '" + [EntryTime] + "', '" & Comments & "'); "

End Sub

View 5 Replies View Related

Insert A Timestamp Into A Sql Statement

Jun 16, 2005

Hi

Help please

My code looks something like this and it work fine by producing an email and inserting into an access database

My problem is I also want to insert a timestamp into the column that already exists in the database and I just can't get the syntax right

SQL= "INSERT INTO Employees "
SQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCode ) "
SQL= SQL & "VALUES ('" & Request.Form("FirstName") & "','"
SQL= SQL & Request.Form("LastName") & "','"
SQL= SQL & Request.Form("Address") & "','"
SQL= SQL & Request.Form("City") & "','"
SQL= SQL & Request.Form("Region") & "','"
SQL= SQL & Request.Form("PostalCode") & "')"

I appreciate I might have to do

SQL= "INSERT INTO Employees "
SQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCode ,timestamp) "
SQL= SQL & "VALUES ('" & Request.Form("FirstName") & "','"
SQL= SQL & Request.Form("LastName") & "','"
SQL= SQL & Request.Form("Address") & "','"
SQL= SQL & Request.Form("City") & "','"
SQL= SQL & Request.Form("Region") & "','"
SQL= SQL & Request.Form("PostalCode") & "')"

but I cannot for the life of me workout the SQL line needed

Help would be much appreciated

View 3 Replies View Related







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