Issues with Using Excel as a Database Sample File

[cycleninja]

I've downloaded the sample Excel-As-Database-demo-v1 workbook and have modified it to meet the layout of the workbook I'm drafting. I've kept the same 3 combo boxes but have renamed throughout with the new cmb names and SQL fields, the Named Range 'dataSet' has been amended also, along with the structure of the data table which I've renamed classification.


When running the code, I get a Type 13 Mismatch error which highlights the following line of code:


'cmbUniclassGroupCode.AddItem rs.Fields(0)'


in the cmdUpdateDropDowns_Click Sub


I


Any assistance is greatly appreciated.


Dave

 

[SirJB7]

Hi, cycleninja!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


As you made so many changes to the downloadable file you should upload your file in order to analyze it properly. Please refer to second green sticky post for guidelines.


Regards!

 

[cycleninja]

Hi SirJB7,


Thanks for the mega quick response. I've had a trawl through the tags etc and a few other locations regarding this error but to no avail.


Please see attached link to my amended file. There are no passwords.


https://dl.dropbox.com/u/94076756/BBW%20Engineering%20Standards%20Database.xlsm


Thanks in advance


CycleNinja

 

[SirJB7]

Hi, cycleninja!


The issue is that you have null entries within the three recordset sources (e.g., Group Code, column B of sheet 'classification', rows 1590-1592, and more downwards...).


Having null values is not a highly recommended practice, so you have two choices:


a) delete all the null (empty for Excel) entries or fill them from the drop down listbox


b) change the 3 strings for the select statement in sheet View for cmdUpdateDropDows_Click event code from:

strSQL = "Select Distinct [Uniclass Group Code] From [classification$] Order by [Uniclass Group Code]"

strSQL = "Select Distinct [Uniclass Sub Group Code] From [classification$] Order by [Uniclass Sub Group Code]"

strSQL = "Select Distinct [Uniclass Section Code] From [classification$] Order by [Uniclass Section Code]"

to:

strSQL = "Select Distinct [Uniclass Group Code] From [classification$] Where Not(IsNull([Uniclass Group Code])) Order by [Uniclass Group Code]"

strSQL = "Select Distinct [Uniclass Sub Group Code] From [classification$] Where Not(IsNull([Uniclass Sub Group Code])) Order by [Uniclass Sub Group Code]"

strSQL = "Select Distinct [Uniclass Section Code] From [classification$] Where Not(IsNull([Uniclass Section Code])) Order by [Uniclass Section Code]"

(that's to say, add a Where clause to filter null/empty values)


Just advise if any issue.


Regards!

 

[cycleninja]

Hi SirJB7,


The fix worked a treat. I've added the Where statement as the data is still in the process of being finalised.


I can now use this technique on a number of similar projects I'm working on.


Thanks very much for your assistance.


Regards


CycleNinja

 

[SirJB7]

Hi, cycleninja!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!