Autofill a subform on main form new record

[RhysAZ]

Hi-

I would like to know how to autofill subforms rows with data when I open my main form, which will start with a new record.

Example:
Main form name is "Food". Subform name is "Fruit". When I open the "Food form, I want the subform field (named Fruit Name") to auto fill with various fruit names. The main form and subform are linked via a RecordID number.

sfrmFruit
row Fruit Name
1 Apple
2 Banana
3 Cherry

I need for the fruit names to autofill everytime I open a new record on main form Food.

Can you anyone help me please or point me where to find this answer? Thanks in advance!

 

[dcb]

Do you have the Fruit listed in another table and you are using ID's?

If so

INSERT INTO SUBFORMTABLE ( FruitID, MainID )
SELECT Fruit.FruitID, [Forms]![MainForm]![ID] AS MainID
FROM Fruit;

Cheers

 

[RhysAZ]

Thanks for the quick reply dcb.

Sorry, I didn't include all the info. Basically I asked this question because of a db I'm designing and I wanted to use an example similar to what I want mine to do. Please bear with me as I really need your help with this.

My db is for Daily Task tracking for 25+ employees, currently using individual spreadsheets to track how much time it took them (in minutes) to do a particular task that day.

There are 80 distinct tasks that the employees could possibly do in a day.
We have those distinct tasks broken down into specialized groups (7), such as Time Off tasks, Miscellaneous tasks, Special Assignments tasks, etc.

Currently, I have a main table (RecordID is Pkey) and 7 other tables, one for each task group. The relationship between the main table and the other 7 tables are a one to many, with RecordID as the link.

I have built subforms for each of those 7 task group tables. I have placed a tab control on the main form and placed the 7 subforms on there own tab. One of the field names in all 7 subforms is Tasks. I have made that field a combo box on all subforms. Each subform Tasks combobox is fed its task selections from queries for the particular task group. And it all works fine.

For the sake of being more user friendly, instead of having the employees select a different task in the combo box for each subform record, I want to preload those tasks (that are applicable to that task group) into the task groups subform automatically when the open a new main form record.

I apologize for the extremely long response. Any suggestions on how I can achieve this or will your explanation above do it for me as well?

 

[yanie]

emm..i created and attach simple database..i hope this will help u...this is from what i understand for your first question ...pls look at the frm..

 

[dcb]

Currently, I have a main table (RecordID is Pkey) and 7 other tables, one for each task group. The relationship between the main table and the other 7 tables are a one to many, with RecordID as the link.

I have built subforms for each of those 7 task group tables. I have placed a tab control on the main form and placed the 7 subforms on there own tab. One of the field names in all 7 subforms is Tasks. I have made that field a combo box on all subforms. Each subform Tasks combobox is fed its task selections from queries for the particular task group. And it all works fine.

For the sake of being more user friendly, instead of having the employees select a different task in the combo box for each subform record, I want to preload those tasks (that are applicable to that task group) into the task groups subform automatically when the open a new main form record.

Wow ok that changes the dynamics a little:

Not sure I agree with your methodology re User -> 7 Tables -> Sub
Personally I think this is going to really hurt you further down the design road. Are you fixed on this Idea or are you willing to redesign?

As for your actual question (no matter what the design) you then shouldnt be doing this at table level - If you had to add all of this every day you will bloat your data with alot of repeats and leave the data unused

The natural way is to use comboboxes but there is a way to do what you are asking - however to explain it here is going to kill me !

If you want to change your db as mentioned lets deal with that first
Upload your db (assume you dont have real data in it yet) and let me do an example for you....

 

[RhysAZ]

Yanie- This is similar to what I'm looking for. However, in this example db, you cannot enter new records that have prefilled rows in the subform. Thanks for you help though.

dcb- No, I am definitely not married to my design of this db. I've looked across the web for something similar to what I want to do but have never found anything. Hence, why I've come up with my own design. If you you can come up with something more efficient (and user friendly) then my design, I will be more then happy to use it.

I am aware in my current design, that if I do have prefilled subform records, it could be a waste of db space. My aim though is to have a fresh db tables for every year, so space wouldn't be a problem if the subform records were prefilled.

I need to remove some items and cleanup the db before I can upload it.Would you prefer for me to send it to you directly or upload it here? Thanks dcb.
Btw- There are 8 tables for the task groups and 54 tasks in total. I was incorrect before.

 

[dcb]

Yip upload if you can - here is best - there may be others that have better ideas than myself and they could also take a look...

I hate the concept of writing stuff to a table that doesnt need to be there - bad bad bad

Cheers

 

[RhysAZ]

Here you go. I have "not" some of the macros so it will open correctly. If you have questions please let me know. Thanks dcb!

Update- I cant see my uploaded db. Is there a way for me to try to upload it agai or do I need to post another reply?

 

[RhysAZ]

Attempting to upload it again.

 

[dcb]

I am looking at it
Just making sure I have my mind around it

 

[Scooterbug]

If I may jump in....

Your database is not Normalized. You have tables that have identical structure..the only difference is the table name. Clear sign of non-normalized data.

From what I can gather, you are tracking tasks that need to be completed by your employees. There can be up to 80 tasks in 7 catagories. In your database, each catagory has it's own table. Correct?

Now, what happens if management, down the road, decides to restructure the catagories? Your table structure(s) will now need to be redone. Big headache.

What you should do:

tblTaskDescription
TaskID
CatagoryID
TaskDescrip

tblCatagories
CatagoryID
CatagoryDescrip

tblEmployee
EmployeeID
Other Employee Data


Now, you will need a table to store the following data:
EmployeeID
TaskID
TaskDate

I think you should concentrate on fixing up the data structure first, as it will make things a lot easier to deal with down the road.

Also, you do not need a separate table for the Months. Month names can be formatted from a date.

 

[RhysAZ]

Scooterbug- Yes, you are correct. It's for tracking daily tasks the reps do. There are 8 tables for the task groups and 54 tasks in total.

Question1: Should CategoryID,TaskID and EmployeeID all be primary keys (within their own tables) and use autonumbers?
Question2: How should the relationships for the tables look like?

As for the table with EmployeeID,TaskID,TaskDate...I think that is the tblMain, right?

About the Months table, I forgot to delete that. That is not being used.

dcb- I haven't forgotten you. Your advice would be appreciated as well.

 

[Scooterbug]

Scooterbug- Yes, you are correct. It's for tracking daily tasks the reps do. There are 8 tables for the task groups and 54 tasks in total.

Question1: Should CategoryID,TaskID and EmployeeID all be primary keys (within their own tables) and use autonumbers? -Yes
Question2: How should the relationships for the tables look like?
-The main table needs to store the Primary key as the Foreign key for Task and Employee. Catagory is already stored with the Task.

As for the table with EmployeeID,TaskID,TaskDate...I think that is the tblMain, right?

About the Months table, I forgot to delete that. That is not being used.

dcb- I haven't forgotten you. Your advice would be appreciated as well.

I'm usually busy in the morning...but give me a bit and I'll do up an example for ya...I know I learn best by seeing concepts.