Data entry form to add customer as a room point of contact?

[ittechguy]

Thanks again for the help.

I've solved the problem of the null combo boxes. It was really easy. I simply set the other combo box's value to nothing in the AfterUpdate event.

I have a very long if statement to deal with if it is in one combo box, or the other. Also if they're a facility manager or a room POC. You were right about setting the focus to a save place. I keep getting errors, need to fix that. Also, I still haven't created a private sub for noCustomerReset, its on the list. I'm aware there's lots of areas like that I can reduce code, and I will.

Private Sub cmdReset_Click()
    Me.cboShop = ""
    Me.cboFullName = ""
    Call Form_Load
End Sub
Private Sub cmdAddRecord_Click()
    Dim isPerson As Boolean
    Dim isRoomPOC As Boolean
    isPerson = False
    isPerson = isPerson Or Not Me.cboFullName.Value = "" And Not IsNull(Me.cboFullName.Value)
    isRoomPOC = False
    isRoomPOC = isRoomPOC Or Not Me.cboRoomName.Value = "" And Not IsNull(Me.cboRoomName.Value)
    If isRoomPOC Then
            If isPerson Then
                   If IsNull(Me.cboRoomName.Value) Or (Me.cboRoomName.Value) = "" Then
                            If MsgBox("Please ensure you've selected a Room name. Would you like to try again?", vbYesNo) = vbYes Then
                           
                            Else
                                   Call cmdReset_Click
                            End If
                    Else
                            If MsgBox("Are you sure you would like to assign that customer to that room?", vbYesNo) = vbYes Then
                                   Call AddNamePOCEntry
                            Else
                                   Call cmdReset_Click
                            End If
                    End If
             Else
                    If IsNull(Me.cboRoomName.Value) Or (Me.cboRoomName.Value) = "" Then
                           If MsgBox("Please ensure you've selected a room name. Would you like to try again?", vbYesNo) = vbYes Then
                           
                           Else
                                   Call cmdReset_Click
                           End If
                    Else
                           If MsgBox("Are you sure you would like to assign that customer to that building?", vbYesNo) = vbYes Then
                                   Call AddShopPOCEntry
                           Else
                                   Call cmdReset_Click
                           End If
                    End If
            End If
    ElseIf isPerson Then
                   If IsNull(Me.cboBuildingName.Value) Or (Me.cboBuildingName.Value) = "" Then
                            If MsgBox("Please ensure you've selected a Building name. Would you like to try again?", vbYesNo) = vbYes Then
                           
                            Else
                                   Call cmdReset_Click
                            End If
                    Else
                            If MsgBox("Are you sure you would like to assign that customer to that room?", vbYesNo) = vbYes Then
                                   Call AddNameFacilityEntry
                            Else
                                   Call cmdReset_Click
                            End If
                    End If
             Else
                    If IsNull(Me.cboBuildingName.Value) Or (Me.cboBuildingName.Value) = "" Then
                           If MsgBox("Please ensure you've selected a building name. Would you like to try again?", vbYesNo) = vbYes Then
                           
                           Else
                                   Call cmdReset_Click
                           End If
                    Else
                           If MsgBox("Are you sure you would like to assign that customer to that building?", vbYesNo) = vbYes Then
                                   Call AddShopFacilityEntry
                           Else
                                   Call cmdReset_Click
                    End If
            End If
    End If
    
End Sub


Private Sub Form_Load()
    Me.cboBuildingName.Visible = False
    Me.cboRoomName.Visible = False
    Me.Box41.Visible = False
    Me.Box44.Visible = False
    Me.Label43.Visible = False
    Me.Label45.Visible = False
    Me.cmdAddRecord.Visible = False
    Me.optCustIs.Value = 0
End Sub
Private Sub optCustIs_AfterUpdate()
    If Me.optCustIs = 1 Then
        Me.cboBuildingName.Visible = True
        Me.cboRoomName = ""
        Me.cboRoomName.Visible = False
        Me.Box41.Visible = True
        Me.Label43.Visible = True
        Me.Label45.Visible = False
        Me.Box44.Visible = False
        Me.cmdAddRecord.Visible = True
    Else
        Me.cboBuildingName.Visible = True
        Me.cboRoomName.Visible = True
        Me.Box44.Visible = True
        Me.Label45.Visible = True
        Me.Box41.Visible = False
        Me.cmdAddRecord.Visible = True


    End If
End Sub
Private Sub cboShop_AfterUpdate()
     If Not IsNull(Me.cboShop.Value) Or Not (Me.cboShop.Value) = "" Then
             Me.cboFullName = ""
             Me.lstFacilityMgr.RowSource = " SELECT tblFacilityMgr.CustomerFK, tblBuilding.BuildingName AS Building " _
            & " FROM tblBuilding INNER JOIN tblFacilityMgr ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK " _
            & " WHERE (((tblFacilityMgr.CustomerFK)=[cboShop]))"
            Me.lstRoomPOC.RowSource = " SELECT tblRoomPOC.CustomerFK, tblBuilding.BuildingName, tblRoom.RoomName " _
            & " FROM (tblBuilding INNER JOIN tblRoom ON tblBuilding.BuildingPK = tblRoom.BuildingFK) INNER JOIN tblRoomPOC " _
            & " ON tblRoom.RoomPK = tblRoomPOC.RoomFK " _
            & " WHERE (((tblRoomPOC.CustomerFK) = [cboShop]))" _
            & " ORDER BY tblBuilding.BuildingName, tblRoom.RoomName "
     End If
        Me.lstFacilityMgr.Requery
End Sub
Private Sub cboFullName_AfterUpdate()
    If Not IsNull(Me.cboFullName.Value) Or Not (Me.cboFullName.Value) = "" Then
             Me.cboShop = ""
             Me.lstFacilityMgr.RowSource = " SELECT tblFacilityMgr.CustomerFK, tblBuilding.BuildingName AS Building " _
            & " FROM tblBuilding INNER JOIN tblFacilityMgr ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK " _
            & " WHERE (((tblFacilityMgr.CustomerFK)=[cboFullName]));"
            Me.lstRoomPOC.RowSource = " SELECT tblRoomPOC.CustomerFK, tblBuilding.BuildingName, tblRoom.RoomName " _
            & " FROM (tblBuilding INNER JOIN tblRoom ON tblBuilding.BuildingPK = tblRoom.BuildingFK) INNER JOIN tblRoomPOC " _
            & " ON tblRoom.RoomPK = tblRoomPOC.RoomFK " _
            & " WHERE (((tblRoomPOC.CustomerFK) = [cboFullName])) " _
            & " ORDER BY tblBuilding.BuildingName, tblRoom.RoomName "
     End If
     Me.lstFacilityMgr.Requery
End Sub
Private Sub AddShopFacilityEntry()
     Set db = CurrentDb
     Set Rs = db.OpenRecordset("tblFacilityMgr")
     If DCount("*", "tblFacilityMgr", "[CustomerFK]=" & Me.cboShop & " And [BuildingFK] =" & Me.cboBuildingName & "") > 0 Then
             MsgBox "That customer is already assigned as a Facility Manager for this building." & vbCrLf & vbCrLf 'Change to Yes/No
     Else
     Rs.AddNew
     Rs("CustomerFK").Value = Me.cboShop
     Rs("BuildingFK").Value = Me.cboBuildingName
     Rs.Update
     End If
     Me.lstFacilityMgr.Requery
End Sub
Private Sub AddShopPOCEntry()
     Set db = CurrentDb
     Set Rs = db.OpenRecordset("tblRoomPOC")
     If DCount("*", "tblRoomPOC", "[CustomerFK]=" & Me.cboShop & " And [RoomFK] =" & Me.cboRoomName & "") > 0 Then
             MsgBox "That customer is already assigned as a point of contact for this room." & vbCrLf & vbCrLf 'Change to Yes/No
     Else
     Rs.AddNew
     Rs("CustomerFK").Value = Me.cboShop
     Rs("RoomFK").Value = Me.cboRoomName
     Rs.Update
     End If
     Me.lstRoomPOC.Requery
End Sub
Private Sub AddNameFacilityEntry()
     Set db = CurrentDb
     Set Rs = db.OpenRecordset("tblFacilityMgr")
     If DCount("*", "tblFacilityMgr", "[CustomerFK]=" & Me.cboFullName & " And [BuildingFK]=" & Me.cboBuildingName & "") > 0 Then
             MsgBox "That customer is already assigned as a Facility manager for that building." & vbCrLf & vbCrLf 'Change to Yes/No
     Else
     Rs.AddNew
     Rs("CustomerFK").Value = Me.cboFullName
     Rs("BuildingFK").Value = Me.cboBuildingName
     Rs.Update
     End If
     Me.lstFacilityMgr.Requery
End Sub
Private Sub AddNamePOCEntry()
     Set db = CurrentDb
     Set Rs = db.OpenRecordset("tblRoomPOC")
     If DCount("*", "tblRoomPOC", "[CustomerFK]=" & Me.cboFullName & " And [RoomFK]=" & Me.cboRoomName & "") > 0 Then
             MsgBox "That customer is already assigned as a Point of Contact for that room." & vbCrLf & vbCrLf 'Change to Yes/No
     Else
     Rs.AddNew
     Rs("CustomerFK").Value = Me.cboFullName
     Rs("RoomFK").Value = Me.cboRoomName
     Rs.Update
     End If
     Me.lstRoomPOC.Requery
End Sub

Anyhow, I will do what you said w/ this query. I think you're right about the join between tblOrganization and tblCustomer, but I don't think thats going to resolve my problem. More I think about it, I'm going to run into problems when I get at work and put real data here because although every customer has an OrganizationName, not all customers have a ShopName, and not all Customers have an OfficeSymbol (its one, the other, or both).

Still doesn't resolve the bigger issue. I'm getting duplicate values because its bound to CustomerPK. I'm trying to use GROUP BY to remove them, but I'm definitely doing something wrong.

[ItsMe]

I am not positive I have a great understanding of your 'either this combo or the other combo' thing. Perhaps I am not devoting enough energy in order to understand. Regardless, the principles are the same, data validation. Giving the User the choice which combo depends on the other is simply additional code to control the flow of statements.

You might discover the duplicate records issue will solve itself as you employ the proper subquery.

Reducing code is OK. I know a lot of people like to point a finger at others and proclaim how they can name that tune in two notes. Sometimes, and often, I will add code so I can understand what the heck is going on. It is best to use sub procedures (like you are doing) and or functions. In some languages these are referred to as helper methods. Your code should read like a book. The sub procedures should have names that indicate what is going on. This way, an event triggered by a User that drives the application will simply call out a series of procedure names. As you read the names, it should tell a story about what is going on. Each name does something specific and does it well.

To help illustrate, I am attaching an example here. It is an example from one of my video tutorials. Except, I added some validation to it. It may not be exactly how I would implement it but it is damn near. The following is a combo's rowsource.

SELECT tblMain.MainKey, tblMain.CustomerKey, tblMain.PO FROM tblMain WHERE (((tblMain.CustomerKey)=[Forms]![frmCascade]![cmbCustomer]));

This SQL is using an object name to satisfy a dynamic parameter and the SQL is placed directly in the combo control's Rowsource property. Another approach would be to use VBA and assign the Rowsource to the combo. The idea here is that one combo is dependent on another.

Anyway, food for thought.

[ittechguy]

In the AfterUpdate event of cboShop, I placed this code:

Me.cboFullName = ""

Then in the AfterUpdate event of cboFullName, I placed this code:

Me.cboShop = ""

Basically it forces only one combo box to not be null at a time.

As for the query, I re-did the query. I also removed OrganizationFK from tblCustomer and I added OfficeSym to tblShopName. Thats something I should've done a long time ago. I realized how I had it wasn't the best way but at the time the benefits to changing it didn't seem to outweigh the hassle I'd have to go through (copying data over).

Here is what I have now:

SELECT tblCustomer.CustomerPK, tblOrganization.OrganizationName, tblShopName.ShopName, tblShopName.OfficeSym
FROM (tblOrganization INNER JOIN tblShopName ON tblOrganization.OrganizationPK = tblShopName.OrganizationFK) INNER JOIN tblCustomer ON tblShopName.ShopNamePK = tblCustomer.ShopNameFK;

It still doesn't resolve the problem of duplicate data.

[ittechguy]

Figured it out! The field which is the primary key needs to have max(pk) and the other field or fields need to be GROUP BY. I was using GROUP BY tblCustomer.CustomerPL.

This is the SQL for the combo box:

SELECT Max(tblCustomer.CustomerPK) AS MaxOfCustomerPK, Trim([tblOrganization.organizationName]) & ": " & Trim([tblShopName.ShopName]) & ": " & Trim([tblShopName.OfficeSym]) AS [Shop Name or Office Symbol]FROM (tblOrganization INNER JOIN tblShopName ON tblOrganization.OrganizationPK = tblShopName.OrganizationFK) INNER JOIN tblCustomer ON tblShopName.ShopNamePK = tblCustomer.ShopNameFK
GROUP BY Trim([tblOrganization.organizationName]) & ": " & Trim([tblShopName.ShopName]) & ": " & Trim([tblShopName.OfficeSym]);

The form is 95% complete now. Lookin sharp too. Building and room combo boxes are hidden when the form opens. The only thing I need to do is create a way for users to delete records. I'm thinking I'll have them select a record in one of the list boxes and then hit a "delete" button. Not sure how to do that yet.

[ItsMe]

Glad to hear you were able to work through the issues. It is good to keep in mind that Group By, Having, and Select Distinct can create performance issues. I always try to retrieve records using only SELECT and WHERE, even if I have to apply a WHERE clause to a subquery.

[ittechguy]

Can the same thing be achieved using a where clause? If so, how?

[ItsMe]

Can the same thing be achieved using a where clause? If so, how?

If I knew exactly how or if it was possible, I would have mentioned that. So you would have to go through the process I described earlier. When you create a join, ask yourself if it is necessary. Perhaps there is a way to retrieve the data needed using a subquery and where criteria. There might be a way to use a variable from a source that was not considered before. I will hardcode values to get what I am after and then ask myself where that hardcoded value might exist at the time I need it.

If you still have issues getting unique values to join on, see if employing a SELECT DISTINCT or GROUP BY in a subquery makes sense. Use DISTINCT or GROUP BY on the smallest dataset possible. Before using a HAVING statement, see if it makes sense to use a WHERE clause in a subquery to a GROUP BY statement.

One thing to keep in mind is that if your tables violate Normalization Rules, you will always have difficulties creating queries.

[ittechguy]

One other question. Do you know how I can add a feature which allows users to delete records? Basically, what I'm thinking is they'd select a customer and then it would display the room POCs and the building facility managers, and then they could select one from the list box and hit a Delete button.

I've use dao recordsets to add new records as you can see in my code above, I'm not sure how to delete the record.

[ItsMe]

I use code to manage referential integrity and cascade deletes. On a more complex DB I will create custom classes for appends and deletes and use Enumerations to code the table names. The other approach is to use intrinsic Constraints and Referential integrity Rules. The Relationships Window in Access will offer a lot of tools. However, I am not well versed with this and I am not sure if you can or how to manage Junction tables using the Relationships Window.

You can use DAO. It is probably easiest to use the Query Builder to generate SQL statements that do stuff. With SQL in hand, you can execute your SQL statements. You can use the SQL as standalone or you can nest it inside a DAL loop. For instance, with a junction table, you may need to nest some SQL within DAO.

Having said that, I will usually not allow deletes. Instead, a Boolean will indicate active or not active. I will typically have several Booleans. One might indicate where, within Production, the record is. It is important to distinguish a Boolean that the User would use as a Delete.

The exception I will use for not allowing deletes is when the User is first creating a record. If the user is entering organic data, I will give them the choice to "Back Out". They can Delete stuff they, themselves, entered.

[ittechguy]

Thanks for the help.

You've mentioned boolean before. What do you mean by that? I assume you have a field which indicates status and then it can be something like Operational, In Storage, Etc. This is the setup I currently have for Equipment, but I didn't think it was necessary for storing room POC information.

Essentially, I want to know which customer is the current POC for which room. If Mr. Bob is no longer a POC for Building A, Room 100 I do not see the need to store that he used to be the POC, I'd prefer I just deleted that record in tblRoom POC. Which, being a junction table, it'd only contain RoomFK and CustomerPK.

[ItsMe]

... I do not see the need to store that he used to be the POC...

It is entirely up to you. I will incorporate historical data into the Design of the Database, starting with the ERD. If historical data is not recognized as important at the time of design, it may be understood to be important at a later date. If data fits as Historical, I do not delete. I may not include a User Interface to interact with it, but the data is in the tables.

A Boolean can have three states, Yes, No, and Null.

In Access, you can create a Yes/No field. You can also adjust its format. IIRC, you can choose Yes/No or True/False or -1/0

The format does not matter. I will format as True False and assign -1 or 0 in VBA code. In SQL statements, I use True or False.

[ittechguy]

I see.

So for most my tables, a yes/no field will work fine. However for equipment, there is more than 2 fields. To deal with this, I created a field within tblEquipment called "EquipmentStatusFK" and I liked it to the PK in tblEquipmentStatus. This table contains PK 1 "Operational", PK 2 "In Storage", PK 3 "In Maintenance", PK 4 "Removed from inventory", etc. etc. Then in the vba code I've used If/then statements to handle it. If EquipmentStatusFK is 1 then....


I came up with that myself and it seems to work. Am I going about it the correct way? The only issue I see is that I've hard coded the fields from tblEquipmentStatus. But I don't ever foresee those numbers/text changing.

[ItsMe]

Rather than using an If Then ElseIf statement to control the Flow of Statements, I will include the Boolean column within a SELECT query. So the original Recordsource or Recordset will either retrieve a specific Record/Row or it will not. For instance a Boolean would default as -1 when a new record is created. If a User decides this Record should be deleted, I provide an interface for them to change the value from -1 to 0, aka True To False or Yes to No.

A SELECT statement will include a WHERE clause to include MyYesNoField = -1

Allowing a User to UPDATE the MyYesNoField field to 0 is the same as deleting, from their point of view. This is because all of the Recordsources for the forms, combos, etc include a WHERE clause MyYesNoField = -1