Data source name not found error

[anbusds]

Hi All,
i recieved error on the following connection string in the combo box change event
code as follows,

Private Sub LotNo_Change()
Dim conn As New ADODB.Connection
Dim sConnString As String


'Create connection string
sConnString = conn.Provider = "SQLOLEDB; Data Source=SDSACCOUNT; Initial Catalog= SSPLCO; user ID=sa; Password=sa;"

'Open connection and execute
conn.Open sConnString

'Do your query
conn.Execute "Select ITEMNO FROM ICITEM;"

End Sub
Any Solution?
Thanks in advance...

 

[pbaldy]

For starters, the change event is likely not the right place for this, as it fires with every keystroke. In any case, if you're using MySQL, that doesn't look like a valid connection string (looks like SQL Server). See if you find the right one here:

http://www.connectionstrings.com/

 

[Galaxiom]

And what do you hope to achieve by just Executing a Select query?

 

[anbusds]

Hello,
This is my amended query and nothing come out in the combo 4
Anything need to add?

Private Sub Combo4_Change()
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
objMyRecordset.Open strSQL
Me.Combo4.RowSourceType = "Table/Query"
End Sub

 

[Galaxiom]

Set Me.Combo4.Recordset = objMyRecordset

You would not need to edit the RowSourceType each time.

However as Paul pointed out earlier, it is not appropriate to use the change event of the combo as this will fire on every keystroke.

I cannot see why you would want to read the recordset of the combo from a table when you enter something into it. If the recordset comes from the same table as the form's RecordSource it won't see it until the record has been saved.

You might reload it in the AfterUpdate event of the Form so that it includes any new values entered in the current record.

 

[anbusds]

Hi,
Noted, Now i copy the code in the After update event
Run Time Error '7965'
The object you entered is not a Valid recordset property.


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
'Do your query

strSQL = "Select ITEMNO FROM ICITEM;"
Set Me.Combo4.Recordset = objMyRecordset
Set objMyRecordset.ActiveConnection = conn
objMyRecordset.Open strSQL

Me.Combo4.RowSourceType = "Table/Query"
End Sub

Thanks...

 

[Galaxiom]

You need to Open the recordset before you apply it to the combo.