In over my head - VBA query - help!

[GrandmasterB]

Ok, folks, I have a custom database that I'm trying to update at my company.

On my Jobs entry form there is a button that "submits" new jobs a salesperson enters to the proper people internally via email notification. This part isn't elegant, but it works.

I want to add to that button a routine that checks the current CustomerID's open invoices, finds the oldest one, and compares that to that CustomerID's Terms for Payment field in the Customer table.

So far, I've been able to add the following:

Dim TermsCheck As Variant
        
    TermsCheck = DLookup("terms", "tblcustomers", "customerID ='" & Me.Parent.CustomerID & "'")

I think TermsCheck should be an integer, but that is a story for another time.

Anyway, that piece of code returns the proper terms just fine (usually 30 days).

Now, what I need to do next sucks, as far as I can tell. I wrote the following query using the query builder to find the oldest invoice, and then viewed it as SQL code. It returns the proper oldest invoice as listed in the tblInvoice invoicing table, linked across the tblJobsSub (holds info on individual visits to a particular location) table through the parent tblJobs table (holds the information on a particular location with the customer's info) through to the tblCustomers table (which holds info on the customer who could have many sites):

SELECT tblJobs.CustomerID, Max(DateDiff("d",[invoicedate],Date())) AS [Days Old], tblInvoice.Total, tblInvoice.AmountReceived
FROM (tblJobs INNER JOIN tblJobsSub ON tblJobs.JobNo = tblJobsSub.JobNo) INNER JOIN tblInvoice ON tblJobsSub.SubID = tblInvoice.SubID
GROUP BY tblJobs.CustomerID, tblInvoice.Total, tblInvoice.AmountReceived
HAVING (((tblJobs.CustomerID)="2497A") AND ((tblInvoice.Total)<>0) AND ((tblInvoice.AmountReceived) Is Null Or (tblInvoice.AmountReceived)<[total]));

...for customerID "2497A".

How in the hell do I write the VB code that does what this SQL statement does, stores that max invoice age somewhere, and then compares it to the terms?

If I can do that, I can come up with an if/then statement a la:

If MaxInvoice > TermsCheck Then
MsgBox "Your customer sucks at paying, this job cannot be submitted"
Exit Sub
Else
...continue on with submission process
End If

Can anybody help me, or do I need to take a class or something?

Thanks!

 

[jal]

Ok, let's say you named the above query "qryMax". Well, the problem with it is that you don't refer to a specific customerID, so you might want to add a WHERE clause (I think it goes after the FROM clause).

WHERE CustomerID = @CustomerId

The "@CustomerID" is a parameter (a variable). Then in your VBA code
Dim qDef as DAO.QueryDef
Set qDef = CurrentDb.queryDefs("qryMax")
qDef.parameters("@CustomerID").value = Me.Parent.CustomerID
dim rs as Dao.Recordset
set rs = qdef.OpenRecordset
Dim max as Long
max = rs("Days Old")

 

[jal]

I forgot about the joins. Put the WHERE clause after the joins.

 

[GrandmasterB]

Well, that little bit of code, and learning about querydefs and recordsets, have cleared things up in the case that a customer does have open invoices. Thank you.

New question -- when a customer has NO open invoices, I get the error "no current record" and then the routine is canceled.

How do I handle that?



EDIT: I just found out about the .EOF and .BOF property. throwing that in there with a little if/then seems to work great.

Thanks jal!