By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Update query

P: 6
I have two tables many to many relationship between them, and i've created third one that to have both IDs.
my problem is that I want to update both of them in the same time.
I do not know how to build and access my updating query from my single form.
I have done my select query and it worked fine, but my problem is that i do not know how to update and add new records.??

Any help pls??
Sep 4 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,418
U can access an updatin or appending query by using
a macro or code
docmd.runsql runs an action query specified as SQL

The following example updates the Employees table, changing each sales manager's title to Regional Sales Manager:

Public Sub DoSQL()

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"


End Sub

OR the other way is docmd.openquery with the needed query name:

OpenQuery Action
You can use the OpenQuery action to open a select or crosstab query in Datasheet view, Design view, or Print Preview. This action runs an action query. You can also select a data entry mode for the query.

Note This action is only available in the Microsoft Access database environment (.mdb). See the OpenView, OpenStoredProcedure, or OpenFunction actions if you are using the Access project environment (.adp).

The OpenQuery action has the following arguments.

Action argument Description
Query Name The name of the query to open. The Query Name box in the Action Arguments section of the Macro window shows all queries in the current database. This is a required argument.
If you run a macro containing the OpenQuery action in a library database, Access looks for the query with this name first in the library database, then in the current database.

View The view in which the query will open. Click Datasheet, Design, Print Preview, PivotTable, or PivotChart in the View box. The default is Datasheet.
Data Mode The data entry mode for the query. This applies only to queries opened in Datasheet view. Click Add (the user can add new records but can't edit existing records), Edit (the user can edit existing records and add new records), or Read Only (the user can only view records). The default is Edit.

If you use Datasheet for the View argument, Access displays the result set if the query is a select, crosstab, union, or pass-through query whose ReturnsRecords property is set to Yes; and it runs the query if it is an action, data-definition, or pass-through query whose ReturnsRecords property is set to No.

The OpenQuery action is similar to clicking the Open button or Design button after clicking Queries under Objects in the Database window, and then selecting a query. With this action you can select additional options.


You can select a query in the Database window and drag it to a macro action row. This automatically creates an OpenQuery action that opens the query in Datasheet view.

Switching to Design view while the query is open removes the Data Mode argument setting for the query. This setting isn't in effect even if the user returns to Datasheet view.

If you don't want to display the system messages that normally appear when an action query is run (indicating it's an action query and showing how many records will be affected), you can use the SetWarnings action to suppress the display of these messages.

To run the OpenQuery action in Microsoft Visual Basic, use the OpenQuery method of the DoCmd object.

You also can do it by using macros! The commands are the same!
Sep 4 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.