Need some Help on Subform population

[rck3]

Ok so it has been a long time since I have dont much in access. I have been able to get the database setup but have run into 1 glitch.

What I have (well the basics as there are some other lookup tables for things) is a single table with a form and subform. I have it set up so when you open the form you can select and Emp ID which then autopopulates the name/company into fields onto the main form. There is then a subform which allows you to enter a date/project/hours in datasheet view. Basically let an employee select their ID and then enter in which projects they worked on that day as there are multiple.

My issue is I have it setup so the main form works fine and then the subform works but creates seperate records without the main data. So if I select 1 employee ID then enter 2 times in the subform I end up with 1 record with employee data but the time data is blank and 2 records of time data with employee data blank.



What am I missing here? Thanks

[ssanfu]

I would have two tables: an Employees table and a Projects (or EmpProjects) table. The linking field would be the Employee_PK field.The main form record source would/could be a query on the Employees table and the subform record source would be a query from the Projects (or EmpProjects) table.

[rck3]

It is not my database creation completely so it would be a pain to redo their setup. Is there anyway around this? I have been able to tweek it so I can enter one record and have it populate correctly but then you have to exit the form and bring it up again. I inserted Me.Form.AllowAdditions = False
DoCmd.Requery "Time_Data subform1" which forces access to save to the same master record but I can not goto the next record. It saves it but the code to goto a new record isnt working now. I could make due if I can have the form come up, let them enter one record of time data then click a button which would save and bring the form up again with the prior employee ID filled in (not sure how/if) then have another button that will save and load a clear form.

Any help?

[ssanfu]

I've had a little experience with databases like that. (some of my own design )

With enough code you could get it to work the way you want, but a bad design is a bad design. If you are stuck with the design.........

if I can have the form come up, let them enter one record of time data then click a button which would save

Instead of DoCmd.Requery "Time_Data subform1", you might try using "Me.dirty = False". You will have to get the right wording to reference and save the subform.

If you care to post the code you have now.....??

[rck3]

Ok so I have been playing around with this (its project specific so wont be resused). I think the easiest thing to do at this point is to ditch the subform they were using and just use a straight up form. Is it possible to have 2 buttons, one to clear the form and goto the next (this I already have working fine) and another button that will clear the form, goto the next, and insert the employee ID from the previous record? This would save them time from reselecting their ID.

[ssanfu]

It's a bound form, right? you don't need to clear it, just move to a new record. Moving to any other record will automatically save the record.

I would have an unbound combo box/list box in the header of the form to select the employee ID and have the form before update event enter the ID into the record. Less coding....

[rck3]

Thanks for talking me through. I can usually figure things out on my own with a little research. So I ended up taking your 1st advice and have it working. I redid the design and split it into 2 tables with a foreign key to link. My last issue now is related to what you mentioned above.

Everything works, I can select a empl and then in the subform enter as many items as I need. The reason I wanted a save&clear key is so that once another employee goes to enter I wanted the form to be blank (I know it was saved on entry but many users will not think its saved without that button)

[ssanfu]

When a new employee is selected, the subform should automatically clear.

It sounds like the subform is in datasheet view. Maybe you could have a SAVE button that selects an employee ID (like 0000) where the name is blank, then re-queries the main form. That will save and clear the subform.

[rck3]

Thanks, yes dataview is easier for the subform. I ended up created an close form button and added a switchboard so when the user is done they click that and return to the switchboard. Thanks for the help

[ssanfu]

Ready to mark it solved?

[rck3]

yup yup yup (Needed 10 char)