Need a form button to append to separate record

[logan3975]

I'm way out of my knowledge area while doing this project so bare with me if I don't explain myself very well.

Database story: Facility does mask testing. Volunteers come in to wear the mask during testing. The volunteers would come in and fill out a form each time. They want to do all this in a database which I've begun setting up. They want to have a drop down menu with the volunteers names (done) and when they select a name, the form populates with the most recent of their records that are held in 5-6 tables. The tables were broken up into different history tables (mask history, medical history, family history, etc). When the volunteer returns, they will pull up his info in this database and he/she can make changes on the form. However, the problem I'm facing is, the client doesn't want the volunteer to copy over his current record, he wants whatever changes take place, to be a separate/new record. I was trying an Append Query but can't figure out how to append one record to the same table. I either can append all records or none.

OR

Do I set this up differently? Maybe have the history tables keep the older copies while a different table keeps the most recent? But then how do I even begin setting that up?Volunteers1.zip



Attached is the database.

[June7]

Just need to move to New Record row on form. Use the intrinsic form navigation tools to do that or build code behind a button.

I hope you are using a form/subform arrangement. Each of the history tables should be a separate subform. Use a Tab control and place each form on a page of the Tab control.

[logan3975]

I thought a New Record would be a blank record. I want the volunteer to uncheck/check, change text on that particular history tab and then click a button that will put those changes in a new record. Am I explaining it okay?

Just curious, why should the history tables be using a subform on the form? I have the whole form using one query that pulls from each of the history tables and it seems to be working - for now. So what are the benefits to using a subform instead?

[Dal Jeanis]

If you are "merging" various tables on a single form, then trying to create new records, you have a world of finnicky stuff to code your way through. You'll have to create each of the relevant records in their own tables, effectively at the same time, with the keys of the 5-6 different tables not available until after the records have been created... how easy does that sound?

The form/subform arrangement allows Access to do all that work for you, the way it's designed.

Now, you CAN have a button that will execute VBA code to create all those records and populate them with default information. That's not hard. But the other method is preferred, for small numbers of related tables.

5-6 tables at once? I'd probably set up a button that pops up an entry form, and walks the user through the steps to create all the related entries, then refreshes the main form.

[logan3975]

I don't think I'm merging tables...I'm not even sure what that is exactly. And that doesn't sound fun at all.

Doesn't the subform display is datasheet though? I'd want it to look and feel like the rest of the form.

It may not update 5-6 tables at once. So the volunteer is reviewing his information. Let's say that his family history changed, so he clicks on the family history tab and makes whatever changes there. My though is to have a button on that tab for when he's done, he can click and those changes will go to a separate record within that history table. Is this something that can work in Access or is a different setup needed to accomplish this?

[June7]

If you open form to existing record then uncheck/check and change text that will modify data in that record. Can't do these edits in an existing record as an effort to create a new record. Can have new record populate with default values.

You stated "the form populates with the most recent of their records" which implies there were will be many records in each history table for each volunteer. That is one-to-many relationship. Each history table MUST be on separate form. Each of these subforms can be on a main form bound to Volunteers so display of associated records is synchronized and still facilitate data entry to all tables in one interface.

[Dal Jeanis]

If your form is set up on tabs, then you already have subforms. In this case, they're just using the tabs control rather than the subform control, to hold and display the related records.

NOTE to forum - we need a strikethrough font, so I can strike out erroneous portions of my statements without just deleting them and pretending I didn't make them....

A subform can display in form view or datasheet view, however you design it.

[June7]

Dal, I don't quite follow what you mean by your first paragraph. Still need a subform container control on the tab pages.

[logan3975]

So I created a test tab and inserted a subform into that tab. Got it setup and it seems to work great. I also just tried to add a button. I tried the New Record button as well as Duplicate. Both ways end up creating a new record for every table. Is there a way to only duplicate record or add new record to one table?

[June7]

The subform RecordSource should be 1 table, not all 4 tables.

I don't really understand why this would create a new record in all tables.

If you want to provide db for analysis, follow instructions at bottom of my post.

[logan3975]

Ah, I have the subform pointing to a query that sorts the data. If I put the subform in using the table, it doesn't show me the most recent. Can I sort in the table or can I only do that in a query?

So the tab labeled Page134 is using qry_volunteer_info so I can sort the Last Updated field to show the most recent record. The tab Page142 uses the table. I went into the table to sort but it's not showing it in the form. For example, if you select John Doe, his Last Updated date should be 11/12/2013 like is shows in the Exercise tab and the Page134 tab. The Page142 tab shows 11/7/2013.

Suggestions?

Volunteers1.zip

[June7]

Forms can reference table as RecordSource - many prefer a query, I am fine with table. Depends if you want filtering and sorting in the RecordSource or just use form properties to dynamically filter and sort.

However, your query is not appropriate because it includes all the tables. Change form RecordSource to reference one table or a query object that pulls from one table or an SQL statement.

SELECT * FROM [History-Exercise] ORDER BY [Volunteer Info ID];

Same goes for the Volunteers form.

Just keep in mind - one table to one form, even if pulling through a query.


Advise no spaces or special characters/punctuation (underscore is exception) in names, nor reserved words as name. Better would be ExerciseType or Exercise_Type. The hyphen in table names also causes issue. These features require use of []. There are a few cases where [] will be required regardless, but use of these features will require them always and makes it harder for Access to automatically apply the [] format and you have to think to type them. A nuisance code.