Using a check box on a bound form to control the NotinList combo box event to take 2 controls from form, inserting into 2 fields in another table

[AccessAllAreas]

Dear Experts

For me, this is big issue No.3 stopping me from completing this DB.

The Goal: On a bound form, with many different controls, one of which being a combo box control with a list of Managers/Team Leaders, and another being a check box indicating whether the person whose record it is is indeed a Manager/Team Leader or not. The goal is to be able to check the checkbox if they are a Manager/Team Leader, which would then update a separate table that contains all the list of the Managers/Team Leaders, adding the person's name into that record, then refreshing the combo box to show that new name as the Manager/Team Leader of themselves. And then, in reverse, if the check box is unchecked, we want to delete the record from the Manager/Team Leader table, update the combo box and then set it to blank (no selection made.)

The Form:
Bound to table: Tab_Emergency_Contact_List
Relevant Controls:
Publisher Surname (Text box)
Publisher First Name (Text box)
Group Overseer? (check box)
Group Overseer (combo box) - Data type - Number (Long integer), lookup source: SELECT [Tab_Group_Overseers].[GroupID], [Tab_Group_Overseers].[GODisplayName] FROM Tab_Group_Overseers ORDER BY [GODisplayName], [GroupID];

Here is my current code. Note: Again, I took more than one idea for doing this off of the Web and tried to convert it/ them to my needs. Hence, I am afraid it might be a mess. Sorry.

Public Sub cmbGroupOverseer_NotinList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String
Dim db As dao.Database
Dim rs As dao.Recordset

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("Tab_Group_Overseers")

' DoCmd.OpenTable "Tab_Group_Overseers"
'Exit this sub if the combo box is cleared
    ' If NewData = "" Then
        ' Exit Sub
    ' End If

' NewData = (Me.Publisher_Surname = [Tab_Group_Overseers.GroupOverseerLastName]) & (Me.Publisher_First_Name = [Tab_Group_Overseers.GroupOverseerFirstName])
' NewData = (Me.Publisher_Surname.Value = (Tab_Group_Overseers.GroupOverseerLastName)) & (Me.Publisher_First_Name.Value = (Tab_Group_Overseers.GroupOverseerFirstName))
Msg = "'" & NewData & "' is not currently in the list of Group Overseers." & vbCr & vbCr _
    & "Do you want to add it to the list?"

' i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Group Overseer")
    
If MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Group Overseer?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Tab_Group_Overseers", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Tab_Group_Overseers = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
      
    
    ' If i = vbYes Then
        ' strSQL = "Insert Into Tab_Group_Overseers ([GroupOverseerLastName]) " & _
                 "values ('" & NewData & "');"
        ' CurrentDb.Execute strSQL, dbFailOnError
        ' Response = acDataErrAdded
    ' Else: Response = acDataErrContinue
    ' End If
' DoCmd.Close , [Tab_Group_Overseers]

' If MsgBox("Do you want to add " & " & NewData & " & " as a Group Overseer?", vbQuestion + vbYesNo) = vbNo Then
        ' Response = acDataErrContinue
        ' Me.Group_Overseer = Null
    ' Else
    ' CurrentDb.Execute "INSERT INTO Tab_Group_Overseers (GroupOverseerLastName) select " & " & NewData & "
            ' Response = acDataErrAdded
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Private Sub Group_Overseer_Check_Box_Click()
Dim Response As Integer
Dim NewData As String

Call cmbGroupOverseer_NotinList(NewData, Response)


End Sub

I know it is now a mess. Been working on this for months on and off.

Any assistance to get a workable solution will be greatly appreciated.

 

[bob fitz]

Can you post a copy of the db with a few fictitious records

 

[Deleted member 147267]

I don't get the point of the checkbox when you can just allow typing the new name in the combo. However, since you want to also remove list items, why not just manage this with a popup form driven by a button or something? You have to be careful with deleting data - you can create orphan records. Usually better to flag such records as expired (with a date, for example) or in some other way.

 

[AccessAllAreas]

Hi Bob

I will clean out the records apart from a few, and let post a copy.

It will likely be to krrow, as i have never actually had to do that before myself!

Many learning curves!

 

[AccessAllAreas]

Hi Bob (and all other experts out there who are so kindly reading this thread)

Apologies for the late reply on this subject.

I have cut down the database somewhat, leaving a few employees (publishers) in the DB. I have also left some Group Overseers (managers) in the DB.

Just to reiterate - the aim is to autopopulate the Tab_Group_Overseers table with a new name of a "Group Overseer" who is a publisher in the DB by clicking on a check box on the form For_Add_New_Publisher, the control labelled "Group Overseer?". Additionally, i hope it is possible to then have theCombo box on the form "Group Overseer" to then automatically show as that publisher's name on their own publisher record. And, when that same check box is unticked, I would want the reverse to happen, that is, the name of that publisher who has been added as a Group Overseer to the Tab_Group_Overseers table to be removed from such and then deselect that name from their own "Group Overseer" drop down list, going back to Null at that point.

One kf the goals for doing it this way is to stop user input error by having to type out the name again on another form.

Hope that helps.

Many thanks for any and all assistance in this matter.

 

[Minty]

I haven't looked at your db, but if you already have these names (publishers?) listed in a table why not simply have a flag in that table IsOverrseer?
Then you would simply add or remove the flag and your combo uses that to determine who is listed in it?

 

[bob fitz]

AccessAllAreas

I've had a quick look at your db. I have no wish to be or sound unkind but IMHO you would be better off addressing more fundamental issues first, rather than the one that you posted about.

I would suggest that you:

Define your table relationships in the Relationship window
Use field names that, have NO spaces and do not use punctuation signs etc (#?/) with the exception being the underscore.
Do not use Lookup fields in tables, only on forms.

 

[AccessAllAreas]

Hi Minty and Bob

Many thanks for your kind input.

Bob, I agree with your thoughts, in principle. The table relationships are actually in place. I had to strip the DB down quite a lot in order not to over complicate matters for anyone wishing to offer help. I thought i had got it to a level which worked still for the question raised without causing more questions, but obviously i missed a lot. The relationships do actually work in the full DB. But I understand the comment.

I appreciate the other comments also. I will take them on board and action as appropriate. I am fairly new at this but not a complete novice. I have tried various Normalisation techniques, but found applying some of them actually stopped things from working. And, according to at least some in the industry, they suggest Normalisation until things stop working, and then scale back such until it does. This was my experience. Hence the limits in Normalisation in my DB.

Minty

Thanks for the suggestion on this issue. I am very happy to try that. But I have never heard of that method before. Any guidance gratefully received.

 

[Minty]

Simply add a field to your table containing the names.
I would call it IsOverSeer make it a bit field default value of 0 (or No)

Your checkbox on your form should simply update the value of IsOverSeer for the record you want to update.

In your combo box select the names that have a IsOverSeer value of -1 (Or Yes)

 

[AccessAllAreas]

Great. Thank you very much. Hopefully, I understand the idea properly and will attempt to implement. I will let you know how I get on!

 

[AccessAllAreas]

Hey Minty

Thank you for such a simply effective solution. It works (well, almost)!

If I close the form and re-open it, the combo box does/doesn't show the newly added (or recently removed) entry, as expected. However, the combo box does not show the change immediately, as I am hoping.

I am as certain as a slightly more-than-novice can be that I have not got the requery element correct.

I have tried the DoCmd.Requery method (or is it a function?). I have also tried the control requery method (or is it function?) Neither seem to work! But I am certain I am doing something incorrect.

Anyone who would like to school me about this would be gratefully received and heard.

PS I have used a select case statement set to determine what happens with message boxes during this interaction. I am not sure if that changes what method should be used and where.

 

[Minty]

You probably need to save the record you are on for the combo to reflect the changes. Until it is saved the underlying records you are looking at won't update.
Try after the update of the check box

If Me.Dirty Then Me.Dirty = False
Me.YourCombo.Requery

It should then show the updated combo records.

If not perhaps you could paste the code up as you now have it for the update.

 

[AccessAllAreas]

That worked! I didn't really understand the first line fully (as everything I read about IF statements says following a Then would be another action (which I recognise in the second line fine). But hey, it works, so I'm not complaining.

Only one thing missing to complete this - how on earth do I get the name that has just been added to the combo box to display (be selected and showing) automatically once everything else has happened?

 

[AccessAllAreas]

Sorry.Spoke too soon.

The addition and removal of the new entries to the combo box works fine. However, when I wish to cancel the update (controlled by a message box with a vbYesNo, then it doesn't cancel the operation requested. it either still adds or still removes the entry from the combo box despite No being pressed!

Now, with a checked check box, the combo box doesn't display the name in the list. And when it is unchecked, it displays the name in the list! That's the opposite way round!

 

[Minty]

What is the rowsource code in your combo?

The first line is saving the record. The .Dirty property indicates if the current record has unsaved changes i.e. is in a "Dirty" state.
Setting it to false forces a record save. To write it out in full it would be;

If Me.Dirty = True Then
    Me.Dirty = False
End if

But being intrinsically lazy and cutting the typing down is a habit I find difficult to get out of.
This is probably why your Cancel event doesn't work.

 

[AccessAllAreas]

Hi Minty

Many thanks for your further input.

I totally understand the idea of cutting down on the typing and using shortcuts instead. Years of configuring Cisco IOS devices caused me to take a similar approach. But I knew all the necessary shortcuts then and could figure out suggested code with shortcuts cause I knew and used them. Being a moderate novice in VBA in Access, I have to ask questions to understand. I hope that is ok.

So, I have implemented that clarification you outlined. Unfortunately, the cancel (pressing No) event does not stop the update of the combo box entries, either adding or removing, that would be expected if I pressed Yes to those Msgboxes! I am starting to wonder if those message boxes are doing anything at all, apart from displaying messages!

Here is the row source for the combo box that you asked about:

SELECT [Tab_Emergency_Contact_List].[Group Overseer?], [Tab_Emergency_Contact_List].[Publisher Display Name] FROM Tab_Emergency_Contact_List WHERE [Group Overseer?] = -1 ORDER BY [Publisher Display Name];

The [Group Overseer?] field is the check box.

I can post below the three sections of code that I have that pertain to this (there are some obsolete, rem'd out code in there too - just ignore):

Private Sub Group_Overseer_Check_Box_AfterUpdate()

' Requery the GOcombo box to display any newly added or removed entries in the list of GO.
' Do i add an undo statement here for when vbNo is selected from the Message Boxes?

If Me.Dirty = True Then
    Me.Dirty = False
End If
Me.cmbGroupOverseer.Requery

End Sub

Private Sub Group_Overseer_Check_Box_Click()
Dim Response As Integer
Dim NewData As String

' Call other sub to execute when GOCHKBOX is clicked

Call cmbGroupOverseer_NotinList(NewData, Response)

End Sub

Public Sub cmbGroupOverseer_NotinList(NewData As String, Response As Integer)

Dim i As Integer
Dim MsgRemove As String
Dim MsgAdd As String
Dim GroupOverseercmbboxcntl As Control

' Create variable equivalent to Publisher Display Name
NewData = Me.Publisher_Surname & ", " & Me.Publisher_First_Name

' Msg to display when adding a new GO
MsgAdd = "'" & NewData & "' is not currently in the list of Group Overseers." & vbCr & vbCr _
    & "If you click Yes, this brother's name will be added to the Group Overseer's list below " _
    & "and set to the Group Overseer for this publisher record."

' Msg to display when removing a GO
MsgRemove = "'" & NewData & "' is currently listed as a Group Overseer." & vbCrLf & vbCrLf _
        & "Do you wish to remove this brother from the list of Group Overseers?" & vbCrLf & vbCrLf _
        & "If so, press Yes." & vbCrLf _
        & "If not, press No.  This will cancel the operation."

' i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Group Overseer")
If Me.[Group Overseer?].Value = True Then
    Select Case MsgBox(MsgAdd, vbYesNo + vbQuestion, "Add New Group Overseer?")
        Case vbYes:
            MsgBox "'" & NewData & "' has been added to the list of Group Overseers.  Please go through and add publishers assigned" _
            & " to this group to have him as their Group Overseer.", vbInformation + vbOKOnly, "Success"
                        
        Case vbNo:
            MsgBox "'" & NewData & "' has NOT been added to the list of Group Overseers", vbInformation + vbOKOnly, "Operation Cancelled"
                Me.Undo
                If Me.[Group Overseer?].Value = True Then
                  Me.[Group Overseer?].Value = False
                End If
        
    End Select
    Else
        Select Case MsgBox(MsgRemove, vbYesNo + vbQuestion, "Remove Group Overseer?")
            Case vbYes:
                MsgBox "'" & NewData & "' has been removed from the list of Group Overseers.", vbInformation + vbOKOnly, "Success"
                                
            Case vbNo:
                MsgBox "'" & NewData & "' remains in the list of Group Overseers.", vbInformation + vbOKOnly, "Operation Cancelled"
                Me.Undo
                If Me.[Group Overseer?].Value = False Then
                  Me.[Group Overseer?].Value = True
                End If
        
    End Select
End If
End Sub

I am sure that this is not how a professional would do it. But it works, kind of, with input from yourselves.

Any ideas on how to fix greatly appreciated.

 

[AccessAllAreas]

Hi All

Just wondering if anyone has a thought on what I am doing wrong. To re-cap, the outstanding issue is described in this paragraph:

So, I have implemented that clarification you outlined. Unfortunately, the cancel (pressing No) event does not stop the update of the combo box entries, either adding or removing, that would be expected if I pressed Yes to those Msgboxes! I am starting to wonder if those message boxes are doing anything at all, apart from displaying messages!

All help gratefully received, as always.

 

[Minty]

You can't cancel an event by simply issuing an undo. You need to use an event that can cancel the record update.
This is normally achieved in the FORMS before update event. This is where you would perform all the validation required for a record and cancelling it means those changes aren't committed.

Unless I have misunderstood the situation, you appear to have a slightly unusual requirement to save a change to a record but not all the changes... This may well require a change of approach.

Maybe you could populate the combo in code with a value list and add/recreate the value list after the checkbox value is changed without saving the underlying record?

 

[AccessAllAreas]

Many thanks for your thoughts, Minty.

Thank you for teaching me about record level validation. That will be useful for ongoing planning.

I agree with you this is slightly unusual requirement. This is one reason why I decided to create a separate table called Tab_Group_Overseers to house all the GOs discretely. i thought that might help with Normalisation plus allow for updates within a separate entity. However, I have been shown through this discussion that does not work.

I like the sound of your idea at the end. How would I go about that?

 

[Minty]

It's not the simplest of options, however, the basics are you would open a recordset with your combo data in it and loop through it to add those items to the list
You'll need to adapt this to suit.

Public Sub procLoad_List(objCtrl As Object)
    'Retrieves a list from a table and inserts
    'loads it into the form & combo box held in objCtrl.
    'Demo code only has two fields - adjust accordingly!

    Dim sSql             As String
    Dim rs               As Recordset
    Dim i                As Integer

    'Remove any values already there
    For i = 1 To objCtrl.ListCount
        objCtrl.RemoveItem (0)
    Next i
 
    'Get new list
    sSql = "SELECT Field1 , Field2 "    ' Your Fields for the combo
    sSql = sSql & "FROM tblYourTable "  ' Your table Name
    sSql = sSql & "WHERE 1 = 1 "        ' Add any Criteria here
    sSql = sSql & "ORDER BY Field1 ASC "   ' Add any sorting
    Debug.Print sSql

    Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
 

    rs.MoveLast
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        For i = 1 To rs.RecordCount
            objCtrl.AddItem rs.Fields("Field1") & ";" & rs.Fields("Field2")
            rs.MoveNext
        Next i
    End If

    rs.Close
    Set rs = Nothing

End Sub

Call this with the name of your combo control. Use a similar technique to add the value to the combo when you change the checkbox record you are on.
Removing the values will be more complicated, as you would need to know which entry to remove, but I am guessing this is an unusual occurrence.