Thats a great idea... Sorry, I didn't think of that... here is what the problem
was:
I was using a table named LookupLists to store values for different comboboxes
on my userform. The table contained multiple columns for multiple comboboxes
and each column had different no of rows. The UserId field which I was
hardcoding in my test macro had maximum no of rows (436) and the other columns
have lesser no of row. When I was using my other macro and passing arguments to
it, I was using a column name with only 7 rows. When I run the do while loop,
VBA looped till 436th record however there were no values in the fields after
7th rows. And this generated an error because a Null can't be added to a
combobox.
I made a little change to my code by adding an "If - Then" statement and its
working fine now. Such a small problem but took me more a 2 hrs to figure
out... the error message confused me a lot...
Below is my new code:
Sub AddItemtoDropDownList(cboBox As Object, cboSourceTable, cboField As String)
Dim db As Database, rs As Recordset
Set db = OpenDatabase(CurrentDB(), False, False, DBPassword())
Set rs = db.OpenRecordset("SELECT * FROM " & cboSourceTable, dbOpenDynaset)
cboBox.Clear
Do While Not rs.EOF
If Not rs.Fields(cboField) = "" Then 'I added this line to avoid the
error
cboBox.AddItem rs.Fields(cboField)
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
_________________________________________________________________________________________________
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________________________________
From: Mudassar Ramzan <m...@worldcall.net.pk>
To: excel-macros@googlegroups.com
Sent: Monday, 9 February, 2009 6:48:54 PM
Subject: $$Excel-Macros$$ Re: Run-time error '-2147352571 (80020005) - Type
Mismatch
So why you don't want to share with group mates???
----- Original Message -----
From: Alokeshwar Tiwary
To: excel-macros@googlegroups.com
Sent: Monday, February 09, 2009 2:46 PM
Subject: $$Excel-Macros$$ Re: Run-time error '-2147352571 (80020005) - Type
Mismatch
Kindly ignore the request.. I figured it out myself..
_________________________________________________________________________________________________
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________________________________
From: Alokeshwar Tiwary <alokeshwar.tiw...@yahoo.com>
To: excel-macros@googlegroups.com
Sent: Monday, 9 February, 2009 5:52:54 AM
Subject: $$Excel-Macros$$ Run-time error '-2147352571 (80020005) - Type Mismatch
Hi,
I am trying to populate an excel userform combo box from a table in access
database using DAO.
When I pass arguments as following:
AddItemtoDropDownList Me.cboUserName, "LookupLists", "UserId"
VBA returns following error:
=======================
Run-time error '-2147352571 (80020005)
Type Mismatch=======================
When I click on debug VBA highlights below line from the code:
cboBox.AddItem rs.Fields(cboField).value
Here is my code:
=======================================
Sub AddItemtoDropDownList(cboBox As Object, cboSourceTable As String, cboField
As String)
Dim db As Database, rs As Recordset
Set db = OpenDatabase(CurrentDB(), False, False, DBPassword())
Set rs = db.OpenRecordset("SELECT " & cboField & " FROM " & cboSourceTable
& " Order by " & cboField, dbReadOnly)
Do
cboBox.AddItem rs.Fields(cboField).value
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
=======================================
If hardcode field name and tablename in my code as following it works pefectly
fine.
What could be the problem? with the code mentioned above while the code
mentioned below is working.
Also i will be grateful if you could suggest some alternate method to populate
combobox is excel userform from a field in an access database table.
Sub AddItemtoComboBox
Dim db As Database, rs As Recordset
Set db = OpenDatabase(CurrentDB(), False, False, DBPassword())
Set rs = db.OpenRecordset("SELECT UserId FROM LookupLists Order by UserId",
dbReadOnly)
Do
Me.cboUserId.AddItem rs.Fields("UserId").value
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End SUb
_________________________________________________________________________________________________
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________________________________
Add more friends to your messenger and enjoy! Invite them now.
________________________________
Connect with friends all over the world. Get Yahoo! India Messenger.
________________________________
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.233 / Virus Database: 270.10.19/1941 - Release Date: 02/06/09
17:28:00
Add more friends to your messenger and enjoy! Go to
http://messenger.yahoo.com/invite/
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
