I'm trying to get an Insert statement to execute whenever the user ticks the tickbox however my code is failing to find the output table 'Manager':
Code:
Private Sub Check34_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
[Code] .....
I am working on a database which will basically serve to be a massive Auditing Checklist. Lots of "Is this done" type yes/no questions to record, some with a comment.
Brief Description: I have many child-forms on a tab-control, each with a different heading/category of things to check for. When someone visits a store, I would like them to record their findings in this form.
In the above example, if the Floors are clean, no input is necessary. If the floors are not clean then the user ticks the checkbox and the text "Floors were not clean" should be automatically entered into the field: Cleanliness 1. The reason for this is that I wish to link the value from this field to a Microsoft Word document using mail merge for reporting purposes, and in the report it needs to state any problems in full sentences.
I have tried using this code in order to update the comment field when the checkbox is ticked, however, the problem with this is that I would have to insert code manually for every single checkbox in my forms, and I have about 150 checkboxes in total.
Code:
Private Sub Cleanliness_A1_AfterUpdate() If Cleanliness_A1.Value = -1 Then 'If checkbox is checked
Cleanliness_C1.Value = Cleanliness_C1.DefaultValue
Cleanliness_C1.Enabled = True
Else
Cleanliness_C1.Value = ""
Cleanliness_C1.Enabled = False
End If
End Sub
Hi All.
What I would like to do, is have a form popup when the main form is opened in my db, which will detail all the changes I have made to it since the last version (this bit is OK) and also, I would like a checkbox on the popup form, that the user can tick once they have read the details so it will not show up everytime they open the main form. (Just like the "hidestartupform" property within the Northwind Database)
I create the form and all that, but do not know how to do the checkbox so once it is checked, the form will not appear.
Hope this makes sense and someone can enlighten me and point me in the right direction?
Many Thanks.
Frank.
I'm a novice user of Access 2013.
I would like to be able to use a checkbox control to make either text boxes or forms appear when ticked, ultimately to stop a form being so untidy/to conserve space. I'd be using this on multiple occasions.
(If actioned, would it automatically shift the other controls in the form down to make space, or would it just overlap?
I have 2 tables both linked to SQL Server 1 has policy information and the other has error information both tables are linked with the relationship policy Ref. The error table(table2) has fields Pol ref, Error_Type,error, Comments, response_comments and response_Date.
There are 25 types of error so what I have done is copy all fields from table 2 25 times exlcuding the policy ref and Error_Type but to make them relate to the relevent error I need to setup some VBA code that does an if Error1_checkbox is ticked then make Error_Type ="error1" and Error=1 else if error1_checkbox is unticked then make error_type null(blank).
I am using ACCESS 2010....
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
Code:
sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "
CurrentDb.Execute sSQL, dbFailOnError
Hi All
I have a residential address and a postal address on my form. What I want to do is when the user completes the 3 fields from the Residential Address Eg: Address, State, Postcode and then ticks the checkbox (Residential Address is Postal Address), the information previously entered will automatically fill the same fields in the Postal Address.
If Checkbox1 = true then
RAddress = PAddress
RState = PState
RPostcode - PPostcode
End If
I just can't work out the correct code.
Many thanks for any help.
I have a form where the user selects check boxes to choose what fields to include in a report. Because of the massive amount of data, I need to send it to a temp table and then on to Excel and not use a query.
I have this line of code, for the insert into temptables for other forms that don't require the yes/no box and it works very well, but I can't figure out how to do it with these yes/no conditions.
This is the execute line that inserts into the TempTable for the other forms:
db.Execute "INSERT INTO TempPicktbl (Field1, Field2, Field3, Field4, Field5)" & strQuery, dbFailOnError
How would I write:
If Check1=True Then INSERT INTO TempPicktbl Field1
If Check 2=True Then INSERT INTO TempPicktbl Field2
If Check 3=True Then INSERT INTO TempPicktbl Field 3
etc.
I'm pretty sure it's the INSERT level where I need to put this code.
What is the correct way to update the checkbox value in a table with an IF statement?
Desired Result:
Check if UserID is not 'ME'
If it is not 'ME' then uncheck the box in the table.
My current faulty code below:
NoUser = DLookup("[UserID]", "[TABLE]") If NoUser <> "ME" Then
[AllowLogin] = 0
End If
In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:
If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if
This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true).
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
I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.
What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.
Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)
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
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
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 Relatedhi 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?
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
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
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
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
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
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
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
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], ...
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.
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.