SemperFi,
Thanks for your help.
I am pretty sure the error is raised when the record source is set. That is when the first error message tells me that the syntax is wrong.
After I close the first message box then a second one indicates the refresh event failed. I supposed that the record source problem created the refresh fail.
The actual code is more extensive than what I showed on my post. There are actually four ado record source settings in the sequence.
I am trying to interlock the sales information, using ado record source with a specific record drawn from another ado.
What is so puzzeling is that the entire sequence works fine until either BOF or EOF are reached???? Then, the next button press, raises the error.
Here is the entire sequence as I wrote it for the procedure.
I hope it is ok to post such a large message.
If this is not exceptable I hope someone will let me know.
I am new at VB6 and so I use lots of tags in my coding to help me troubleshoot. Hopfully this won't interfere with your abilty to understand my code.
Since I am self taught some of my methods may raise some giggles.
Private Sub cmdMoveNextInvestment_Click()
On Error GoTo ErrorHandler
If adoHoldings.Recordset.EOF Then
adoHoldings.Recordset.MoveFirst
Else
adoHoldings.Recordset.MoveNext
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'loads (into txtSales) total sale amounts by market symbol and account# and InvestmentID
frmHoldings.adoSales.RecordSource = "SELECT SUM (Amount_Of_Sale) FROM Holdings WHERE Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Investment_ID = " & frmHoldings.txtInvestmentID & " AND Account_Number = '" & frmHoldings.txtAccountNumber & "' AND Symbol = '" & frmHoldings.txtSymbol & "'"
frmHoldings.adoSales.Refresh
'loads total shares sold by market symbol and account# and InvestmentID
frmHoldings.adoSharesSold.RecordSource = "SELECT SUM (NUmber_Of_Shares_Sold) FROM Holdings WHERE Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Investment_ID = " & frmHoldings.txtInvestmentID & " AND Account_Number = '" & frmHoldings.txtAccountNumber & "' AND Symbol = '" & frmHoldings.txtSymbol & "'"
frmHoldings.adoSharesSold.Refresh
'loads total broker fees on sales by market symbol and account# and InvestmentID
frmHoldings.adoBrokerFeeSell.RecordSource = "SELECT SUM (Broker_Fee_Sell) FROM Holdings WHERE Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Investment_ID = " & frmHoldings.txtInvestmentID & " AND Account_Number = '" & frmHoldings.txtAccountNumber & "' AND Symbol = '" & frmHoldings.txtSymbol & "'"
frmHoldings.adoBrokerFeeSell.Refresh
'loads total broker fees on buys by market symbol and account# and InvestmentID
frmHoldings.adoBrokerFeeBuy.RecordSource = "SELECT SUM (Broker_Fee_Buy) FROM Holdings WHERE Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Investment_ID = " & frmHoldings.txtInvestmentID & " AND Account_Number = '" & frmHoldings.txtAccountNumber & "' AND Symbol = '" & frmHoldings.txtSymbol & "'"
frmHoldings.adoBrokerFeeBuy.Refresh
'ENDS ADO RECORDSOURCE ASSIGNMENTS FOR RECORD UPDATE OR NEW RECORD
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'#######################################################
'This loads and formats these text boxes to prevent
'errors if no sales data is available for the investment
If frmHoldings.txtSharesSold = "" Then
frmHoldings.txtSharesSold = FormatNumber(0, 3)
End If
If frmHoldings.txtSales = "" Then
frmHoldings.txtSales = FormatCurrency(0, 2)
End If
If frmHoldings.txtBFS = "" Then
frmHoldings.txtBFS = FormatCurrency(0, 2)
End If
If frmHoldings.txtBFB = "" Then
frmHoldings.txtBFB = FormatCurrency(0, 2)
End If
'##########################################################
txtBrokerFeeAll = Int(txtBFS) + Int(txtBFB)
txtBrokerFeeAll = FormatCurrency(txtBrokerFeeAll, 2)
'BEGINS CALCULATIONS FOR INDIVIDUAL INVESTMENT STATUS
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'loads current adoHoldings market symbol into status box
lblStatusSymbol = txtSymbol
'Calculates loss/gain of current investment
'txtInvestmentStatus loads from vb code (no ado)
txtInvestmentStatus = (Int(txtCurrentValue) + Int(txtSales)) - txtAmountInvested
txtInvestmentStatus = FormatCurrency(txtInvestmentStatus, 2)
If txtInvestmentStatus < 0 Then
txtInvestmentStatus.ForeColor = vbRed
Else
txtInvestmentStatus.ForeColor = vbBlue
End If
'calculates percent loss/gain on investment
txtIPS = (txtInvestmentStatus / txtAmountInvested) * 100
txtIPS = FormatNumber(txtIPS, 0)
lblPercent.Visible = True
'ENDS CALCULATIONS FOR INDIVIDUAL INVESTMENT STATUS
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'this procedure holds current value in case of user error
'and sets record position and count
RecordCountCVhold
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf _
& "Error Description: " & Err.Description
Thanks,
Dwight