Insert values from selected fields from a form into a table?

[PhilipJFry]

Hi All,

I was hoping that someone could point me in the right direction since this is my first Access project, and I’m still trying to pick things up.

We have projects that will need attention at certain pre-determined dates, which are partially selected by a user, and partially calculated.
For example:

--I have a form where there are fields for Project Name, Start Date, 1, 2, 3, 4…10, 11, 12 Months.
--The Project Name is selected from a dropdown, Start Date is entered by the user, and the remaining dates are calculated based on the Start Date.
--Each date field would have a check box next to it to indicate if the project will need attention at that time point.
--Not all projects will need attention at the same interval or number of times.
--What I’m hoping to have is a way for the user to select say, Start Date, 1, 3 and 10 months, then click a button to insert those dates into a table along with the Project Name.
--I’m hoping to avoid having empty cells in a table due to certain dates not being selected.

Does anyone have any ideas on how to insert this data into a table based on a check box selection? I’m honestly not even sure if the dates would be better to be listed where the dates go across columns (such as column header= Date1, Date2, Date3…) or down the rows (Column headers= Month, Date).

Is there a simply way to do this? Would it require VBA? Any help is appreciated.

Thanks!

 

[JANR]

I’m honestly not even sure if the dates would be better to be listed where the dates go across columns (such as column header= Date1, Date2, Date3…) or down the rows (Column headers= Month, Date).

Definitly NOT accross columns (Boldpart), this is a one-many setup which requier another table (RedPart).

If you have a Project table and a ProjectStatus table that holds the child records from Projecttable, it is relatively easy to assign review dates at different interval by inserting the ProjectID and a reviewdate.

Possible setup:

tblProject:
ProjectID --- PrimaryKey
ProjectName
StartDate
.. other relevant fields

tblProjectReview:
ReviewID ---- PrimaryKey
ProjectFK --- ForeignKey linked to tblProject ProjectID
ReviewDate

See attached MDB, I have included a form that will insert future dates based on selection of a project and that a user selects a interval from a MultiSelect listbox and hits the savebutton.

The listbox gets its values from a intervaltable so should the need to expand the interval you can just add records in the table. No need to redesign a form.

Hope this can give you some pointers.

JR

 

[PhilipJFry]

Thanks JANR! Things make more sense now in light of your explanation... This definitely helps me out.

I have a more general question too if you don't mind. I've noticed that many people tend to use an autonumber ID as a primary key. Is this still necessary (or just good practice) if I know that I will have some sort of other unique identifier in my table? For example, in my project table, I know that no two projects would ever have the same name and so I set the project name as the primary key. Maybe this would be better as part of different discussion?

Thanks for your help!

 

[JANR]

No it is not necessary to use an autonumber as a primary key, if your projectnumber is truly unik then use that. The autonumber is just a very conveniant to use as it take care of itself as a systemkey, I don't have to maintain or think about it once I have made it.

In my view a tables primary key should never be exposed to the user or let the user manipulate it. We humans tend to do things we think is best and Murphy's Law creeps in and whaaam there goes you system.

JR