Modules & VBA :: Copy Records To 2nd Table
Mar 28, 2014
I'm getting myself into a bit of a muddle.
I have four tables:
tblinvoice / tblinvparts
tblquotes / tblquoteparts
when I open the tblinvoice form it has tblinvparts as a subform. tblinvoice has a listbox that lists quote numbers linked to vehiclereg. I have a button on the tblinvoice that will open a popup tblquoteparts continuous form linked to the quoteid on tblinvoice. These filtered records have a select button so I can select all or some.
I have now got stuck as I need a button on tblquoteparts popup that will copy the selected records from tblquoteparts and paste them into tblinvparts where quote (on tblequoteparts)=quoteid in tblinvoice.
In a nutshell, I would like to copy selected records from tblquoteparts to tblinvparts.I need to duplicate the records because only 10% of invoices are generated from a quote and the quote parts/prices may differ from the final invoice.
View Replies
ADVERTISEMENT
Jun 22, 2015
I have two tables in my Access database, their fields are exactly the same (for now). One table is called Uncheched. The other one is called Checked. So what is need is a macro that takes selected records in table Unchecked and copies them to the end of table Checked. Actually CUTS from Unchecked and PASTES to the end of Checked table.
Sub MoveRecords()
DoCmd.RunCommand acCmdCut
DoCmd.OpenTable "tblChecked", acNormal, acEdit
DoCmd.GoToRecord , , acLast
DoCmd.RunCommand acCmdPaste
End Sub
View 13 Replies
View Related
Aug 3, 2015
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
View 3 Replies
View Related
Jun 22, 2006
Hi all
This gonna be a long one.... I've search the whole forum for answer to this problem, but couldn't find the one suitable enough.
I have a quote table with the following field:
QuoteName
QuoteDescription
QuotePrice
and booking Table:
BookingName
BookingDescription
BookingPrice
The real table is much more complex, but this will do for now.
When a client call up for a quote, their request will be added into the "QuoteTable". One name can occur many times depend on how many they ask for quote. Once they decided to proceed with the booking, all the records need to be transfered into "BookingTable".
Currently, I use either one of the following codes found in this forum:
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
Do Until rstQuote.EOF
rstBooking.AddNew
For Each Field In rstQuote.Fields
rstBooking.Fields(Field.Name).Value = _
Nz(rstQuote.Fields(Field.Name).Value, "")
Next Field
rstQuote.MoveNext
rstBooking.Update
Loop
or
Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
If Not rstQuote.EOF And Not rstQuote.BOF Then
rstBooking.AddNew
For lngInnerCounter = 0 To rstQuote.Fields.Count - 1
rstBooking.Fields(lngInnerCounter) = _
Nz(rstQuote.Fields(lngInnerCounter), "")
Next lngInnerCounter
rstBooking.Update
End If
But none seem to work. Can anyone help?
Cheers
arnodys
View 8 Replies
View Related
Jul 9, 2014
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
View 8 Replies
View Related
Feb 16, 2006
I have an Access database with linked tables from Sybase SQL Anywhere, via an ODBC data source. I have just linked the tables recently, all the data used to be in native Access tables. It is fairly common for members of our team to open these tables in datasheet view, and copy/paste several records to create new records, then edit a few fields on the new records. This used to work fine with native Access tables, but when we try it with the linked tables we either get an ODBC error - "primary key value already exists" or the new records show up with #Deleted.
The root of the problem is this: In the old Access tables, the primary key was an autonumber field, and Access was smart enough to assign new ID's when you copy/pasted records. In the linked table, the primary key is type "Number" in Access, and Access is not smart enough to let Sybase assign new ID's when you copy/paste records in datasheet view. Access is trying to force the existing ID's into the primary key field, and Sybase says "too bad so sad".
Of course, I could just write some quick append queries to copy/paste the data. And in the short term, that's exactly what I will have to do. But is there any way, long term, to allow members of my team to do it the "quick & dirty" way by copy/pasting in datasheet view? I have tried to change the primary key to an autonumber field in design view, but Access doesn't allow that. Is there a way to do it in code, or a way to force Access to allow Sybase to always handle the primary key field?
View 1 Replies
View Related
Sep 7, 2013
I'm using Access 2007.
I have a button on a form that when clicked, does the following:
I have a table called [Workorder Parts] that has 128 records in it with Fields named WorkOrderPartID (Autonumber), WorkorderID(Number), PartID(Number), Quantity(Number), UnitPrice(Currency), Notes(Memo), KitID(Number).
I want to copy records into another table called [tKitsWorkorderParts] that has the identical structure based upon a value in the field WorkorderID. For testing purposes let's assume that the field contains the value "12". There are 28 records in the table that have the value set to "12"
If I hardcode the value "12" into the following SQL statement, it finds and copies the 28 records correctly.
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = 12"
so far so good.....
Now, on the form there is a Text Box called WorkorderID that contains the value "12".
If I change the code to the following, I get all 128 records instead of just the 28 I am expecting:
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = WorkorderID"
I have inserted a few MsgBox displays to display the value contained in WorkorderID and it shows "12"...
What am I doing wrong???
View 2 Replies
View Related
Apr 20, 2015
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
View 3 Replies
View Related
Jun 18, 2014
I have a database with two tables, and a form that is bound to the 1st table. I have a duplicate record button on the form. One of the functions I want to happen is when the duplicate button is pressed, one value is copied from the form and added to the 2nd table. The tables are basically set up the same with an ID column which is the key and an autonumber, and the second value is a number. So I want to copy the number to the other table and add a record. What is the best method? Can I use an insert into and just copy from a variable, or is the better method with a recordset. Either way, I have not been able to figure this silly thing out and it is preventing me from moving forward with this database.
View 11 Replies
View Related
Apr 15, 2014
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
View 6 Replies
View Related
Dec 13, 2011
I have a split form (frmPatient Schedules) that shows me my records in the datasheet view at the bottom section of the form. My records have a field with a checkbox (ckbxHOLD).how can I get the records that don't have their checkbox checked to be copied as new records to a different table (tblHome Visits)?
View 9 Replies
View Related
Aug 27, 2013
I have a projects Database with: Projects_Table, Staff_Table.
Both tables has forms for data entry or update. Each project has many staff allocated to it.
In the Staff_Form I placed the Project_Code in the form heading section. The form opens based on a search criterion on the project_code so it shows the staff for that project only. The issue is when I try to add a new staff for that project on the Staff_Form, the project_code stays blank in the staff_table.
What I need is that the project_code in the staff_table for that newly added person to have the same project_code on the opened form. Simply to link the staff to the project they work in when I add them using the form.
View 1 Replies
View Related
Feb 20, 2015
I want to save the timestamp created in a table when a user opens the database. My vba saves the computer name and user name and sets the currentuser = yes. The table is mysql odbc, and has the requisite timestamp and id fields. I have a form that I can open that shows who is online at that moment. When a user closes the database, the currentuser field is set =no. All of that works well. Now, I have added a "starttime" field in which I would like to store the timestamp that was set upon opening, since when a user logs off, the timestamp gets changed to the log off time.
Code:
Dim myQry As String
Dim sUserName As String
Dim sUserComputer As String
Dim currentVersion As Date
[code]....
The UserTimestamp in red is the field value of the first record in the table, not the usertimestamp in the user's record. How do I store the log on timestamp in the "starttime" field?
View 1 Replies
View Related
Jan 12, 2015
I have the usual setup with a front and back end.
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
View 1 Replies
View Related
Dec 11, 2013
I'm using the DoCmd.CopyObject method to copy a table from my front end db to my back end. My back end db is password protected and it keeps prompting me for the password. Can I somehow send the password in code so that the user is not prompted?
View 8 Replies
View Related
Jul 16, 2014
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
View 14 Replies
View Related
Dec 2, 2014
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
View 14 Replies
View Related
Jul 11, 2013
I need some simple code that will copy an Excel file or a table in Access to a specific location on an FTP server. I would think this would be a very simple task, but I have yet to find any sample code that is *simple*. I have seen lots of code that requires downloading this dll or that mda, but the examples don't work. There must be something built into MS Access 2010 that will allow a file to be uploaded to an FTP site.
All the variables are known:
The FTP location (it never changes)
The FTP Username and Password (they never change)
The destination folder on the FTP site (it never changes)
The File type (it never changes)
The File name (available from the form in Access from which this will be executed)
I can either produce an output file, then copy it to the FTP site, or I can export the table directly to the FTP site with the file name for that day.
This seems to be a very simple task with no simple solution. Currently I am using an FTP app to get the file to the FTP site, but I would like to automate this. The process that creates the output file is already automated, so I would just like to add this to the existing code as its own module.
I'm looking for a simple command on the idea of:
DoCmd.FTPFile, acExel, TableName, FTPFilePath, FTPFileName, Username, Password, acUpload
just made all that above up and none of it is a real function/command in VBA, but is just the kind of thing I'm looking for.
I would think that since I can download and XML file from an FTP site that it should be child's play to upload a simple file to an FTP site, but I can't figure it out.
View 8 Replies
View Related
Aug 5, 2014
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected
View 14 Replies
View Related
Feb 7, 2014
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
View 14 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
View 8 Replies
View Related
Sep 8, 2014
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
View 14 Replies
View Related
May 5, 2015
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
Attached sample database for more details.
View 4 Replies
View Related
Mar 21, 2014
I have a form with a listbox that displays the name of a table. Once the listbox item is selected, the table name is set to a variable called myFile. I want append the records from the table (myFile) into another table.
View 1 Replies
View Related
Jan 16, 2015
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox
Code:
Private Sub cmdAddMet_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Set DB = CurrentDb
[Code] .....
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
Code:
Set DB1 = OpenDatabase("serverotherdb.accdb")
strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'"
Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code:
For i = 0 To lstFacilities.ListCount - 1
If lstFacilities.Selected(i) = True Then
RS.AddNew
RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
RS!MEASUREMENT_PERIOD = Me.cboMeasure
[Code] .....
Is it even possible to do this?
View 10 Replies
View Related
