2 sql command

  • Thread starter enrico via AccessMonster.com
  • Start date
A

Allen Browne

enrico via AccessMonster.com said:
can you run two or more sql queries in one button? how?
Click to expand...

Action queries? Use RunSQL, or Execute. My personal preference is for
Execute, for the reasons explained here:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

If you are talking about SELECT queries, use OpenQuery.
 
E

enrico via AccessMonster.com

its an INSERT query. my save button is coded and not wizard created. i'm
using two insert queries because there are two tables to where i'm saving,
their link is the "ClientID".

e.g.
tblclient
ClientID - primary key
Lastname
Firstname
MI

tblanswer
AnswerID - primary
ClientID
A1
A2
A3

how do i code it that all data of tblAnswer is linked to tblclient through
ClientID?
 
A

Allen Browne

You will have to write the SQL statements. Use query design to mock them up,
and then switch to SQL View to see an exmaple of the string you need to
create.

Then code like this:
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT ...
db.Execute strSql, dbFailOnError

strSql = "INSERT ...
db.Execute strSql, dbFailOnError
 
E

enrico via AccessMonster.com

good am. i got these error using the code you gave me:

'object variable or with block variable bot set'

i don't know where the error is. these is my code by the way for your
reference:

Dim db As DAO.Database
Dim strSql As String

strSql = ("INSERT INTO tblGenInfo(Lastname, " & _
" Firstname, " & _
" MI, Age, " & _
" Sex, " & _
" CivilStatus, " & _
" MembershipCategory, " & _
" MembershipType, " & _
" Address, " & _
" Hospital, " & _
" DateAdmitted, " & _
" DateDischarge, " & _
" DateofSurvey) " & _
"VALUES ('" & Me.txtLastName & "', " & _
" '" & Me.txtFirstname & "', " & _
" '" & Me.txtMI & "', '" & Me.txtAge & "', " & _
" '" & Me.cboSex & "', " & _
" '" & Me.cboCivilStatus & "', " & _
" '" & Me.cboMemCategory & "', " & _
" '" & Me.cboMemClass & "', " & _
" '" & Me.txtAddress & "', " & _
" '" & Me.cboHospital & "', " & _
" '" & Me.txtDateAdmitted & "', " & _
" '" & Me.txtDateDischarged & "', " & _
" '" & Me.txtDateofSurvey & "')")
db.Execute strSql, dbFailOnError

strSql = ("INSERT INTO tblGenInfo(InterviewerID) " & _
"VALUES (tblInterviewer.InterviewerID) " & _
"WHERE tblInterviewer.Lastname = '" & Me.cboILname & "' ")
db.Execute strSql, dbFailOnError
 
A

Allen Browne

Here's how you can debug your query.

Add the line:
debug.print strSql
just ahead of the Exeucte line.

When it fails, open the Immediate Window (Ctrl+G) and see what came out.
Creata a new query, switch it to SQL View, and paste the statement in. Then
see what's wrong with it.

Access may help by highlighting, or you might see something that's not
right.

I assume you do have the line where you set db, e.g.:
Set db = CurrentDb()
 
E

enrico via AccessMonster.com

it does save already but only on the first INSERT statement. it gives an
error on the second INSERT statement, stating:

missing semicolon (;) at the end of SQL statement.
 
A

Allen Browne

You're not putting 2 SQL statements in the one string, I hope?

If so, build the string for the first one, and execute it.
Then build the 2nd string, and execute it.
 
E

enrico via AccessMonster.com

i can't understand what do you mean by 1 string. my code is what i post above:
after the first INSERT statement i execute the second one. i don't know if
it's the right way to execute it.
 
A

Allen Browne

Examining your SQL statement in more detail, it will give errors:

a) if any of the fields are not Text fields. For example if Age is a Number
field, the quotes are wrong, or if DateAdmitted is a a Date/Time field it
needs # as the delimiter.

b) if any of the controls on the form are Null. The SQL statement will be
mal-formed for Number or Date/Time fields, and may fail on Text fields too
if zero-length strings are not allowed.

If you are still stuck post what you got frmo your:
Debug.Print strSql
 
Top