Query converted to VBA code

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi

Any way to convert a query to vba code, run the query and put the result
into a variable or a control? The query would be a simple SELECT query
calculating a SUM of another query. In other words, my query I want to
convert to vba, only has one calculated field.

eg.

SELECT DISTINCTROW Sum([Query1].[Sub Total]) AS [Sum Of Sub Total]
FROM [Query1];

TIA

Regards

Chris

PS! I apologize if I'm posting in the wrong forum, where else?
 
Even though there's only one field and one row, you still have to open a
recordset and retrieve the field from the recordset.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngSum As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")
With rsCurr
If Not .BOF And Not .EOF Then
lngSum = ![Sum Of Sub Total]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
 
Thanks for that, but I ran into a problem on the line:

Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")

The debugger stops at this line with the error. "Run-time error 3061. Too
few parameters. Expected 2." What is missing?


Thanks

Chris


Douglas J. Steele said:
Even though there's only one field and one row, you still have to open a
recordset and retrieve the field from the recordset.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngSum As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")
With rsCurr
If Not .BOF And Not .EOF Then
lngSum = ![Sum Of Sub Total]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Hi

Any way to convert a query to vba code, run the query and put the result
into a variable or a control? The query would be a simple SELECT query
calculating a SUM of another query. In other words, my query I want to
convert to vba, only has one calculated field.

eg.

SELECT DISTINCTROW Sum([Query1].[Sub Total]) AS [Sum Of Sub Total]
FROM [Query1];

TIA

Regards

Chris

PS! I apologize if I'm posting in the wrong forum, where else?
Click to expand...
Click to expand...
 
You sure all of the table and field names were typed correctly?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Thanks for that, but I ran into a problem on the line:

Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")

The debugger stops at this line with the error. "Run-time error 3061. Too
few parameters. Expected 2." What is missing?


Thanks

Chris


Douglas J. Steele said:
Even though there's only one field and one row, you still have to open a
recordset and retrieve the field from the recordset.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngSum As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")
With rsCurr
If Not .BOF And Not .EOF Then
lngSum = ![Sum Of Sub Total]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Hi

Any way to convert a query to vba code, run the query and put the result
into a variable or a control? The query would be a simple SELECT query
calculating a SUM of another query. In other words, my query I want to
convert to vba, only has one calculated field.

eg.

SELECT DISTINCTROW Sum([Query1].[Sub Total]) AS [Sum Of Sub Total]
FROM [Query1];

TIA

Regards

Chris

PS! I apologize if I'm posting in the wrong forum, where else?
Click to expand...
Click to expand...
Click to expand...
 
Yes... My query works fine if I run it in the usual way under Queries...
Btw. I'm using Access 2000 if that makes any difference.

I've also tried the example in the MSDN Library which I modified and got the
same error.

Any ideas?


Chris

Douglas J. Steele said:
You sure all of the table and field names were typed correctly?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Thanks for that, but I ran into a problem on the line:

Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")

The debugger stops at this line with the error. "Run-time error 3061. Too
few parameters. Expected 2." What is missing?


Thanks

Chris


Douglas J. Steele said:
Even though there's only one field and one row, you still have to open a
recordset and retrieve the field from the recordset.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngSum As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")
With rsCurr
If Not .BOF And Not .EOF Then
lngSum = ![Sum Of Sub Total]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hi

Any way to convert a query to vba code, run the query and put the result
into a variable or a control? The query would be a simple SELECT query
calculating a SUM of another query. In other words, my query I want to
convert to vba, only has one calculated field.

eg.

SELECT DISTINCTROW Sum([Query1].[Sub Total]) AS [Sum Of Sub Total]
FROM [Query1];

TIA

Regards

Chris

PS! I apologize if I'm posting in the wrong forum, where else?
Click to expand...
Click to expand...
Click to expand...
 
Nothing appears to be wrong to me.

See whether getting rid of the DISTINCTROW makes any difference.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Yes... My query works fine if I run it in the usual way under Queries...
Btw. I'm using Access 2000 if that makes any difference.

I've also tried the example in the MSDN Library which I modified and got the
same error.

Any ideas?


Chris

Douglas J. Steele said:
You sure all of the table and field names were typed correctly?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Chris said:
Thanks for that, but I ran into a problem on the line:

Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")

The debugger stops at this line with the error. "Run-time error 3061. Too
few parameters. Expected 2." What is missing?


Thanks

Chris


Even though there's only one field and one row, you still have to
Click to expand...
Click to expand...
open
a
recordset and retrieve the field from the recordset.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngSum As Long

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCTROW Sum([Query1].[Sub
Total]) AS [Sum Of Sub Total] FROM [Query1]")
With rsCurr
If Not .BOF And Not .EOF Then
lngSum = ![Sum Of Sub Total]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hi

Any way to convert a query to vba code, run the query and put the result
into a variable or a control? The query would be a simple SELECT query
calculating a SUM of another query. In other words, my query I
Click to expand...
Click to expand...
want
to
convert to vba, only has one calculated field.

eg.

SELECT DISTINCTROW Sum([Query1].[Sub Total]) AS [Sum Of Sub Total]
FROM [Query1];

TIA

Regards

Chris

PS! I apologize if I'm posting in the wrong forum, where else?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top