Records in combo box catch22 (1 Viewer)

Minty

AWF VIP
Local time
Today, 06:12
Joined
Jul 26, 2013
Messages
10,355
Excuse me for chirping in, but if your order lines can be updated / added to on different days, then silently record the order_date per line, and use that to determine the visibility of the items?

CJ's method is definitely the way to go with this.

We have the it with employee ID's in combos, albeit simply are the here now or were they when the record was created...
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
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

So for a new record what value do you have in Me.TransactionDate. My equivalent field is NULL and it's falling over on this.
I'm sure I'm getting my dates the wrong way round but I've checked this over and over. I'm sure I'll get there
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:12
Joined
Sep 21, 2011
Messages
14,048
I have the default value of Date() in the form, else I believe you would need to use the NZ function again for your date?
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
I have the default value of Date() in the form, else I believe you would need to use the NZ function again for your date?

Thanks for your patience!! And thanks to Minty for chirping in. I think I'm getting there but I'm not sure I've properly explained what I'm really after which has probably led to the confusion in my head. Maybe if I use an example.
In the following the prefix combo shows the combo box. The fields are item, date added to order. I say order as I started off this thread with order thinking it would simplify it. I still think it does so I shall continue with it. The items in the combo box may have a date when discontinued.

Comboboxitem1 12/12/2018
Comboboxitem2 13/12/2019
Comboboxitem3 12/12/2018
.
ComboboxitemN 15/12/2018

The above appear on a continuous form. Each Line is a record in order table.
Lets say item 2 was discontinued on the 16/12/2018.
If the user clicks on the 2nd line he will want to see Item2 in the item list.
If the user clicks on line 1 or 3 through line N then item2 shouldn't appear in the list. Nor should it appear for a new record. This way he can't select a discontinued item other than the one that is already there.

I think the behavior I get from CJ_London and Gasman's solutions is that for new records it's perfect no discontinued items. For rows with a discontinued item I obviously have the item displayed but it also appears in the combo box for all the other rows that aren't discontinued.

Now i'm pretty sure, although I've done so many iterations I probably wouldn't be able to replicate it, that with either method I'd either get item2 appearing in the list for each line including the new record OR I'd get a blank entry for where it did exist and not elsewhere so the above would look like (the underscore _ represents a space.

Comboboxitem1 12/12/2018
____________ 13/12/2019
Comboboxitem3 12/12/2018
.
ComboboxitemN 15/12/2018



Now using bit from everyone's response I've ended up with something that is nearly what I want. I'm using the current event to dynamically build the rowsource for each row. So if they click on a row that isn't a discontinued item the list doesn't have a discontinued item in. If they click on a row that is a discontinued item then the list does. The new record will not have a discontinued item in.

This works except for 2 different scenarios. When the form is first opened. I believe the current record at this stage is the first displayed so the rest of the rows will take on the characteristic of the first row. Now if this is a discontinued item then every row will include the discontinued item. This doesn't matter because when you click on subsequent rows it refreshes itself. If the first row doesn't contain a discontinued item all rows will NOT contain discontinued items which is a problem as when the form is first displayed they will contain a blank. You could argue that as soon as the combo is clicked for those rows then the item reappears but it looks rubbish. Similarly if you click on a record/row which has an item that is not discontinued then all the discontinued items disappear (blanked out) again.

So am I misunderstanding the solutions or am I asking for something that is impossible to achieve.

Thanks for all of you for persevering with me!
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:12
Joined
Sep 21, 2011
Messages
14,048
I think you are over complicating it.?

My combo is a lookup to get a value to store in a record.
If I remove it from the list (not active) as you have found, when it was active and you visit a record that has it at that time, the entry is blank as that is no longer in the combo.

So in the current event I just effectively requery the recordsource just by setting it , but because I am comparing to a date, in my case the transaction date, the entry appears.

If I did not have a default value for transaction date on a new reccord, then it would be null and fail, so I set the default value.

I see no need for the discontinued date in the combo source, unless you wanted to display it for some reason.

So the date is only used in selecting which records to bring in to the recordsource for the combo.

Does that explain it any better.?
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
I think you are over complicating it.?

My combo is a lookup to get a value to store in a record.
If I remove it from the list (not active) as you have found, when it was active and you visit a record that has it at that time, the entry is blank as that is no longer in the combo.

So in the current event I just effectively requery the recordsource just by setting it , but because I am comparing to a date, in my case the transaction date, the entry appears.

If I did not have a default value for transaction date on a new reccord, then it would be null and fail, so I set the default value.

I see no need for the discontinued date in the combo source, unless you wanted to display it for some reason.

So the date is only used in selecting which records to bring in to the recordsource for the combo.

Does that explain it any better.?

I told you dates do my head in! I need to go into a darkened room and read this over a few times.

Right so my combo uses the item id to do a look up. The item id is from the order table. I use it to display the correct description for the item and too allow the user to change it to another it item.

I think I'm doing what you do. The problem I have is when the form is first displayed the rows with the discontinued item(s) are blank. If I click on them they are refreshed. So in your DB do you get blanks displayed?
 

Minty

AWF VIP
Local time
Today, 06:12
Joined
Jul 26, 2013
Messages
10,355
This appears to be more of an aesthetic issue than data...

Have you considered simply having a text box with the item description in it (it wouldn't be limited to discontinued items), and hiding a combo under it, that only had current items. Size things so that the combo drop down arrow is visible to the right of the text box.

When you clicked on the combo down arrow, by default the combo comes to the front to allow you to see it, make your selection, and when you tab off it goes back to where it belongs, hidden behind your recently updated textbox...

Just an alternative approach.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:12
Joined
Sep 21, 2011
Messages
14,048
So in your DB do you get blanks displayed?

No, as when I select a record or move records the record source is refreshed.

Can you upload your DB or at least the parts needed to do what you are trying.?

It sounds like you need to use the order date to compare against the discontinued date.?
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
This appears to be more of an aesthetic issue than data...

Have you considered simply having a text box with the item description in it (it wouldn't be limited to discontinued items), and hiding a combo under it, that only had current items. Size things so that the combo drop down arrow is visible to the right of the text box.

When you clicked on the combo down arrow, by default the combo comes to the front to allow you to see it, make your selection, and when you tab off it goes back to where it belongs, hidden behind your recently updated textbox...

Just an alternative approach.

I had thought of displaying the description separately so hiding combo is a good idea.
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
No, as when I select a record or move records the record source is refreshed.

Can you upload your DB or at least the parts needed to do what you are trying.?

It sounds like you need to use the order date to compare against the discontinued date.?

I'm now using the date
Yes I can but remember it's not really an order. It'll take me some time to cut it down just to that form so maybe early next week if you don't mind
 

ryetee

Registered User.
Local time
Today, 06:12
Joined
Jul 30, 2013
Messages
952
No, as when I select a record or move records the record source is refreshed.

Can you upload your DB or at least the parts needed to do what you are trying.?

It sounds like you need to use the order date to compare against the discontinued date.?

Right I've got a wee database
I've got what I think is your code in it and also mine
There's a taxt box in the header defaulted to "2" which runs your code
Change to "1" runs mine - best change the default in design mode to see happens when first loaded
Yours shows all discontinued items in the combo for historic records. Mine only shows hen if the actual record itself is discontinued.
Yours doesn't show discontinued items in new record. Mine shouldn't either.
You can select a discontinued item in historic records. I have a frig in so it can't.
Mine shows blank lines from time to time if you for instance shift focus from a discontinued historic item and open another rows combo
In the new record yours will show a discontinued item if the date it was discontinued is today (see item3)
To set item as disccontinued use item form
To see behaviour see item price group form

Hope this makes sense.
 

Attachments

  • messaround.accdb
    740 KB · Views: 43

Gasman

Enthusiastic Amateur
Local time
Today, 06:12
Joined
Sep 21, 2011
Messages
14,048
Sorry, I cannot open that, I get 'unrecognized format' even with the shift key. I only have 2007 and get that constantly. :-(

I would have thought that it was OK to change the historic item, as long it was not discontinued at that time.
If the item is discontinued today, I would have also thought that would be at the end of the day?, so still valid?, if not take the = out of >= in the comparison.

Hopefully someone who can open you DB can chip in.?
 

Users who are viewing this thread

Top Bottom