Working with multiple tables simultaneously...

[mesavage]

I have a subform. In this subform, I would like for some information to show from one table. I also want some fields from another table to be updateable.

I had the idea to put all the information into a single recordset, but I couldn't figure out how to combine it all.

It seems to me the only way to do it is to create a temporary table to store everything. Is there a way to combine all this together without creating a temporary table? The biggest problem is I am in a multi-user environment. I'm not sure how to uniquely create a temporoary table...

Thanks for any help.

 

[bitech]

I think I have an idea of what you're trying to do. But to grasp a better idea, let me ask you:

1.) Do the two tables have a Relationship. (Ex. do they relate by ID number or some other unique field)

If the two tables share the same unique key, this task is rather simple. The recordsource of your subform will need to be a SQL statement. Take my example:

SELECT Company.Company, Company.Address1, Company.Address2, Company.City, Company.State, Company.Zip, Company.Phone, Company.Phone2, Company.Fax, Company.Email, Company.Website, Minority.*
FROM Company INNER JOIN Minority ON Company.ID = Minority.ID;

In this Statement, my form, MINORITY has information from the Company table and also from the Minority Table. The tables are linked by ID. Therefore when I edit information, it edits both tables. If this helps, let me know, or maybe we can try something else

 

[mesavage]

Thanks! I'm going to give it a try. If I have problems, I'll be sure to let you know.

To answer your question, yes they do have a relationship. As my team leader always says, "Sometimes you just need a second set of eyes!" This solutions seems to obvious now. )

Miriam

 

[mesavage]

OK, I'm having a bit of trouble.

I'm using MS Access as a front end for my SQL Server. I'm using stored procedures for all my queries. I have a stored procedure that takes in a list of IDs and some other information, puts the records into a temporary table, and then returns a recordset with those records. (The temporary table is needed so we end up with a single recordset with all the information.)

Anyway, for some reason my subform that has all this information, isn't updateable. It shows all the correct info, I just can't change it. I attempted this with a really simple table (single table) and stored procedure, to see if it was the fact I was using a stored procedure for my source. Well, that worked. I have no idea what is different, other than the fact that the test stored procedure only dealt with a single table.

Ideas? Thanks.

Miriam

 

[bitech]

Is the Allow Edits Property marked Yes on the Form.