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.
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, "")
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.
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update" I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
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:
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:
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:
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'
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)
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
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: "SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table ORDER BY Bookings_Table.Booking_Time;"
But when i add the WHERE
Code: "SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table WHERE (((Bookings_Table.Booking_Date)=[TB_CAL_DATE])) ORDER BY Bookings_Table.Booking_Time;"
it doesn't work [TB_CAL_DATE] is a textbox with a Date in it...
I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:
One Lender to Many Relationship IDs One Relationship ID to Many Tax IDs One Tax ID to Many DocumentsRequired
My forms are nested as follows:
Relationship ID form (contains info for Relationship ID and Lender) -> Tax ID -> DocumentsRequired
I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.
Dim strtext As String Dim strsearch As String strtext = Me.SearchBoxTxt.Value strsearch = "SELECT [Relationship ID] " & _ "FROM CustomerNormQuery " & _ "WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _ "OR [EIN/SSN] Like ""*" & strtext & "*"" " & _ "GROUP BY [Relationship ID]" Me.RecordSource = strsearch
I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.
There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:
Case {T,T,T,T}
Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}
Case {T,F,F,F}
Case {F,F,F,F}
What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
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,
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
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...
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:
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 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
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.
