Puzzling behavior

[Dave14867]

Hello all,

I creating a new file for a friend and have started to put some bogus data in it and ran into some strange behavior.

When you open the file the Navigation opens, select #9, the main form will then open and aside from some clean up thins are good. If you select "Medications" and/or "Conditions" on the tabbed form, all looks as expected. Next select the next record and here is where things get wonky. You'll notice the animals name has changed to "Brenda" but on the "Vaccinations" tab the name is for the previous Animal "Roxy", however if you select the "Medications" or "Conditions" tab the name is correct.

Why isn't the name correct on the Vaccinations Tab?


Pets-2 Help.zip

Thanks in advance


Dave

[ssanfu]

This is a good one. Took a little bit to understand what was going on.

In each of the tabs, "Vaccinations", "Medications" and "Conditions", you have an unbound text box to display the pet's name. I couldn't find any code to set the text boxes to the pet name.
But then I found it. The reason the 3 text boxes are acting "wonky" is because you have set the DEFAULT value of the text boxes to the pet's name (first record). When you change to the next record, the DEFAULT value is displayed.... just as you programmed it.


There are several solutions -
1) change the unbound text box to a bound text box. Set the Control Source to "AnimalName" and set the Locked property to True and set the Enabled property to False. (Delete the value in the DEFAULT property)
2) Delete the value in the DEFAULT property and set the Control Source property to "=[Forms]![frmAnimalDataEntry-Tabbed]![AnimalName]" (because the text box control is in a tab control and not a sub form, you can just use "AnimalName" - don't have to use the fully qualified path)
3) Delete the value in the DEFAULT property and add 3 lines in the form Form_Current() event to push the "AnimalName" value into the 3 unbound text boxes.


My preference would be solution 1.....

[Micron]

Took me a while too - quite confusing. I'd agree that there's no real reason to not have the control bound, but I'd also put it on the form rather than each tab. 3 or 5 controls to display the same info isn't the best design approach, same as multiple forms/reports/queries for the same basic functions. Ditto on the rest.

[ssanfu]

I'd also convert all of the Macros to VBA code (I really don't like Macros - can't read them)....... but that is just me.

And all of the check box items in table tblVetClinic are actually data and should be in their own table.
What would happen if you wanted to add another service? You would have to redesign your whole dB - table, query, form, macro,code and report(s).

[Dave14867]

Thanks for the replies, I agree no reason to have them there actually, just thought it would clarify that the items were specific to that pet. I also agree now that there is no reason to have them unbound.

ssanfu,

I see what your saying about the check boxes but could you expand a little more on that.

[edit] I created a table for specialties but I don't see how I can have different ones defined for each clinic.

Thanks

Dave

[ArviLaanemets]

I see what your saying about the check boxes but could you expand a little more on that.

[edit] I created a table for specialties but I don't see how I can have different ones defined for each clinic.

One possibility:
Create tables tblSpecialities/tblServices, and tblAnimalTypes. Then tables tblClinicSpecialitie/tblClinicServices and tblClinicAnimalTypes, which bound the list of services and list of animal types with entry in ctblVetClinic . Instead of those check boxes, add 2 subforms - one with continuous subform for animal types treated, another with continuous subform of available services.

Of course you can have also multi-selection lists instead of subforms (and drop bounding tables, having 2 multivalue fields in tblVetClinics instead), but I don't adviece this, as this may cause problems when e.g. in future you want to port your app to some other database system. And reading data for e.g. reports will be complicated.

[Dave14867]

ArviLaanemets,

I am having trouble understanding what you mean, sorry for being so dense about this.

tblSpecialities would just hold the list of Specialities correct?
tblServices would hold what info?
tblAnimalTypes would hold what info?

Could you put together a sample of each so I could get a better understanding?

I thought I had a grasp of what to do but when I tried it out it didn't work, I want to do it the correct way, I just can't wrap my head around this one.

Thanks
Dave

[ArviLaanemets]

In your post you used name Specialities. In your form is used name Services. I was not sure which one to use.

tblAnimalTypes: AnimalTypeID, AnimatTypeName
AnimalTypeID AnimaTypeName
1 Small animals
2 Large animals
3 Exotic animals

tblClinicAnimalTypes: ClinicAnimalTypeID, VetClinicID, AnimalTypeID

And in similar way with services (or specialities).

[Dave14867]

Arvilaanemets,

I used services and specialties interchangeably, I do that often, sorry. I created a tblSpecialties already and have

SpecialtyID
SpecName
Description
SpecChk
VetClinicID
Comments

My problem is I put in an entry for each type of specialty so in total I so far have 24 entries but I don't know how to tie that into a subform. I can only have 1 VetClinicID for each entry correct?

Thanks

Dave

[Dave14867]

Here is what I put together



Is this what you meant?

Thanks

Dave

[ArviLaanemets]

1. Remove VetClinicID from tblSpecialities (and leave only 1 record for every speciality);

2. Create a table tblVetClinicSpecialities: VetClinicSpecialityID, VetClinicID, SpecialityID;

3. Create a continuous form based on tblVetClinicSpecialities, where SpecialityID is linked with combo box with source "SELECT SpecialityID, SpecName FROM tblSpecialities". Set field count to 2, SpecialityID as linked field, and column widths = "0, 2.5". Hide controls (text boxes) linked with VetClinicSpecialityID and VetClinicID;

4. Add created continuous form into VetClinics form as subform. When linking fields for both forms are same (VetClinicID), then the link is created automatically. When not, then you have to set parent and child keys manually (NB! In subform properties. You have a subform which Access names exactly same as it's source form [e.g. fVetClinicSpecialities]. To avoid cofuses in future, I recommendate to change the subform name [e.g. sfVetClinicSpecialities]).

When all was made correctly, in subform is displayed a list of all specialities defined for clinig active in parent form. And whenever you add a new speciality into subform, it will be automatically linked with clinic currently active in parent form.

To avoid double registering of activity for same clinic, you have to create an unique index in tblVetClinicSpecialities consisting fields VetClinicID and SpecialityID.

[Dave14867]

ArviLaanemets,

Now I am totally confused but I will try to do as you say step by step and see how I make out. This is far beyond what I have done so far with a database but hey, always learning right.

Removed VetClinicID from tblSpecialities

tblSpecialities does have only 1 entry for each specialty ( for a total of 28 records)

Will work on new table and form based on that table and let you know how I make out.

Thanks
Dave

[Dave14867]

ArviLaanemets,

Attached is the file again with the subform as you suggested. Is the what you were talking about? It does seem to work as you said it would ( I never doubted you but like I said, I don't really understand the concept yet).

I am not sure yet what you mean by "To avoid double registering of activity for same clinic, you have to create an unique index in tblVetClinicSpecialities consisting fields VetClinicID and SpecialityID."

Can you take a look and see what other suggetions you have to assist.

I still am not sure yet what to do about "Animals Treated" or would that be done the same way with a subform?

Pets-3.zip

Thanks
Dave

[ArviLaanemets]

Attached is the file again with the subform as you suggested. Is the what you were talking about? It does seem to work as you said it would ( I never doubted you but like I said, I don't really understand the concept yet).

My home computer is Ms Office-free, so during weekend I can't look at it.

I am not sure yet what you mean by "To avoid double registering of activity for same clinic, you have to create an unique index in tblVetClinicSpecialities consisting fields VetClinicID and SpecialityID."

Lets assume you register a clinic Clinic1, and in table tblVetClinicsActivities you register for this clinic activity Activity1. Unless you take some steps to prevent this, you can register the same activity for this clinic again. and the you can regiter the same activity for same clinic third time, etc.
To avoid this, the easiest way is to define for tblVetClinicsActivities an index which prevents it. You can determine, that fields VetClinicID and ActivityID in tblVetClinicsActivities together compose an index, give a name to this index, and determine, that this index is unique. This means, that any combination of those fields can be inserted into table no more than once. Whe you try to do this, an error is returned and the entry is aborted.

I still am not sure yet what to do about "Animals Treated" or would that be done the same way with a subform?

Exactly the same way.

[Dave14867]

So do you mean make the Primary key from the 2 fields?