Append queries - current record(s) from form and subform

[karatelung]

I have a form based on the table [tblFoster] with a subform based on [tblIndFoster].

The unique ID for [tblFoster] is tblFoster.Identification. The unique ID for [tblIndFoster] is tblIndFoster.FosterID.

I need to append the current record to [tblFoster] and the associated records of the subform to [tblIndFoster].

I'm guessing I could achieve this by writing code that would save the record then run two append queries, but it's just a guess. Even if my assumption is correct, I still don't know how to create the queries that would append only the current record.

Any suggestions on the best way to achieve this? Please help.

TIA

 

[Jack Cowley]

I am not sure that I understand what you are trying to do. If you have a form based on tblFoster then as you enter data into that form the data is saved to the table. The same with the subform. As soon as you move to a new record Access saves the data to the tables so I do not understand the need for an update query....

 

[karatelung]

ok. I'll explain a little better.

i already have a form set up with lots of information about foster parents. a lot of the code is based on whether they are inactive or active (0 or -1), and all the subsequent tables are based on the [ID] field.

we now have a need for a section that is only for potential recruits. that's why I just want to make two additional tables (one for the form and one for the subform) with minimal info concerning potential recruits.

if the recruit becomes an active foster parent, the user will enter an [ID] and click a button that saves the record and runs the append query(ies).

i know there are other options using the same tables, but i've decided that appending the records from a separate recruitment table will be the easiest way with my existing structure.

so if someone can help me with those append queries, i'd be most appreciative.

thanks

 

[Rich]

Since they can already be flagged as inactive why do something that's unneccesary?

 

[karatelung]

even if a foster parent is flagged inactive, they are still a foster parent. there will be two categories of recruits that have nothing to do with active/inactive foster parents.

with the lookup/navigation system already set up based on inactive/active, i think it will be a lot easier for me to create a separate recruits table with minimal information. the in depth info is only needed once the recruit becomes accepted as a foster parent.

i know it's not necessary to do it this way, but i think it will a lot easier for me.

thanks

 

[David R]

Try changing your Active/Inactive field to an option group, with four options. I believe the Option Group wizard allows you to identify the appropriate values, so you won't have to change ALL your code around, you can leave Active = -1 and Inactive = 0. Make ActiveRecruit = 1 and InactiveRecruit = 2, if that helps.

Be careful when changing the data type of your field over from Yes/No to Numeric though. Might want to make an entirely new field instead and run an Update Query, then rename the new field back to the old name.

You can link multiple subtables to the same main table so I don't think you're gaining what you think you are by creating new tables, to be honest.

 

[karatelung]

thanks. i guess i'll go that route since i really don't need the extra tables.

i'm still going to set it up the way i originally intended strictly for knowledge and experience. i'm sure i'll have to use pieces of it somehow in the future.

i figured everything out except for one last part that i'll post in the VBA forum.