Populate ComboBox from External Database

[mfaisalwarraich]

Hi Everybody, im getting Run-time error '13' Type mismatch on the following code:

cbSNO is a combobox whereas SNO is a text datatype of tbl_MainEntryTable in dbExternal.

Private Sub cboSNO_GotFocus()

With Me![cboSNO]
.RowSourceType = "Value List"
.ColumnCount = 1
.ColumnWidth = -1 'Default Width


End With

Const conPathToExternalDB As String = "C:\dbExternal.mdb"
Dim wrkJet As Workspace, strSQL As String
Dim dbsMain As DAO.Database, rstMain As DAO.Recordset


strSQL = "SELECT DISTINCT tbl_MainEntryTable.SNO FROM tbl_MainEntryTable;"


Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)


Set dbsMain = wrkJet.OpenDatabase(conPathToExternalDB)

Set rstMain = dbsMain.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rstMain.EOF
Me![cboSNO].AddItem rstMain![SNO]
rstMain.MoveNext
Loop
rstMain.Close
dbsMain.Close
wrkJet.Close

Set rstMain = Nothing
Set dbsMain = Nothing
Set wrkJet = Nothing

End Sub

Please tell me whats wrong with the code??? its highlighting Me![cboSNO].AddItem rstMain![SNO] in yellow.

[FishVal]

Hi, there.

I'd like to suggest you to use SQL to query external database.

SELECT ... FROM ... IN <path to external database>;
 

P.S. Your code may fail because RowSourceType of the combobox is not set to value list.

[mfaisalwarraich]

Hi, there.

I'd like to suggest you to use SQL to query external database.

SELECT ... FROM ... IN <path to external database>;
 

P.S. Your code may fail because RowSourceType of the combobox is not set to value list.

RowSourceType is already set to Value List. The Same code is working on the other field of the table but not on SNO. i failed to understand why? please tell me cuz i wana populate combo box with this particular field. thank u.

[FishVal]

Hi, there.

Just curious - why don't you want just to set the combobox RowSource to SQL expression quering external database? Like this:

SELECT DISTINCT tbl_MainEntryTable.SNO FROM tbl_MainEntryTable IN X:\ExternalDataBaseName.mdb;"
 

[puppydogbuddy]

Hi Everybody, im getting Run-time error '13' Type mismatch on the following code:

cbSNO is a combobox whereas SNO is a text datatype of tbl_MainEntryTable in dbExternal.

Private Sub cboSNO_GotFocus()

With Me![cboSNO]
.RowSourceType = "Value List"
.ColumnCount = 1
.ColumnWidth = -1 'Default Width


End With

Const conPathToExternalDB As String = "C:\dbExternal.mdb"
Dim wrkJet As Workspace, strSQL As String
Dim dbsMain As DAO.Database, rstMain As DAO.Recordset


strSQL = "SELECT DISTINCT tbl_MainEntryTable.SNO FROM tbl_MainEntryTable;"


Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)


Set dbsMain = wrkJet.OpenDatabase(conPathToExternalDB)

Set rstMain = dbsMain.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rstMain.EOF
Me![cboSNO].AddItem rstMain![SNO]
rstMain.MoveNext
Loop
rstMain.Close
dbsMain.Close
wrkJet.Close

Set rstMain = Nothing
Set dbsMain = Nothing
Set wrkJet = Nothing

End Sub

Please tell me whats wrong with the code??? its highlighting Me![cboSNO].AddItem rstMain![SNO] in yellow.

Try changing this:
Me![cboSNO].AddItem rstMain![SNO]

To:
rstMain![cboSNO].AddItem rstMain![SNO]

[FishVal]

Hi, PDB.

I'm almost sure that problem is as indicated - type mismatch.
ComboBox.AddItem method gets string type argument.

Changing
Me![cboSNO].AddItem rstMain![SNO]
to
Me![cboSNO].AddItem "" & rstMain![SNO]

should solve the problem, though I still don't see any reason to populate combobox this way. ;)

Regards,
Fish

[puppydogbuddy]

Hi, PDB.

I'm almost sure that problem is as indicated - type mismatch.
ComboBox.AddItem method gets string type argument.

Changing
Me![cboSNO].AddItem rstMain![SNO]
to
Me![cboSNO].AddItem "" & rstMain![SNO]

should solve the problem, though I still don't see any reason to populate combobox this way. ;)

Regards,
Fish

Hi Fish,

You are probably right.....I wasn't sure about this, but wanted to give Mohammad something to try. It would be nice if he provided some feedback.

Hi Mohammad,
It would be helpful for other members with a similar problem in the future, if you could update us with the final solution. Thanks.

PDB