By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Populate ComboBox from External Database

100+
P: 194
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.
Dec 8 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

I'd like to suggest you to use SQL to query external database.
Expand|Select|Wrap|Line Numbers
  1. SELECT ... FROM ... IN <path to external database>;
  2.  
P.S. Your code may fail because RowSourceType of the combobox is not set to value list.
Dec 8 '07 #2

100+
P: 194
Hi, there.

I'd like to suggest you to use SQL to query external database.
Expand|Select|Wrap|Line Numbers
  1. SELECT ... FROM ... IN <path to external database>;
  2.  
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.
Dec 9 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Just curious - why don't you want just to set the combobox RowSource to SQL expression quering external database? Like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tbl_MainEntryTable.SNO FROM tbl_MainEntryTable IN X:\ExternalDataBaseName.mdb;"
  2.  
Dec 9 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
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]
Dec 9 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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
Dec 9 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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
Dec 11 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.