Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I modified your tables/relationships somewhat. (just suggestions)


    Click image for larger version. 

Name:	David1.png 
Views:	17 
Size:	211.0 KB 
ID:	38078

    I think you still need some work on the tables/relationships......

    My $0.02.......
    Attached Files Attached Files

  2. #17
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Dave14867 View Post
    So do you mean make the Primary key from the 2 fields?
    In this case you can make from those 2 fields a multy-field Primary Key too, but sometimes it may be a cause of difficulties.

    You can have only one Primary Key in table. But additionally you can have any number of other indexes - unique or not unique, single-field or multy-field. Having too much indexes may make your database slower. At same time having some field indexed makes queries, using this field for grouping, filtering or ordering, to work much faster. Or as in current case, allowing avoid having time-consuming code to check for double entries. (This is theory part.) With one additional index you'll have no problems at all.

  3. #18
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    ssanfu,

    Can you take a look at this, I cannot get the new subform to accept any entries and I just cannot figure out why.
    The new subform is on the frmVetClinic as a subform frmAnimalTypesSubForm.

    I have tried everything I can think of including recreating the Sub Form from scratch several times.

    Pets-3 modSS.zip

    [EDIT] I got this working finally, had some issues with the design of the subform I was overlooking.

    Thanks

    Dave

  4. #19
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    ssanfu, Arvilaanemets,

    Attached is where I am now, if you have a chance please take a look and let me know is this will work properly and be error free. I made both ID Fields in both tables indexed with duplicates, is that correct to prevent the errors mentioned earlier, Like I said before, I am not familiar with doing things this way.


    Pets-4 Dave.zip

    Thanks

    Dave

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    Had delete a couple of records, and compact and repair the database to be able to upload the attachment, as it reached 2MB limit for zip files.

    At start, open in your original app Vet Clinics form! For clinic opened at start, try to enter Small Animals 2nd time! No problems with it?

    Try the same with updated app in my attachment! You get an error message (A long and ugly one, but having Access to give readable error messages is entirely another topic!).

    The reason updated app works differently is the unique (select Field Name field in row with index name, and look at 3 fields in bottom left corner of <Indexes: tblAnimalTypes window - for middle one Yes is selected) index VetClinicAnimalType I defined for tblClinicAnimalTypes. Two additional indexes you defined (ANimalTypeID and VetClinicID) can make some queries to work faster (when they use those fields in WHERE, ORDER BY or GROUP BY clauses), but they don't prevent any double entries.
    By the way, you have also a table tblVetClinicAnimals with exactly same fields set. One of them is abundant.

    In same way, I created an unique index VetClinicSpeciality for tblVetClinicSpecialities.

    Some additional remarks/advices:

    In Navigation form, most of buttons are there doubled. This only crowds the form, and is confusing. When you want the user to easily differ between buttons used for main work, and used for specific rare occasions only, simply place them in 2 separate groups (e.g. left one and right one, with a border around group and some header label at top);

    Instead of table tblBreed, better use a table tblSpeciesBreed. Current setup assumes, you have a lot of breeds which are same for different species! As much as I know, a breed is a subcategory of species.
    tblSpeciesBreed: tblSpeciesBreedID, SpeciesID, Breed, ...
    When you then add a Breed "not defined" or "undefined" or something other like this for every species defined (for cases when e.g. the dog is a mutt). Then you can have this table as the one every specific animal is linked to. With this setup, you also may have the field Breed in form linked to single-field combo with a single selection to this "not defined" breed name and set combo's LimitToList property to False. The user can enter any breed name freely, and can select this "not defined" breed from combo.
    Another way is to handle breed as additional info of species. User can register any breed of species, but it is optional. With this setup, there is no need for "not defined" breeds, and the table every animal is linked to will be tblSpecies. When you want breed info for specific animal to be displayed too, an additional field for breed in tblAnimalDataEntry is needed;

    All this long text about species and breeds was, because without defining either in tblSpecies or in tblSpeciesBreed the animal type there will be a hole in your database. When user defines that a specific clinic does handle only e.g. exotic animals, then there must not be a way to register a horse for procedures in this clinic. When there is a field in one of those tables, which determines, that a horse is big animal, you can prevent this.

    Btw. To limit possible entries accordingly some rules, combo boxes are best tool. I did see, that in your forms you use them rarely.
    Last edited by ArviLaanemets; 04-15-2019 at 02:26 PM. Reason: An essential word (made it bold) was left out

  6. #21
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like there passed too much time after I uploaded the attached file - it was gone when I saved the post.
    Attached Files Attached Files

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Some of my naming conventions:
    A PK field will have the suffix of "_PK".
    A FK field will have a suffix of "_FK".This is to make it easier (for me) to know which fields are PK or FK when looking at form designs.
    If a control is hidden (not visible) I make the background a light yellow.
    If a form is a sub form, I use the prefix "sf".
    I use a prefix "jnct" (junction) for junction tables. Examples: "jnctVetClinicSpecialties"....."jnctClinicAnimalTypes"
    Object names are only letters and numbers (exception is the underscore). NO dashes......

    Remember, these are my naming conventions. You should develop a convention

    You don't need both the form frmClinicAnimalTypes and the form frmClinicAnimalTypes-SubForm. Pick one.
    Attached Files Attached Files

  8. #23
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    ssanfu,

    I see that you had changed the names of some of the items and I had figured out part of the naming convention and it does make sense and easier to follow. I do appreciate that you also said that you changed the color of invisible fiels and labels, I was wondering why those were a different color in the one subform but that too makes perfect sense and easy to identify at a glance.

    I would like to thank both of you for assisting me on this, I just could figure out how to get it the way I wanted. You have been a huge help and I learned a lot.

    Thanks Again

    Dave

  9. #24
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're very welcome.

    Good luck with your project....

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Puzzling validation rule for text only
    By bwelton in forum Database Design
    Replies: 4
    Last Post: 12-03-2015, 09:08 AM
  2. puzzling syntax error in this query
    By BrockWade in forum Queries
    Replies: 16
    Last Post: 12-17-2013, 02:53 PM
  3. puzzling slowdown
    By Lewis in forum Access
    Replies: 2
    Last Post: 11-06-2012, 03:27 PM
  4. Puzzling DCount behavior
    By RagJose in forum Access
    Replies: 8
    Last Post: 04-15-2012, 06:26 PM
  5. Puzzling #Name? error
    By sprovoyeur in forum Forms
    Replies: 9
    Last Post: 03-24-2010, 08:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums