Can my form fill in a field, yet allow me to override?

[David K]

Each customers is sent to us from a salesman, and we provide service for 1-7
days before the activity ends. A new charge is generated for each day of
service.

Employee #1 uses the customer service form to generate a bill, and includes
the name of the salesman. Employee #2 uses the form to generate a bill on
the next day, and has to look up the first bill, in order to know which
salesman to record. The same thing happens every day until the activity ends.

With each new referral for service, there's a strong chance it will be due
to a different salesman, so the customer number and salesman can't be rigidly
linked. But the employees don't like having to go find the most recent bill
to know which salesman to credit. Can the form propose a name, based on the
most recent prior one?

 

[Curis]

There may very well be a much easier method for this, but one method might be
to create a function that will find the Employee on the most recent Invoice
for a particular Customer.

Below is something very quickly compiled on the top of my head, and makes
certain assumptions regarding table and field names, etc. You would then
simply set the DefaultValue of the text box to
"=GetMostRecentBill([txtCustomerID])".
-------------

Private Function GetMostRecentBill(ByVal lngCustomerID As Long) As String
On Error GoTo Err_Handler

Dim db as DAO.Database, rs as DAO.Recordset
Dim stSQL as String, stOutput as String

stSQL = "SELECT tblEmployees.ID, tblEmployees.EmployeeName
Max(tblInvoices.InvoiceDate) AS LatestInvoiceDate FROM tblInvoices INNER JOIN
tblEmployees ON tblInvoices.EmployeeID = tblEmployees.ID GROUP BY
tblInvoices.CustomerID HAVING (((tblInvoices.CustomerID)=" & lngCustomerID &
"));"

Set db = CurrentDb()
Set rs = db.OpenRecordset(stSQL,,dbReadOnly)

With rs
If Not (.bof and .eof) Then
stOutput = !EmployeeName
End If
End with

GetMostRecentBill = stOutput

Exit_Handler:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Function

Err_Handler:
MsgBox Err.Number & " – " & Err.Description
Resume Exit_Handler
End Function

----------------

Again, there is probably a much simpler method to accomplish this, in which
case I am certain that the Access gurus that reside here (and who possess
much more knowledge than I do) will happily offer it to you.

In any case, I hope this helps,
Jeff