Form to add records to table

[ittechguy]

I'm building a form which first allows users to search for a customer by Last Name, First Name, Organization, Shop Name, or office symbol.

I then have five text boxes, each bound to their respective fields (lastname, firstname, etc). These display the result of the search form so that the user knows what customer they are viewing.

At which point, I have a combo box that allows a user to select a building name. Then there is an option group which allows the user to select if the customer is a Building Facility Manager (option 1) or a Room Point of Contact (option 2).

If they are a building facility manager, I need a button to appear (visibility = true) which says "add customer to building." I need to then take the customer ID (tblCustomer.CustomerPK) from the customer search form and the Building ID (tblBuilding.BuildingPK) from the building name combo box and I need to add those IDs into tblFacilityMgr. I have not worked with SQL insert, so this is confusing to me.

If they are a room point of contact, I need a list box to appear which displays the rooms within the building which was selected in cboBuildingName. Then they need to select one of the rooms in that list box. At which point, a button should appear that says "Add customer to room." Then, much like w/ facility managers, the customer ID and the room ID (tblRooms.RoomsPK) needs to be added into a junction table, tblRoomsPOC.

Attached is a photo of the form thus far. lstRoomsInBldg is hidden until someone selects a building name.

Here is the vba code that I have so far. I think if I could figure out how to add the values of the Customer ID (from results of search form) and building ID (from cboBuildingName) into the correct tabe, I could easily finish the rest of the form myself. I believe that would be INSERT INTO. I'd really appreciate it if someone at least pointed me in the right direction.

Option Compare Database
 
Private Sub Form_Load()
'make some things not visible on load
 
    Me.lstRoomsInBldg.Visible = False
    'Me.RecCnt.Visible = False
  '  Me.btnAssignPOC.Visible = False
End Sub
Private Sub cmdSearch_Click()
Dim strWhere As String
    Dim lngLen As Long
    Dim startStr As String
    If Not IsNullOrEmpty(Me.cboSearchLastName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
    End If
    If Not IsNullOrEmpty(Me.cboSearchShopName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
    End If
    If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
    End If
    Call MsgBox(strWhere, vbOKOnly, "Debug")
        MsgBox strWhere
         If DCount("*", "qryPopupAddCustomerToRoom", strWhere) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                Me.FilterOn = False
                Me.cboSearchOrganization = ""
                Me.cboSearchShopName = ""
                Me.cboSearchOfficeSym = ""
                Me.cboSearchLastName = ""
                Me.cboSearchFirstName = ""
         Else
            Me.Filter = strWhere
            Me.FilterOn = True
        End If
     If Me.Frame13 = 1 Then
        MsgBox "Check to ensure a Lastname was selected, and a Building has been selected" & vbCrLf _
               & "FacilityMgr selected:" & vbCrLf _
               & "Assign FaciityMgr Role for this Bldg to this Customer" & vbCrLf _
               & "Get all Rooms for the selected Bldg and assign this Customer as RoomPOC for each"
 
 
    Else
        Me.Frame13 = 2
        MsgBox "RoomPOC selected:" & vbCrLf _
               & " Get all Rooms for the selected Bldg '" & Me.cboBuildingName & "'" & vbCrLf _
               & " Make Listbox of Rooms visible " & vbCrLf _
               & " Select room(s) in Bldg and assign this Customer as RoomPOC for each selection"
 
        Debug.Print Me.cboBuildingName.Column(0)
        Me.lstRoomsInBldg.RowSource = "SELECT RoomsPK,BuildingFK,RoomName,SecOptionFK from tblRooms where BuildingFK =" & Me.cboBuildingName.Column(0)
        Me.lstRoomsInBldg.Visible = True
     '   Me.btnAssignPOC.Visible = True
        Me.Requery
    End If
 
End Sub
 
 
 
Function IsNullOrEmpty(val As Variant) As Boolean
   'First conditional validates for Nothing
   'Second condition validates for an Empty String situation "" or "     "
   Dim ret As Boolean: ret = False
   If IsMissing(val) Then
      ret = True
   ElseIf (val Is Nothing) Then
      ret = True
   ElseIf (val & vbNullString = vbNullString) Then
      ret = True
   ElseIf (Len(Trim(val)) <= 0) Then
      ret = True
   End If
 
   IsNullOrEmpty = ret
End Function
 

[zmbd]

1) NZ() (click here) is your friend

if nz([controlname],"") & "" = "" then
   ... true condition
else
   ... false condition code
 

2) You have at least two questions here (maybe more?):

1. ." I need to then take the customer ID (tblCustomer.CustomerPK) from the customer search form and the Building ID (tblBuilding.BuildingPK) from the building name combo box and I need to add those IDs into tblFacilityMgr (...)
2. . If they are a room point of contact, I need a list box to appear which displays the rooms within the building which was selected in cboBuildingName(...)

So let's narrow this down to what you need first; however, before we start does the term "Normalization" (click here) mean anything to you as it relates to database design?