Records in combo box catch22 (1 Viewer)

ryetee · Mar 15, 2019

This isn't exactly what I've go tut it's easier to explain this way!

I have 2 tables. Items and Orders.
Some of the items are longer and have been discontinued.
The order form it is bound to a table that contains the item id.
I have a combo box. The control source is the item ID from the order table.
The row source is a select from the items table.
Now when an item can no longer be ordered it becomes discontinued. It is left on the item table but can not be used for FUTURE orders but should be visible for historic orders.
Now the record source for the for the combo box doesn't include those that have been discontinued and looks like "SELECT Items.ID, Items.Description, Items.[Is Discontinued] FROM Items WHERE (((Items.[Is Discontinued])=False));" Now this works as the record doesn't appear in the list now and therefore can not be picked for future orders however those orders that used to have a discontinued item in now show a blank where it is displayed.

I can get around this by taking off the where in the above select and validating the item instead but the user down't want to see it in the combo box. Am I missing something here?

Ranman256 · Mar 15, 2019

you should have another query, qsAllItems
(current and discontinued)
to use for reporting purposes.

the qsActiveItems, (where Discontinued = false) is for the combo.
tho Items.[Is Discontinued] does not need to be in the SELECTED part, only in the criteria.

Gasman · Mar 15, 2019

I handled it like this. This way I get to see all historic caseworkers.

HTH

Code:

Private Sub Form_Current()
Dim strCWSource As String, strActive As String, strOrder As String
strCWSource = "SELECT Lookups.ID, Lookups.Data From Lookups"
strCWSource = strCWSource & " WHERE ((Lookups.DataType) = 'Email')"
strActive = " And ((Lookups.DataActive) = True)"
strOrder = " ORDER BY Lookups.Data"

Me.CaseWorker.RowSource = strCWSource

If Me.NewRecord Then
    Me.CaseWorker.RowSource = strCWSource & strActive
    Me.TransactionDate.SetFocus
End If
Me.CaseWorker.RowSource = Me.CaseWorker.RowSource & strOrder
'Debug.Print Me.CaseWorker.RowSource

End Sub

CJ_London · Mar 15, 2019

the issue you have is with your discontinued field - there are only two options - either it is discontinued or it isn't. Generally speaking it is not a good basis for a field

better instead to use a discontinued date field. if it is populated, it's discontinued, if it is blank it is not - giving you the same answer as your discontinued field.

But you can do more because it is a date - you can determine the status for any given date by comparing the given date with the discontinued date.

e.g. in your example in post#1

where not (me.orderdate>nz(discontinueddate,me.orderdate))

ryetee · Mar 16, 2019

Ranman256 said:
you should have another query, qsAllItems
(current and discontinued)
to use for reporting purposes.

the qsActiveItems, (where Discontinued = false) is for the combo.
tho Items.[Is Discontinued] does not need to be in the SELECTED part, only in the criteria.

Not quite sure I understand what you are saying.
I was simplifying what I have in my original post but I have actually got a query that includes current and discontinued in my record source for the form. Even in my example the table items will have both anyway.

Are you suggesting therefore to replace the combo control from SELECT Items.ID, Items.Description FROM Items WHERE (((Items.[Is Discontinued])=False)); to a query that is much the same? IF so I've tried that and getting the same results so I'm probably misunderstanding you.

ryetee · Mar 16, 2019

Gasman said:

I handled it like this. This way I get to see all historic caseworkers.

HTH

Code:

Private Sub Form_Current()
Dim strCWSource As String, strActive As String, strOrder As String
strCWSource = "SELECT Lookups.ID, Lookups.Data From Lookups"
strCWSource = strCWSource & " WHERE ((Lookups.DataType) = 'Email')"
strActive = " And ((Lookups.DataActive) = True)"
strOrder = " ORDER BY Lookups.Data"

Me.CaseWorker.RowSource = strCWSource

If Me.NewRecord Then
    Me.CaseWorker.RowSource = strCWSource & strActive
    Me.TransactionDate.SetFocus
End If
Me.CaseWorker.RowSource = Me.CaseWorker.RowSource & strOrder
'Debug.Print Me.CaseWorker.RowSource

End Sub

Thanks. I'll give it a go if I can work out what it should be in my system!!

ryetee · Mar 16, 2019

CJ_London said:
the issue you have is with your discontinued field - there are only two options - either it is discontinued or it isn't. Generally speaking it is not a good basis for a field

better instead to use a discontinued date field. if it is populated, it's discontinued, if it is blank it is not - giving you the same answer as your discontinued field.

But you can do more because it is a date - you can determine the status for any given date by comparing the given date with the discontinued date.

e.g. in your example in post#1

where not (me.orderdate>nz(discontinueddate,me.orderdate))

Ok, I like that idea. My example isn't actually an order as I was making the post easier to understand and, the main reason, easier for me to get my point across! This discontinued field appears all over the place in the system so I will go down the date route. There isn't actually a date on the table I'm using but there actually should be!! So I'm going to add it in and give this a whirl.

CJ_London · Mar 16, 2019

dates are always better flags for these sort of situations - often with a 'start' or 'new' date field - by the same token with your 'old order' example, if a users has the opportunity to change the item for something else, you probably would not want them choosing something that didn't exist at that time.

Another benefit is you can enter a discontinued/end/to date in the future now and it won't have any impact until that date is reached - although you wouldn't be able to use the 'if it is populated, it's discontinued, if it is blank it is not ' rule, you would use the 'me.orderdate>nz(discontinueddate,me.orderdate' rule

there will still be times where perhaps a simple y/n flag is sufficient

ryetee · Mar 16, 2019

CJ_London said:
the issue you have is with your discontinued field - there are only two options - either it is discontinued or it isn't. Generally speaking it is not a good basis for a field

better instead to use a discontinued date field. if it is populated, it's discontinued, if it is blank it is not - giving you the same answer as your discontinued field.

But you can do more because it is a date - you can determine the status for any given date by comparing the given date with the discontinued date.

e.g. in your example in post#1

where not (me.orderdate>nz(discontinueddate,me.orderdate))

I've very quickly tried this so I now have as my row source

SELECT Items.ID, Items.Description FROM Items WHERE not (me.dateadded>nz(datediscontinued,me.dateadded))

Dateadded is a new field on the the bound table and also a control on the form itself. datediscontinued is a new field on the item file.

When I run this I have one or 2 problems. Firstly I get asked to enter a parameter field for me.dateadded. I enter a date say of 1/1/2018. The date discontinued of a particular item i have is 7/3/2019. I get the same behaviour as before, ie I can't select the said item from the list anymore which is good, but where it already existed the control is blank instead of displaying the description of the item

********************UPDATE!!!****************
I've changed me.dateadded to just dateadded and it's got over the problem of asking for a parameter value. I now see my description in the control but I can still select it from the control as well.

ryetee · Mar 18, 2019

CJ_London said:
dates are always better flags for these sort of situations - often with a 'start' or 'new' date field - by the same token with your 'old order' example, if a users has the opportunity to change the item for something else, you probably would not want them choosing something that didn't exist at that time.

Another benefit is you can enter a discontinued/end/to date in the future now and it won't have any impact until that date is reached - although you wouldn't be able to use the 'if it is populated, it's discontinued, if it is blank it is not ' rule, you would use the 'me.orderdate>nz(discontinueddate,me.orderdate' rule

there will still be times where perhaps a simple y/n flag is sufficient

See my reply t your first post. I've changed as you have suggested but I'm still getting he same problem. If the date is in the past the record quite rightly doesn't show in the combo box, nut the combo box is now blank for the item that was discontinued.
Maybe I wasn't clear in my example.
Let's say an order is being built up over a couple of days as the client adds to it.So when you go into his order you may see some order lines that contain an item that has recently been discontinued (in the combo box). So the behaviour I want is for the discontinued item still visible if if was already on the list but that item not available in the combo box for adding any more. My solution and, if I understood yours, your solution give be a blank line in the combo box for those items that have been discontinued. Hope that makes it clearer!

ryetee · Mar 18, 2019

Gasman said:

I handled it like this. This way I get to see all historic caseworkers.

HTH

Code:

Private Sub Form_Current()
Dim strCWSource As String, strActive As String, strOrder As String
strCWSource = "SELECT Lookups.ID, Lookups.Data From Lookups"
strCWSource = strCWSource & " WHERE ((Lookups.DataType) = 'Email')"
strActive = " And ((Lookups.DataActive) = True)"
strOrder = " ORDER BY Lookups.Data"

Me.CaseWorker.RowSource = strCWSource

If Me.NewRecord Then
    Me.CaseWorker.RowSource = strCWSource & strActive
    Me.TransactionDate.SetFocus
End If
Me.CaseWorker.RowSource = Me.CaseWorker.RowSource & strOrder
'Debug.Print Me.CaseWorker.RowSource

End Sub

OK this sort of works in that for new records the item that has been discontinued is no longer in the list but for historic items it has to be otherwise it gets displayed as a blank if present historically. Which means that the user will see said discontinued item if if he wants to change one of the historic items.

Gasman · Mar 18, 2019

CJ_London's method is a much better method, though think you would need to correct/update the date field to reflect the true situation.

Admittedly mine works without amending any data, but a workaround at best.

I don't think letting a user change historic items is a good idea? I generally lock the record/form when record is meant to be completed.?

ryetee said:
OK this sort of works in that for new records the item that has been discontinued is no longer in the list but for historic items it has to be otherwise it gets displayed as a blank if present historically. Which means that the user will see said discontinued item if if he wants to change one of the historic items.

ryetee · Mar 18, 2019

Gasman said:
CJ_London's method is a much better method, though think you would need to correct/update the date field to reflect the true situation.

Admittedly mine works without amending any data, but a workaround at best.

I don't think letting a user change historic items is a good idea? I generally lock the record/form when record is meant to be completed.?

I could't get CJ_London's method to work probably because I din't fully appreciate what he was trying to tell me. To me it was a more flexible meth using a date to using a yes/no field but it was always going to end in does it appear on the list or not. Unfortunately if it was if not then the historic ones come out as blank.

It's not actually historic data, nor is it an order. Was easier to explain that way! Yours solution works for me not that I've found a way of locking the combo if the record is now currently discontinued and all i need is some validation stating that a discontinued item is not valid for an existing record. It would have been good not to have to bother with extra validation and I thought CJ_London's method would have covered it but not for me!

Gasman · Mar 18, 2019

You are just comparing two dates. The discontinued items should have a date of when discontinued

ryetee · Mar 18, 2019

Gasman said:
You are just comparing two dates. The discontinued items should have a date of when discontinued

Yes I know which is effectively a yes/no box isn't it?

IS orderdate <= datediscontinued is either a yes o a no?

I think I'm missing the point if this works.

Gasman · Mar 19, 2019

The point is you now know *when* the item is discontinued.

So for the combo you select all where date is empty, but because of the NZ function, you use a date of your choice (Date())? to show those for new records.

As the 'historic' dates are < or = discontinued dates they still show.

You have to decide whether you would use the = as well, depending on your process, which is why CJ_London went with > Nz(DiscontinuedDate,YourDate)

ryetee · Mar 20, 2019

Gasman said:
The point is you now know *when* the item is discontinued.

So for the combo you select all where date is empty, but because of the NZ function, you use a date of your choice (Date())? to show those for new records.

As the 'historic' dates are < or = discontinued dates they still show.

You have to decide whether you would use the = as well, depending on your process, which is why CJ_London went with > Nz(DiscontinuedDate,YourDate)

I tried this and I got blanks in the history. I must have messed the dates up so will look again as I like this method.

Gasman · Mar 20, 2019

I think you still need to requery on record change like I did, just that you can write a simple statement for the RowSource, where I was changing mine piecemeal.?

Strangely enough I was thinking on how to implement this instead of my method on the coach back from my holiday. Sad or what.?

In my case I will use the TransactionDate to compare against

Gasman · Mar 21, 2019

Just implemented CJ_London's method.

I use Date() as that is when I add new records, so want the current active caseworker list, otherwise the deactive date is compared to when the transaction was entered and still shows historic, now inactive caseworker names.

The code is in the Current event of the form, so changes as I move record.

Result!

Code:

strCWSource = "SELECT Lookups.ID, Lookups.Data From Lookups WHERE Lookups.DataType = 'Email' And nz(Lookups.DeActiveDate,Date()) >= #" & Format(Me.TransactionDate,"mm/dd/yyyy") & "# ORDER BY Lookups.Data"
Me.CaseWorker.RowSource = strCWSource

ryetee · Mar 21, 2019

Gasman said:
Just implemented CJ_London's method.

I use Date() as that is when I add new records, so want the current active caseworker list, otherwise the deactive date is compared to when the transaction was entered and still shows historic, now inactive caseworker names.

The code is in the Current event of the form, so changes as I move record.

Result!

Code:

strCWSource = "SELECT Lookups.ID, Lookups.Data From Lookups WHERE Lookups.DataType = 'Email' And nz(Lookups.DeActiveDate,Date()) >= #" & Me.TransactionDate & "# ORDER BY Lookups.Data" Me.CaseWorker.RowSource = strCWSource

I'm looking at it at the moment. I hate dealing with dates. To think I did wave theory 40 years ago at college and can't get my head round 2 dates! If you were thinking about this on your way back from holiday then I'm not giving up!

Records in combo box catch22 (1 Viewer)

Registered User.

Well-known member

Enthusiastic Amateur

Super Moderator

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Enthusiastic Amateur

Registered User.

Similar threads

Users who are viewing this thread