Invoicing and Payment Tracking

[reltub]

Also, what kind of code/query would I need to auto-populate invoice number, line item, fee name, and amount fields in my invoice form? Maybe if I post an end result I'm looking to achieve it will make things a little clearer. I will upload some examples when I get home from work.

 

[reltub]

Sometimes it helps if I work backwards. So attached is the end result I'm looking for. I can't figure out how to sum the line items, or get the line item # onto the invoice.

 

[Lightwave]

reltub

The recommended method of getting invoice numbers into a field would be to set the field type in the table definition to autonumber. This results in Access creating a new incremental number whenever a new record is created. That number will however be purely numeric but you can set it so it never repeats. It fulfills perfectly the requirements needed of an invoice number. If at all possible I would use this method otherwise you are probably looking at concatenating an autonumber field to some kind of text field which in reality has no gain other than aesthetics.

With regard to calculating the cumulative of invoice number you essentially do this the same way whether within a form or a report.

Go to the tools menu (or whatever it's called in Access 2010) and add an unbound field to the form or report.

Please note that it is IMPORTANT where on the form you add it. You are going to place a formula something like =Sum([Amount]) in that unbound field and the scope of the calculation will depend on where it is placed on the form or report. If you place this field in form footer it will add all field values from the field Amount in the main part of the form. If you place it in the record part of the form it will only calculate it for the individual record (this can be useful)

On reports if you put it in the group footer it will add all items in a group and if you put it in the report footer it will add all items in the whole report.

You can pretty much do any summary calculation providing you get the syntax correct in these summary field so you could. Access limits you to certain functions but you can make up your own if you can't find a pre built one.

=Sum(([Amount] - [Discount]))

=Sum(Cos([Amount])

Etc...

Note that Access will throw a wobbly if you ask it to sum NULL values

You get round this by asking it to ignore null values

=Sum(Nz([Amount]))

Nz() function wrapped around any field name will prevent problems with null values.

 

[AccessMSSQL]

retlub:
If you want to have the system autogenerate invoices my answer assumed that each customer would be billed the same quarterly, or yearly amounts throughout the year. Yes, you do need tblFeeTypes AND tblCustomerFees. tblCustomerFees will be to store the repeating charges that always get billed quarterly or yearly. If you have fees that will vary the system can't autogenerate that - you have to manually put the information in.

What I just realize I think you want to do is be able to print the "reports" out automatically for quarterly and yearly customers (but you will already have the data entered into the tables?)

 

[reltub]

That's exactly what I want to do!

 

[reltub]

Since I'm not using a form to generate the invoices, there is no data in tblInvoice and tblInvoiceDetail, so the line items aren't populating on my reports.

 

[reltub]

Here are some screenshots of what I have so far..

 

[AccessMSSQL]

Well..give me some time and I'll see if I can post another sample with some more examples.

 

[Joe8915]

Reltub, ss you can see there is alot of great help here. I won't chime in since you are getting alot of hints on how to build your db.

 

[reltub]

Yes you all have been VERY helpful and I want to thank you again for taking the time to help as I've learned a lot. Quick side question - on my reports, how can I filter on load by 3 'AND' criteria e.g., [tblHistory].[Difference]<>0 AND [tblHistory].[BillingPeriod]="Q" AND [tblHistory].[BrokerPay]="No" ?

 

[reltub]

Currently I'm getting a 'data type mismatch' error.

 

[AccessMSSQL]

pass the where clause in your open statement:
docmd.OpenReport "reportname",acViewPreview,,"[tblHistory].[Difference]<>0 AND [tblHistory].[BillingPeriod]='Q' AND [tblHistory].[BrokerPay]='No'"

As long as your report recordsource includes a join to tblHistory.

on another note, have not had time yet to put together another example of running reports for your invoices. How is that going for you?

 

[AccessMSSQL]

Datatype mismatch - check to make sure your tblHistory.Difference field is indeed a numeric field. Also, if your BrokerPay field is Yes/No you need to change it to BrokerPay = 0 for no or -1 for Yes

 

[reltub]

I actually decided to go in a different direction. Now, that tblHistory is basically going to include all the invoice info. Instead of using line items and posting payments to certain line items, I'm posting payments to certain invoices and printing invoices when amountdue-amountpaid <>0. I tried to narrow down the db but it includes so much sensitive info that it is hard to post a sample - very frustrating.

The statement you provided above didn't work. I'm getting 'you can't switch to a different view at this time' error. I tried the default view but it still won't work. All the info comes from tblHistory. Shouldn't I just be able to go to the 'data' tab and type in ,,"[tblHistory].[Difference]<>0 AND [tblHistory].[BillingPeriod]='Q' AND [tblHistory].[BrokerPay]='No'" into the filter field and put 'filter on load' to YES?

 

[reltub]

Ah ok. BrokerPay is indeed yes/no field. I'll try changing it

 

[reltub]

I have an append query that I'm using to 'generate' invoices to an invoice history table.

qryCreateInvoices:
CustomerID
BillingID
InstitutionName
ReportDate
BillingDate
FeeName
FeeAmount

My question is how can I create a multi-select list box that includes the institution name on a form that will make the InstitutionName criteria in qryCreateInvoices the values selected in the list box?

 

[reltub]

Here's what I have so far. I have frmCreateInvoice which contains an unbound listbox and a button to run a query. These are named lstCustomers and cmdOpenQuery, respectively. lstCustomers row source is as follows:

SELECT DISTINCT tblCustomers.InstitutionName FROM tblCustomers UNION SELECT "All" FROM tblCustomers;

cmdOpenQuery has some VBA on click event, which is supposed to allow users to select one or many Institution Names from the listbox and run qryCreateInvoice when clicked. The part in red is the trouble. The code works when I use strSQL = "SELECT * FROM tblCustomers, but when I try to select certain fields to use, I run into problems. Here is the code:

Option Compare Database
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT tblCustomers.CustomerID, tblCustomers.BillingID, [Enter Report Date] AS ReportDate, Date() AS BillingDate, tblFeeTypes.FeeName, tblCustomerFees.FeeAmount, tblCustomers.ContactName, tblCustomers.Address, tblCustomers.BrokerPay, [tblCustomers]![City]+", "+[tblCustomers]![State]+" "+[tblCustomers]![Zip] AS CityStateZip, tblCustomers.Cancelled

FROM (tblCustomers INNER JOIN tblCustomerFees ON tblCustomers.CustomerID = tblCustomerFees.CustomerID) INNER JOIN tblFeeTypes ON tblCustomerFees.FeeTypeID = tblFeeTypes.FeeTypeID

WHERE (((tblCustomers.Cancelled)=False))"

'Build the IN string by looping through the listbox
For i = 0 To lstCustomers.ListCount - 1
If lstCustomers.Selected(i) Then
If lstCustomers.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstCustomers.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [InstitutionName] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryCreateInvoice"
Set qdef = MyDB.CreateQueryDef("qryCreateInvoice", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCreateInvoice", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstCustomers.ItemsSelected
Me.lstCustomers.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Sub

 

[AccessMSSQL]

been off forums for about a week. Did you resolve your issue?

 

[reltub]

I did, thanks for all your help! I would never have completed this if it weren't for the awesome people on this forum. Thank you again. I will try to get some samples of it together so maybe it can help someone else trying to do the same thing.