none
VBA Compile error: Sub or function not defined RRS feed

  • Question

  • Hello,

    I am having an issue with some code that I pulled from an older database.  I am trying to use a form  to add multiple records to a table.  The form field 'Select all personnel to be included' is the area the user selects all the new records to be added to the table.  All the other data fields need to be replicated for each selection.  Then I want to use the information in the table to produce a report using the most recent addition to the table.  I am using code from another database that does this, however when I click the I get a compiling error.  The sytem highlights the RSQ code in the second insertSQL line (bolded).  What does the RSQ function (What does RSQ do?).  I have removed this function and changed the 'DoSQL insert SQL' to doCmd.RunSql (insertsql).  When I do this it only adds the first record the number of times other records were selected (not the selected records).  This is the code I have been trying to convert.  Any assistance would be great. 

    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
        'MsgBox visitRequestID
        DoCmd.OpenReport "Visit Request", acViewReport, , "[Visit Request Header].visitRequestID = " & visitRequestID

    Any suggestions on how to make this work a bit easier?

    Monday, November 16, 2015 1:27 PM

Answers

  • RoyalViking,

    If you don't know what RSQ function do, go to old db, go to code, and click right button on rsq name end select definition.


    Michał

    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 2:02 PM
  • Hello,

    I am having an issue with some code that I pulled from an older database.  I am trying to use a form  to add multiple records to a table.  The form field 'Select all personnel to be included' is the area the user selects all the new records to be added to the table.  All the other data fields need to be replicated for each selection.  Then I want to use the information in the table to produce a report using the most recent addition to the table.  I am using code from another database that does this, however when I click the I get a compiling error.  The sytem highlights the RSQ code in the second insertSQL line (bolded).  What does the RSQ function (What does RSQ do?).  I have removed this function and changed the 'DoSQL insert SQL' to doCmd.RunSql (insertsql).  When I do this it only adds the first record the number of times other records were selected (not the selected records).  This is the code I have been trying to convert.  Any assistance would be great. 

    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
        'MsgBox visitRequestID
        DoCmd.OpenReport "Visit Request", acViewReport, , "[Visit Request Header].visitRequestID = " & visitRequestID

    Any suggestions on how to make this work a bit easier?

    RSQ is a custom function in your previous database.  You'll need to either copy the function to your new database or remove the calls from your current code.

    As Michal explains, you can go to your old database and the function you're trying work with, place the mouse on the function name, then right click it.  In the displayed short-cut menu, select Definition.  You can also place the cursor anywhere within the function name and press Shift+F2.  Either method will take you to the procedure definition.  This works for custom procedures in your modules as well as VBA defined procedures.


    • Edited by RunningManHD Monday, November 16, 2015 4:03 PM
    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 4:03 PM
  • You need that RSQ function or you need to make sure your SQL statement is clean before you try to execute it. I bet RSQ stands for "Remove Single Quotes" which probably strips out single quotes so the SQL statement is properly formed.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 9:04 PM

All replies

  • RoyalViking,

    If you don't know what RSQ function do, go to old db, go to code, and click right button on rsq name end select definition.


    Michał

    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 2:02 PM
  • Hello,

    I am having an issue with some code that I pulled from an older database.  I am trying to use a form  to add multiple records to a table.  The form field 'Select all personnel to be included' is the area the user selects all the new records to be added to the table.  All the other data fields need to be replicated for each selection.  Then I want to use the information in the table to produce a report using the most recent addition to the table.  I am using code from another database that does this, however when I click the I get a compiling error.  The sytem highlights the RSQ code in the second insertSQL line (bolded).  What does the RSQ function (What does RSQ do?).  I have removed this function and changed the 'DoSQL insert SQL' to doCmd.RunSql (insertsql).  When I do this it only adds the first record the number of times other records were selected (not the selected records).  This is the code I have been trying to convert.  Any assistance would be great. 

    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
        'MsgBox visitRequestID
        DoCmd.OpenReport "Visit Request", acViewReport, , "[Visit Request Header].visitRequestID = " & visitRequestID

    Any suggestions on how to make this work a bit easier?

    RSQ is a custom function in your previous database.  You'll need to either copy the function to your new database or remove the calls from your current code.

    As Michal explains, you can go to your old database and the function you're trying work with, place the mouse on the function name, then right click it.  In the displayed short-cut menu, select Definition.  You can also place the cursor anywhere within the function name and press Shift+F2.  Either method will take you to the procedure definition.  This works for custom procedures in your modules as well as VBA defined procedures.


    • Edited by RunningManHD Monday, November 16, 2015 4:03 PM
    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 4:03 PM
  • You need that RSQ function or you need to make sure your SQL statement is clean before you try to execute it. I bet RSQ stands for "Remove Single Quotes" which probably strips out single quotes so the SQL statement is properly formed.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by David_JunFeng Wednesday, November 25, 2015 2:05 PM
    Monday, November 16, 2015 9:04 PM