Option of table you wish to write to

[JuniorWoodchuck24]

I'm using forms to write information into tables. The question I have is what VBA code would you have to use to prompt the user on which table he wants to place the item in. For example you are running a grocery store and you want to save a certain item under the produce department, but you don't want to make multiple buttons to add to each individual table. Can you make one button and once it's clicked it asks which table it should save the information to?

 

[rainman89]

You could, but what if the person doesn't know the table names? Why not have a drop down with the table names in it and have them select that before they click the save button?

Another question is, are all your tables going to have the same structure? If not this could become an issue when they are filling out the form, but it can be done with some insert statements and the correct fields.

 

[ghudson]

You might want to consider putting all that data into one table and have a field that identifies the item type [produce, meat, frozen, etc] instead of having a table for each item type. This sounds like your data tables are not following the rules of normalization.

 

[rainman89]

You might want to consider putting all that data into one table and have a field that identifies the item type [produce, meat, frozen, etc] instead of having a table for each item type. This sounds like your data tables are not following the rules of normalization.

Was going to be my 2nd point after OP's response to first post. Thanks Ghudson!

 

[JuniorWoodchuck24]

All tables have same fields. Reason there are different tables to place on is due to following:

Overall Report: Pulls data from OverallTable
IndividualReport1: Pulls data from IndividualTable1

and so forth

All tables share same fields, and yes I know that when you write/save to a table from a form you have to call it out in the VBA code. Thus if the field isn't there you get an error. My issue is that my boss wants separate reports per individual group and one for overall. I was just trying to cut time by adding two buttons: cmdOverallAdd and cmdIndividualAdd. The cmdOverallAdd just simply adds the information to the OverallTable, while the cmdIndividualAdd will prompt the user to make a selection on which table he wants to place the item in. I don't want to make multiple buttons per individual because I have way too many. So a drop down box would be much simpler. I was mostly curious on what the code would be with the msg box.

In simple terms:
Click cmdIndividualAdd
Msg Box appears prompting user (can you put a pulldown in msg box?)
User makes selection
New Msg Box (Ok box) to confirm selection is correct
Adds to Table

Part I'm not familar with is Msg box with a pulldown

 

[boblarson]

You should not have separate tables for separate departments. You should have it as ghudson said (see here for more about normalization) and you have a department ID in the table which identifies where it belongs. You then use QUERIES to get the appropriate data for the appropriate reports. You can use queries in 99.9% of the places you use a table.

 

[highandwild]

It would be a good idea to heed the advice given to you on here.

The time spent normalising the data will pay off in the long run. I can assure you of that.

Once normalised, you will see how much easier it is to do almost everything.

By doing things in seperate tables everthing you do from now on will be that much more difficult, take longer and be harder to maintain later on.

 

[JuniorWoodchuck24]

So in short: I should just put all the info into one table and pull info into separate reports using VBA code?

I'm familiar with forms/tables and somewhat with reports. Just prefer to add fields to a report and have it auto fill. Guessing I'd have to have some kind of VBA code to pull off the department modifier and void all the other items that don't meet the criteria.

 

[rainman89]

So in short: I should just put all the info into one table and pull info into separate reports using VBA code?

I'm familiar with forms/tables and somewhat with reports. Just prefer to add fields to a report and have it auto fill. Guessing I'd have to have some kind of VBA code to pull off the department modifier and void all the other items that don't meet the criteria.

Well not just one. You should normalize it. You can have a department table along with your data table, but you can do all the info into one report. I don't see how that would be a problem.