CODEWITHSUNDEEP
ms-access
dani jinji
dani jinji

Reputation: 471

access - one form, update one table and insert to another

I am new to the syntax of vba in access, and I have a question about the queries.

I have a form, and in the form I have for example a field called "A" which I want to insert into table table_a and a two other fields, one called "B_ID" which contains ID and another field called "B" which I want to update in table_b (where the ID is equal to field "B_ID".

any ideas how to do it in VBA for access?

in php and MySQL I would simply have two queries like so: INSERT INTO table_b (field_A) VALUES ($_POST['A']) UPDATE table_b WHERE b_id = $_POST['B_ID'] SET B = $_POST['B'] * or something like this...

thank you!

Upvotes: 1

Views: 4679

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

You don't need any VBA code if you create the form based on updateable query. Tables should be linked using column(s) with unique key in master table and query should not contain aggregate functions. There are some other limitations, but in most cases it will work for simple queries

If tables are not linked or you don't want to use then on form, use code like this:

Dim db As Database

Set db = CurrentDb
db.Execute "INSERT INTO table_b (field_A) SELECT " & Me.A, dbFailOnError
db.Execute "UPDATE table_b SET B=" & Me.b & " WHERE b_id = " & Me.B_ID, dbFailOnError

Here we suppose that you have on form fields A, B and B_ID with numeric values and corresponding columns in tables are numeric. If fields are text, add single quotes in SQL strings.

Upvotes: 1

Related Questions