locked
Form Code to insert data to table RRS feed

  • Question

  • I am working on a database created by someone else. I need to have a form insert data to a table and generate a report on the click of a button. I am new to VBA and need a good reference to walk me through the process. The code that was moved from an older dbase is below.  I don't know who to get it to work with the new dbase.  I get an error and it takes me to the second insertSQL line. Specifically to the RSQ (what does RSQ do or mean?). Can some explain what all this means or recommend a guide to help me understand this:

    Private Sub Submit_Click()
        Dim insertSQL As String
        Dim idSQL As String
        Dim visitRequestID As Integer
        Dim db As DAO.Database
        Dim idRS As DAO.Recordset
        Dim personID As Variant

        Set db = CurrentDb
       
        insertSQL = "insert into VisitRequests (MILSEC, SMO, namePOC, faxPOC, phonePOC, firstDay, LastDay, purpose) values "
         insertSQL = insertSQL & "(" & Me.MILSEC & ", '" & RSQ(Nz(Me.SMO)) & "', '" & RSQ(Nz(Me.namePOC)) & "', '" & RSQ(Nz(Me.faxPOC)) & "', '"
         insertSQL = insertSQL & RSQ(Nz(phonePOC)) & "', '" & Me.firstDay & "', '" & Me.lastDay & "', '" & RSQ(Nz(Me.purpose)) & "')"
        DoSQL insertSQL
        
        'Get the visitRequestID of the record just added to VisitRequests
        idSQL = "select top 1 visitRequestID from VisitRequests order by visitRequestID desc"
        Set idRS = db.OpenRecordset(idSQL)
        idRS.MoveFirst
        visitRequestID = idRS!visitRequestID

        For Each personID In Me.PersonsList.ItemsSelected
            insertSQL = "insert into VisitRequestPersons (visitRequestID, personID) values (" & visitRequestID & ", " & Me.PersonsList.ItemData(personID) & ")"
            DoSQL insertSQL
        Next
       
        DoCmd.Close

    Thursday, November 12, 2015 11:23 PM