473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Transferring data from form to table

3
I am learning Access 2007 on my own, any help would be greatly appreciated. I have a form which I have associated with a table, TB1. There are 3 values from this table which I would like to transfer to a second table, TB2. How do I go about transferring data from TB1 to TB2 when the Add button is selected?

Thank you.
Aug 19 '11 #1
6 2261
patjones
931 Expert 512MB
Hi,

A little more context would be helpful...in particular, why are you trying to move the data from TB1 to TB2 in the first place? We often ask questions like this in response to questions in order to better focus the answer.

Also, is the form bound to TB1, meaning that any record you enter through the form - or changes that you make to existing records - are immediately reflected in the table?

Pat
Aug 19 '11 #2
P Ryan
3
TB1 contains information regarding the company fleet such as year, make, model, insurance info, driver, information for inspection, it also has information regarding the purchase of the vehicle, cost, date of purchase and mileage. All of the information on the form is recorded in TB1. I also have a second table with the odometer readings for the car which the drivers will update every few weeks or so. What I am trying to accomplish is taking the purchase date and purchase mileage and moving them to the table which will keep track of the odometer readings as this is the initial setting for the vehicle.
Aug 19 '11 #3
patjones
931 Expert 512MB
It sounds like you want to keep a history of odometer readings versus date. Do you have something that uniquely identifies each vehicle (e.g. VIN, fleet number, etc.), and which you can use to link the two tables together?

For populating purchase date and mileage for the records that you already have...this can be accomplished a couple of ways. One is to use Query Design view in Access to build an insert query that will perform the record insertions into TB2. This is a pretty straightforward 'point and click' method to quickly accomplish what you want. But a unique identifier (primary key) is helpful...really almost necessary, to accomplish this. A more advanced way to do this is to write a SQL INSERT query. This is basically the same thing - and in fact when you use the design grid to build your query that's what Access is doing, and you can even switch to SQL view to see the SQL that Access constructed for you.

Going forward, I would eliminate the initial odometer reading column from TB1 and keep that information just in TB2, as this will eliminate duplication of the data. When you initially enter a vehicle in the database, you would want to populate both TB1 and TB2 with the necessary information, so that involves attaching TB2 to the form as well.

I highly suggest taking a look at the article in our Insights section on how to properly structure database tables: Database Normalization and Table Structures. It has lots of useful information about creating primary keys and minimizing data duplication.

I know this is all pretty general, but I think it's the right direction for you to go in.

Pat
Aug 19 '11 #4
P Ryan
3
Pat, thank you for the help. I do have a primary key for the tables which is a vehicle id number. I realize that there will be duplication of information, but there should not be more than 30 or 40 vehicles to track at any one time. And since I am very inexperienced with Access I thought it would be simplier to copy the one duplicate record to the second table to elimate trying to figure out how to determine the purchase date and mileage when needed. I think I will try attaching both tables to the form and see how that works.

Peggy
Aug 19 '11 #5
patjones
931 Expert 512MB
Hi Peggy,

Sounds good, but let us know if you have problems attaching both tables to the form. This would mean joining the tables together via the VIN if they aren't already joined.

Pat
Aug 19 '11 #6
NeoPa
32,556 Expert Mod 16PB
P Ryan:
I thought it would be simplier to copy the one duplicate record to the second table to elimate trying to figure out how to determine the purchase date and mileage when needed.
That's a very common misconception held by inexperienced database designers Peggy. The reason that experienced developers advise against it (as Pat was doing when he directed you to that article) is because we know from experience that it will only lead you deeper and deeper into the mire as you try to use and develop your database. Very similar to the biblical reference to building houses on sand if you're familiar with that.

Obviously, your choices are your own and it's not our responsibility to push you one way or another, but we don't feel it would be fair to you to avoid expressing reservations about your current approach.

That said, I'm fully confident that Pat can lead you forward in this, whichever course you choose ultimately.
Aug 19 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: r_burgess | last post by:
I am looking for some guidance on transferring data between two pages in my ASP.net Web app (intranet). I have a form that will have a button and a text box on it (among other controls of course)...
3
by: phong.lee | last post by:
Hello all, i'm new at this. I need some assistant in transferring data from excel to access. I created a macro that basically gather all the necessary data that i need to bring into access. I...
0
by: AZ Jack | last post by:
I have been using a form generated with the Data Form Wizard as a model to generate a standard update form. The wizard uses BindingContext to tie a form to a Database Table. I am using the...
15
by: http://www.visual-basic-data-mining.net/forum | last post by:
Does anyone have any idea how to transferring data from TextBox1 in form1 to textBox2 in form2..... That means after i fill in any data in textBox1 and click Next button... It will bring me to...
1
by: abc my vclass | last post by:
Is VS2005 debugger let me to see dataset, datatable data as table form? I found if I watch some table or dataset variable is very very hard to see. Is there any good tools or add-ins for debugger...
1
by: go2bee | last post by:
Tamplat is it HN: 49xxxxx Name : Cxxxx xxxxx PID : 3xxxxxxxxxxxx Address : 1xxx x xxxx , Tambonxxxx , Ampurxxxxxxx ,Provincexxxxx Postcode : 1xxxx At xxx is Data form Table Name
2
hsriat
by: hsriat | last post by:
I have a page working on Ajax. The problem is, after doing many changes using Ajax (like uploading, changing name, adding to favorites etc), the status bar starts behaving unexpectedly. Even when...
3
by: angusfreefa | last post by:
Dear All, I am facing a problem of transferring data between 2 tables within the same database. I set up 2 tables. The first table is the permanent table (oos_table) for saving records. the...
35
computerfox
by: computerfox | last post by:
I'm writing a work order system using PHP and MySQL and I'm stuck on two things-transferring data and updating the data. Here is my code: i'm using checklogin.php to do most of everything so...
0
by: rahul more | last post by:
My aim is - Access data from table and show it into a form's textboxes , comboxes for edition of data. I am using listview. I am selecting particular row of listview by clicking on that row and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.