Using A Form To Save Data From One Table To Another
Jun 14, 2012
There is a public master database with a bunch of tables and data in it being maintained by another group. My boss wants to skim some information from this, add some of his own information to it, and save it in a completely separate .mdb file on our server.
I've used Access to link to the public database, built a custom table just for us, and built a form. The form uses bound controls on the left side to pull in data from the public database, and unbound controls on the right side for user entry of data. I coded a VBA save button that should save all controls (bound/imported as well as unbound/data entry) to our local table.
The unbound controls save just fine, but the bound controls are missing from the table. A new row is created with no problems, I get no error messages, but half the fields in the table are just blank.
Code:
DoCmd.GoToRecord , , acNewRec
Dim Rs As Recordset
'Dim SDB As Recordset
'Dim strSQL As String
Set Rs = CurrentDb.OpenRecordset("Supervisor Table", dbOpenDynaset)
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
i got a form with three normal fields where i add data i then have two auto number fields i.e. SupplierID and PersonID the supplierID works fine, i can add a new record and click save and it will save the data in the suppliers table.The problem is with my PersonID field, i need it to retrieve the data from my subform and firstly display in the field on my main form and secondly, when i click save it should save save the number that is displayed into my Suppliers table.
I have used DateSerial to calculate a future date in Microsoft Access form, but it wont save the calculated date on a table (I need the calculated date on a table so that I can generate a phone list sorted by dates).
I have tried to use the formula (=DateSerial(Year([StartDate]),Month([StartDate]),Day([StartDate])+21) in Defaul Value, without avail, and while the formula works in the Countrol Source, it wont save it to a table because it wont accept the formula and link together, so that I can do a report, or search on it.
Hi Guys, What i am trying to do is, i have two tables called Table1 and Table2.
I have created a form called Form1. This Form1 has all the fields from Table1.
What i want to do is, as soon as a user fills in the details in Form1, obviuosly it saves those details in Table1, BUT i want it to save a couple of field values into Table2 as well.
How do i go about doing this??
In Table1 i can access the fields by "Me.[Fieldname]" (from the VB script), but how do i access Table2 OR how do i save data to Table2 from Form1.
Table 1: Vender Name, Number, contract, amount, quantity,and order number. Table 2: Doc #, Date. Multiple Doc #'s and dates will be saved under one vendor name (hence the two tables).
What I need is a MACRO where once I save the Doc #and Date to a record, I need to be able to go back to that record and enter a new Doc # without saving over the one I originally did.
I am about to start working on a new project where I have a front-end in Access2000 that is linked to a ODBC Data Source (ORACLE).
There could be some performance issues in the future, when the users have to retrieve (query) the data from the ODBC over the network and it slows down. Nothing is sure yet, but when this is the problem, I will need to look at an alternative plan. I had the following in mind:
I would like to create a scheduler program, that will retrieve all the relevant data from the ODBC and update the tables that are local to each user’s Access .mdb file. Can I do something like this in VBA, or do I need to do something with the Windows Scheduler?
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
What I'm trying to do: I have created an unbound field within a subform's footer to calculate the average of the displayed record values. The subform is in datasheet view. The records are returned based on a query with a relationship between two tables. I need the calculated data (which I currently have displayed on the main form) to populate within the master table.
I can't figure out how to automatically do this. I created a simple command to get it there, but I'd like the user not to need a button to display a calculation.
Main form based on TableA; subform based on TableB.
Here's how I have it set up (Btw, I suck at SQL I just figure this is easiest to read):
Query SQL looks like this: SELECT tblB.Field1, tblB.Field2 FROM tblB.Field1 INNER JOIN tblA ON tblB.Field1 = tblA.Field1
Main form: unbound txtbox = [Forms]![frmA]![subfrmA].Form.[txtAvg]
The field that is averaged is tblB.Field2. There are potentially a bunch of records displayed in the subform.
I need that [txtAvg] field to save to TableA. My command button is simply Me.tblA.FieldName = Me.unboundtxtbox. I'd like to do away with that.
I tried to use the on current or on load event for the main form; but the calculated field value is 0 until it calculates it. It seems like there is a short delay before the value shows up on the form at which point the on current or on load events don't pick up the calculated value; just the 0 that is initially there.
I am using Access 2003.I have created a Makle Table Query which gives me the invalid argument error because it makes the database balloon to over 2gb. I have two questions:
1. Is it possible to get the make table query to save data in one database until it reaches say 1.9 gb and then begin saving the rest of the data in another database?
2. Is there a wey to pause or stop a make table query to see the results and then let it run again?
I have some issue when i save list view data in a access table.In form I have a list box(listtotalcount) and one text box(ID). In form load event I run a query on that list box like: "SELECT count(id) FROM table A" and it gave me the correct result. Now I want to store this data on a different table (table b) and I use this code:
Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("table b", dbOpenDynaset)
[code]....
But the problem is when I click the save button it store only id not the list box value.
I have a form that no longer saves data in some fields, but does save data in others. Nothing has changed, except that it no longer saves the new data which it did save at one time. I looked at the code behind the form, and the requery statement is still there.
In my UpdateForm I have 3 fields. PartNumber, Description and SerialNumber. I use 2 tables for these. My MainTable and PartsList Table.
In my PartsList table I have the list of PartNumber in Column (0) and Description in column (1).
In my MainTable I have 4 fields: TransactionID (autoNumber), PartNumber(text), Description(text) and SerialNumber (text).
Now, In my UpdateForm I want the user to just select the PartNumber with a combo box (that also show the "Description" (I created this using the combo box wizard)). But I want to auto populate the field in my "Description" text box every time the user will enter new record and will also update my MainTable with all the values they entered in my UpdateForm.
I tried this codes in the after update of PartNumber combo box (properties):
I have created single form and 7 different tables. In my form I have a combobox which have different branch names (Table names are as per combobox values). Now what i want to do is whenever I select a branch name from combo box and click on the save command button the particular record should be saved in particular table.
I want to open an existing record via a form and then edit that data but save the form as a new record. Basically a form used for handover/takeover process where most of the data remains the same but the new form is the basis for the next handover/takeover.
The DLookup works fine, just I want the value to be stored in the table. Please help me ! I have been browsing the internet for the whole day and i can't seem to find the right solution! Thanks!
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
I have a button that when clicked moves a piece of data to a subform. I have put the whole sequence below. The trouble I am having is :
- The event will not happen until the form is saved. I think this is because fkTaskID is a new record (auto) number which hasn't generated yet. - If I force the form to save it does work but then I get an error on the INSERT command when not all required fields of the form are complete (see sample in second part below).
Is there a way to save the record and maintain the integrity of the form input - and still have this code work?
BUTTON CODE ========== Private Sub BTNAddReasonRw_Click() Dim dIndex As Long DoCmd.SetWarnings False For dIndex = 0 To Me.LISTReworkReasonsUnselected.ListCount - 1
I have a form which calculates alot of numbers. Im trying to figure out how to save the calculation to a table field. Is this possible? Can someone help me with a solution please
I have a form which contains one subform. On the subform I have a command button which saves the record just entered. On the main form I have a "refresh form data" button which updates the main form so that the calculated controls can show the correct results based on the data just entered?
Can anyone tell me how I can get the "save record" button in the subform to subsequently refresh the data in the main form as well, thus saving a button???
Hi.. I cannot save the data in the forms I have made. once I close the form, all the saved infomation is lost when I reopen the form. But somehow they reman stored in the table. Any suggesstion on how to retain the records in the form too. Regards Rahul
I have a client I'm currently working for that has dumped Access on me. I'm not originally here for Access and am sort of learning as I go along (I'm here to setup, run, and maintain servers, etc.)
The problem is they have a large contact database in Access, and want the forms to pop up a dialog asking if data changes are okay before actually updating. I know that Access normally just does the change, but there is an issue with someone that doesn't understand computers at all making a mess of it (adding in one client 15 times, each with one different piece of information, erasing things in an attempt to search, etc, etc.)
I've been going all over the place trying to research the issue for hours and have had no luck finding a solution. Any help would be beyond appreciated. Thanks in advance for any help!
On my form I have calculated fields based on information from my sub forms. I have the calculations all working fine however I can seam to figure out how to save them in the table that my form is linked to as my control source is the calculation. Should I be using my calculations in an area other than the control source?
Hi everybody, i am an U Student from Singapore.and i am a new MS ACCESS learner. So please kindly advice me a question about MS ACCESS. I created one form called MonthlyTransaction. Inside this Form, i have another sub-form called Forecast. So my question is how do i save the MonthlyTransaction data into monthlyTransaction table and Forecast data into Forecast table? But the field name in Forecast table called MonthlyTransID is the value of MonthlyTransID in the MonthlyTransaction table.
