Modules & VBA :: Code To Copy Partial Data To Another Form / Table
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.
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
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" .
I have a form which is bound to a table. I want some of the Data to go to another table which is not the control source. I am trying this code, but no luck. What am I doing wrong?
My user will open the database & use frm_TEMP. They will see a record using the form. The data will be in tbl_TEMP at first. My user will check all data, make any changes, then I want them to click a button on the form that will move the corrected record from tbl_TEMP to tbl_FINAL. It will then refresh the form and bring up the next record.
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
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.
I have a form with 2 subforms, each based on their own table. One displays categories of invoices (e.g. rent, electricity, etc.) including some details like monthly costs.
I would like users to be able to select one of those categories and copy this to another table, after which they can enter on what date the invoice was paid, to make a history of payments.
At first I based fields in the history table on fields in the categories table so that you could simply pick a category from a combobox. I had an after update event on that combobox that also automatically set the 'costs' combobox to the matching price.
Worked fine, but had one snag: if I update the costs of a category in the categories table (e.g. the rent goes up) then all the costs in the history table was also updated because the fields got their info from that table.
So now I no longer have field from the history table based on the other table, and want to use 'set value' to copy values from one table to the other. I'd like the user to be able to somehow select a category with a single click on a button, and getting the info for that category copied. However, how can I get access to know from which record I want the fields copied?
Or is there a completely different way to get a history of payments that works much better?
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
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.
I have a form where I enter the students lastname, firstname I need a field that just pulls the first 2 letters of the lastname as well Is there an easy way to do this
I have a form where I enter the students lastname, firstname I need a field that just pulls the first 2 letters of the lastname as well Is there an easy way to do this
I have a form where I enter the students lastname, firstname I need a field that just pulls the first 2 letters of the lastname as well Is there an easy way to do this
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
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.
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
I am trying to search for a partial number in the primary key field. I have based my search off Allen Browne's search form [URL]
I have posted my full code below. I am having trouble with the portion searching the Member_ID field.
My first version:
I would get a Run-time error '3464':
Data type mismatch in criteria expression.
Code: If Not IsNull(Me.txtMember_ID) Then strWhere = strWhere & "([Member_ID] = """ & Me.txtMember_ID & """) AND " End If
My second Version: (I was searching for 54)
I would get a Run-time error '3075'
Syntax error (missing operator) in query expression '([Member_ID] = Like "*54*")'
Code: If Not IsNull(Me.txtMember_ID) Then strWhere = strWhere & "([Member_ID] = Like ""*" & Me.txtMember_ID & "*"") AND " End If
Version 3:
If I use this the search works when the Member_ID matches exactly
Code: If Not IsNull(Me.txtMember_ID) Then strWhere = strWhere & "([Member_ID] = " & Me.txtMember_ID & ") AND " End If
Code: Private Sub Member_Search_Click() 'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter. 'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _ we remove the trailing " AND " at the end. ' 2. The date range works like this: _ Both dates = only dates between (both inclusive. _ Start date only = all dates from this one onwards; _ End date only = all dates up to (and including this one).
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.
Hi: Can any one please help me when user select 2 dates from DDLDate1 10/09/2006 and DDLDate2 10/12/06 and the name and it close the form. I need to create multiple records in the another table on the basis of two dates like that.
Data in continous form table1 ----------------------------- Data entered in continous form 10/09/2006 10/12/2006 John 10/12/2006 10/13/2006 Petty 11/11/2006 11/11/2006 Stiffny
Required data into table table2 10/09/2006 10/09/06 John 10/10/06 10/10/06 John 10/11/06 10/11/06 John 10/12/06 10/12/06 John 10/13/06 10/13/06 John 10/12/2006 10/12/2006 Petty 10/13/2006 10/13/2006 Petty 11/11/2006 11/11/2006 Stiffny
I am currently using a continuous form where there are two drop downs one for AppDate and other for EndDate. I need a script here once use close the form, Script check the data and create multiple records on the basis of date selection into table2. I am using two tables. One for the continous form and the other for the reports (Both have same fields). Continous form table i empty it every time once it close. So i need the result into another table assume form table name is table1 and table where i need multiple records name is table2.
I have a database as follows: Date1 Name Issue Action_Item1 Status1 Action_Item2 Status2 .....
with up to 5 max action items/status; where Status is a checkbox.I ONLY want the report to return records where there is text in an action_Item input AND the corresponding status is FALSE. The issue I'm having is that there may be ACtion_Item inputs withOUT text; so I can't simply query the Status checkbox for false.how to get started.
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.
How do I get an update query to only copy part of a fields value?For example: The original field has a date in month, day, and year. I only want to update the new field with only the year.
I have a field called Shipdate. I have created a new field that is called invoicedate. What I want to do is copy all of the shipdates and paste them in the invoicedate field for my old records. From here on out they would be distinct fields but for old records and reporting purposes I need those dates.
I have a problem.... How can I copy data from one column in a table to another colunm, with WHERE and UPDATE? And how can I find the data, which is the closest, for example using LIKE '%' UPDATE tag_info,tag_info_kilde SET tag_info.TAG_DESCRIPTION = [tag_info_kilde].[TAG_DESCRIPTION_KILDE] WHERE [tag_info].[TAG_NAME]=[tag_info_kilde].[TAG_NAME_KILDE];
In one table (Alpha1) I have a person's general information (e.g., name, SSN, address, phone number, etc.).
I have a person enter his or her SSN and the general information is populated.
I want the person to click on an icon and all the Alpha1 information is transferred to a new table (same field names), then bring up that same record in a new form.
I'll do the work. I simply need the general concept of making this work.
