string variable truncated to 255 characters

[lcsgeek]

- Office XP sp3
- Coding an event on a button on an Access Form

[font=Verdana]Dim db As DAO.Database 'Database connection[/font]
[font=Verdana]Dim rs As DAO.Recordset 'RFMS, Access recordsets[/font]
[font=Verdana]Dim varSQL, varSQLlist As Variant[/font]
[font=Verdana]...[/font]
[font=Verdana]...[/font]
[font=Verdana]...[/font]
[font=Verdana][font=Verdana]varSQL = "SELECT PriceList.Private_Style, ColorDesc.PrivateColor, PriceList.PrivStyleNum, PriceList.Price1, PriceList.Price2, PriceList.SeqNum " & _[/font][/font]
[font=Verdana][font=Verdana]"FROM PriceList INNER JOIN (ColorDesc INNER JOIN ColorAdd ON ColorDesc.ColorSeqNum = ColorAdd.ColorSeqNum) ON PriceList.SeqNum = ColorAdd.PriceListSeqNum " & _[/font][/font]
[font=Verdana][font=Verdana]"WHERE PrivStyleNum = '" & Left(Me.listSKU.ItemData(intLine - 1), InStr(Me.listSKU.ItemData(intLine - 1), " ") - 1) & "';"[/font][/font]
[font=Verdana][/font] 
[font=Verdana][font=Verdana][font=Verdana]Set rs = db.OpenRecordset(varSQL)[/font][/font]
[/font]

When looking at the Locals Window in debug, varSQL is truncated after 255 characters and so the rs never gets created. I've read many many posts like this on the net none of which that I've seen has a solution.

Would someone be willing to create a variable just like I have and then look in the Locals Window to see if it contains all the data, Please?

Thanks in advance.
Darin

 

[lcsgeek]

Couple things:
1) make sure your checking for errors in your sub (I know this should be automatic but...). For example:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click
 
'your code for this sub goes here
 
Exit_Command47_Click:
	Exit Sub
Err_Command47_Click:
	MsgBox Err.Description
	Resume Exit_Command47_Click
	
End Sub

2) after checking the errors I found that this piece of code:

Set rs = db.OpenRecordset(varSQL)

generated an error similar to this:
"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an Identity column."
so I changed the code to read:

Set rs = db.OpenRecordset(varSQL, dbOpenForwardOnly, dbSeeChanges, dbReadOnly)

and now I don't get any error.

As far as the Locals Window is concerned I've learned that String or Variant variables only display 255 characters when viewing in this Window. This doesn't mean that they don't contain all of the expected data (try writing these values to a MsgBox and you'll see all the data) you just can't see it in the Locals Window. Not very useful when diagnosing problems with long stings but whatever...I'm just glad to have an uderstanding. I don't know if this is an Office thing or an ADO, DAO or VB issue.

Hope this helps someone else.

Darin