Inactive records?

[Gasman]

Evening everyone,

I have a table tblCommissionType that holds records that a combobox on a form uses as source. I added an Active field to the table so as to be able to keep the list to a more manageable size and to ignore types no longer used.
That part works fine.

However if I select a record as inactive, then for existing records in tblCommission that have been processed that have the ID of the CommissionType in them, the CommissionType does not show up in the form?

How can I get the best of both worlds please?

I only have the one form that acts as a subform that processes the Commission records at the moment.

TIA

 

[bob fitz]

Can you post a copy of the db with a few dummy records to show the problem

 

[Gasman]

Hi Bob,

Will do on Monday, as the db is in work.

Thanks for the reply.

 

[Gasman]

Hi Bob,
Please find test DB attached.

To see the problem.
Step 1.
Log in as John Cutter, pw JohnCutter
Select Commissions, Normal Commission
Bradley Forrest has an adjustment for £50 as the last record.
Exit database

Step2
Login again as Paul Steel pw PaulSteel
Select Maintenance/Maintain Commission Types
Select Adjustment record and clear the Active flag.

Now exit the DB and repeat Step 1
Record now has no value in the combo box, as the source only selects Commission Types that are active.
What I am trying to achieve is only show Commission Types that are active when entering new records, not realising that they disappear for processed records that have used them in the past.
The reason for the two logins is only certain forms are available to John Cutter and amending Commission Types is not one of them.


Thank you for looking at this.

 

[bob fitz]

Now exit the DB and repeat Step 1
Record now has no value in the combo box, as the source only selects Commission Types that are active.

Don't understand the problem.
What record and what combo box are you referring to.

 

[Gasman]

Hi Bob,
I've realised you can do all of this as Paul Steel, no need for different logins.

Login as Paul Steel. Select Employees and the Commission tab. It is the same form used in Maintaining Commissions from the Switchboard.
Select Bradley Forrest.
You will see Picture 1.
Then using the previous steps clear the active flag for Adjustments. Refresh all and then go back to the Employee and Commission tab.
You will then see Picture 2.
The control in question is Commission Type. the form is frmCommission

 

[CJ_London]

think you need a cascading combo solution

The 'default' rowsource should be for all commission types

on entering the commission type combo have code to restate the rowsource filtered

on exiting the combo, have code to restate the rowsource unfiltered.

 

[Gasman]

Thank you CJ,
I can't quite get it right.
The control row source is no longer filtered.

Opening the form, the commtype shows correctly, but when I click into a new record, it is filtered out from the combo as I am trying to do, but the previous commtype with that entry is also cleared?

Private Sub cboCommType_GotFocus()
Dim strSQL As String
strSQL = " SELECT tblCommissionType.CommTypeID, [CommTypeName] & "" - "" & [ProductName] AS CommName, tblCommissionType.EnableCommAmount, tblCommissionType.AppendDate, tblCommissionType.EnableAmount, tblCommissionType.CommTypeName, tblCommissionType.CommActive " & _
" FROM tblCommissionType INNER JOIN tblProduct ON tblCommissionType.ProductID = tblProduct.ID "

If Me.NewRecord Then
    strSQL = strSQL & " WHERE (((tblCommissionType.CommActive)=True))"
End If
cboCommType.RowSource = strSQL
cboCommType.Requery

End Sub
Private Sub cboCommType_LostFocus()
Dim strSQL As String
strSQL = " SELECT tblCommissionType.CommTypeID, [CommTypeName] & "" - "" & [ProductName] AS CommName, tblCommissionType.EnableCommAmount, tblCommissionType.AppendDate, tblCommissionType.EnableAmount, tblCommissionType.CommTypeName, tblCommissionType.CommActive " & _
" FROM tblCommissionType INNER JOIN tblProduct ON tblCommissionType.ProductID = tblProduct.ID "
cboCommType.RowSource = strSQL
cboCommType.Requery
End Sub

 

[CJ_London]

you don't need the requery command. When changing the rowsource it is automatically requeried.

May not make a difference but I always use the enter and exit events for this sort of thing rather than gotfocus/lostfocus.

Also not sure why you are limiting the change to new records? What if someone edits an existing one?

 

[Gasman]

you don't need the requery command. When changing the rowsource it is automatically requeried.

OK, I'll remove that

May not make a difference but I always use the enter and exit events for this sort of thing rather than gotfocus/lostfocus.

I'll place the code there

Also not sure why you are limiting the change to new records? What if someone edits an existing one?

My thinking was if you are still able to edit the commtype then you would need all the values, not filtered? Admittedly this is unlikely to happen as soon as the records are processed (processed date) they are locked, but would like to allow for the eventuality.

I'll try again tomorrow.

 

[Gasman]

Had a thought last night.
When the record have been processed the data entry controls are locked, so I test for that instead.
I still have an issue which I have yet to pin down, whereby if I enter the control and then enter the next record (which is a new record) the control still goes blank. re enter the relevant record and it appears again. Do not touch it and it works fine.

I've also tried checking the ProcessedDate for NULL and the same thing happens?:banghead:

What I am trying to achieve with these checks is whether I need to show the whole list or not. On a new record, unlocked control or null Processed date, I only want what is marked as active.
When the control is locked or ProcessedDate has a value, then I can show the whole list whether active or not.