Microsoft Access: can a form update 2 tables?

Question

I am a teacher, constructing a video store database for my class. Can I create a form that will accept data for a "rental", and which updates two tables: Rentals, and Rental/Films? How? Thank you!

Answer 1

Yes.

1) Use VBA code - There are numerous ways but for a single line command to make a simple update, try the CurrentProject.Execute "(sql command)"

2) Use sub-forms - It's like having two or more table/query views on the same form. In this case, each would be updated only if you change something in the subform. For instance, a typical rental will have one rental transaction supporting one or more films to being rented. So the main form would be the rental transaction (name, cost, date rented), and the subform would be each film being rented for this transaction. In one screen you can update the rental transaction, and add/changed/delete the films associated with the transaction.

If you're finding you are trying to duplicate data across tables, you may want to re-consider your design. A good relational database design minimizes the need for redundancy.

Answer 2

The short answer is "yes"

The longer answer is "there is more than one way to skin a cat". I won't go into each possibility, so I'm going with the route that will most likely be easiest to teach: (Without seeing the database, my description will be a bit abstract - but you'll get the idea)

Create an update query for your tables. When you design the query, reference the form for the value of the update something like this:

[Forms!formname.fieldname.value]

When you've entered your value into the form, assign a button to execute your queries. Optionally, you can have a module that runs the queries - then just assign the button's "click" action to the module.

Answer 3

Yes, you can. There are two seperate ways to do this. One through Access using VBA (not recommended). Another way is using a window forms based program (such as Visual Basic 2005 Express Edition) and customizing the program to do exactly what you'd like for it to do (using Ole DB it's quite easy to set up access, queries, etc to multiple databases).

Check out the video tutorials that come with Visual Basic 2005 as they go through the exact steps of creating a program using databases. If you need more direction, shoot me an email at:

[e-mail]

Answer 4

the DoCmd.OpenForm approach enables you to specify the thank you to open the variety. To open with present records for modifying you are able to in simple terms open the variety: DoCmd.OpenForm FormName To open, exhibiting no records, waiting to function a clean one: DoCmd.OpenForm stDocName, , , , acFormAdd all the commas could desire to proceed to be; they are place holders for unused parameters. So, you've gotten 2 buttons on your menu, one to open for modifying, yet another to open for including. //