dlookup and dao.recordset

[SuzyQ]

can dlookup be used to look up a record in a recordset?

I have the following code and my dlookup statement is returning an error
"could not find table or query"... which I guess answers my question, if so,
then how can I look up the value? Do I have to iterate through the recordset?

Function writeMissingInvoices(haveData As Boolean, Optional rs As
DAO.Recordset) As Boolean
'need to account for invoices entered for a specific project on specific
date range but no labor occured in that date range
Dim rsProjects As DAO.Recordset
Dim strSQL As String
Dim strProject As String
Dim dblProjectTotal As Double
Dim booAddIt As Boolean
Dim booChecked As Boolean
Dim strDistrict As String
Dim strGroup As String
Dim strDescription As String

'first get all the invoices for the specified date range
strSQL = "SELECT tblInvoiceLineItems.ProjectToCharge,
tblInvoice.InvoiceDate, "
strSQL = strSQL & "tblInvoiceLineItems.PerUnitAmount,
tblInvoiceLineItems.Quantity, "
strSQL = strSQL & "[quantity]*[perUnitAmount] AS total "
strSQL = strSQL & "FROM tblInvoice INNER JOIN tblInvoiceLineItems ON "
strSQL = strSQL & "tblInvoice.InvoiceID = tblInvoiceLineItems.InvoiceID "
strSQL = strSQL & "WHERE ((tblInvoice.InvoiceDate) Between "
strSQL = strSQL & "#" & [Forms]![frmReports]![txtFromDate] & "# And #"
strSQL = strSQL & [Forms]![frmReports]![txtToDate] & "#) "
strSQL = strSQL & "ORDER BY tblInvoiceLineItems.ProjectToCharge "

Set rsProjects = CurrentDb.OpenRecordset(strSQL)

If Not rsProjects.EOF Then
'look in rs data set to make sure this project hasn't already been
assigned to a record amount
strProject = Nz(rsProjects!ProjectToCharge, " ")
If haveData Then 'some projects have already been handled elsewhere
booAddIt = False
booChecked = False
Else 'no projects have been handled - add all projects
booAddIt = True
booChecked = True
End If

Do While Not rsProjects.EOF
'next check to see if that project number is already handled in
the rs recordset if necessary
If strProject = Nz(rsProjects!ProjectToCharge, " ") Then 'still
on same code
If Not booChecked Then 'if code has not been checked yet,
check to see if it needs to be added
If IsNull(DLookup("[ProjectCode]", "rs", "[ProjectCode]
= '" & strProject & "'")) Then ... 'ERROR RIGHT HERE

 

[Sylvain Lafontaine]

No but you could use the Find method of the recordset. Also, when looking
for the Find method on the internet, don't confuse the DAO recordsets with
the ADO recordsets. These two types of recordsets have a lot in common but
also many differences and the use of Find is one of these.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

can dlookup be used to look up a record in a recordset?

I have the following code and my dlookup statement is returning an error
"could not find table or query"... which I guess answers my question, if
so,
then how can I look up the value? Do I have to iterate through the
recordset?

Function writeMissingInvoices(haveData As Boolean, Optional rs As
DAO.Recordset) As Boolean
'need to account for invoices entered for a specific project on
specific
date range but no labor occured in that date range
Dim rsProjects As DAO.Recordset
Dim strSQL As String
Dim strProject As String
Dim dblProjectTotal As Double
Dim booAddIt As Boolean
Dim booChecked As Boolean
Dim strDistrict As String
Dim strGroup As String
Dim strDescription As String

'first get all the invoices for the specified date range
strSQL = "SELECT tblInvoiceLineItems.ProjectToCharge,
tblInvoice.InvoiceDate, "
strSQL = strSQL & "tblInvoiceLineItems.PerUnitAmount,
tblInvoiceLineItems.Quantity, "
strSQL = strSQL & "[quantity]*[perUnitAmount] AS total "
strSQL = strSQL & "FROM tblInvoice INNER JOIN tblInvoiceLineItems ON "
strSQL = strSQL & "tblInvoice.InvoiceID = tblInvoiceLineItems.InvoiceID
"
strSQL = strSQL & "WHERE ((tblInvoice.InvoiceDate) Between "
strSQL = strSQL & "#" & [Forms]![frmReports]![txtFromDate] & "# And #"
strSQL = strSQL & [Forms]![frmReports]![txtToDate] & "#) "
strSQL = strSQL & "ORDER BY tblInvoiceLineItems.ProjectToCharge "

Set rsProjects = CurrentDb.OpenRecordset(strSQL)

If Not rsProjects.EOF Then
'look in rs data set to make sure this project hasn't already been
assigned to a record amount
strProject = Nz(rsProjects!ProjectToCharge, " ")
If haveData Then 'some projects have already been handled elsewhere
booAddIt = False
booChecked = False
Else 'no projects have been handled - add all projects
booAddIt = True
booChecked = True
End If

Do While Not rsProjects.EOF
'next check to see if that project number is already handled in
the rs recordset if necessary
If strProject = Nz(rsProjects!ProjectToCharge, " ") Then 'still
on same code
If Not booChecked Then 'if code has not been checked yet,
check to see if it needs to be added
If IsNull(DLookup("[ProjectCode]", "rs", "[ProjectCode]
= '" & strProject & "'")) Then ... 'ERROR RIGHT HERE

 

[Larry Linson]

As far as I know, you can specify a "domain"... that is a table, or a query.

I'm not sure what your recordset represents, nor exactly what you want to
accomplish.

If you have a DAO recordset... and it was opened as a table, you can use
Seek to find a record; if you have it opened as a Dynaset, you can use
FindFirst to find a particular record. Seek will be somewhat faster, but
unless you are dealing with vast numbers of records, you won't be able to
tell much difference looking at the screen.

If by "interate through the recordset", you mean read each record until you
get to the one you want, that shouldn't be required unless you are
processing every record.

Without trying to carefully analyze partial code, it would appear to me that
you could use criteria in a Query to select only the records you want...

Larry Linson
Microsoft Office Access MVP

can dlookup be used to look up a record in a recordset?

I have the following code and my dlookup statement is returning an error
"could not find table or query"... which I guess answers my question, if
so,
then how can I look up the value? Do I have to iterate through the
recordset?

Function writeMissingInvoices(haveData As Boolean, Optional rs As
DAO.Recordset) As Boolean
'need to account for invoices entered for a specific project on
specific
date range but no labor occured in that date range
Dim rsProjects As DAO.Recordset
Dim strSQL As String
Dim strProject As String
Dim dblProjectTotal As Double
Dim booAddIt As Boolean
Dim booChecked As Boolean
Dim strDistrict As String
Dim strGroup As String
Dim strDescription As String

'first get all the invoices for the specified date range
strSQL = "SELECT tblInvoiceLineItems.ProjectToCharge,
tblInvoice.InvoiceDate, "
strSQL = strSQL & "tblInvoiceLineItems.PerUnitAmount,
tblInvoiceLineItems.Quantity, "
strSQL = strSQL & "[quantity]*[perUnitAmount] AS total "
strSQL = strSQL & "FROM tblInvoice INNER JOIN tblInvoiceLineItems ON "
strSQL = strSQL & "tblInvoice.InvoiceID = tblInvoiceLineItems.InvoiceID
"
strSQL = strSQL & "WHERE ((tblInvoice.InvoiceDate) Between "
strSQL = strSQL & "#" & [Forms]![frmReports]![txtFromDate] & "# And #"
strSQL = strSQL & [Forms]![frmReports]![txtToDate] & "#) "
strSQL = strSQL & "ORDER BY tblInvoiceLineItems.ProjectToCharge "

Set rsProjects = CurrentDb.OpenRecordset(strSQL)

If Not rsProjects.EOF Then
'look in rs data set to make sure this project hasn't already been
assigned to a record amount
strProject = Nz(rsProjects!ProjectToCharge, " ")
If haveData Then 'some projects have already been handled elsewhere
booAddIt = False
booChecked = False
Else 'no projects have been handled - add all projects
booAddIt = True
booChecked = True
End If

Do While Not rsProjects.EOF
'next check to see if that project number is already handled in
the rs recordset if necessary
If strProject = Nz(rsProjects!ProjectToCharge, " ") Then 'still
on same code
If Not booChecked Then 'if code has not been checked yet,
check to see if it needs to be added
If IsNull(DLookup("[ProjectCode]", "rs", "[ProjectCode]
= '" & strProject & "'")) Then ... 'ERROR RIGHT HERE

 

[ejc]

hello,
in DLookup("[ProjectCode]", "rs", "[ProjectCode] ), rs is not a table or a
query (and it must be...)
sorry for my english,
jero
= '" & strProject & "'")

 

[David W. Fenton]

can dlookup be used to look up a record in a recordset?

No.

It's not entirely clear what you're trying to do, but it looks like
you're trying to create invoices for projects that don't already
have an invoice.

It seems to me that you would want to do something like this:

SELECT DISTINCT tblInvoiceLineItems.ProjectToCharge
FROM tblInvoice INNER JOIN tblInvoiceLineItems ON
tblInvoice.InvoiceID = tblInvoiceLineItems.InvoiceID
WHERE tblInvoice.InvoiceDate
Between [Forms]![frmReports]![txtFromDate] And
[Forms]![frmReports]![txtToDate]

This will return a list of the projects billed during the requested
period. You can then use that as a virtual table to join to the list
of projects.

SELECT DISTINCT tblProjects.ProjectID, tblProjects.CustomerID
FROM
[SELECT DISTINCT tblInvoiceLineItems.ProjectToCharge
FROM tblInvoice INNER JOIN tblInvoiceLineItems ON
tblInvoice.InvoiceID = tblInvoiceLineItems.InvoiceID
WHERE tblInvoice.InvoiceDate
Between [Forms]![frmReports]![txtFromDate] And
[Forms]![frmReports]![txtToDate];].
AS BilledProjects
RIGHT JOIN tblProjects ON
BilledProjects.ProjectToCharge = tblProjects.ProjectID
WHERE (((BilledProjects.ProjectToCharge) Is Null)
AND ((tblProjects.ProjectStart)
Between [Forms]![frmReports]![txtFromDate] And
[Forms]![frmReports]![txtToDate];

(obviously, you'll want to do the proper string concatenation to get
rid of the direct form references, as you did in your original code,
but I didn't want to confuse the discussion by mixing up the process
of writing the correct SQL with the process of constructing the SQL
string for assignment to a variable)

This gets you a list of the projects that that have a ProjectStart
date within the selected date range and that have no invoices during
the same date range.

Now, this is the point at which you're going to need your recordset
-- and the SELECT statement above will give you something to work
from.

Assuming you've opened a recordset with that SQL string (with
suitable additional fields from the projects table added to the
SELECT statement), this is what you'd do:

Dim lngInvoiceID As Long

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do Until .EOF
strSQL = "INSERT INTO tblInvoice ( CustomerID ) "
strSQL = strSQL & "VALUES (" & !CustomerID & ")"
CurrentDB.Execute strSQL, dbFailOnError
strSQL = "SELECT @@IDENTITY"
lngInvoiceID = CurrentDB.OpenRecordset(strSQL)(0)
strSQL = "INSERT INTO tblInvoiceLineItems ( InvoiceID, "
strSQL = strSQL & "ProjectToCharge )"
strSQL = strSQL & "VALUES (" & lngInvoiceID & ", "
strSQL = strSQL & !ProjectID & " )"
CurrentDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
End If
rs.Close
Set rs = Nothing

Now, obviously, I haven't included the invoice item amounts or
quantities or any number of things, but the basic structure is there
to create an invoice for each project.

Now, if you want to create an invoice for each customer that has
multiple items, that's more complicated. In that case, you'd have to
do things differently, but the structure would be the same. All
you'd do would be to sort your recordset by CustomerID and then have
an inner loop for the to advance through the recordset until you
come to a new CustomerID:

Dim lngCustomerID As Long
Dim lngInvoiceID As Long

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do Until .EOF
If !CustomerID <> lngCustomerID Then
strSQL = "INSERT INTO tblInvoice ( CustomerID ) "
strSQL = strSQL & "VALUES (" & !CustomerID & ")"
CurrentDB.Execute strSQL, dbFailOnError
strSQL = "SELECT @@IDENTITY"
lngInvoiceID = CurrentDB.OpenRecordset(strSQL)(0)
End If
lngCustomerID = !CustomerID
strSQL = "INSERT INTO tblInvoiceLineItems ( InvoiceID, "
strSQL = strSQL & "ProjectToCharge )"
strSQL = strSQL & "VALUES (" & lngInvoiceID & ", "
strSQL = strSQL & !ProjectID & " )"
CurrentDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
End If
rs.Close
Set rs = Nothing

That works such that if the next record has a different CustomerID
than the previous record, it will start a new invoice.

On a side note, this code:

If haveData Then 'some projects have already been handled
elsewhere
booAddIt = False
booChecked = False
Else 'no projects have been handled - add all projects
booAddIt = True
booChecked = True
End If

....can be replaced with this:

If Not haveData Then
'no projects have been handled - add all projects
booAddIt = True
booChecked = True
End If

This is because a Boolean variable is initialized as FALSE and
unless somewhere earlier in your code you might be initializing them
as TRUE, you don't need to set them to FALSE -- they will already be
FALSE.