none
using function for SELECT statement RRS feed

  • Question

  • Hello.  I am trying to come up with a function that provides the info to use in a SELECT statement.  This is obviously VBA.  The tables are linked SQL Server tables.  Below is what I have so far in the Function and below the '###' is the part of the form load event that this would be used.  

    To give a brief background; there is a Note screen (and table) that is being used for currently 3 different forms/tables.  What is separating the individual notes in the Note table is the recordid and recordtypeid.  The table does have a PK field.  The recordid is the PK from the record being added.  The recordtypeid is assigned to each different form.  Such as Unit is 1, Vendor is 2, Transaction is 3, and so on.  

    The SELECT statement will be the same with the exception of the table, recordid, and recordtypeid.  The function is to eliminate the use of multiple select statements when only those 3 items change.

    Sofar, what has occurred (no errors) is that when the form loads, it hits the IF statement then the CALL line and jumps to the Function.  A msgbox at the end of the Function displays the correct information for the 2 id's but not the table.  Also, below the rowsource line that is commented out with the '???' I am uncertain as to what to place there.

    Any assistance is appreciated to get this better organized.

    Thank you.

    John

    Function FormLoadSelectStatement(table, recordid, recordtypeid)
        
        Dim sql As String
        
        sql = "SELECT * " & _
                "FROM table " & _
                "WHERE Remove = 0 " & _
                    " AND RecordID = " & recordid & _
                    " AND RecordTypeID = " & recordtypeid & _
                " ORDER BY DateNoteAdded DESC"
                
    End Function
    
    ####################
    part of the Form_Load event
    
        If Forms!frmUnit!lblRecordTypeID.Caption = 1 Then
            
            Call FormLoadSelectStatement(dbo_tblNote, Forms!frmUnit!txtUnitID, Forms!frmUnit!lblRecordTypeID.Caption)
    
        ElseIf Me.txtRecordTypeID = 3 Then
        
        
        Else
        
        End If
        
        '========================================================
        
        Me.lstNoteList.RowSource = ""
        Me.lstNoteList.ColumnCount = 9
        Me.lstNoteList.ColumnWidths = "1000;0;2500;2000;4000;3000;0;0;0"
    '    Me.lstNoteList.RowSource = ???????
        Me.txtNoteCount = Me.lstNoteList.ListCount
    

    Friday, September 11, 2015 5:37 PM

Answers

  • Hi John. If you're trying to modify the row source of the listbox using your function, then you'll have to modify the function to return the new row source SQL. For example:

    Function FunctionName(var1,var2,var3) As String

    sql = "...

    FunctionName = sql

    End Function

    Then, to assign it to the row source, you could use it this way:

    Me.lstNoteList.RowSource = FunctionName(table,recordid,recordtypeid)

    Hope that helps...

    • Marked as answer by johnboy0276 Friday, September 11, 2015 7:19 PM
    Friday, September 11, 2015 7:10 PM

All replies

  • What are the three tables?

    If you don't need to update the data then use a union query to pull all three table together, adding a field to identify tables.


    Build a little, test a little

    Friday, September 11, 2015 5:48 PM
  • Hi John. Not sure I understand what you're trying to do but if the "table" is supposed to be passed to the function, then you should move the variable name outside the quotes after the FROM clause. Just a thought...
    Friday, September 11, 2015 6:03 PM
  • Thanks to both.  I made a small miscalc on the SELECT for the form load.  I am loading from the tblNote table the whole time.  The 2 id's are what would change.  Once I tried what DB guy indicated then I realized the miscalc.  So, the id's would change depending on the form that is calling for the Note Screen to open.  Example above is the Unit form opening the Note screen.  Another form called Vendor would open the Note screen.  Same with the other forms that are sharing the Note Screen form and table.  

    My issue now is what is placed in the canceled out RowSource line with the question marks?

    ...John

    Friday, September 11, 2015 6:48 PM
  • Hi John. If you're trying to modify the row source of the listbox using your function, then you'll have to modify the function to return the new row source SQL. For example:

    Function FunctionName(var1,var2,var3) As String

    sql = "...

    FunctionName = sql

    End Function

    Then, to assign it to the row source, you could use it this way:

    Me.lstNoteList.RowSource = FunctionName(table,recordid,recordtypeid)

    Hope that helps...

    • Marked as answer by johnboy0276 Friday, September 11, 2015 7:19 PM
    Friday, September 11, 2015 7:10 PM
  • DB, thanks.  I missed the FunctionName = sql line.  Added that and now all is fine.  I need to slow down and look at this stuff closer so the stupid little errors don't sneak by.

    Thanks again.

    John

    Friday, September 11, 2015 7:18 PM
  • Hi John. You're welcome! Glad to hear you got it sorted out. Karl and I were happy to assist. Good luck with your project.
    Friday, September 11, 2015 7:22 PM