Listbox to assign transactions to billing statement (1 Viewer)

[yeasir01]

I Have a table that stores transactions details that occur during a normal business period.

*The Columns on that table are as followed*
ID (PK)
Date
Client_ID (CK)
Volume
Amount
Statement_ID (default is blank)

at the end of the cycle, I would like to select which transactions get assigned to the opened billing statement. I set up a form that looks similar to an invoice. A client is selected from the combo box then a date range is used to filter and display the data within that date range on the popup form.

On the popup form, there is a listbox which has the same columns with "ID" hidden, the objective is to update a selected list item record with the current statement ID. If the row is selected Update Statement_ID to equal the current ID or if it is unselected then update statement_id to null. I included some photos for reference. Iv tried researching the matter but, haven't had any luck with putting a code together.

I am self-taught & continuously learning providing sample VBA is greatly appreciated as I can study it, modify, test it then post back the working code for someone else in the community to benefit.

I tried posting photos according to instruction, but it doesn't seem to work for g drive accounts.
https://drive.google.com/open?id=1w_hy38zKyvvZaa9Gadhn-SrY6TlXtEXW

 

[CJ_London]

for security reasons, I (and many others) won't access files on googledrive or similar. Suggest copy your files to your c drive and upload from there.

As to your question, can you explain a bit more why you want users to select what appears in the opened billing statement - wouldn't this just be all unreconciled items? no user intervention required.

 

[yeasir01]

for security reasons, I (and many others) won't access files on googledrive or similar. Suggest copy your files to your c drive and upload from there.

As to your question, can you explain a bit more why you want users to select what appears in the opened billing statement - wouldn't this just be all unreconciled items? no user intervention required.

Sorry just noticed the additional options below, was using the insert image above.

Those transactions perform other functions such as calculating the daily over/short, if there are excluded then it will throw those numbers off so all transactions need to be inputted, however, if there's a dispute about a certain transaction I can omit that transaction until the dispute is closed then I can choose to include and discount the client on the next billing cycle.

 

[yeasir01]

Currently, I'm setting the statement Id in the table for illustration purposes.

 

[arnelgp]

Only the id you want to change? Hiw abiut the volume and amount, are they not updated and summed up?

 

[yeasir01]

Only the id you want to change? Hiw abiut the volume and amount, are they not updated and summed up?

Those are edited by another form. Only the statement_id on the transaction side.

 

[arnelgp]

something like this on your pop up listbox afterupdate:

private sub listboxname_afterupdate()
dim var as variant
if me.listboxname.itemsselected.count=0 then
forms!mainform!id=null
else
for each var in me.listboxname.itemsselected
if nz(forms!mainform!id, 0) <> me.listboxname.itemdata(var) then
forms!mainform!id=me.listboxname.itemdata(var)
forms!mainform.dirty=false
exit for
next
end if
forms!mainform.recalc
end sub

 

[CJ_London]

So to clarify, if you don't want to include a record, you remove the ID value?

Also, can transactions remain over more than one period? In which case what happens to the statement ID?

 

[yeasir01]

So to clarify, if you don't want to include a record, you remove the ID value?

Also, can transactions remain over more than one period? In which case what happens to the statement ID?

Yes. If there is no statment_id it will continuously show in the add transaction popup form until it is assigned to a statement.

 

[CJ_London]

Ah OK - so the action is to populate with a statement number.

If you are using a multi select listbox, then Arnel's solution should do the job.

 

[yeasir01]

something like this on your pop up listbox afterupdate:

private sub listboxname_afterupdate()
dim var as variant
if me.listboxname.itemsselected.count=0 then
forms!mainform!id=null
else
for each var in me.listboxname.itemsselected
if nz(forms!mainform!id, 0) <> me.listboxname.itemdata(var) then
forms!mainform!id=me.listboxname.itemdata(var)
forms!mainform.dirty=false
exit for
next
end if
forms!mainform.recalc
end sub

Thank you for the response. I'm not understanding the logic here, I don't think this will give me the desired result. What I was expecting was an update record function where the specific record is highlighted as shown in the photo. The statement_id would be passed from the main form behind the popup. Could you elaborate?

 

[yeasir01]

Thank you for the response. I'm not understanding the logic here, I don't think this will give me the desired result. What I was expecting was an update record function where the specific record is highlighted as shown in the photo. The statement_id would be passed from the main form behind the popup. Could you elaborate?

Forgot to mention the subform on the billing statement form only displays transactions with a related statment_id.

 

[CJ_London]

why not use a subform and have a click or doubleclick event on the statementID control -something like

if isnull(statementID) then statementID=parent.statementID else statementID=null

 

[yeasir01]

why not use a subform and have a click or doubleclick event on the statementID control -something like

if isnull(statementID) then statementID=parent.statementID else statementID=null

I would like to ultimately hide the statment_id column.

Heres a vba code I found, it looks like it adds new rows which is not what I need. can this be modified to work?

    Dim n As Integer
    Dim strCriteria As String
    Dim strSQL As String
    
    With Me.lstCourses
        For n = .ListCount - 1 To 0 Step -1
            strCriteria = "StudentID = " & Nz(Me.StudentID, 0) & " And CourseID = " & .ItemData(n)
            If .Selected(n) = False Then
                ' if item has been deselected then delete row from table
                If Not IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
                   strSQL = "DELETE * FROM StudentCourses WHERE " & strCriteria
                   CurrentDb.Execute strSQL, dbFailOnError
                End If
            Else
                ' if item has been selected then insert row into table
                 If IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
                   strSQL = "INSERT INTO StudentCourses (StudentID, CourseID, StatusID) " & _
                        "VALUES(" & Me.StudentID & "," & .ItemData(n) & ",1)"
                   CurrentDb.Execute strSQL, dbFailOnError
                End If
            End If
        Next n
    End With

Just to clarify need the statement ID past from main form to popup listbox if highlighted, if not remove to leave blank.

 

[CJ_London]

I would like to ultimately hide the statment_id column.

OK, in that case put the code on a different field

 

[arnelgp]

Have you tried it so as to say it will not work?
Just replace the mainform on the code with the name of your form and the ID in the code with statement_id.

 

[CJ_London]

yes - remove the first part of the if statement since you don't want to delete anything, and modify the second part to update your record

it is essentially what Arnel suggested.

 

[arnelgp]

It is better not to use Update query on selecting/deselecting from listbox because the in-memory record is on the form. You will only get warning message that the record has change when you leave or save the form.

 

[yeasir01]

Have you tried it so as to say it will not work?
Just replace the mainform on the code with the name of your form and the ID in the code with statement_id.

I get a compile error "next without for"

 

[yeasir01]

It is better not to use Update query on selecting/deselecting from listbox because the in-memory record is on the form. You will only get warning message that the record has change when you leave or save the form.

That's good to know, thanks!