Multiple records, one form

[andwhy81]

I'm having trouble understanding sub forms and mutiple records,

i have tblevents with

"Pilotname", "Code", "Date"

i have another table that has every possable Code
i need to be able to select and add multiple codes per pilot per date

right now it works for one record at a time, i need to be able to drop down a combo box with pilot name, then select UP TO 4 codes,
I can have 4 combo boxes.

any insight is greatly appreciated.


added a cleaner version with 2007, 2000 versions



update @ 13:45 cleaner,

[weekend00]

In a form, usually add one record at a time.

If you want to add up to 4 record at a time, you need to click a button with some codes to check the 4 combo box and add records.

[Rawb]

Why can you only select up to 4?

As to being able to select multiple items, that part's fairly easy. It's getting that data into the database that gets a little tricky. Check out the Multi-select List Boxes problem I posted recently and see if that helps.

[andwhy81]

4 is the most that is requiered for this, if i learn to add those i can add more later if required.

Why can you only select up to 4?

As to being able to select multiple items, that part's fairly easy. It's getting that data into the database that gets a little tricky. Check out the Multi-select List Boxes problem I posted recently and see if that helps.

[andwhy81]

i guess the issue i have is when i selct one code in the "1st" box then its added to the other 3, i need to have up to 4 seperate or only 1.

In a form, usually add one record at a time.

If you want to add up to 4 record at a time, you need to click a button with some codes to check the 4 combo box and add records.

[weekend00]

leave "Control Source" blank for all combo box. if you the control source are the same, they will be updated at the same time when any one is updated.

[andwhy81]

What? then it wont pull the list of codes from my other static table

leave "Control Source" blank for all combo box. if you the control source are the same, they will be updated at the same time when any one is updated.

[Rawb]

You can leave the information in Row Source, Default Value, etc. Just make sure that Control Source is left blank.

Control Source only controls where a Form Object's data is saved to in the db. The other fields control what data is displayed.

[andwhy81]

Ok, im going to be stupid here for a sec,
then,how is what is choosen in the combo box get sent to my events table?

You can leave the information in Row Source, Default Value, etc. Just make sure that Control Source is left blank.

Control Source only controls where a Form Object's data is saved to in the db. The other fields control what data is displayed.

[andwhy81]

Added my DB to the top post, it isnt pretty yet, just look at the tables and EventLog form

[Rawb]

It will most likely need to be some sort of VB Function that determines whether or not a Record exists, and whether or not it needs to create or delete one.

All this depends on the design of the Tables themselves and the layout of the Form though. It could be that I'm making things more complex than they need to be (or going in the wrong direction altogether).

[Rawb]

Unfortunately, I can't open Access 2007 dbs. Can you save it as a 2000 MDB?

[andwhy81]

here you go

Unfortunately, I can't open Access 2007 dbs. Can you save it as a 2000 MDB?

[Rawb]

OK, first I'll respond directly to the question. After that though, I have some suggestions (including one very strong one) that I'd like to make.

With your current setup, it's actually going to be fairly simple to do what you want. As long as you don't necessarily want the ability to delete Events from the Log (or at least want that capability on a different Form), your solution is a 10-liner piece of VB coding.

All you need to do is point your Save button to a VB function that writes the date, pilot name, and TR Code to the Table. If you're not familiar with VB programming, I can certainly help you with that.

Now for the suggestions:

• Major
  
  1. Don't use any non alpha-numeric characters in Table names or Fields. This means that you shouldn't use spaces, punctuation, or special characters (including the ampersand (&)). Programming around Tables with these special characters in them is HARD and can lead to many different problems. I strongly recommend changing your TblT&RS Table's name to get rid of the "&".
  
  
• Minor
  
  • Use the TblPilots Table in your Form. Currently you're querying a list of all the names that have already been used in the Event Log. That means that you can't ever add new Pilots to it since the only ones that will show up in your list, have to already have events. Be sure to reference them by their IDs to save space and allow for things like name changes due to marriage, divorce, or legal name change.
  • You need to correct the Row Source of the Codes Combo Box. It's pointing to a Table that doesn't exist.
  
  

Other than those three items, your Form actually looks really really nice. I really like how Access 2007 updated the appearance of the Form elements. It results in a much cleaner, less cluttered look than dbs made in previous versions.

[andwhy81]

Ok, thanks for that, I cleaned it up further...

The part about the pilots table is that I need them to be able to add new pilots as needed, this will be given to some planners and they will add the pilots as needed so I think I need it to be able to add new pilot names to the tblpilots then be able to select previous entries.

I absolutely will need your help with the coding

also. I won’t need to delete events from the form but, they want to do a "what if" by entering info and seeing how its forecasted then clearing the "what if" data, I’m not sure how to approach that part of this. I was thinking of somehow have a query that queries the "live" data AND the fictional to create the pivot table i have already, is that a good approach?

also i cant figure out where that form is referenced where i get the error on open

OK, first I'll respond directly to the question. After that though, I have some suggestions (including one very strong one) that I'd like to make.

With your current setup, it's actually going to be fairly simple to do what you want. As long as you don't necessarily want the ability to delete Events from the Log (or at least want that capability on a different Form), your solution is a 10-liner piece of VB coding.

All you need to do is point your Save button to a VB function that writes the date, pilot name, and TR Code to the Table. If you're not familiar with VB programming, I can certainly help you with that.

Now for the suggestions:

• Major
  1. Don't use any non alpha-numeric characters in Table names or Fields. This means that you shouldn't use spaces, punctuation, or special characters (including the ampersand (&)). Programming around Tables with these special characters in them is HARD and can lead to many different problems. I strongly recommend changing your TblT&RS Table's name to get rid of the "&".
• Minor
  
  • Use the TblPilots Table in your Form. Currently you're querying a list of all the names that have already been used in the Event Log. That means that you can't ever add new Pilots to it since the only ones that will show up in your list, have to already have events. Be sure to reference them by their IDs to save space and allow for things like name changes due to marriage, divorce, or legal name change.
  • You need to correct the Row Source of the Codes Combo Box. It's pointing to a Table that doesn't exist.

Other than those three items, your Form actually looks really really nice. I really like how Access 2007 updated the appearance of the Form elements. It results in a much cleaner, less cluttered look than dbs made in previous versions.