Semi Unique Combox Record Source on Continuous Form (1 Viewer)

Briguy01 · Jan 20, 2019

I have a main form which tracks Task Information based on tblTask. There is a subform based on junction table tblTaskDetail. sfrmTaskdetail is a continuous form with a comboBox that looks up AssetID/SerialNumber.

I found another post from this forum which was close to my issue:
https://access-programmers.co.uk/for...d.php?t=102479

For my purposes I want the rowsource to not only exclude ANY Serial numbers used in tblTaskDetail instead only the ones used for the current Task.

So, if for the current task you have selected 2 Asset Serial Numbers, when selecting the third all asset serials EXCEPT for the two already selected should show up as rowsource for the combobox.

The Serial numbers should only be available in the combobox rowsource once for each task.

I included the query in the sample with a left outer join and 'Is Null' where criteria (that return serials for assets not used in any tasks). I am close but bit confused at this point.

Can Anyone help?

June7 · Jan 20, 2019

Cross posts

http://www.accessforums.net/showthread.php?t=75311 (has response)

https://www.utteraccess.com/forum/index.php?showtopic=2052271&hl=

isladogs · Jan 20, 2019

@Briguy
Please read this article about cross posting https://www.excelguru.ca/forums/showthread.php?518-Article-A-message-to-forum-cross-posters

I deleted your other thread where you added 10 nonsense posts pointlessly.
If I hadn't already seen your question, I would have banned you as a probable spammer. Please follow forum guidelines.

Briguy01 · Jan 20, 2019

Copy that.

Right after I logged in after registering I was directed to post an introduction post.

After that I attempted to post my question and sample db. Then I noticed the 10 post minimum in order to attach files. The subsequent posts to the intro commenced.

June7 has replied in the first cross post link she posted.

isladogs · Jan 20, 2019

You can post files any time in the first 10 posts as long as they are zipped.
If you fill your post total with nonsense, you could be flagged as a spammer and banned. As it was, it was just fortunate for you the order I read the posts in.

The cross-posting guidelines are there to stop people wasting time answering questions where answers had been given on another forum. In this case by June7 (female) at AF.net

MajP · Jan 20, 2019

Code:

Private Sub FilterCombo(Filter As Boolean)
    Dim strSql As String
    'Show all options
    strSql = "SELECT pkAssetID, SerialNumber FROM tblAsset "
    Me.cboAssetID.RowSource = strSql
    Me.cboAssetID.Requery
    'Code adapted from CJ_London
    'now change the rowsource filtering to include the upstream combos
    '- this will only affect the current combobox until it loses focus
    If Filter Then
        'wait for initial refresh to complete
        DoEvents
        strSql = "SELECT pkAssetID, SerialNumber FROM tblAsset where pkAssetID not in "
        strSql = strSql & "(Select fkAssetID from tbltaskdetail where fkTaskID = "
        strSql = strSql & Me.Parent.pkTaskID & ")"
        Me.cboAssetID.RowSource = strSql
    End If
End Sub

Private Sub cboAssetID_AfterUpdate()
 FilterCombo False
End Sub

Private Sub cboAssetID_GotFocus()
 FilterCombo True
End Sub

Private Sub cboAssetID_LostFocus()
 FilterCombo False
End Sub
Private Sub Form_Load()
  FilterCombo False
End Sub

Briguy01 · Jan 21, 2019

This appears to work quite well. Thanks a lot.

Quite interesting approach to the solution!

MajP · Jan 21, 2019

Quite interesting approach to the solution!

The Sql to remove the previous selected items is pretty trivial. The complicated part is that provided by CJ_London that allows you to refresh the combo boxes in a continous form without them going blank.

Briguy01 · Jan 21, 2019

I actually am experiencing some issues with the combo boxes going blank. Trying to pinpoint what is causing.

MajP · Jan 21, 2019

I actually am experiencing some issues with the combo boxes going blank. Trying to pinpoint what is causing.

Any chance you can post? The code provided is specifically to avoid just that case. Without the code it would be expected to go blank.

June7 · Jan 21, 2019

Cause is likely due to combobox RowSource is a lookup with alias. When item is not in RowSource list, record cannot display the alias. This is why cascading comboboxes don't work nicely with continuous or datasheet form. I already described this and a method to handle in other thread.

MajP · Jan 21, 2019

Cause is likely due to combobox RowSource is a lookup with alias.

No, there are no lookups are aliased fields. The code works fine on my end.

June7 · Jan 21, 2019

Not fields in table. There is lookup alias combobox used on form in database provided in other thread, which I assume is same db posted here. The combobox was originally bound to field and that will cause 'blank' box with cascading combobox.

MajP · Jan 21, 2019

Nope. It uses the approach posted by CJ_London. Where in the On focus event you first unfilter and then Filter.

https://www.access-programmers.co.uk/forums/showthread.php?t=275155&highlight=combobox

This overcomes the limitation of selections not in the rowsource to go blank. Why it works, I cannot wrap my head around.

June7 · Jan 21, 2019

Tested the code. Combobox loses display value when scrolling subform records or when focus goes to another control.

MajP · Jan 21, 2019

Code:

Tested the code. Combobox loses display value when scrolling subform records or when focus goes to another control

I see the issue. I chopped off the bottom of the code when I posted

Code:

Private Sub FilterCombo(Filter As Boolean)
    Dim strSql As String
    'Show all options
    strSql = "SELECT pkAssetID, SerialNumber FROM tblAsset "
    Me.cboAssetID.RowSource = strSql
    Me.cboAssetID.Requery
    'Code adapted from CJ_London
    'now change the rowsource filtering to include the upstream combos
    '- this will only affect the current combobox until it loses focus
    DoEvents
    If Filter Then
        'wait for initial refresh to complete
        DoEvents
        strSql = "SELECT pkAssetID, SerialNumber FROM tblAsset where pkAssetID not in "
        strSql = strSql & "(Select fkAssetID from tbltaskdetail where fkTaskID = "
        strSql = strSql & Me.Parent.pkTaskID & ")"
        Me.cboAssetID.RowSource = strSql
    End If
End Sub

Private Sub cboAssetID_AfterUpdate()
 FilterCombo False
End Sub

Private Sub cboAssetID_GotFocus()
 FilterCombo True
End Sub

Private Sub cboAssetID_LostFocus()
 FilterCombo False
End Sub

Private Sub Form_Load()
  FilterCombo False
End Sub

Need to add the lost focus event and afterupdate events to unfilter. With the modified code the only way I can break it is if the list is so long that the scroll comes up. Clicking above or below the scroll with the focus on the cmbo will cause it to go blank.

Briguy01 · Jan 22, 2019

I was just reviewing CJ-London's demo project "ContinuousCascadingCombo" and it seems to have vanishing issues as well.

Specifically when you click on the productFK control on multiple records on left side continuous form view and then click into a field on right side datasheet view.

I will test further.

I may end up attempting a hybrid approach or abandon and code in logic to not allow duplicate entry and throw up message box.

I was really hoping to make it not possible to enter duplicate by making it not possible to select in first place.

MajP · Jan 22, 2019

Ensure you add those extra events I posted. After Update and Lost Focus. If I add those then I have few if any issues. I have to really force the issue to make it fail.

You can fake it pretty well with a textbox on top of a combobox if you want to go the other route. This will eliminate any possibility of control going blank.

Briguy01 · Jan 22, 2019

I will try the extra events first.

Btw
The resizing parts of CJ-London's project are kind of cool too.

Briguy01 · Jan 23, 2019

-I copied the FilterCombo private sub into a private sub on the main form and changed the references to the subform control for combo requery.

-I added Filter combo False to Main form OnCurrent event and mainform subform control OnExit event.

-I added a little piece I am using in my main project on the continuous subform to only allow edit on blank field (which had no effect on the vanishing).

Code:

'Allow edits only on blank fields
     Me.AllowEdits = (Not Len(Me.cboAssetID & "") > 0)

Now the only vanish I get is with a subform scroll. Which I am going to try to find bit more information what events if any occur during that action.

Interesting workaround is to use record selectors and hide scroll bar. For some reason moving in the same way without the scroll bar does not appear to have the vanishing behavior.

I was messing with the render events on the subform in the subform control, sadly that did not seem to be the culprit.

Semi Unique Combox Record Source on Continuous Form (1 Viewer)

Registered User.

Attachments

AWF VIP

MVP / VIP

Registered User.

MVP / VIP

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

Registered User.

Attachments

Similar threads

Users who are viewing this thread