We help IT Professionals succeed at work.

Access Forms and tables

actsoft used Ask the Experts™
I have linked tables from a source that cannot be changed.
I have created a query to pull data from these tables.
Set parameters to select a particular item.
So far so good.
Now I need to create a form using the select data from the query.
Change the data in one of the fields and print x number of labels.
Each time i try I get an error because the data cannot be changed.
Would it be possble for someone to explain in a nut shell how to do what I want done?
Or, point me in a direction where I can get it.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can create temporary table, where you will store results of your query (change type of query to Add query) and use this table in your form.
You should also clear this temp table before query will be executed.

What to do depends on your objective. As you can't change the source tables, your only option for changes is to store the data in a temp table and have a form for updates bound to that table. This will however create the problem of keeping the data in sync with the original tables I guess....
Best what you can do is use unbound controls. When selecting an item loop through the data and add the data to the controls using vba. I only use unbound controls, it gives you more flexibility. You can use to store data in a temp table. But this will cause your data to be out of sync. Like ggfab allready commented.

But for my knowledge, you change the data in the bound control? Perhaps changing the form in only readonly. Because your form is editable. Because a form is like a mapping of a table the data is directly manipulated if controls are bound.

Change the way you use your controls. Bound or not  bound. Read only your form so data is not editable.

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
I would consider the Temp table option for this. There's really no need to go with the overhead of unbound controls (and I'm a BIG advocate of unbound controls when necessary). But for printing labels, just use a Make Table query to generate a local table from your linked table, then base your form on that:

con.Execute "DROP TABLE YourTempTable"
con.Execute "SELECT * INTO YourTempTable FROM YourLinkedTable"

With those two statements you've created your TempTable, and can base your form on it. Obviously, you'd wnat to include error checking to insure that the table exists before issuing those statements.

Are you using your database in a multi user environment? Or do you use your database application for single use only?


I've requested that this question be deleted for the following reason:

issue resolved
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

What does "issue resolved" mean, and why have you not responded to any of the Expert comments?


Thank you all for your assistance. Temp table seem to be the way to go. i just need to learn more about Access to do that.