run multiple queries in VBA

[randria]

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.

 

[Stefan Hoffmann]

hi Randira,

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 ?

What are you trying to achieve?

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

Your queries are selection queries returning data. Do you want to modify
data? Then you need to create an action query (DELETE, INSERT, UPDATE).
These can be used with DoCmd.RunSQL or CurrentDb.Execute.


mfG
--> stefan <--

 

[randria]

Thanks Stefan for your quick reply, I dont want to modify or update, I just
want the queries to return values.

 

[Stefan Hoffmann]

hi,

Thanks Stefan for your quick reply, I dont want to modify or update, I just
want the queries to return values.

Then use DoCmd.OpenQuery to display the results.



mfG
--> stefan <--

 

[randria]

I did start with DoCmd.OpenQuery but it was not what I wanted. Let me
elaborate more ( sorry for not given full information )
In my form i have a control called File Ref, and 4 combos that show the
number of rows of each query.

so after the user updated the File Ref, I want the queries to run in the
background so that my combos get the number of row of each query. basically
that is what I m trying to do.
Many thanks.

 

[Stefan Hoffmann]

hi,

so after the user updated the File Ref, I want the queries to run in the
background so that my combos get the number of row of each query. basically
that is what I m trying to do.

Then a

ctlComboBoxX.Requery

should work. Call it for each combo box.


mfG
--> stefan <--