VBA code to add records to table

[ittechguy]

I have a search form which searched through 5 different fields in tblCustomer and returns tblCustomer.CustomerPK, LastName, FirstName, OrganizationName, ShopName, and OfficeSym. Those values are stored in 5 text boxes which are bound to the respective fields.

There is then a combo box which allows the user to select a building name. I then have an option group which allows the user to select if the customer is a building facility manager, or a room point of contact.

If they are a building facility Manager, a button becomes visible that says "add customer to building." At which point, I need to insert the customer ID from the search form and the building ID from the combo box into tblFacilityMgr (a junction table between tblCustomer and tblBuilding). So if the customer ID was 10 and building ID was 1, those values need to be added to tblFacilitymgr.

This is what confuses me, I'm not sure how that is done. I'm assuming I'd use INSERT INTO. If someone could point me in the right direction, I'd appreciate it.

If the customer is a room point of contact, a list box appears which displays all the rooms in the building which was selected in cboBuildingName. Then the user is able to select a room and a button appears that says "Add customer to room". Then, same as w/ facility managers, the customer ID and the room ID (tblRooms.RoomsPK) need to be added into tblRoomsPOC.

Below is the form thus far, with much work that needs to be done.





Below is my vba code thus far.

Option Compare Database

Private Sub Form_Load()

    Me.lstRoomsInBldg.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
        'Code to add txtCustomerID (or tblCustomer.CustomerPK) and cboBuildingName (or tblBuilding.BuildingPK) to tblFacilityMgr here
    Else
        Me.Frame13 = 2
        'Code to add txtCustomerID (or tblCustomer.CustomerPK) and the room ID from the room which was selected in lstRoomsInBldg to tblRoomPOC here
        Me.lstRoomsInBldg.RowSource = "SELECT RoomsPK,BuildingFK,RoomName,SecOptionFK from tblRooms where BuildingFK =" & Me.cboBuildingName.Column(0)
        Me.lstRoomsInBldg.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

Again, the most confusing part for me, is the code which adds the customer ID and the building (or room) ID into the respective junction tables. I can figure the rest out myself. If someone knows the answer or can at least point me in the right direction, I'd really appreciate it!

[maximus]

hello,

You can use DAO to insert data in you table. I don't have details of your tables and field names but I will try my level best to illustrate:

dim rst as DAO.recordset

set rst=currentbd.openRecordset("tblFacilityMgr")
rst.AddNew
rst!tblCustomer=Me.CustomerPK 'Name of your text box on your form
rst!tblBuilding=Me.BuildingName 'Name of your Building Combo on your form
rst.Update
rst.close

set rst=Nothing

[NTC]

the easiest would be to put that table into a sub form right on your existing form..... then if you want to automate the task one is working just with the control names......

a more experienced way would be to use an Append query ....this requires a 2 step design - you first must make a select query that results in the correct single record with the 2 values you seek - save that with a Name....and then make your Append query using the saved query as its starting record set..... one then fires just the Append query from an event......