Newbie VBA question

[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

 

[Douglas J. Steele]

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]

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]

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

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)

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

 

[Jeremy]

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

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]

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

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)

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