Form Data Not Saving In Table

[Aussie60]

Hi this is my first post and am very new to Access and would appreciate some help with the following problem.

I have created a database with a Switchboard, on the switchboard are buttons to open various forms. When I open create new Project the form opens and I am able to enter the required data and save the record. This data is saved to the tblProjectDetails, this works fine.

When I open the Input Data To Project form this is where my problems begin. At the bottom of the form is a combobox that looks up project numbers. When I select a project number it brings up the data that was previously inputted when creating a new project. This appears in the top half of the form.

On the bottom half of the form is where I enter everyday data to the project number that I have selected.When I enter the data in this section save and close the form it is not recording in the tblProjectDetails.I have attached my Database so you can see what I am trying to achieve. Any help would be greatly appreciated.

 

[missinglinq]

All Controls on the lower half of the form, with the exception of the "Date" Textbox, are Unbound! In order for a Control's Value to be saved to the underlying Table or Query it has to be bound to Field in said Table or Query.

In Form Design View

1. Right Click a Control
2. Click Properties
3. Click Data Tab
4. Set the Control Source Property to a field in the underlying Table, using the dropdown box
5. Repeat Steps 1-4 for each Control that you want saved to the Table

Now, about this Field named "Date." Date is a Reserved word in Access and Reserved words should never be used as Field/Control name so you need toi change this to something else.

Welcome to AWF!

Linq ;0)>

 

[Aussie60]

Thanks for that Missingling

One other problem I have is when I select the Project number to add additional data how do I get it to generate another row in the tblProjectDetails and save it as that Project Number

 

[missinglinq]

I don't know your business nor the associated business needs, but it appears that your data is not normalized properly. From your fields I'm assuming that one project can possibly have multiple subcontractors, multiple suppliers, as well as multiple employees assigned to the project.

The general rule of thumb is, if a single entity, such as a project, can have one or more items in the same category, such as subcontractors, the category needs its own table.

If my assumption is true, as stated above, you need separate tables for each of these categories. Just as a very simplified example, to show you the concept, let's look at a ProjectTable and a SubcontractorTable.

ProjectTable
ProjectID Primary Key
ProjectName
QuotedPrice
etc.

SubcontractorTable
SubcontractorID Primary Key
SubcontractorName
ProjectID Foreign Key
etc

These two tables would have a One-to-Many relationship. One project could have Many Subcontrators.

This type of scenario is the classic case for using a Main Form/SubForm setup.

The Main Form, in this case probably a Single View Form, would display data from the ProjectTable, data that will only need to appear once for a given project, such as its ID, name, etc.

The SubForm, usually a Datasheet View Form, will display data from the SubcontractorsTable, such as the subcontractor ID, Subcontractor name, etc.

The Main Form will be linked to the Subform using the ProjectID field that appears in both tables.

While on record for a given project in the Main Form, the Subform will show all Subcontractor records for that project. If you need to add project details that involves a Subcontractor you add a new record to the Subform.

In a like manner you'd have a Table and Subform for Suppliers, linked to the ProjectTable in the same manner as above. Ditto for Employees assigned to the project.

As a matter of practicality, for this kind of thing, you would usually have a Main Form, with the ProjectTable data, then a Tabbed Control with one of the Subforms appearing on a separate page.

So you'd have a project's main data on the Main Form, then you could click on different tabbed pages to look at related subcontractor data, suppliers data, employees data, etc.

Linq ;0)>