run Query in VBA

  • Thread starter Thread starter john lawlor
  • Start date Start date
J

john lawlor

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
 
You need to get each queries SQL into a character string.

The syntax to do this is:

Dim strSQL as string
strSQL = "my SQL"

to continue a long line use:

strSQL = "my SQL" & _
"plus more SQL"

then to run your query
CurrentProject.Connection.Execute strSQL

if you want to know how many records affected:
Dim myCount as long
CurrentProject.Connection.Execute strSQL,myCount

If you queries return data (yours dont), you need to open a recordset which
is another story

Look in Access HELP, its all explained there.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo
 
Thank you both.

1. is the & like concatenate?

2. What is the difference between inserting the sql text or

DoCmd.RunSQL (Queryname)

3. How do I go about making the module run by clicking a button?

John


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

john lawlor said:
This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
 
1. Yeah, the purpose of the ampersand is to concatenate
2. for running the query I think's better to use
currentdb.execute("queryname")
3. In your button insert the code I sent you before e.g.
Private Sub Command1_Click()

docmd.setwarnings false ''to switch off system messages
docmd.runsql("DROP TABLE tbl_insertion")
docmd.setwarnings true ''to switch on system messages

End Sub

Cheers Paolo

john lawlor said:
Thank you both.

1. is the & like concatenate?

2. What is the difference between inserting the sql text or

DoCmd.RunSQL (Queryname)

3. How do I go about making the module run by clicking a button?

John


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

john lawlor said:
This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
In my opinion, it's better to use the Execute method of the Database object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable error
that can help if something goes wrong (plus it doesn't require you to change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to be able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

john lawlor said:
This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
 
Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors. What am I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a control in a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or 'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just use the
query name. If it may help to see the SQL view of one of the queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




Douglas J. Steele said:
In my opinion, it's better to use the Execute method of the Database object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable error
that can help if something goes wrong (plus it doesn't require you to change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to be able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

john lawlor said:
This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors. What am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a control in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or 'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just use
the
query name. If it may help to see the SQL view of one of the queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




Douglas J. Steele said:
In my opinion, it's better to use the Execute method of the Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable
error
that can help if something goes wrong (plus it doesn't require you to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to be
able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
I tried CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError and I get
Run-time error '3065': Cannot execute a select query.

I tried
Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "SELECT Sum(IIf(BDTP.bed='sgl',1,0)) AS countsgl, MNST.[File
Reference], MNST.travelling, RMMT.Room, BDTP.bed" & "FROM BDTP INNER JOIN
(RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON BDTP.ID = RMMT.Bed" &
"GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed" &
"HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)='sgl'));"

dbCurr.Execute sglSQL, dbFailOnError ' Run-time error '91' Object
variable or with block variable not set
End If

End Sub

I get Run-time error '91' Object variable or with block variable not set.
Did I re-write the statement from sql view correctly ? what is Oject variable
or with bock variable in the error msg ?

Many thanks.







Douglas J. Steele said:
You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors. What am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a control in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or 'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just use
the
query name. If it may help to see the SQL view of one of the queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




Douglas J. Steele said:
In my opinion, it's better to use the Execute method of the Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable
error
that can help if something goes wrong (plus it doesn't require you to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to be
able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
Hold on a moment. What are you trying to accomplish?

Both RunSQL and the Execute method are only intended to be used with Action
queries (UPDATE, INSERT INTO, DELETE, SELECT ... INTO, etc.)

The reason for your Run-time error '91', btw, is that you haven't
instantiated dbCurr. If you look at my original suggestion to John, you
should see a statement

Set dbCurr = CurrentDb()

before I use the Execute method.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
I tried CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError and I get
Run-time error '3065': Cannot execute a select query.

I tried
Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "SELECT Sum(IIf(BDTP.bed='sgl',1,0)) AS countsgl, MNST.[File
Reference], MNST.travelling, RMMT.Room, BDTP.bed" & "FROM BDTP INNER JOIN
(RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON BDTP.ID =
RMMT.Bed" &
"GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed" &
"HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)='sgl'));"

dbCurr.Execute sglSQL, dbFailOnError ' Run-time error '91' Object
variable or with block variable not set
End If

End Sub

I get Run-time error '91' Object variable or with block variable not set.
Did I re-write the statement from sql view correctly ? what is Oject
variable
or with bock variable in the error msg ?

Many thanks.







Douglas J. Steele said:
You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors. What
am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a control
in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or 'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just use
the
query name. If it may help to see the SQL view of one of the queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid =
MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




:

In my opinion, it's better to use the Execute method of the Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable
error
that can help if something goes wrong (plus it doesn't require you to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather
than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to
be
able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue
the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and
other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the
characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
Thank you - superb answer and explanation!

J

Douglas J. Steele said:
In my opinion, it's better to use the Execute method of the Database object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable error
that can help if something goes wrong (plus it doesn't require you to change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to be able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue the line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

john lawlor said:
This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
Maybe I m completly at the wrong side of the court, let me tell you what I am
trying to accomplish.


I have a Form that has a control called [File Ref] and 4 other controls that
count the number of rows returned by 4 different queries ( each control for
each query ).

After the user updates [File Ref], I want the 4 queries to run in the
background and the controls to show the number of rows each query returns.

based on that , I dont know if I m trying to use the wrong methods.

Right, I missed out the initiation of dbCurr , I should have noticed that ,
but after running the codes, I got a syntax error on my sql statement ( This
is my first attempt writing SQL in VBA so I apologise for the stupid mistakes
).

Many thanks



Douglas J. Steele said:
Hold on a moment. What are you trying to accomplish?

Both RunSQL and the Execute method are only intended to be used with Action
queries (UPDATE, INSERT INTO, DELETE, SELECT ... INTO, etc.)

The reason for your Run-time error '91', btw, is that you haven't
instantiated dbCurr. If you look at my original suggestion to John, you
should see a statement

Set dbCurr = CurrentDb()

before I use the Execute method.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
I tried CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError and I get
Run-time error '3065': Cannot execute a select query.

I tried
Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "SELECT Sum(IIf(BDTP.bed='sgl',1,0)) AS countsgl, MNST.[File
Reference], MNST.travelling, RMMT.Room, BDTP.bed" & "FROM BDTP INNER JOIN
(RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON BDTP.ID =
RMMT.Bed" &
"GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed" &
"HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)='sgl'));"

dbCurr.Execute sglSQL, dbFailOnError ' Run-time error '91' Object
variable or with block variable not set
End If

End Sub

I get Run-time error '91' Object variable or with block variable not set.
Did I re-write the statement from sql view correctly ? what is Oject
variable
or with bock variable in the error msg ?

Many thanks.







Douglas J. Steele said:
You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors. What
am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a control
in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or 'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just use
the
query name. If it may help to see the SQL view of one of the queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid =
MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




:

In my opinion, it's better to use the Execute method of the Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an trappable
error
that can help if something goes wrong (plus it doesn't require you to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather
than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful to
be
able
to print out the contents of the string to help troubleshoot problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue
the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance, tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and
other
characters are added, but I am having difficulty divinig the logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the
characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
The only way to use SELECT SQL statements in code is to open a recordset.

To get row counts, just use the DCount function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
Maybe I m completly at the wrong side of the court, let me tell you what I
am
trying to accomplish.


I have a Form that has a control called [File Ref] and 4 other controls
that
count the number of rows returned by 4 different queries ( each control
for
each query ).

After the user updates [File Ref], I want the 4 queries to run in the
background and the controls to show the number of rows each query returns.

based on that , I dont know if I m trying to use the wrong methods.

Right, I missed out the initiation of dbCurr , I should have noticed that
,
but after running the codes, I got a syntax error on my sql statement (
This
is my first attempt writing SQL in VBA so I apologise for the stupid
mistakes
).

Many thanks



Douglas J. Steele said:
Hold on a moment. What are you trying to accomplish?

Both RunSQL and the Execute method are only intended to be used with
Action
queries (UPDATE, INSERT INTO, DELETE, SELECT ... INTO, etc.)

The reason for your Run-time error '91', btw, is that you haven't
instantiated dbCurr. If you look at my original suggestion to John, you
should see a statement

Set dbCurr = CurrentDb()

before I use the Execute method.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
I tried CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError and I get
Run-time error '3065': Cannot execute a select query.

I tried
Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "SELECT Sum(IIf(BDTP.bed='sgl',1,0)) AS countsgl, MNST.[File
Reference], MNST.travelling, RMMT.Room, BDTP.bed" & "FROM BDTP INNER
JOIN
(RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON BDTP.ID =
RMMT.Bed" &
"GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed"
&
"HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)='sgl'));"

dbCurr.Execute sglSQL, dbFailOnError ' Run-time error '91' Object
variable or with block variable not set
End If

End Sub

I get Run-time error '91' Object variable or with block variable not
set.
Did I re-write the statement from sql view correctly ? what is Oject
variable
or with bock variable in the error msg ?

Many thanks.







:

You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it
as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors.
What
am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a
control
in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or
'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just
use
the
query name. If it may help to see the SQL view of one of the
queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File
Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid =
MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




:

In my opinion, it's better to use the Execute method of the
Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an
trappable
error
that can help if something goes wrong (plus it doesn't require you
to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather
than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful
to
be
able
to print out the contents of the string to help troubleshoot
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue
the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance,
tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);

The examples revealed in an internet search show that "" _ and
other
characters are added, but I am having difficulty divinig the
logic.

1. Could someone be so kind as to re-write some of the above

and

2. Suggest online sites which would explain the use of the
characters.

Thank you,

John
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas,

Douglas J. Steele said:
The only way to use SELECT SQL statements in code is to open a recordset.

To get row counts, just use the DCount function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


randria said:
Maybe I m completly at the wrong side of the court, let me tell you what I
am
trying to accomplish.


I have a Form that has a control called [File Ref] and 4 other controls
that
count the number of rows returned by 4 different queries ( each control
for
each query ).

After the user updates [File Ref], I want the 4 queries to run in the
background and the controls to show the number of rows each query returns.

based on that , I dont know if I m trying to use the wrong methods.

Right, I missed out the initiation of dbCurr , I should have noticed that
,
but after running the codes, I got a syntax error on my sql statement (
This
is my first attempt writing SQL in VBA so I apologise for the stupid
mistakes
).

Many thanks



Douglas J. Steele said:
Hold on a moment. What are you trying to accomplish?

Both RunSQL and the Execute method are only intended to be used with
Action
queries (UPDATE, INSERT INTO, DELETE, SELECT ... INTO, etc.)

The reason for your Run-time error '91', btw, is that you haven't
instantiated dbCurr. If you look at my original suggestion to John, you
should see a statement

Set dbCurr = CurrentDb()

before I use the Execute method.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError and I get
Run-time error '3065': Cannot execute a select query.

I tried
Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "SELECT Sum(IIf(BDTP.bed='sgl',1,0)) AS countsgl, MNST.[File
Reference], MNST.travelling, RMMT.Room, BDTP.bed" & "FROM BDTP INNER
JOIN
(RMMT INNER JOIN MNST ON RMMT.rmmtid = MNST.Roomate) ON BDTP.ID =
RMMT.Bed" &
"GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed"
&
"HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)='sgl'));"

dbCurr.Execute sglSQL, dbFailOnError ' Run-time error '91' Object
variable or with block variable not set
End If

End Sub

I get Run-time error '91' Object variable or with block variable not
set.
Did I re-write the statement from sql view correctly ? what is Oject
variable
or with bock variable in the error msg ?

Many thanks.







:

You can't use RunSQL or CurrentDB.Execute with query names: both are
expecting an actual SQL string.

If you've got a saved query, though, there's an Execute method for it
as
well:

Private Sub File_Ref_AfterUpdate()

If Me.Dirty Then
Me.Dirty = False
CurrentDb.QueryDefs("BGVHSGL").Execute dbFailOnError
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I have been following this thread and I tried Paolo's method and
Douglas'recommendation but either way I m getting run-time errors.
What
am
I
doing wrong ?

I have 2 select queries that I want to run in afterUpdate of a
control
in
a
form.
My query names:BGVHDBL,BGVHSGL

So I tried using RunSQL first as follow

Private Sub File_Ref_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
DoCmd.RunSQL ("BGVHSGL")
End If
End Sub

and I m getting Run-time error '3129'
Invalid SQL statement; expected'Delete,'insert','select'or
'update'

Then I tried

Private Sub File_Ref_AfterUpdate()
Dim dbCurr As DAO.Database
Dim sglSQL As String

DoCmd.SetWarnings False
If Me.Dirty Then
Me.Dirty = False
sglSQL = "BGVHSGL"

dbCurr.Execute sglSQL, dbFailOnError
End If

End Sub

and I m getting Run-Time error '91':
Object variable or with block variable not set

I have not written SQL in VBA before that is why I m trying to just
use
the
query name. If it may help to see the SQL view of one of the
queries:

SELECT Sum(IIf(BDTP.bed="sgl",1,0)) AS countsgl, MNST.[File
Reference],
MNST.travelling, RMMT.Room, BDTP.bed
FROM BDTP INNER JOIN (RMMT INNER JOIN MNST ON RMMT.rmmtid =
MNST.Roomate)
ON
BDTP.ID = RMMT.Bed
GROUP BY MNST.[File Reference], MNST.travelling, RMMT.Room, BDTP.bed
HAVING (((MNST.[File Reference])=[Forms]![BGVCH]![File Ref]) AND
((MNST.travelling)=True) AND ((BDTP.bed)="sgl"));

What corrections do I need to make to have those 2 queries running ?
Many thanks.




:

In my opinion, it's better to use the Execute method of the
Database
object
than to use RunSQL.

The reason for this is that the Execute method will raise an
trappable
error
that can help if something goes wrong (plus it doesn't require you
to
change
the SetWarnings setting)

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "DROP TABLE tbl_Insertion"

dbCurr.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_FORM_CA_BSI_Events SET " & _
"DateOfEvent2 = Format([DateOfEvent],'yy/mm')"


dbCurr.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " & _
"DateOfEvent2 text(6)"

dbCurr.Execute strSQL, dbFailOnError

Set dbCurr = Nothing

The reason I prefer using a string variable to hold the SQL, rather
than
simply putting it as part of the command as in

dbCurr.Execute "DROP TABLE tbl_Insertion", dbFailOnError

is that when you're doing more complicated SQL, it's often useful
to
be
able
to print out the contents of the string to help troubleshoot
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi john lawlor,

docmd.setwarnings false ''to switch off system messages
eg1
docmd.runsql("DROP TABLE tbl_insertion")
eg2
docmd.runsql("UPDATE tbl_FORM_CA_BSI_Events SET " _ ''to continue
the
line
on another line
& "DateOfEvent2 = Format([DateOfEvent],'yy/mm')")
eg3
docmd.runsql("ALTER TABLE tbl_Form_CA_BSI_Events ADD COLUMN " _ &
"DateOfEvent2 text(6)")

HTH Paolo

:

This is my first foray into VBA.

I want to run many queries (which work) in a VBA module.

e.g. 1

DROP TABLE tbl_insertion, tbl_maintenance,
tbl_form_ca_bsi_events,
tbl_BiopatchTestFactors_FormDetails,
tbl_BiopatchTestFactors_FormNumbers,
tbl_CHGScrubTestFactor,tbl_Form_CA_BSI_Catheter,
tbl_Form_LineDaysAggregateData, tbl_Form_LineDaysAggregateInfo;
;
e.g. 2
UPDATE tbl_FORM_CA_BSI_Events SET DateOfEvent2 =
Format([DateOfEvent],"yy/mm");
e.g. 3
ALTER TABLE tbl_Form_CA_BSI_Events
ADD COLUMN DateOfEvent2 text(6);
Click to expand...
Click to expand...
Click to expand...
 
Back
Top