Newbie VBA question

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

Currently I use the following VBA to fill a field. As many of you already
know the DLookup function is quite slow. Can someone help me or give me a
example of how I could do the same thing with SQL? I don't know SQL very
well.

Private Function avggross(person As String) As Long
Dim varx As Variant
Dim crit As String
crit = "[Sales Person] = '" & person & "'"
varx = DLookup("[SumOfSumOfGross Profit1] ", "year_salesperson_avg Query",
crit)
avggross = varx
End Function
 
The SQL would be something like:

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

You can run it use DAO or ADO. If you're using Access 2000 or 2002, you'll
need to set a reference to the Microsoft DAO 3.6 Object Library to make DAO
work.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim varx As Variant

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
With rsCurr
If Not .BOF And Not .EOF Then
varx = rsCurr![SumOfSumOfGross Profit1]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing



Doug Steele, Microsoft Access MVP

(No private e-mails, please)
 
Thanks, But got some more questions. While testing it and trying to figure
out the code, when it hits this line
varx = rsCurr![SumOfSumOfGross Profit1]
Click to expand...
It acts like it dumps out of the routine and nothing gets assigned to varx.
What exactly is that rsCurr! doing? Also any good books I could pick up
that would help in my quest to speed up reports and forms and get rid of
dlookups and dcounts.

--


Jeremy Kettelhohn


Douglas J. Steele said:
The SQL would be something like:

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

You can run it use DAO or ADO. If you're using Access 2000 or 2002, you'll
need to set a reference to the Microsoft DAO 3.6 Object Library to make DAO
work.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim varx As Variant

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
With rsCurr
If Not .BOF And Not .EOF Then
varx = rsCurr![SumOfSumOfGross Profit1]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing



Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jeremy said:
Currently I use the following VBA to fill a field. As many of you already
know the DLookup function is quite slow. Can someone help me or give me a
example of how I could do the same thing with SQL? I don't know SQL very
well.

Private Function avggross(person As String) As Long
Dim varx As Variant
Dim crit As String
crit = "[Sales Person] = '" & person & "'"
varx = DLookup("[SumOfSumOfGross Profit1] ", "year_salesperson_avg Query",
crit)
avggross = varx
End Function
Click to expand...
Click to expand...
 
I did however figure what I did wrong, but still curious if you knew of a
good book to help me understand SQL and using it in access or using the
Access DAO better.

--


Jeremy Kettelhohn

Jeremy said:
Thanks, But got some more questions. While testing it and trying to figure
out the code, when it hits this line
varx = rsCurr![SumOfSumOfGross Profit1]
Click to expand...
It acts like it dumps out of the routine and nothing gets assigned to varx.
What exactly is that rsCurr! doing? Also any good books I could pick up
that would help in my quest to speed up reports and forms and get rid of
dlookups and dcounts.

--


Jeremy Kettelhohn


Douglas J. Steele said:
The SQL would be something like:

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

You can run it use DAO or ADO. If you're using Access 2000 or 2002, you'll
need to set a reference to the Microsoft DAO 3.6 Object Library to make DAO
work.

Using DAO, it would be something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim varx As Variant

strSQL = "SELECT [SumOfSumOfGross Profit1] "
strSQL = strSQL & "FROM [year_salesperson_avg Query] "
strSQL = strSQL & "WHERE [Sales Person] = '" & person & "'"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
With rsCurr
If Not .BOF And Not .EOF Then
varx = rsCurr![SumOfSumOfGross Profit1]
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing



Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jeremy said:
Currently I use the following VBA to fill a field. As many of you already
know the DLookup function is quite slow. Can someone help me or give
Click to expand...
Click to expand...
me
a
example of how I could do the same thing with SQL? I don't know SQL very
well.

Private Function avggross(person As String) As Long
Dim varx As Variant
Dim crit As String
crit = "[Sales Person] = '" & person & "'"
varx = DLookup("[SumOfSumOfGross Profit1] ", "year_salesperson_avg Query",
crit)
avggross = varx
End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top