Limit SubForm Combo Based on Existing Records

[misscrf]

I have been searching for a way to limit the values in a combo box, so that records already added in a continuous subform can't be chosen again. Weird thing is, I'm not seeing this as a common thing to do.

I have a subform with "requirements". The sub form has a combo with requirement types. So if the main form record source is contacts, and the sub form record source is contactrequirements, how does one limit those requirement types in that combo to ones that are not in the contactrequirements table, for the main contact form's ID. I tried to set the combo's rowsource to this:

    SELECT tblReqType.ID, tblReqType.txtRequirementType, tblReqType.txtRequirementPage
    FROM tblReqType
    WHERE (((tblReqType.ID) Not In (Select [tblMContactRequirements]![FKRequirementType] From tblMContactRequirements Where [tblMContactRequirements]![FKContact] = Forms![frmMContacts]![ID] )))
    ORDER BY tblReqType.txtRequirementType;

It limits the combo, but all the records that already exist on the (continuous) sub form are blank now, because they were limited from showing.

Is this something anyone has been able to figure out successfully?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I typically provide the entire list of records in the combo box but add a column that identifies if the Requirement has already been added. I then sort so the already added are at the bottom of the list and I check the column that identifies and pop up a message box if they select the same requirement.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

You cannot use a combobox to do this, as you found out. When you filter the rowsource of the combo it cannot display values not in the rowsource. If I want to do this I fake it. I take a text box and a combobox and bind it to the same control source. I then shrink the combobox down so that you only see the "down arrow" part and place that next to the textbox. So now the textbox looks like a combo box. With a little formatting you can make the two controls look and act like a combobox. Not perfect, but works pretty well and hard to tell it is not a combo.

 

[dhookom]

I like MajP's suggestion but I believe it requires including tblReqType in the form's record source so you can bind the text box to txtRequirementType which allows viewing the desired column value. I expect you don't want to see the tblReqType.ID value.

MajP, please correct me if I'm wrong ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

Thanks to you both. I was getting nowhere with this challenge, so I changed my tact. I am leaving them in, but I did something else. These are linked SQL tables, and I added an index on the 2 fields, for the sub form's record source, of the contact requirements. The unique index is for contact foreign key, and the requirement type foreign key. This will prevent duplicates from ever getting in. The challenge now, is that I get an odbc error, if I try to add a duplicate of those 2 keys. (

http://files.engineering.com/getfil...3-93e801a00f3d&file=odbcerroruniqueindex.jpg)

I get that, when I pick a requirement type that has already been added for the same contact. I want to trap the moment they pick the value, and this doesn't trip until I leave that record (on the continuous sub form). I'm struggling to check the value they pick, before they go over to notes, or get distracted (lol). I'm now finding that it's really hard for Access to trap a sql odbc error. This is the code I've been trying, but nothing gets trapped:

Private Sub FKRequirementType_BeforeUpdate(Cancel As Integer)
Dim i As Integer
    If Nz(Me.FKRequirementType) <> 0 Then
        i = DCount("*", "tblMContactRequirements", "FKMC=" & Me.Parent!ID & " And FKRequirementType=" & Me.FKRequirementType.Value)
        If (i > 0) Then
            MsgBox "Cannot enter same requirement twice", vbOKOnly + vbExclamation, "Duplicate"
        Cancel = True
        End If
    Else
        Cancel = True  
    End If
End Sub

I tried that in before insert too. Nothing seems to stop the code from moving on. I have another code, in a public function, for checking all those values after update (which can also be applied to change, but I've been commenting that out, while I troubleshoot). That does a whole other purpose of checking the requirements that exist, and ensuring their corresponding tab control pages & sub forms are visible or not, based on if there is that requirement for that contact. I have further code, for deleting a requirement, to check for corresponding requirement type detail records, confirm deletion of those, and then resetting the visible property for their tab control tabs. The visibility, and deletion all work. This is the last piece of the puzzle I need to solve.

I'm happy to post the rest of that code, if it will be of help in figuring this out. I don't think it really matters, as this challenge is about catching what type they choose, before it's sent to SQL, checking to make sure if it would make a duplicate on the 2 key index, and if so, backing it out and backing out the event of attempting to add anything. Make it so that the form dirty is false, as it was before they tried to add the duplicate requirement.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Yes. Good point since you are probably wanting to display the txtReqType and store the ID. So the subform would need to be based on a query that includes the text. The textbox is bound to the requirement text and the combo to the ID. So this would force you to only use the dropdown and not be able to type into the text box. That would be a limitation. You would need to lock the text box. On enter you could run the code to dropdown the combobox selections, but still require the user to only select from the drop down.

 

[misscrf]

Yeah, I'm not a fan of the textbox and combo box concept, as when i do stuff like that, it ends up being like scotch tape. I'm ok with all the requirement types showing, including ones they've chosen for an existing contact. I just want to capture when they choose a requirement type on a new record. The moment it's chosen, before it's sent to sql as a new record, I want to check it against the existing records for that client. If it would trigger the index error, (ie there is already a record for that contact and the requirement type chosen) then I want to tell the user with a nice message, and back out the event, so it never happens.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I got it!!! I put the following on change of the type, and then I can check for a dup, before anything else happens. If it would be a dupe, I tell them and undo the action. If it's not, I run the function to check the requirements, in case they changed one, so I have the right forms showing.

Private Sub FKRequirementType_Change()
Dim i As Integer
    If Nz(Me.FKRequirementType) <> 0 Then
        i = DCount("*", "tblMContactRequirements", "FKMC=" & Me.Parent!ID & " And FKRequirementType=" & Me.FKRequirementType.Value)
        If (i > 0) Then
            MsgBox "Cannot enter same requirement twice", vbOKOnly + vbExclamation, "Duplicate"
            Me.Undo
        Else
            Call ShowReqs(FKMC)
        End If
    End If
End Sub

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

This is how I would handle the task as per my earlier post.
Set the Row Source to something like this (untested):

SELECT RT.ID, RT.txtRequirementType, RT.txtRequirementPage, IIf(IsNull(FKContact),"Y","N") as PickMe
FROM tblReqType RT LEFT JOIN tblMContactRequirements CR 
  ON RT.ID = CR.FKRequirementType AND CR.FKContact = Forms![frmMContacts]![ID]
ORDER BY IIf(IsNull(FKContact),"Y","N") DESC,RT.txtRequirementType

Make sure the number of columns is 4 and possibly display the PickMe column to your users. Then set the On Change code to the much "lighter":

Private Sub FKRequirementType_Change()
    [COLOR=#4E9A06]'Check the PickMe column for "N" which means it has already been selected[/color]
    If Me.FKRequirementType.Column(3) = "N" Then
        MsgBox "Cannot enter same requirement twice", vbOKOnly + vbExclamation, "Duplicate"
        Me.Undo
    End If
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Actually this demo works better than I thought. Use a dlookup for display purposes to show the text. Then on the textbox's enter event set focus to the combobox and drop it down. You can then type in the combobox because it will move in front of the textbox.

 

[misscrf]

That's a really cool solution majp. I like your stuff too, Duane. Thank you both! I love how there are so many ways to fix one challenge.

misscrf

It is never too late to become what you could have been ~ George Eliot