Invalid use of bracketing error

[anbusds]

Hi All,
I am getting this invalid use of bracketing error Anything wrong?
Set objMyRecordset = CurrentDb.OpenRecordset("select itemno from " & Chr(34) & "[" & icitem & "]" & Chr(34))

Thanks,
Anbu

 

[spikepl]

The recipe to check this sort of thing is here: http://www.baldyweb.com/immediatewindow.htm

 

[anbusds]

Hello,
what solution is there?

 

[Galaxiom]

Use a variable to render the result of the concatenated stuff first then Debug.Print that variable.

It will show what is being fed to the OpenRecordset command.

Given that there are no quotes used around a table name in Access SQL then I expect you should drop the Chr(34)s.

 

[anbusds]

Hi,
Noted, i make it clear the my previous code is bit trouble to run
My amended code as follows,
Run Time Error '3078'

Private Sub Combo4_AfterUpdate()
Dim conn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset

Dim strSQL As String

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = SQLOLEDB; Data Source=SDSACCOUNT; Initial Catalog= SSPLCO; user ID=sa; Password=sa;"
conn.Open
strSQL = "select itemno from icitem"

Set objMyRecordset.ActiveConnection = conn
Set objMyRecordset = CurrentDb.OpenRecordset(strSQL)
Debug.Print strSQL
Me.Combo4.RowSourceType = "Table/Query"

End Sub

Thanks!!!

 

[CJ_London]

looks to me like you don't need the Chr(34)'s

("select itemno from [" & icitem & "]")