Problem with split database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have had trouble with splitting an old database causing bugs, and I have
now tried decompiling it first, but I still have a problem with text boxes on
a form no longer working. They are the boxes on the clients form showing the
date and amount of the last invoice. The control source for last invoice
amount is shown as =LastInvoiceAmount([Customer Code]). 'Customer Code' is
clear enough as it is a field in the current table, but can anyone suggest
where I should look for LastInvoiceAmount and why it does not work on the
front end? Would it be safer to go back to the unsplit database?

Thanks for any help.
Dudley
 
Sounds like it might be a function. Open any code module, use Ctrl-F to open
the Find command, set it for the entire database and look for
LastInvoiceAmount.

As far as I'm concerned, it's never safer to use an unsplit database.
 
Thanks very much for your help. I have found the function and it is:

Function LastInvoiceAmount(Client As String) As Currency
Dim Invs As Recordset
Dim LastDate
Dim LastIN As Long

LastDate = "never"
LastIN = 0
On Error GoTo LastInvoiceAmountNo
Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst

Do While Invs.EOF = False
If Invs![Customer Code] = Client And (LastDate = "never" Or
Invs![Creation Date] >= LastDate) Then
LastDate = Invs![Creation Date]
LastIN = Invs![Invoice Number]
End If
Invs.Move 1
Loop

LastInvoiceAmountNo:
If LastIN = 0 Then
LastInvoiceAmount = 0
Else
LastInvoiceAmount = InvoicePriceIncVAT(LastIN)
End If
End Function

Could the problem be that it is looking for CurrentDb() in the front end and
if so how do I set it to look in the back end?

Thanks
Dudley

Douglas J. Steele said:
Sounds like it might be a function. Open any code module, use Ctrl-F to open
the Find command, set it for the entire database and look for
LastInvoiceAmount.

As far as I'm concerned, it's never safer to use an unsplit database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dudley said:
I have had trouble with splitting an old database causing bugs, and I have
now tried decompiling it first, but I still have a problem with text boxes
on
a form no longer working. They are the boxes on the clients form showing
the
date and amount of the last invoice. The control source for last invoice
amount is shown as =LastInvoiceAmount([Customer Code]). 'Customer Code' is
clear enough as it is a field in the current table, but can anyone suggest
where I should look for LastInvoiceAmount and why it does not work on the
front end? Would it be safer to go back to the unsplit database?

Thanks for any help.
Dudley
Click to expand...
Click to expand...
 
I have had trouble with splitting an old database causing bugs, and I have
now tried decompiling it first, but I still have a problem with text boxes on
a form no longer working. They are the boxes on the clients form showing the
date and amount of the last invoice. The control source for last invoice
amount is shown as =LastInvoiceAmount([Customer Code]). 'Customer Code' is
clear enough as it is a field in the current table, but can anyone suggest
where I should look for LastInvoiceAmount and why it does not work on the
front end? Would it be safer to go back to the unsplit database?
Click to expand...

It's a function, probably in a Module in the modules tab. Douglas showed you
how to find it.

My guess is that the function uses the SEEK method to find the last invoice
for a given customer code. SEEK works with local tables, but not with linked
ones; you'll need to either change how the recordset is defined, or use the
FindFirst method instead of Seek. If you find the code for LastInvoiceAmount
you can post it here, someone should be able to help get it working.

And I fully agree with Douglas. *Split the database*.

John W. Vinson [MVP]
 
No, the problem is the dbOpenTable parameter in the OpenRecordset method.
You can't use dbOpenTable with a linked table.

Realistically, though, that function is poorly written. The following should
work much better:

Function LastInvoiceAmount(Client As String) As Currency
On Error GoTo Err_LastInvoiceAmount

Dim LastDate As Date
Dim LastIN As Long

LastDate = Nz(DMax("[Creation Date]", "Invoices", _
"[Customer Code] ='" & Client & "'"), 0)
If LastDate > 0 Then
LastIN = Nz(DLookup("[Invoice Number]", "Invoices", _
"[Creation Date] = " & Format(LastDate, "\#yyyy\-mm\-dd\#")), 0)
End If

End_LastInvoiceAmount:
If LastIN = 0 Then
LastInvoiceAmount = 0
Else
LastInvoiceAmount = InvoicePriceIncVAT(LastIN)
End If
Exit Function

Err_LastInvoiceAmount:
MsgBox "Error " & Err.Number & ": " & _
Err.Descriptiong
Resume End_LastInvoiceAmount

End Function

This assumes that [Customer Code] is a text field. If it's numeric, use

LastDate = Nz(DMax("[Creation Date]", "Invoices", _
"[Customer Code] =" & Client), 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dudley said:
Thanks very much for your help. I have found the function and it is:

Function LastInvoiceAmount(Client As String) As Currency
Dim Invs As Recordset
Dim LastDate
Dim LastIN As Long

LastDate = "never"
LastIN = 0
On Error GoTo LastInvoiceAmountNo
Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst

Do While Invs.EOF = False
If Invs![Customer Code] = Client And (LastDate = "never" Or
Invs![Creation Date] >= LastDate) Then
LastDate = Invs![Creation Date]
LastIN = Invs![Invoice Number]
End If
Invs.Move 1
Loop

LastInvoiceAmountNo:
If LastIN = 0 Then
LastInvoiceAmount = 0
Else
LastInvoiceAmount = InvoicePriceIncVAT(LastIN)
End If
End Function

Could the problem be that it is looking for CurrentDb() in the front end
and
if so how do I set it to look in the back end?

Thanks
Dudley

Douglas J. Steele said:
Sounds like it might be a function. Open any code module, use Ctrl-F to
open
the Find command, set it for the entire database and look for
LastInvoiceAmount.

As far as I'm concerned, it's never safer to use an unsplit database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dudley said:
I have had trouble with splitting an old database causing bugs, and I
have
now tried decompiling it first, but I still have a problem with text
boxes
on
a form no longer working. They are the boxes on the clients form
showing
the
date and amount of the last invoice. The control source for last
invoice
amount is shown as =LastInvoiceAmount([Customer Code]). 'Customer Code'
is
clear enough as it is a field in the current table, but can anyone
suggest
where I should look for LastInvoiceAmount and why it does not work on
the
front end? Would it be safer to go back to the unsplit database?

Thanks for any help.
Dudley
Click to expand...
Click to expand...
Click to expand...
 
Back
Top