I need help creating some relatively simple query by forms in Access 97. I am very new to this software (about 8 hours experience so far!), and although I have been making good use of Google, I’m stuck and I don’t know why things aren’t working.
This is long, but I’m trying to be as clear as possible, so I can get the best advice possible!
Here is what I’m trying to do;
I am creating a database with 4 tables. They are as follows. The relationships are 1-to-many, indicated with matching *s:
tblProductType - this has an autonumberID(*), a product common name (Widget Name), and a product code
(eg. 12, Super Widget, SWD-100-02)
tblCountry - autonumberID(**), CountryName, ContactCompany
(eg 7, Argentina, Widget Sales Inc.)
tblDocumentType - autonumber, document type (1, ABC-Form) (these are industry-related document names, less important, but I use it for a drop down in the next table and entry form to make things more uniform…).
And finally, the one of interest:
tblForeignMarket - this has autonumber, productType numberID(*), country NumberID(**), document type, document name, revision number, date of letter, http link to pdf of document.
The dropdown menus in tblForeignMarket are ProductTypeNumber (drops down numberID next to common name next to code, registers the numberID), Country Number (drops down the numberID next to the country name, but registers the numberID) and DocumentType, which just drops down the list of document names.
I have succeeded in making a form to enter new data, and that’s good enough for now (though sooner or later I’ll want to refine that, I just need to know the requirements better).
What I want to do now is allow users to search without entering data - they most commonly will want to know a) what version of widget is marketed in which country and b) which countries have what versions of widgets. I have tried following step-by-step instructions from different websites, but nothing seems to actually work.
For the sake of simplicity, lets assume we only want to know where a certain widget is sold.
I have a form, frmWidgetSearch, with a combobox cboWidgetList (or listbox, I tried both but neither works anyways!) with all the widget common names (Widget 100, Super Widget, Super Widget 3000 etc). The form also has a command button, which, using the Wizard, I asked it to run my query called qrySearch.
qrySearch is set up to return to me Widget Name, Widget Code, Country, Document Number, Revision, Document Date and weblink columns.
Obviously, I want the combobox to give me a specific parameter - eg all records for Super Widget. So, in the Widget Name entry of my query, I put in [Forms]![frmWidgetSearch]![cboWidgetList] as a criteria. I also set the parameter for that as “text” in the Parameter menu (right click on query window).
I have found something like 148577 websites that tell me that this should work.
It doesn’t.
So now what?
If I run the query without the form but with that parameter string there, and get the pop-up and type in the widget name myself, it DOES work, so clearly the problem is in the form-to-query communication. If I use a TEXTBOX, and not a Combo or listbox on the form, that ALSO works. I don’t understand what is different about the combobox vs a text box. The command button will return all results from the query if the query has no parameter specified, so that works too.
We have too many widgets and too many in-house erroneous names to rely on people typing their own entries (the SuperWidget-BD is usually called the Widget-CL because we also have a SuperWidget-CL, no one knows the actual name for the UnderWaterAwesome Widget… you get the idea). For country search, there’s 100+ countries using our Widgets, so again, letting people type their own can be a problem.
I don’t really understand SQL, but if there is a surefire way to make this work, can someone please tell me the code for it? I would like to learn, but I am really starting at zero and have very little programming experience. I can read some SQL code and make some sense of it in context, but there’s no way I can create it from scratch. I have seen some websites with examples of SQL which I changed to fit my table and form and query names, and they don’t work either. They all return to me a blank query response (empty results table).
In the long run, I’d like to have one search form only, with the choice of selecting Widget type (maybe more than one at once), AND/OR country (perhaps more than one at once) and getting all the results back… in the end, the important thing is the type-country association and the document associated with it.
Other than helping with my specific problem described above, can anyone give me advice on where to go from here? I’ve come a long way in a few hours, but without knowing what to look for, it’s hard to find the right path!
Thanks so much ahead of time! This is interesting work, but so hard!