I am new to Access and some what familiar with VBA, ANY help would be GREATLY appreciated! I know Access has a query tool but my customer wants something easier to handle. So, I would like to build a GUI that lets the user select items for an attribute and then output all records with that item for that attribute, and also be able to select multiple items for different attributes with ORs placed between each item.
The over all goals would be to build and submit mSQL commands to Access. I would like the user select items from combo boxes and then select which items to include in the output. The guts of the form would then take these selections and build a mSQL statement that would be processed. The outputs could be in an Access table or exported to Excel at the user request.
Here is an example:
The database contains car information: color, year, make, and model. The form would have a combo box for each attribute. The user can select green, red, 1999, Saturn, and LS300. The form would then build a mSQL statement like:
Return all cars where color = green OR color = red AND year = 1999 AND make = Saturn AND model = LS300.
This statement would then be sent to Access and the results would be placed in an Excel spreadsheet that the user named and chose the location for.
Here are some questions. Any help, suggestions, or page numbers in the book would be greatly appreciated.
Should this be done in VB
.net or Access forms?
I currently have combo boxes for each attribute I want the user to pick from but the list of items in the boxes is not unique and out of order. How do I fix this?
Is it possible for the combo box to stay open such that user can select multiple items? The SQL script would include ORs between each item for a column and ANDs between columns.
Is it possible to include a output box that allows the user to select the output to go to an Access table or an Excel spreadsheet?
Once all the selection are made and the mSQL commands put together how would the form submit them to Access?
Please excuse inaccuracies in the example and if I am using certain terms incorrectly. I look forward to your responses.