Syntax error raised on ado call

[DK47]

The following code works OK with the command buttons for "FIRST" and "LAST" and OK too while inside the last and first records.
When I hit "NEXT" while on the last record (ie 5 of 5)
I get a syntax error. no error number it just says "Syntax Error in string.


Note: txtInvestorNumber and txtInvestmentID are Integers. The rest are vachar.
I would appreciate any help.

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

Thanks,
Dwight

 

[SemperFiDownUnda]

Which line is actually getting the error?
Is it when you set the record source or when you .Refresh?

Do frmStartPage.txtInvestorNumber, frmHoldings.txtInvestmentID, frmHoldings.txtAccountNumber, and frmHoldings.txtSymbol all have values when it errors? Especially frmHoldings.txtInvestmentID andfrmHoldings.txtAccountNumber

I would break this up into this code.

Dim sSQL as String 
sSQL = "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.RecordSource = sSQL frmHoldings.adoSharesSold.Refresh

then make sure sSQL is a valid SQL statement. Run it in something like query anyliser.

 

[DK47]

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 &quot;Error Number: &quot; & Err.Number & vbCrLf _
& &quot;Error Description: &quot; & Err.Description


Thanks,
Dwight

 

[DK47]

If anyone is interested, I think I solved my problems concerning the above posts.

Becuase the standard coding method of moving from next to first always seems to bring up a blank record between the first and last records,or between the last and first, those blank records raised errors in sub routines or calls from the main ado procedure. For example, in my code above I used information returned from one ado to extract information from the same table but using additional ado's.
Here is how I solved the problem.
So simple I can't believe this bugged me for several days.

For comand buttons for previous records I did this:
Private Sub cmdMovePreviousInvestment_Click()

On Error GoTo ErrorHandler
If adoHoldings.Recordset.AbsolutePosition = 1 Then
adoHoldings.Recordset.MoveLast

Else
adoHoldings.Recordset.MovePrevious

End If

For moving forward, it was a little more detailed but still very simple.
I set up two text boxes. One to hold the absolute postion and the other to hold the record count. If they match when the command button is pressed, it's back to the first record.

Private Sub cmdMoveNextInvestment_Click()
On Error GoTo ErrorHandler
If txtAbsolutePosition = txtRecordCount Then
adoHoldings.Recordset.MoveFirst
Else

adoHoldings.Recordset.MoveNext
End If

Both of these code examples bypass the peskey problem that arises when empty fields display in text boxes ....

I hope this helps anyone who has been troubled by this.

Also if anyone know of any drawbacks to this method I would love to here them.

Regards,
Dwight