Writing Form data to two tables

[Robert88]

I have been searching to the point now where I am now scratching my head, I hope someone can help.......

I have a form(frmDailyBoatCatch) that contains information on daily boat catches (as I am a member of a fishing club). Currently the info from each boat is placed on the form. One record contains the results from two people (Captain and passenger), Name, Date and Weight. This form is connected to a table (tblDailyCatch1).

For the weekly reports I need information about each individual so in order to arrange each person I export the data to an excel sheet and append it twice to another table(tblDailyCatch2) in order to seperate my passengers and captains to individual records. This also allows for all the reports and queries to work other items in the Database. A little messy but it works.

I am looking to create a form with at least textboxes txtCaptainName, txtCaptainWeight, txtPassengerName, txtPassengerWeight and txtDate. I was hoping to have a command button which would write the info to two tables, tblDailyCatch1 as one record and to tblDailyCatch2 as two records, one for each individual.

I have been looking through three programming books (Beginners =>Advanced) on access and cannot seem to find anything on directly placing textbox info directly into tables using VBA and wondering if this is possible?

Any advice would be appreciated as it would make everything I have done so far a lot easier if this is possible??????????

 

[Mile-O]

Your approach is completely wrong here.

You need to split everything down to objects and decide which objects relate to other objects. Now, everything here is an object: boats, people, person type, and catches. There may, and probably will, be other things that will need tables created for them. However, using the four objects listed above we can keep this relatively simple.

We have boats, people, person types, and catches. You currently have something like this in the same table, don't you?

Date
CaptainName
CaptainWeight
PassengerName
PassengerWeight

This presents a problem. It violates the very first rule normal form of database normalisation (the steps to follow for good and appropriate design) which says that all data should be atomic (which it is) and requires you to also elimnate what we term a repeating group. In this instance the repeating group is fields of person after person: captain, passenger. The reason this is bad is that rules and limits can be prone to change. It may not happen here but if you had to add a third person to the boat then you would have to fix all your tables, all your queries, all your forms, all your reports, and anything else that makes use of the new person. That is why a database is built to grow downwards and not across. A repeating group is an indication that something requires a table of its own.

So, the solution, as I've said is to break the objects down into separate parts.

We need a table for boats, as you've said, and this should be straightforward. I don't know what sort of info you keep about the boat; I'll just add some basic fields.

tblBoat
BoatID (Autonumber, primary key)
BoatName (Text)

You can add other details to the boat table but the info you add in fields must depend on the Boat. Adding a group of tickboxes for stuff that it may hold on board is a repeating group and needs a new table i.e. outboard motor, oars, first aid kit (you never know )

The next thing to look at is the people. As captains and passengers are the same thing we just need one table for them. Rather than just store their name it's always best to split it out. Since its a club we can keep other information on them here, such as join date

tblMembers
MemberID (Autonumber, primary key)
Forename (Text)
Surname (Text)
DateJoined (Date/Time)

Now, as they stand we don't know anything about who is a captain and who is a passenger. So, we need another table to let us list the different people who can go on a boat.

tblMemberTypes
MemberTypeID (Autonumber, primary key)
MemberType (Text)

This means we can list in the MemberType field the different people who can go on board in the table's rows.

But we must return to the Members table as we need a way to differentiate between who is a captain and who is a passenger. We add a new field to the table, a Number field, with the exact same name (preferably) as the primary key of the table we are going to join. This creates a foreign key.

So, the members table is edited to look like this:

tblMembers
MemberID (Autonumber, primary key)
Forename (Text)
Surname (Text)
DateJoined (Date/Time)
MemberTypeID

Now, the next step is to think about catches. As I think of it there is two ways we can look at it. The first is that each pair of people has a specific boat assigned to them and this does not change. The other is that the boat people are assigned does not matter.

Which of these two situations is most relevant? Once you answer this I'll continue.

 

[Robert88]

This is great...........

Hmmm, This is great I was discussing this issues with a friend today as well. The key here is "Database Normalisation". After I have created new tables and primary keys the need to transfer it from form labels to a table might not be necessary, I think?

To answer your question about crew on the boats, usually the captain stays in his boat and the passengers rotate daily. After talking to a friend he made me aware it might be better to have both movable as we have had incidences whereby people have been sent to hospital (heart atack and hooks in them) and somebody else (captain or passenger) mans thier boat...

I am building this one in my own time more as a hobby database so it might take me a while to rearrange all the tables and data. I think the advice you have given to date is great.

Just one thing, is it possible to take a textlabel on a form and place it in a table using VBA, since I spent so much time on trying to find this as a solution I am still curios if this can be done? Might be handy for other things, or is this situation never required?

Look forward to your further advice as you suggested.