Stop duplication of appointment

[jzwp11]

Find your post with the attachement. Click on Edit Post button and then click on Go Advanced. Scroll down to the section called Manage Attachments. Find the attachment. Then go to the upper right corner of the attachment icon, there should be an X to delete.

[cheyanne]

Hi

The other screenshop was a datasheet view of tblAppointments. If that wasn't what you meant please
give me a clue to help research it for you.

Regards

Cheyanne

[jzwp11]

There was a syntax error in your code. I corrected it. I also added another criteria to the subquery: appttime is not null, because if someone enters a new visit but does not assign a time, the code does not populate the time list box correctly. You may want to have the appointment time as a required file in the table or have some code in the appointment form to make sure an appointment time is selected. The amended database is attached (I removed the client last name and phone numbers).

By the way, your tblPets already referenced the client ID, so you do not need tblClientPets as I had suggested.

[cheyanne]

Hi

Thanks very much for your patience and assistance.

The tblClientPets is not used in the database. It was designed purely to match the Clients and their Pets.

Once it was correct the file was renamed as tblPets.

Was I supposed to do anything else with the database you sent? I exported tblClients into it but have ended up with
three records - all for the same date i.e. 4th June 2012 and all for a 12.00 appointment. Have attached a screenshot.

Can you delete the attachment you sent? I have downloaded it.

Thanks again anyway

Cheyanne

[jzwp11]

OK, I deleted the previous database, but my database shows different results in tblAppointments. So I'm not sure what you did.

I also failed to remove the unnecessary fields in your tblAppointments, so a new version of the database is attached. So you will want to take a look at the fields in the appointment table. I removed both the client and client ref fields since you do not need those because you have a relationship established to the client via the pet. Repeating the client info in the appointment table violates normalization rules. I found several other instances in other tables where you do the same thing, so I removed those as well.

This database has no client last names or phone numbers, so it can be left attached to the thread in case others want to see how things are set up. Also, now that I have changed some of the fields in the tables, the forms may need to be reworked some.

[cheyanne]

Hi

Thanks again

Can you remove the attached database - I have downloaded it.

I shall have a look at your latest offering tomorrow, as I am about to have my evening meal.

The time here (in Spain) is just before 21.00, so I am closing down for the night.

I'll let you know how I get on tomorrow.

Regards and many thanks

Cheyanne

[jzwp11]

I will remove it, but after your review to assure that I have removed all sensitive information can I repost it for the benefit of others who may be following this thread?

[cheyanne]

Hi

I have opened the database but I still get the same duplicated results. I have tried to research what you sadi about primary and foreign keys but my reading supports what I thought.
Take this scenario as an example, I have three tables that are linked tblClients (PK ClientRef) , tblPets (PK PetRef) and tblVisits (PK VisitRef) to which the former two link. The first three
fields for tblVisits are VisitsRef, ClientRef (FK)and PetRef (FK). If I remove ClientRef, from the tblVisits, the combo box, which relies on a query using ClientRef as its control source,
no longer works as intended.

As regards the AfterUpdate code goes, I simply cannot get it to work and cannot see why ' if it worked for you. What exactly should happen? What should I visually see happen to show it is working?
I have presumed that the lstTimes list box should, after one has selected a time for a particular day, refresh and then re-populate lstTimes but that it would no longer show the
appointment time that has just been selected.

If, there is no visual clue, would it not make sense to show the user that the appointment would either cause a duplicate or that the appointment is confirmed.

I have attached a screenshot that shows 5 records all made on 4th June, records 1, 8 and 9 are all for a 12.00 appointment. I , of course, had to import the tblClients to
restore the full details and made the lstTimes show "times" rather than the "times" ID. I made the relevant relationships, made the txtClient and txtPetName unbound fields
but apart from that have not changed anything else that I recall. When you ran frmAppointments at home did it work as you intended it to?

As regards the database being online, I believe the Veterinary Surgeon would prefer that not to be the case. It was only because I was getting desperate that I sent it to you
and I suspect that she would not be best pleased if she knew I had done so.

I´ll keep trying to get it to work as this is important. Is there any other way of achieving a similar result that you can think of?

Regards

Cheyanne

[jzwp11]

The first three
fields for tblVisits are VisitsRef, ClientRef (FK)and PetRef (FK). If I remove ClientRef, from the tblVisits, the combo box, which relies on a query using ClientRef as its control source,
no longer works as intended.

That is correct. You would use an unbound combo box based on the client table to filter the pet combo box (cascading combo box technique). The cascading combo box technique is similar to what I proposed to do with the date textbox and the time list box (use the inputted date as part of a filter). Since the pet is already related to the client as you mention, having both the client and pet reference in the appointment (or visit) table is unnecessary. Further, repeating the names in addition to the key field (clientref) violates normalization rules.

I would recommend trying the date filter/list box using a new record rather than an existing record. Enter a date and the list box should filter.

Did you have any errors or security warnings when you opened the database? If so, did you put the database in a defined trusted location?

restore the full details and made the lstTimes show "times" rather than the "times" ID

What exactly did you do to accomplish this? Did you change the bound column of the combo box? If you did that would impact the proper functioning

[cheyanne]

Hi

Someone gets up early! It's 13.30 here as I write.

As far as lstTimes goes all I did was to make the two columns - AppointmentTimeID and AppointmentTime - into one visual one. (NB I changed the name of this field so that I could
better understand what was happening - I removed the relationships - deleted the table and re-entered the data) and simply made the first column 0cm. It is still bound to
field 1 - see attachments. I, of course, altered the one entry in your AfterUpdate code, for the field I changed the name of, and saved it. I did try the database before
making any such changes and it still would not work. Please explain what is supposed to happen visually - how do I know when an appointment has been successful?
I feel that it is important that users can see what is happening and, for example, in lstTimes are able to select an appointment time not just a reference to that appointment time
i.e. the AppointmentTimeID field.


I shall try what you say about the combo box but wonder whether I shall get error messages if I do. I accept that it might violate normalisation rules but sometimes
small deviations from the "perfect" situation are either called for or justified. For obvious reasons I do not want the whole database to collapse in order to make a
few alterations.

I'll let you know what happens.

Regards

Cheyanne

[cheyanne]

Hi

Have done as you said and the results of the combo box are not as I require.

Firstly frmAppointments is no longer a record of the booking of the appointment.
Users need to be able to refer to that form to see who has been boooked in at what time, with which pet and notes etc.
Secondly when you select a name (it does select his or her pets) that name is shown on all the records. I did try a
Me.cboClients.Refresh on the AfterUpdate event but that did not alter anything.

I'll try deleting tblAppointments and see if that helps.

Regards

Cheyanne

[jzwp11]

Please explain what is supposed to happen visually - how do I know when an appointment has been successful?
I feel that it is important that users can see what is happening and, for example, in lstTimes are able to select an appointment time not just a reference to that appointment time
i.e. the AppointmentTimeID field.

After a user creates a record for a specific date and time, the next time a user creates a new record with the same date, the times list box should not show any times that were previously selected. In other words, the list box will have few times from which to select.

[jzwp11]

My proposed solution and other suggestins will probably only work with the form in Form View not Datasheet View; perhaps it would be best if you pursue Bob's approach as noted in post #2 of this thread.

[cheyanne]

Hi Bob

I did as you suggested and communicated with jzwp11 who was extremely helpful, but after innumerable posts I was still unable to get his idea to work.
He suggested that I return to your idea.

Is there any chance that you could give me the DLookup "code" that would achieve what you felt might work? If not it is back to the drawing board!

I have attached two screenshots - one of tblAppointments fields and one of tblAppointmentTimes fields to hopefully assist you.

I do hope you can assist

Regards

Cheyanne

[jzwp11]

I would probably use the DCount() rather than DLookup(). The following assumes that the bound field of lstTimes is the ID field

IF DCount("*","tblAppointments", "AppointmentDate=#” & me.AppointmentDate & “# AND ApptTimeID=” & me.lstTimes ) >0 THEN
‘code to handle duplicate goes here
END IF


If you decide to change your appointment table back to include the time itself rather than ApptTimeID and make the time the bound field in the lstTimes, then the above DCount() will need to be changed as follows. Dates and times must be delimited by # signs.


IF DCount("*","tblAppointments", "AppointmentDate=#” & me.AppointmentDate & “# AND ApptTime=#” & me.lstTimes & "#" ) >0 THEN
‘code to handle duplicate goes here
END IF