problem with entering data in subform creating multiple duplicate form record

[sil]

Hello,
I have built a database to keep track of volunteers (contact info, hours, recognition items, etc). I am having issues with my form & one of my subforms (which are on tabs). In particular, it's my volunteer hour entry subform. Every time I enter hours for a volunteer on the subform, it creates a duplicate form record and I'm not sure why. I have the same set-up for entering in recognition items each volunteer receives, but when I fill in multiple items on the subform, this doesn't happen. Any ideas where I am going wrong & why this is happening?

Thanks for any input!

[June7]

Need to review data structure, queries, form design. If you want to provide db for analysis, follow instructions at bottom of my post.

[sil]

Need to review data structure, queries, form design. If you want to provide db for analysis, follow instructions at bottom of my post.

Here it is. Thank you so much!

[June7]

Should not include the Hours table in the main form RecordSource. Why is there two subforms on Hours tab with essentially the same RecordSource?

Also, cannot create new records into two tables through one form. Is that what you are trying to do with Tbl_VolunteerInformation and Tbl_VolunteerPosition? Can each volunteer have more than one position? Use a subform for positions and don't put Tbl_VolunteerPosition in the main form RecordSource.

Suggest you make form more compact by putting Tbl_VolunteerInformation fields on a tab captioned 'General'.

Building a unique ID with name parts is usually a bad idea. John Smith and Joan Smith will both be SMIJO.

[sil]

Should not include the Hours table in the main form RecordSource. Why is there two subforms on Hours tab with essentially the same RecordSource?

Also, cannot create new records into two tables through one form. Is that what you are trying to do with Tbl_VolunteerInformation and Tbl_VolunteerPosition? Can each volunteer have more than one position? Use a subform for positions and don't put Tbl_VolunteerPosition in the main form RecordSource.

Suggest you make form more compact by putting Tbl_VolunteerInformation fields on a tab captioned 'General'.

Building a unique ID with name parts is usually a bad idea. John Smith and Joan Smith will both be SMIJO.

Should I just delete the part that says "INNER JOIN Tbl_VolunteerHours ON Tbl_VolunteerInformation.VolunteerID = Tbl_VolunteerHours.Volunteer)" from the main form RecordSource? There aren't supposed to be 2 subforms on the Hours tab - I was playing around with different RecordSources & forgot to delete one of them before I posted - sorry about that.

For the Volunteer Position, yes each volunteer can have more than one - I made it a combo box where I could put a check mark by more than one position.

Would it be better if I had everything on tabs except maybe ID, First Name, Last Name, & Group Name?

[June7]

Don't just delete part of query statement in the SQL view, use the query designer to remove table from the query.

Does 'a combo box where I could put a check mark by more than one position' mean you are using a multi-value field? I NEVER use multi-value fields.

Yes, that's what I meant by 'compacting' the form.

[sil]

Sorry, I am not certain how to remove the table from the query...Is it the "PreviousFYTotalHours" I want to delete from "Qry_VolunteerInformation_Frm_Sort?" Or do I remove from another query?

Yes, I was using a multi-value field...is there a better way to do it?

Thank you so much for your help on this!

[June7]

Frm_VolunteerInformation in Design View, click the ellipses (...) next to the RecordSource property. This will open the query designer. Modify the query as described.


Instead of a multi-value field, would need a related child table. If you want to stay with multi-value field, better fully understand limitations and how to work around them. Review: http://office.microsoft.com/en-us/ac...001233722.aspx

[sil]

I think I got it!
June7, THANK YOU SO MUCH!!!

I'll also check out that info you told me to review. Thanks again!!

[sil]

Sorry, me again...now every time I enter a new volunteer record (on the form) it saves the info on the volunteer information table, but the form for that particular volunteer disappears...ugh!

[June7]

I think the form is cycling to next record which happens to be another new blank record.

Can prevent that by setting the form Cycle property to CurrentRecord.

You might want to change the TabOrder of controls to start from the VolunteerID and advance down the form.

[sil]

The form was cycling to "all records." I changed it to current record, but it still happens...
My TabOrder does start from the VolunteerID, but in the Detail section. The Form Header section is before this and starts with the return to welcome page button. Should that be changed?

[June7]

Think I need the latest version of your db.

[sil]

Here it is.

Thank you!

[June7]

The record disappears because the tables are joined in RecordSource with INNER JOIN. Cannot enter data into both tblVolunteerInformation and tblVolunteerPosition by the same form. tblVolunteerPosition should be a subform and remove it from the main form RecordSource.

Still recommend putting the controls for basic volunteer info on another page of the tab control.