none
access 2016 problems RRS feed

  • Question

  • Access  2016

    I’m new to databases but have managed to grasp the basics. What I am trying to do is make a plant database which can be searched by entering a plant name and have the name, image, features and description displayed on another page (or enter features to find plants that match). The database will be stand-alone with no internet requirement. There will be only 1 user – me, (or anyone I choose to give a copy to) so there is no use need for data entry forms.

    I have built a main table with all the unique names, plus the fields which are linked to tables where the features are held. All of the relationships have been made and running a search or report produces correct results – HOORAY!

    I’ve now created 3 forms, 1 to enter name search data, 1 for searching by feature and 1 to display the results – they are very pretty but they don’t work.

     I created a series of combos (e.g. 1 for each feature and used the wizard to complete each combo, so far so good.

    But I do have problems with the controls wizard, sometimes when I select combo, the wizard doesn’t appear, even though it’s switched on. If I do the same thing afterwards, the first wizard page appears, sometimes with 2 questions and other times with 3 – it’s very touchy. Why is this?

    Whether there are 2 or 3 questions, I’m not sure which of the tables is required – I’ve tried the Main table and also the feature tables. The result is the same – whatever is selected from the combo does nothing, it just sits there looking at me. I’m puzzled, If, for example, I select the table “plant type” from the combo and choose “bush”, how does it know that I want to pull the results from the main table (ie, all the records with the  term “bush”)? Through the relationships? If so, it doesn’t work for me. I’ve tried the help and tutorials but got nowhere, except to lose a lot of hair.

    Please could somebody explain how to do this (without using programming jargon or acronyms, I’m a simple layman). Thank you.

    Thursday, March 5, 2020 1:52 PM

All replies

  • Here is pretty good video of how to do that:

    https://www.bing.com/videos/search?q=ms+access+combo+box+search+records&&view=detail&mid=82831A294CD7A15D344782831A294CD7A15D3447&&FORM=VRDGAR&ru=%2Fvideos%2Fsearch%3Fq%3Dms%2Baccess%2Bcombo%2Bbox%2Bsearch%2Brecords%26FORM%3DHDRSC3

    Thursday, March 5, 2020 3:36 PM
  • The fact that you apparently have multiple 'features' tables  concerns me as it suggests that data might be being encoded as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    The sort of problems you are now encountering are typical of an incorrectly designed physical model (the set of tables and relationship types between them).  The key to any successful relational database is getting the model right so that it accurately reflects the real world entity types  and relationship types.  Get the model right and the interface design should flow flow naturally from it.  Get it wrong and you'll for ever be jumping through hoops to work around its deficiencies.

    It would be helpful if you could provide details of your current model.  The easiest way to do this would be to post an image of your database's relationships window, set out so that all tables, all of their columns (fields), and the relationships between the tables are clearly visible.

    In the meantime you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    In this little demo file, in addition to outlining the principles behind the design of the physical model, the section on 'retrieving data from the database' gives a number of examples of how data can be returned on the basis of values entered or selected in unbound controls in forms.


    Ken Sheridan, Stafford, England

    Saturday, March 7, 2020 2:28 PM