PDA

View Full Version : Need front end for db form??



grichey
10-03-2008, 06:26 AM
Hey,
I have never done something like this before so before I jump in, I thought it better to see what method of attack is the best.

I have what is currently in .xls format, a 350 row database with 29 columns. The goal is to make some sort of form that allows to search by various characteristics like name in one box or market cap range X to Y in another and have the data dumped in a new sheet.

Access isn't out of the question if you think that would be much easier.

I appreciate the help.

Demosthine
10-03-2008, 03:40 PM
Hey there Gavin.

First, let me mention that it sounds like your easiest option would be to use Excel's built-in AutoFilter feature. First, make your ActiveCell one of your Header Cells in your Table. Then, go to the Data Menu and select Filter. Select the AutoFilter command.

Otherwise:

Interestingly enough, if you have an Excel Worksheet set up correctly, you can use standard SQL Query Statements to access the data. This means that you can take the "Table" and run the query using your column headers and such.

Now, one thing I must mention is that there are certain requirements for this to work correctly.

01.) Excel seems to always evaluate the first Row of the Range as a Header. Each cell in the Header equates to the Field in an Access Table.

02.) Each Column must contain data of the same type. Based on the very first Row of your data, (not your Header Row), it will set the Field Type of that Column. Any data in subsequent Rows that does not fit that Type is conveniently ignored.


Now, once you have ensured that the data is set up correctly, you'll want to create a UserForm with the Query Options on it. You'll have to decide how detailed you want to allow the Query. Remember, the more detailed, the more programming you have to do to ensure the SQL Statement executes perfectly.

For testing purposes, I would start out with only one Condition. You'll have three or four controls, depending on the result of the User's choices:

01.) The Field (or Column Header) that you want to use as the Criteria Field.

02.) The Calculation Method that you will use. This will be "Equal To", "Not Equal To", "Greater Than", "Less Than", "Between", etc. This choice will determine whether you need one or two additional Controls.

03.) You will always have this Control. In the event your user selects "Equal To", "Not Equal To", "Greater Than", or "Less Than", this will be your last Control for the Condition. If it is a Criterion that requires a range, such as "Between", then you'll need your fourth Control.

04.) This Control will have the second limiting criterion for your statement. If this Control is not used, simply set Visible to False and then set the Width of Control 3 to align with the right edge where Control 4 would be.

Once the user choses to execute the Query, you'll have a Select Case statement to determine which Calculation Method was selected and set up the final Query. You can finally Query the data and transfer the resulting Recordset onto a new Worksheet.


Now, in regards to whether this would be simpler in Excel or Access, the answer is "It Depends." You have to decide how much natural access you want the user to have in Access. A large portion of Database Applications do not give the Users direct access to any Tables or Queries at all, therefore they prevent the User from accessing the Query Builder.

Ultimately, at least from my background and experience, from what you describe, it won't matter either way because you will be doing a large amount of coding period.

debauch
10-03-2008, 04:53 PM
^ access is easier. why manipulate excel to do something that is native in access?

grichey
10-03-2008, 06:42 PM
I figured access was easier. And yes, if this was for me, I would use autofilter! But I'm helping out a friend and a decent front end is part of what he's trying to accomplish.

What would one call what I'm trying to accomplish then so I could do some searches if I'm trying to get it searchable without having them forced to manually run a querey (let's assume my friend is a complete n00b and has enough knowledge to use google and that's about it)

grichey
10-05-2008, 05:22 PM
maybe?

rbrhodes
10-05-2008, 07:46 PM
Hi gavin

Are you searching for a limited selection of items? eg, Name, Company, Address, etc or doing an actual query?

What needs to be 'dumped'? Whole row of any matches found?

Etc.

Perhaps a small example of the DB and an idea of the searches might make it easier to help you

grichey
10-05-2008, 08:55 PM
Examples of searches

All records in Dallas
All records in Dallas with Cash between 5$ and $10
All records in which Jim is a man (man1 - man4)

Yes the whole row would be dumped.

example attached of data

thx!

rbrhodes
10-06-2008, 11:34 PM
Hi Gavin,

Still looking at this one (may take a while). Ultimately, using Excel as the front end and Access as the (hidden) back end may be the way to go.

Since Excel is essentially a flat file DB I think I'm trying to develop a Really Tolerably Limited Simplified SQL for Excel (RTLSSQL4XL)?

The OR query is no problem, the AND query however is breakin' my brain! I love a challenge tho...

grichey
10-07-2008, 07:41 AM
Ha -- appreciate the help. Did you just make up the RTLSSQL4XL ?

Demosthine
10-07-2008, 05:28 PM
Hey there Galvin.

It doesn't sound like you were able to take my last explanation and turn it into something usable, so if it was clear enough, I'm sorry for that.

I've gone through and created two workbooks. Both are in the attached Zip File. They both do similar tasks, but one focuses more on the User specifying the SQL Statement or Query, while the other has three hard-coded statements.

** QueryBuilder.xls **
The first book to look at is called QueryBuilder.xls. Once you open the file, you'll find it has two Sheets: Employees and QueryResults. The Employees sheet was taken out of the Northwinds Database for sample records because when I was working on it, you had not posted your sample. The second Sheet is named QueryResults and is blank.

When you opened the file, a UserForm should have displayed. This form has four Input Controls upon opening. The first is used to set the Field that you want to filter by, such as LastName. The second is an Condition that you use to define whether you want the results to Equal your value, Not Equal your value, etc. The third and fourth are TextBoxes that you enter the literal for the condition, such as 'Jim' for a person's FirstName.

Once you build the Query, click the Query button. The procedures will run and you will find yourself looking at the QueryResults sheet with all of the Rows copied that match the criterion you just defined.

** End QueryBuilder.xls **

** QueryResults.xls **
Once you posted your example workbook and provided a little more detail on your requirements, I took that workbook and the procedures from Query Builder to show you how to execute more complex SQL Statements.

This one will have three CommandButtons on the Records Sheet. Each of these will run a particular Query and the results will appear on the Query Results sheet.
** End QueryResults.xls **

** Notes **
In order for the Query to work correctly, you have a Named Range for the entire set of data you want to use as your Source. You can do this automatically through code at the start of any of your Procedures or manually, as I have done here.

Next, the Recordset Object and other Database related abilities come through referencing the 'Microsoft ActiveX Objects Recordset 6.0 Library' from the VBA Environment.

I hope this helps.
Scott

rbrhodes
10-09-2008, 09:31 PM
Hi Gavin,

The code in this sheet is _not_ optimized at all. I just wrote it all in one big sub adding bits wherever they were needed. Normally I would be embarrassed by it...

What I need is for you to look at it and let me know if it does what you requested. Also if it errors out at all.