Modules & VBA :: Change Value Of A Field In Append Query Using SQL?
Jan 25, 2015
I have a main form which does nothing except filter subforms through a cbo.
On the main form are two subforms. One shows top line data, the other shows a breakdown of the top line data, and are linked by an unbound textbox (it's how it works, and does so perfectly)
I'm running an append query to duplicate a record in the second form using vba/sql BUT... need to have one of the fields values changed based on a field on the parent table.
If Forms!frmmain!frmPost.Form.RecordsetClone.RecordCo unt > 0 Then
strSql = "INSERT INTO [tblposts] ( TopLineID, AccountID, TransDate, Cat, SubCat, Debit, Credit ) " & _
"SELECT " & lngID & " As NewID, AccountID, TransDate, Cat, SubCat, Credit, Debit " & _
"FROM [tblposts] WHERE TopLineID = " & Me.TopLineID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
Credit and Debits are reversed as I want one to zero out the other.
In regards to the AccountID, I've tried allsorts and it's just not working... to the point I'm almost giving up and finding an alternative.
Whats the "Correct" syntax to attach a "WHERE" statement to the highlighted [AccountID]'s (which needs to be the value on AccountID on the Parent table)
View Replies
ADVERTISEMENT
Jun 25, 2014
I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.
I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....
Code:
Option Compare Database
Option Explicit
[code]...
how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.
View 3 Replies
View Related
Apr 4, 2014
I have two tables: tblModels and tblParts. tblModels has primary key ModelID; tblParts has primary key PartID, and also has a ModelID field that's hooked to tblModels's ModelID with referential integrity (cascade all). I have a button on my form that duplicates the model (creating a new record in tblModels and getting a new ModelID), and when that happens, I'd like for the duplication process to run an Append Query that captures all records in tblParts that matches the FIRST ModelID (the original one), and makes new copies of them in the same table, but then sets the ModelID for each new record to the NEW ModelID (that was created with the button press).
I have done this using TempVars in my Append Query, and everything is working great, except for when all the Parts records get copied and added, they all have the OLD ModelID on them, which essentially duplicates the records in the old Model and leaves the new Model empty of associated records!The Append Query is using the ModelID field to find the records I want, so how do I then get it to write a NEW ModelID to each record after they're added to the table?
EDIT: I thought of maybe doing the old TempTable, Append Query dumps to that, Update Query changes ModelID's, Append Query dumps them back to the original table... but that seems like the long way around.
View 4 Replies
View Related
Jul 28, 2015
I have a database where I am pulling my data in my subform from a tbl made by a make table query. However, Once a month the data will be updated and sorted by "Retrieve Date". I have made a function through a module that updates the queries with the latest retrieve date everytime new data is pasted into the database.
I am looking for a way to make the make table query only update the new data and not post records into the tables more than once. Should this be done by writing an IIF then statement and using an append query? I will copy the SQL code from my make table query below.
SQL:
SELECT qrySummary.CounterpartyName, Format([CDSsprd],"Standard") AS CDSspread, Format([Gsprd],"Standard") AS Gspread, Format([RatingsGrade],"Standard") AS RatingGrade, Format([BloombergCDS],"Standard") AS BloomCDS, Format([Avgerage],"Standard") AS [Avg], qrySummary.RetrieveDate INTO tblSummaryQry
FROM qrySummary;
View 1 Replies
View Related
Dec 12, 2014
I am trying to copy a record as new record in vba in access so i make a button for the user so that they can copy a record each time and change a certain field if they wanted. How would i do that.
View 1 Replies
View Related
Aug 22, 2014
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
View 7 Replies
View Related
Mar 23, 2015
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
View 4 Replies
View Related
May 5, 2015
I'm after a piece of code which works as described below.
I have a save button on a form
I have a append query ready to run.
When the save button is clicked, I want the code to see if a checkbox is true or not and if its true I want the append query to run if its not then I want the form to save and nothing else.
View 1 Replies
View Related
Apr 5, 2015
The program I am working with has an option to add a new record to another table using a button. Not all the records, even new ones, need to be appended.
The append query works fine in all situations but one--when a new record is entered and saved, the append query button returns 0 records to be appended. However, if you go to a different record and come back to the one just added in the main table, the append query works fine. The query uses a TempVar to select only the record being seen at the time. The TempVar is declared prior to attempting to append the record to the other table.
Is there a way to make a just entered record act like its been around for a bit (well at least to save it by changing record to another and back)?
View 2 Replies
View Related
Jul 20, 2015
Syntax error in this append query. What is it?
Code:
Dim DataToAdd As String
DataToAdd = "INSERT INTO Address " & _
"customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId " & _
[code]...
I am trying to append data from the query into the table where the value on function field in the query is equal to the word ADD
View 7 Replies
View Related
Jul 25, 2007
When I produce an append query that has field names such as 2007/08, the field name is truncated to 2007/8, which results in the query failing. SQL:
INSERT INTO [tblTemp Input] ([2007/08], [May-07]
Becomes:
INSERT INTO [tblTemp Input] ([2007/8], [May-7]
when the query is saved and re-opened.
I could rename the fields of the source table but is there an easier solution?
I am using Access 2003.
View 2 Replies
View Related
May 23, 2015
I don't know how to bypass the problem. I have two tables : (1) Dett_Lav , (2) MaterialiConformita.
They are thus linked: (1) IDDett_Lav <---one - to- many ---> (2) Dett_LavID I'd like to preserve the referential integrity.
In vba I wrote down this codethat does work if I delete the referential interity, otherwise it fails due to (foreign) key violation. I do insert the absolutely necessary Dett_LavID with the append query, so I'm not sure where lays the problem and how to bypass it.
Code:
strSQL = "INSERT INTO MaterialiConformita (MaterialeID, DettLavorazioneID) " & _
"SELECT Art_Mate.ArticoloID, Art_Mate.MaterialeID FROM " & _
"((Articoli INNER JOIN Art_Mate ON Articoli.IDArticolo = Art_Mate.ArticoloID) " & _
"INNER JOIN Dett_Lavorazioni ON Articoli.IDArticolo = Dett_Lavorazioni.ArticoloID) " & _
"WHERE Art_Mate.ArticoloID = " & strArticoloID & _
" AND Dett_Lavorazioni.IDDett_lavorazione = " & Me.Parent.IDDett_Lavorazione
DoCmd.RunSql StrSQL
View 6 Replies
View Related
Oct 18, 2013
I would like to make an append query to piece together multiple tables into a holding table, once the command button is clicked.
a) how to make an append query in vba,
b) How to call an append query on click of a command button?
View 1 Replies
View Related
Aug 14, 2013
I'm using Access 2010. I'm passing a string into the OpenArgs of my report - works fine. In the report there are 3 rich text fields which may contain the text I passed in, and if so I want to change the color of that text to red so it stands out.
The value passed to the report changes so I'll need to use VBA in the detail's format section to check each of the 3 rich text fields.
View 5 Replies
View Related
May 1, 2008
Hi everybody,
I am appending the information I have in one table to another. The first table's name is Videos and it only has 5 records. When I append the information from the other table which has some of the fields of the Videos table (but doesn't have a primary key), the new records in the Videos table have as primary key value (the autonumber field) values that start from 44699657. Why is this happening?
View 2 Replies
View Related
Dec 24, 2013
I have a tbl that gets populated from an append query and a form that is crated from this table. there is one field that gets populated from the append query called departments, the department names are long and we have abbreviated versions of the department names that I would like to use.
In the table I have two columns “department” (for long name) and “updated department” (for abbreviated department name) When I use the append query to update the department (long name) I want the abbreviated department column to populate.
For example if department column reads "Drilling and Workover" from the append query, I want updated department column to slefpopulate to read "D&WO"
I want the updated department field to automatically fill with the abbreviated name. How do I do this?
View 9 Replies
View Related
Oct 16, 2014
I have successfully used VBA to populate select query results into an excel worksheet on open, for a co-worker. Now I am trying to populate the records from an append query to the bottom of those results, which are now in a table on an excel spreadsheet, Contractor EIF. I am trying to make this work because my co-worker modifies the results in the table, deleting rows, adding fill color etc. and as new projects begin he would like those added to the projects already in the table (without rewriting the entire table).
Private Sub Window_Open()
Dim strDB As String
Dim strMyPath As String
Dim strDBName As String
[code]....
View 2 Replies
View Related
Sep 22, 2013
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
Code:
txtCost = DLookup(cboRate.Value, "tblItem", "ItemID=" & ItemID)
View 5 Replies
View Related
Feb 6, 2014
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
View 1 Replies
View Related
Apr 16, 2015
I have a continuous form with a text field that says "Select". There are two other fields, one of which is Brand. When the Brand Combo box has nothing in it I want the text box to appear (instructing the user to select a Brand) But once the user selects a Brand and goes to the next records, I would like the "Select" in only that record to become not visible. I tried conditional formatting but can't apply that to an unbound control ( or at least it is grayed out when I select the text box) and any other possible solutions I have found changes all of the selects - not only the one in the changed record.
View 7 Replies
View Related
Dec 3, 2014
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
View 8 Replies
View Related
Nov 7, 2013
is possible to run an Append Query and update a field at the sametime?
i.e. I would like the MealDate field to be incremented by 1, just not sure how to write the code within the query!!!
This is what I have so far!!! not working
MealDate: ([MealDate](Date()+1))
View 5 Replies
View Related
Mar 13, 2014
I want to set a table field's default value to whatever is displayed in a certain field on a certain form at the time.In other words, say I have a database with a table called TABLE1, and two fields called NAME and SCHEDULENUMBER. I have a form called CreateSchedule with a SCHEDULE NUMBERCONTROL form and a NAME form, and I can enter names onto it, and it records to the proper SCHEDULENUMBER. So if I pull up SCHEDULENUMBER 4, and add three names, when I go back into TABLE1, I can see those three new names, and each one has the SCHEDULENUMBER set to 4.
What I'm trying to do is write an APPEND QUERY to copy a list of names from a different table, and paste them into TABLE1. The problem is that the other table doesn't have a SCHEDULENUMBER field. What I want to do is put a button on the CreateSchedule form that runs an APPEND QUERY, and sets the SCHEDULENUMBER to whatever value is displayed on CreateSchedule's SCHEDULENUMBERCONTROL field.
I tried setting a default value in TABLE1's field properties for that SCHEDULENUMBERCONTROL field, but I keep getting error messages. I just want TABLE1, whenever I add a new record (regardless of how I add the record: manually typing it or clicking the append query button) to look at the form CreateSchedule, and set it's own SCHEDULENUMBER field to whatever is displayed in CreateSchedule's SCHEDULENUMBERCONTROL form.
View 1 Replies
View Related
Jul 29, 2015
I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1
Start with summary
--================
Part Qty
111000 2
222000 3
End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================
I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.
For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n
maybe a do while or some other approach?
View 3 Replies
View Related
Sep 19, 2013
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
View 2 Replies
View Related
Feb 25, 2014
I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.
So, the best way I saw to go about is to set up an append query and then create a macro that runs it
So my tables have the values FirstName, LastName and isValid (more but keeping it short)
So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it
Field:FirstName
Table:TableOne
AppendTo:[TableTwo].[FirstName]
Criteria:[TableOne].[isValid] = 1
I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.
View 5 Replies
View Related
