Question on Updating tables

[radicalrik]

If I have a form which is based on a Query and is using 3 different tables, how would I go about updating specific records from the form into a specific table?

 

[monkeytunes]

Your question is a bit unclear - I can't tell if you mean 1. "how do I change records in a table through a form", or if you mean 2. "how do I put these changed records from 3 tables into a new table?"

If you meant #1, then you should be able to change records in multiple tables on a form (through a query), depending on how your table relationships are set up. You might need to fiddle a bit in the query changing and making new table relationships (called "ad hoc" relationships, since the relationship will only be present for this particular query. If you want relationships to stay across the database, then open your Relationships window by clicking the button or going to Tools->Relationships from the main Database Window.)

If you meant #2, then you can use an Append Query, set to run via Macro or DB code in the After Update event of your form. There's been a fair amount of recent discussion about it - search these forums for Append Queries for more info.

 

[radicalrik]

Ok, sorry about that. Let me see if I can be a little more clearer.
I have a Form based on a query. This query prompts me for three different responses. 1) System Name... 2) Primary Oncall Person Name..... and 3) Secondary Oncall Person Name.

Now, the "System Name" comes from a table called Oncall, the "Primary oncall person" comes from another table called Employee and the Secondary comes from another table called Employee1. 3 Tables within this one form.

When I have answered all the prompts all the fields are loaded. What I want to do is to be able to save this information into the table called "Oncall".

I have created a command button called save using button wizard. Once I hit save and look into the table, the data isn't updated.

 

[The_Doc_Man]

This is a conceptual problem.

When you create a single joined record and update it, you implicitly update all members of the join. To Access, though it knows the tables are separate, you have merged them (temporarily) by using a joining query. So Access, being very obliging, is treating the three tables together for you. Just like you told it to.

If you don't want to update all of the tables, they can't be members of the join that you update. So either "fake" the lookup or write some VBA code to selectively update recordsets underneath the associated form.

 

[radicalrik]

How would I go about "Faking" the look up? I cannot code VB and I'm a very basic Access user to this point.