Access 2003: Create Project and add Records to Project

[spudracer]

It's been a while since I've messed with 2003 in any complexity.

I've got a form, we'll call Trips. I want to be able to create as many trips as possible (which I already have done).

Secondly, I have a separate form, we'll call Personnel. This form has two functions. One, I need to be able to add and delete personnel (which I already have done). Secondly, I need to be able to show all trips a person has either gone on, or is scheduled for (which I haven't done, but will setup).

Now for the part that I'm struggling with. When I create a trip, I want to have a button setup so that when I click "Save," it saves the trip, then pops up a message box that asks if I want to add personnel to the trip. A form (I haven't created yet) would then pop up with names and a check box next to their name to add them.

Where I'm having the issue for this, is that I don't know if this checkbox needs to be added to the Query (I run all my forms off queries), or if it needs added to the form itself.

I'm also a little fuzzy on the code I would need to use for the button (I don't want to run it through a macro).

 

[spudracer]

Just to be clear, I guess I should have specified, but this is not an actual Access Project, but rather a regular database. I do not have access to MySQL and simply want this to function as a regular database.

 

[jdraw]

What do you have for tables and relationships? A data model?

 

[spudracer]

What do you have for tables and relationships? A data model?

Right now, I don't have any relationships setup.

I have two tables, one for Personnel, one for Trips.

For the Personnel table, I have MemberID as the Primary Key. For the Trips table, I have TripID as the Primary Key.

I've built databases where I can add multiple records to an individual, but I've never created one where I want to add multiple records to one item.

Until I can figure out how to go farther, I probably won't establish any relationships, even though I should probably establish a 1:M within the Personnel table (TripsOn > TripID).

 

[spudracer]

You know, as I take a step back from all of this and look at it, it's actually really easy, I was just looking at it wrong.

 

[spudracer]

Ok, I take that back.

I'm trying to use a list box form for the personnel, so I can select multiple trips they are on that way, plus the other way.

However, they're not saving. Clearly, I'm messing something up that's very simple, but I can't figure out what it is.

 

[jdraw]

Let's use some sample data

PersonnelTable (MemberId, Name)
1 John
2 Dave
3 Sally

TripTable (tripId, TripName)
1 NorwaySpecial
2 Carribean
3 Canada
4 CubaVacation
5 IcelandVolcano

In this scenario, you could have Trips in the TripTable that nobody has taken.
I think you need another table - a junction table.
Call it TripTaken (or whatever you want). It has 2 fields MemberId and TripId.
The values of the Person and the Trip are recorded in this new table.

So John goes to Canada and IcelandVolcano.
Dave goes on Carribean and NorwaySpecial and IcelandVolcano

TripTaken (MemberId,TripId)

1 3
1 5
2 2
2 1
2 5


Just my 2 cents.

 

[spudracer]

Thanks, jdraw! That helped immensely.