New to Access. Help me with Forms. 2

[Thankyou12]

Hello,

I'm new to Access. I need to build a database and create forms to update the database. I'm using Access 2007. I have built the database with 10 fields. The primary key is the ID field.

I want to update, delete or add new records to the database using forms and controls. The updates and deletes have to be based on the 'FactoryName' field. How do I do that? Do I need to know any type of coding? I'm pretty ok with SQL.

There is just one table in the database. The idea is to capture the savings for each location each day. Some amounts would be entered into the database by the field officers using the forms. Other amounts would be imported from Excel spreadsheets. They also need the ability to update, or delete an existing record.

Here are the fields: ID; FactoryName; Dollar Spent; YTD Savings; Annual Savings; Savings Ratio; Projected Savings; Stated Ratio; Stated Savings; Date
The Projected Savings and Stated Savings would need to be calculated based on the Ratios.

Any help is appreciated.

Thanks!

 

[lameid]

Use a form wizard to make your form. By default you can perform all those functions in bound form (one that has a table or updateable query behind it or as its recordsource).

The record navigation buttons at the bottom include a left arrow with a right arrow / triangle and an asterisk that is the new record button. It moves to a new record.

You can also add buttons to your form in design view that perform the same actions if you do not like the navigation buttons. The button wizard should help you through that as well.

As for the values that need to be calculaed. Typically you do not store the results of calculations instead you calculate them using a query for reporting. If you did want to do this it would require code. You would have to do the calculation on the After update event of the underlying field(s). If it is multiple fileds, you should write a common procedure and call it from both after update events.

 

[Thankyou12]

Thanks! This is a lot of fun. I created the form and it works fine. I found some redundant data in my table. I created another table and placed a relationship between the two tables. Is there any way I can update both table using one form? Thanks again.

 

[dhookom]

You generally want to use a form with a subform. The main form would be bound to the parent records and the subform would be bound to the child records. There are Link Master/Child properties of the subform control that allow you to establish and maintain the primary and foreign key values.

Duane
Hook'D on Access
MS Access MVP

 

[Thankyou12]

I now have a switchboard, two forms - one to add the Factory Names and then another to add the savings for each factory and then a report that displays the calculated values for each factory. It is wonderful. Is it feasible to create a button on form 1 so that when a user enters a Factory it adds it to the database and then takes them directly to the Savings form? Thanks for the help.

 

[lameid]

Yes. When you add a button to a form in design view you should get a wizard that will create the code to do either. You can then modify this code to add the other thing. The nice thing about using the wizard is it automatically generates an error handler in case the code blows up for some reason.

The two operations are Save the current record...

docmd.runcommand accmdsaverecord

and open the form...

docmd.OpenForm "Form Name"

So if you want to save the record and open a form named [red]frm Savings[/red], your code would be...

docmd.runcommand accmdsaverecord
docmd.OpenForm "frm Savings"

You get at the code in the form design view. Get the properties of the button, and find the On Click event. Because you have used the Wizard it should say [Event Procedure]. Click once on that text and you should see a little button with three dots on it next to the property. Click that. It will take you to the VBA procedure that is behind the event. Add the appropriate line of code before or after the other line so it looks something like what I have above (you should tab over to make it inline with the existing line for readability reasons; although tabs and spaces are invisible to processing). Be sure to save close and save the form. Now it should do what you want.

I hope that was clear enough.

 

[Thankyou12]

Thanks, that worked great. On the form 2 I need to display the savings values for the factory I entered in form 1. Is there a way to tie them together like that?

 

[lameid]

It sounds like you want a subform.

If you do this and you put the factory information on the opened formed then you should close the other form first because ideally you do not want two forms open with the same table behind them at the same time.