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 Replies
ADVERTISEMENT
May 10, 2006
I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.
FORM
frmAppendSOPTraining
lstEmployees (unbound)
cboSOPNumber (unbound) (the numbers are pulled from an SOP table)
txtRevisionNumber (unbound)
txtTrainingDate (unbound)
cmdAddRecords
TABLE Employees
EmployeeID (AutoNumber)
EmployeeNumber (Number Long)
FirstName (Text)
LastName (Text)
TABLE SOPTraining
TrainingID (AutoNumber)
EmployeeNumber (Number Long)
SOPNumber (Text)
RevisionNumber (Text)
TrainingDate (Date/Time)
I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended. :(
Private Sub cmdAddRecords_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Set frm = Forms!frmAppendSOPTraining
Set ctl = frm!lstEmployees
strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES ("
strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "
For Each varItem In ctl.ItemsSelected
strSQL2 = strSQL & ctl.ItemData(varItem) & ")"
CurrentDb.Execute strSQL2
Next varItem
End Sub
View 4 Replies
View Related
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
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 10 Replies
View Related
Nov 23, 2007
hi i am trying to use the following iif statement in a query.
IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))
So, if user selects 'Resolved' then use 1
if the user selects 'Outstanding' then use 2
if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'
Any help would be much appreciated.
View 1 Replies
View Related
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
I have this in a query and the query runs, it just doesnt do anything. FixedExpenseOccurance is the field name, "*" is the field values (they get looked up from a table). Any clues why this is not working?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 8 Replies
View Related
Aug 10, 2006
(IIf((NextActivtion)>=(Today)),((DueToday)="yes"),((DueToday)="No"))
Says I have the incorrect number of Arguments? Any clues? These are all field name:
NextActivation (Is a date)
Today (is today's date)
DueToday (is a yes/no)
View 13 Replies
View Related
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
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
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
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
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
Mar 19, 2008
Hi everyone,
I'm kind of new to web development but not so much programming. I went to school for 2 years for C and C++ programming, as well as some database stuff... anyways , I'm having some trouble doing a simple INSERT INTO statement...
What I'd like to do is to insert a new row using two session variables, first of all I'm not entirely sure if this works? but I've tried a bunch of different things but I just cant seem to get it to work...
Here is my insert statement:
sqlInsert = "INSERT INTO Events (Participant_Code,Date)
VALUES ('" & code & "','" & date & "')"
Now I've tried both, where I have a variable that has Session("code") and another with Session("date") as well as just using Session("code") where the variable is in the insert.
Here is the whole code, I have a file called database.asp that has function that can be called to open, read, update or close a connection.
call connection("writeyourselffree2.mdb")
sqlInsert = "INSERT INTO Events (Participant_Code,Date)
VALUES ('" & code & "','" & date & "')"
call rsUpdate(sql, conn)
call close(conn)
Heres the error that I have right now:
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Anyways I appreciate any help you can give me!
Cheers
View 1 Replies
View Related
Apr 26, 2014
If I use this INSERT it works fine :
Code:
strSQL = "Insert into tblParkeerbeheer_log select * from parkeerbeheer where [ID] = " & Forms![parkeerbeheer]![fldID].Value & ""
DoCmd.RunSQL strSQL
But if I want to do the same with multiple records with Pnummer (personell number) as criteria in the WHERE clause :
Code:
strSQL = "Insert into tblParkeerbeheer_log select * from parkeerbeheer where [Pnummer] = " & Forms![parkeerbeheer]![Pnummer].Value & ""
DoCmd.RunSQL strSQL
It asks me to prompt the personell numer, while that number is present within the prompt box
View 3 Replies
View Related
Mar 2, 2008
i have an sql statement:
aa = "hello"
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
View 5 Replies
View Related
Oct 9, 2005
hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.
-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
View 4 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 1 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 3 Replies
View Related
Dec 3, 2005
Dear members,
I have two databases, Source and destination ;
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).
My code is as follows:
Private Sub CommandButton1_Click()
Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database
On Error GoTo ErrorHandler
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")
ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else
strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"
Set mdb = DBEngine.OpenDatabase(StrDestDB)
Debug.Print strSQL
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("The Geodatabase tables have been successfully appended!")
End If
Exit Sub
ErrorHandler:
strTemp = Err.Description & " [Update_SystemTab]"
Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub
I get Syntax Error in "Insert into" clause.
Any help on this will be most appreciated.
Thanks in advance.
Irshad
View 2 Replies
View Related
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
Aug 1, 2006
hi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:
SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);
it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.
:o
View 4 Replies
View Related
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
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
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
Jul 31, 2014
The row source for the combo-box is
SELECT tbl_p.P_ID, [plast] & " , " & [pFirst] AS Expr1 FROM tbl_p ORDER BY [plast] & " , " & [pFirst];
If notinlist I would like to add the the new name, how should I write the INSERT Statement?
View 1 Replies
View Related
Apr 3, 2013
Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?
View 2 Replies
View Related
