Modules & VBA :: Copy Value To 2nd Table
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 Replies
ADVERTISEMENT
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
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
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 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
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
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
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
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
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
Feb 14, 2006
My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.
I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.
Any help or examples are very much appreciated.
**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.
Thanks a Bunch!
EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone
Customer History (Table 2):
CustomerID
Name
Phone
That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.
View 14 Replies
View Related
Nov 14, 2013
ok so I have two forms open: One is a SP Bookings form with a subform SPDetails Query subform. The other form is the not connected/related SP Contacts Query form, to which users enter Full Name weight age ect. Need to copy the names of the new customers Full Name to the Lookup Contact Column in SPDetails Query subform. I got this far, with my trial runs are commented out. The only thing it does when I click on the button is move to new record on Sp Contacts Query Full Name and Copy the previous record. How do I get the button to copy the Full Name from form SPContacts Query.Full Name to the SPBooking Query.subform SPDetails Query Subform.LookupContact
Code:
Private Sub Command52_Click()
Me.Full_Name.SetFocus
DoCmd.RunCommand acCmdSelectRecord
[Code].....
View 3 Replies
View Related
Feb 28, 2014
I need to create a function that I can launch using a macro that will copy an excel file from one folder to another in the file system.
View 4 Replies
View Related
Jun 25, 2013
This code is supposed to loop around a directory of photos (named after a project), and then copy a new photos (from another directory) to this folder, numbering them and the logging them in a table tphotos.The first time a copy photos to a new directory the code works fine. And I think I had it working fine before, but now when I copy photos to a pre-existing directory that has already photos, it only overwrites the first photo, instead looking for the last photo and attributing the new photo number n+1.
I've done debug.print in the code, and it looks like is not going into If NewFileName = f1.Name, therefore doesn't compare the newfilename with the files in the folder.
Code:
Private Sub cmdAddPhoto_Click()
Dim fs, f, f1, fc, s
Dim fDial As FileDialog
Dim SourcefileName, destDir, FileExt, NewFileName, prjID As String
Dim intSel, FileNumber As Integer
Dim varSelFile As Variant
Dim rsPhoto As Recordset
Dim myDB As Database
[code]...
View 5 Replies
View Related
Sep 2, 2014
I have searched and have not been able to find anything on creating a read-only copy of a back end DB with VBA. I am creating backups of my back end file and would like to create a local read-only copy to use in the event that the regular back end is not available. The code I am using to create the backup is:
Code:
Public Function BackupBE()
'On Error GoTo BackupBE_Err
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String
[code]....
View 9 Replies
View Related
Sep 24, 2014
I am currently trying to copy a tab from one workbook ("List.xls") to another ("IGEN_QC.xls") but I keep getting an error:
Method 'Copy' of object '_Worksheet' failed
Code:
Sub copydata()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String
On Error GoTo ErrHandl
[code]...
View 2 Replies
View Related
Oct 24, 2013
I need to duplicate a number of controls on a form. They are all acCommandButton types, but they have different images, sizes etc.
Anyway, my thought process was if I have a "template" acCommandButton then when I create my new controls if I could use this template then the code I have hanging off them (I have a function being called on the onClick event, borders are set to hairline, themes turned off and various other settings) would be copied. Save me trying to set them all individually.
So, my question is, can I "copy" a control in form design through VBA. Once I create the copy of the control I can then change the couple of properties I need to and "hey presto" I have skipped a load of settings that I don't need to set.
At the end of the process when I rename my form I can delete my template control and I have a working form.
View 7 Replies
View Related
Aug 4, 2013
I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:
Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _
[Code]....
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
View 3 Replies
View Related
Oct 11, 2013
Below is a snippet of the code that I use, the user is promoters for a week, this has always worked and still does for any number less than 245. I dont know if 246 will work yet as I dont have that data.
Is there anything that would cause an integer with value 245 not to work? The query does still return values when I run it manually with value 245 and the strings for the query are all correct, hence why it works perfectly until you try and run it for week 245.
Code:
' Excel Objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range
[Code] .....
View 7 Replies
View Related
Apr 21, 2015
I have 2 fields in an MS Access form "BTKForm" sending and picking up data form a bank terminal.
Code is executed by clicking a Button on the form.
Field 1, "IssuerID", is an Unbound field picking up a code from the bank terminal. It's numbers between 0 and 100, representing the Card provider.
Field 2 - "IsID", is a value field linked to a table.
I want to take care of (save) (copy) "IssuerID" in to "IsID".
Code:
Public Sub TransferAmount_Click()
Set BAX = CreateObject("BankAxeptSrv.BankAxeptAutomation")
If BAX.Connected And BAX.LicenseVerified And Not BAX.BankMode Then
Dim amnt As Long
Dim cashb As Long
amnt = Round(Amount.Value * 100)
[Code] .....
View 12 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
