beginner in vba - select query

[sab_db_12]

Hi,
may anyone help me?
there is two masks and two tables.

I want to click on a buttom in mask2 and run the following code:

Dim query1 As String
Dim query2 As String

Dim objDB As Database
Set objDB = CurrentDb

query1 = "SELECT mask1.field1, mask1.field2 FROM mask1" _
& "WHERE mask2.field3=mask1.field3";
query2 = "insert into mask2.field1,mask2.field2" _
& "values (' mask1.field1 ',' mask1.field2 ')"

objDB.Execute query1
objDB.Execute query2

Can anyone correct it?

Thank you so much

 

[vbaInet]

What do you want to do with the query? The Execute command does not work on SELECT statements so the execution of query1 will fail but query2 should pass.

By the way, if you're calling your fields mask1, mask2 etc... I would advise you work on your naming convention and in addition look into normalisation.

 

[sab_db_12]

thank you so much for your replay.
but I can't understand what to do.

maybe, it is better to describe you my problem.

I've mask2 where I want to show field1 and field2 I've inserted using mask1.
I want to show them when i click a buttom.
so, I thought to write a vba code and to run it after clicking on the button.
the vba code should be a select query + a insert query.

what have I to modify in the code?
thank you so much

 

[vbaInet]

So why are you copying the values across from one table to another?

Can you show us the structure of both tables.

And have you thought about using a form instead of code?

 

[sab_db_12]

I want to copy the values from a table to a mask such as a sort of check.
the second table is connected to the first table.
I try to explain it using an example.
in table 1, I insert name and surname of a student.
in table 2, I insert the student's laboratory test values. the test are effectued every month.
I would like to show in the mask connected to the table 2 the name and the surname of the student everytime I insert the student's ID. this is a sort of check becuase when i insert the ID I can see the name and surname, so I'm sure to insert the right laboratory test values of that student.
I tried to use a form, but it doesn't work.

thank you so much.

 

[sab_db_12]

i post the structure, as requested.

 

[vbaInet]

Right, what I see here is a form/subform scenario. The main form will be Table 1 and the subform will be Table 2. When you enter a new student in the main form, the subform will display a blank form for you to enter the lab results.

Look into subforms.

 

[sab_db_12]

But I don't want to do a subform.
i dont' like it in access because it is very awkward when i've to insert some data each month.
could you correct my code, or indicate me a useful guide for learning to write vba select code?
thank you so much.

 

[vbaInet]

There's nothing wrong with using a subform. In fact that's one of the most useful controls in Access so you really need to grasp how to use it effectively.

Re the insert problem, you need to construct your SQL statement properly. Have a look at the following link (Example #2):

http://www.techonthenet.com/sql/insert.php

 

[sab_db_12]

thank you so much but it doesn't work.
I used the Example#2, as you advised but there is a problem.
the values are saved in the next record, and not in the record where I'm introducing the other values which are showed in mask 2.
I don't know if it sufficently clear, but please, help me again.

 

[vbaInet]

Let's see the SQL statement. And explain with some examples what you want it to do.

 

[sab_db_12]

thank you so much!!!!
I attach you some file, so the post is as short as required.

 

[sab_db_12]

don't forget me, please!

 

[vbaInet]

Don't worry, I haven't forgotten. I just haven't gotten round to looking at your db. Will hopefully do so today.

 

[vbaInet]

Right, I don't think you understand how relational databases work. You should not be inserting the Surname and FirstName into the other table but rather you should only be inserting the ID_Student. You can then link the this table to your original table via the ID_Student fields to get the Surname and Firstname fields.

 

[sab_db_12]

hi, i found the solution using UPDATE in the query.
tks for ur interest