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

[ittechguy]

I'm trying to create a form that will assign a customer as room point of contact. Basically, it will put tblCustomer.CustomerPK into my junction table, along with tblRooms.RoomsPK.

I'm pretty sure the form needs to be unbound. The issue I'm having is I'm not sure how exactly to ensure the correct customerPK gets assigned. I think perhaps I need to create a search form so that the user can search for a customer?

[ItsMe]

It is OK to bind the form but it is not likely to be necessary. What I usually do is provide combooxes for the User to make selections. In your case, maybe a combo for the Room and a combo for the customer.

Adjust the Rowsource, Column Widths, and bound column property so you can use the Value property of the combos. You will want the Bound Column property to match the PK. Although, it is not absolutely necessary to use the Bound Column property, it is recommended.

[ittechguy]

Thanks for the fast reply.

Are you suggesting I have one combo box for customer?

I'm mostly struggling on how I have many different things that can identify a customer. For an example, LastName/FirstName is almost always unique however there are several cases when its not, for obvious reasons. Email is always unique but the user might not easily know the email. It also can be a combination of Organization, ShopName, OfficeSymbol, and their name and/or phone number.

An idea I had was to create several combo boxes with the rowsource using CustomerPK first and then the other field (like LastName). That way, if a user selects a last name, they're really selecting CustomerPK without knowing it. I'm having a hard time making this work with more than one combo box.

Also, I've never used the bound column property. I'm going to look into that.

[ittechguy]

I just discovered, I can't use several combo boxes with them all bound to CustomerPK because then I would be unable to see unique values in the combo boxes. This becomes a problem when there are more than one LastName or more than one firstname, the user is never sure which one to choose.

I'm thinking I might be able to create a search form which used a query and used combo boxes as the WHERE clause. But I'm not sure how to handle if multiple results are returned.

[ItsMe]

Maybe you can start by building a Query Object with the Query Designer. Add your table, tblCustomers to the query design surface. Drag or double click the PK. Now you have the PK as a column. This is the unique value you will want to append to the junction table. Now add additional columns, like FirstName, LastName, etc. Join additional tables if necessary. Take a look at your query in Datasheet View. This is what the User will see when your Combo is built. Adjust the properties of your combo to hide the PK. Use the Column Widths property 0,2,2,2 and the Column Count property 4


You can save the query object you built and use the name of your query object as the rowsource of your combo or you can use the SQL from the query as the Rowsource.

I created some tutorials about the combo that may be helpful.
https://www.accessforums.net/tutoria...ers-52741.html

[ittechguy]

Thanks for the link! I'm downloading those tutorials. I'm sure they'll come in handy.

I'm not so sure I want to create one combo box. I've done that before, using column width 0;2;2;2, and setting column count accordingly. The problem is that with several hundred customers, I feel it would be annoying trying to find the right customerPK.

[ittechguy]

I've watched your tutorial. It was helpful. I figure I can concatenate Rank, LastName, and FirstName into one combo box. But then I still need to be able to lookup Organization, ShopName, and OfficeSymbol. Some records in tblCustomer don't have names because they are actual shops.

[ItsMe]

Yeah, it is likely you will want to use cascading dependent combos. I know the tutorials are slow and dry, but it is likely the combo series will help you with this part of your project. It can be difficult to listen to curriculum that is review, waiting to catch info here and there that is new to you. One thing the combo series does not offer is a way to update your junction table. So, it is likely you would not adjust the Control Source of the combos. Or maybe, you could bind the form to the junction table and have the combos update directly.

[ittechguy]

Hmm.

Binding the form to the junction table might cause problems. See, I actually have two junction tables. One for Room point of contacts, and one for facility managers (for a building). An idea I had was to let the user select a customer, and then select if they want that customer to be a room point of contact or a facility manager, and then it would add a record in the appropriate table.

[ItsMe]

Somewhere you will need to use variables and VBA to append a record. Might as well use all unbound forms for the junction tables.

[ittechguy]

Ok. Thus far, I have two combo boxes. One with Rank, LastName, and FirstName concatenated together (and bound to CustomerPK). The other with Organization, ShopName, and OfficeSymbol concatenated together (and bound to Customer.PK). I have not yet worked on tblFacilityMgr, I'm focusing on tblRoomPOC right now.



Here is my code:

Option Compare Database



Private Sub cmdReset_Click()
    Me.cboShop = ""
    Me.cboFullName = ""
    Call Form_Load
End Sub
Private Sub cmdAddRecord_Click()
    If Not IsNull(Me.cboShop.Value) Or Not (Me.cboShop.Value) = "" Then
            If MsgBox("Are you sure you would like to assign that customer to that room?", vbYesNo) = vbYes Then
                   Call AddShopEntry
            Else
                   Call cmdReset_Click
            End If
     ElseIf Not IsNull(Me.cboFullName.Value) Or Not (Me.cboFullName.Value) = "" Then
            If MsgBox("Are you sure you would like to assign that customer to that room?", vbYesNo) = vbYes Then
                   Call AddNameEntry
            Else
                   Call cmdReset_Click
            End If
     End If
End Sub


Private Sub Form_Load()
    Me.cboBuildingName.Visible = False
    Me.cboRoomName.Visible = False
End Sub
Private Sub optCustIs_AfterUpdate()
    If Me.optCustIs = 1 Then
        Me.cboBuildingName.Visible = True
        Me.cboRoomName.Visible = False
    Else
        Me.cboBuildingName.Visible = True
        Me.cboRoomName.Visible = True


    End If
End Sub
Private Sub AddShopEntry()
     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
End Sub
Private Sub AddNameEntry()
     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 this room." & vbCrLf & vbCrLf 'Change to Yes/No
     Else
     Rs.AddNew
     Rs("CustomerFK").Value = Me.cboFullName
     Rs("RoomFK").Value = Me.cboRoomName
     Rs.Update
     End If
End Sub

Essentially, what I'm trying to do is call AddShopEntry if the shop combo box is not null, or call AddNameEntry if the FullName combo box is not null. I feel like I have more code than I need and am probably over complicating it. But it works, for the most part.

My biggest problem is that I am not sure how to not allow both combo boxes to not be null at the same time. If cboShop is not null and its value is bound to one CustomerPK, but cboFullName is also not null and its value is bound to a different CustomerPK, that'd get confusing.

It would be nice if there was a way that both combo boxes could not be null and it'd work. Like, that the user could select a shop and also select a name and it'd give an error message if the two did not correspond.

[ItsMe]

So you can bind a combo to a field within a table or query. You do this via the Control Source property. The Control Source property relies on the Bound Column property. If you have a bound combobox control you need to set both the Control Source and the Bound Column properties.

You can take advantage of the Bound Column property with an Unbound ComboBox Control. The Bound Column property will affect the Value property of the ComboBox.
MsgBox Me.cboRoomName.Value

I use the afterupdate event of my combos to determine whether or not a dependent combo will be visible and what the dependent combo's value is. I will add a line of code to the On Exit of a parent combo to reset the values of dependent combos. Be sure to set focus in a safe place when hiding controls.

You might want to consider creating a sub procedure for various scenarios. For instance, the option group is updated or a specific combo does not have a value. Instead of using the load event to
Me.cboBuildingName.Visible = False
Me.cboRoomName.Visible = False

Place that in a sub procedure that also resets the values. Instead of calling the custom sub in the onload, maybe use default values of the controls.

private sub noCustomerReset()

    Me.cboBuildingName.Visible = False
    Me.cboRoomName.Visible = False
    Me.cboBuildingName.Value = ""
    Me.cboRoomName.Valuse = ""

end sub

EDIT:

It would be nice if there was a way that both combo boxes could not be null and it'd work. Like, that the user could select a shop and also select a name and it'd give an error message if the two did not correspond.

You should assign a new Rowsource for each dependent combo. The Rowsource should include the Value of all parent combos' values in its WHERE clause.

/EDIT

[ittechguy]

Thanks for your help.

This is coming along. I'm trying it with an unbound form for now, I may switch to bound if I have to. I'm having a hard time making sure that both combo boxes cannot be not null.

This is my code:

SELECT tblcustomer.customerpk, 
       Trim([tblorganization.organizationname]) & ":" & Trim( 
       [tblshopname.shopname]) & 
       ":" & Trim([tblofficesym.officesym]) AS [Shop Name or Office Symbol] 
FROM   (tblorganization 
        INNER JOIN (tblofficesym 
                    INNER JOIN tblcustomer 
                            ON tblofficesym.officesympk = 
                               tblcustomer.officesymfk) 
                ON ( tblorganization.organizationpk = 
                     tblofficesym.organizationfk ) 
                   AND ( tblorganization.organizationpk = 
                         tblcustomer.organizationfk )) 
       INNER JOIN tblshopname 
               ON ( tblshopname.shopnamepk = tblcustomer.shopnamefk ) 
                  AND ( tblorganization.organizationpk = 
                        tblshopname.organizationfk );

Hope its not too confusing. The issue I'm having is with the first two lines. Its giving me the msgBox whether one combo box is null or they are both not null.

[ittechguy]

I figured it out. I had to write a massive if statement to get it to work. I have another problem.



I'm not sure how to resolve this problem, or if it even can be resolved, but the ShopName combo box rowsource is this:

SELECT tblcustomer.customerpk, 
       Trim([tblorganization.organizationname]) & ":" & Trim( 
       [tblshopname.shopname]) & 
       ":" & Trim([tblofficesym.officesym]) AS [Shop Name or Office Symbol] 
FROM   (tblorganization 
        INNER JOIN (tblofficesym 
                    INNER JOIN tblcustomer 
                            ON tblofficesym.officesympk = 
                               tblcustomer.officesymfk) 
                ON ( tblorganization.organizationpk = 
                     tblofficesym.organizationfk ) 
                   AND ( tblorganization.organizationpk = 
                         tblcustomer.organizationfk )) 
       INNER JOIN tblshopname 
               ON ( tblshopname.shopnamepk = tblcustomer.shopnamefk ) 
                  AND ( tblorganization.organizationpk = 
                        tblshopname.organizationfk );

It looks like this:



The problem I have is that because this combo box is bound to CustomerPK, the results are not unique. If I bound it to ShopNamePK or OfficeSymPK it would be unique, but I need the CustomerPK. What can I do about that? I'm trying GROUP BY now, but I can't get it working.

[ItsMe]

It does not seem like you need a JOIN on tblOrganization to tblCustomer. If the query is for the ShopName combo, start by placing tblShopName on the Designer surface. So, rename this query by placing a z in the front of the old name. Create a brand new query and place tblShopName on the Designer surface.

Now add tblCustomer and tblOrganization. Do not add tblOfficeSym. Creater a join from tblShopName to tblCustomer and tblOrganization. Double click the joins so you retrieve all records from 'tblShopName'. Add a couple of fields to the design grid and take a look at your query in Datasheet View.

You need to start with what I described above and then revisit this new query. When you revisit it, you need to replace tblCustomer and maybe tblOrganization. You need to replace one or both tables with a subquery. When you create your subquery, include table tblOfficeSym.

Having said that, you should have an idea of how your entities relate before you start building forms. You should have an idea on paper that makes sense. Easier said than done and it is likely you will hit a road block that causes you to revisit your table structure.

A little later I am going to upload an example of dependent combos that have VBA for data validation. Maybe it will give you some ideas to tackle the Null thing. I believe your biggest issue on that front is you are not causing one of those two combos to be dependent on the other one.