Building a for retrieving data Via VBA, to view more in form

[mike02]

I have designed a form that you select the client, Product and Date, from combo boxes.



Then it list the data for all investments, for the product/client/date.

Here is what is in the form.

Date: (txtboxName- F_Head1) Tag MO
Product: (txtboxname- P_Head1) Tag IV
Investment: (txtboxname- T_Head1) Tag BF
Brokerage: (txtboxName- B_Head1) Tag PR
Fee: (txtboxname- S_Head1) Tag SF
Price: (txtboxname- X_Head1) Tag TP
Position: (txtboxname- A_Head1) Tag AS

There are seven sets like that on the form,the txt names change to F_Head2 and so on, all do the same. They each have tags...

Here is the code i am using to get the data into the form:

Private Sub Cmd_Get_Click()
If IsNull(Me.MySelector) Then
MsgBox "Select Client"
    ElseIf IsNull(Me.cboProduct) Then
    MsgBox "Please Select the Product"
        ElseIf IsNull(Me.cboMonthlyDate) Then
        MsgBox "Please Select the Date"
Else


Dim db As Database
Dim rst As Recordset
Dim T_rst As Recordset 'MonthDate
Dim F_rst As Recordset 'Investment
Dim P_rst As Recordset 'Product
Dim B_rst As Recordset 'Brokerage Fee
Dim S_rst As Recordset 'Fee
Dim A_rst As Recordset 'Add/Subtract
Dim X_rst As Recordset 'Trade Price


Dim sSQL As String
Dim ctl As Control
Dim ctl_tblPositions As Control
Dim iLoopCt As Integer
Dim iMaxTCt As Integer 'MonthDate
Dim iMaxFCt As Integer 'Investment
Dim iMaxPCt As Integer 'Product
Dim iMaxBCt As Integer 'Brokerage Fee
Dim iMaxSCt As Integer 'Fee
Dim iMaxACt As Integer 'Add/Subtract
Dim iMaxXCt As Integer 'Trade Price


Dim i As Integer




'Clears Clear Dates, Positions, Prices and Fees before getting new ones
For Each ctl In Me.Controls
    If InStr(ctl.Tag, "M") > 0 Or InStr(ctl.Tag, "B") > 0 Or InStr(ctl.Tag, "I") > 0 Or InStr(ctl.Tag, "S") > 0 Or InStr(ctl.Tag, "T") > 0 Or InStr(ctl.Tag, "A") > 0 Then
        ctl = Null
        ctl.Locked = True
    End If
Next ctl


'this section populates the discrete Values of F MonthlyDate
'any field with a TAG property set to MO will be populated
Set db = CurrentDb
sSQL = "SELECT MonthlyDate FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxFCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "MO") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxFCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close


'this section populates the discrete Values of T InvestmentID
'any field with a TAG property set to IV will be populated
Set db = CurrentDb
sSQL = "SELECT InvestmentID FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxTCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "MO") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxTCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close
    
'this section populates the discrete Values of P ProductID
'any field with a TAG property set to PR will be populated
Set db = CurrentDb
sSQL = "SELECT ProductID FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxPCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "PR") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxPCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close
    
'this section populates the discrete Values of B BrokerageFee
'any field with a TAG property set to BF will be populated
Set db = CurrentDb
sSQL = "SELECT BrokerageFee FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxBCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "BF") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxBCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close


'this section populates the discrete Values of S SRate
'any field with a TAG property set to MO will be populated
Set db = CurrentDb
sSQL = "SELECT SRate FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxSCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "SF") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxSCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close


'this section populates the discrete Values of A TradePosition
'any field with a TAG property set to AS will be populated
Set db = CurrentDb
sSQL = "SELECT TradePosition FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxACt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "AS") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxACt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close
    
'this section populates the discrete Values of X TradePrice
'any field with a TAG property set to TP will be populated
Set db = CurrentDb
sSQL = "SELECT TradePrice FROM tblPositions WHERE ClientID = " & MySelector & " And MonthlyDate = " & cboMonthlyDate & " And ProductID = " & cboProduct & " GROUP BY InvestmentID ORDER BY InvestmentID;"
Set rst = db.OpenRecordset(sSQL)


iMaxXCt = rst.RecordCount 'Adjust for new
iLoopCt = 1


If rst.RecordCount <> 0 Then
    rst.MoveFirst
    
    For Each ctl In Me.Controls
        If InStr(ctl.Tag, "TP") > 0 Then 'Adjust for new
            If iLoopCt <= iMaxXCt Then 'Adjust for new
                'this populates values up to the maximum number of records found in the SQL statement
                ctl = rst.Fields(0)
                iLoopCt = iLoopCt + 1
                rst.MoveNext
            End If
        End If
    Next ctl


    
    rst.Close


End If
End If
End If
End If
End Sub

I tried puting it into an array, but had no such luck, could figure out how to have two variable and make them match, the data wont pull and insert the record into the unbound text boxes.

Thanks in advance

[June7]

Why doesn't it work, what happens - error message, wrong results, nothing?

Have you step debugged?

Why unbound form?

[mike02]

nothing happens.

[June7]

Have you step debugged? Set a breakpoint and follow code as it executes, one line at a time. Find where it deviates from expected behavior, fix, test, debug.

Refer to link at bottom of my post for debug techniques.