Date Entry Form, enter multiple records at once

[AdiDoyle]

I'm not sure if this is possible, but I'd really appreciate if someone could let me know.

I have a data entry form for a table, Appointment. The data that goes into this includes Doctor_ID, Patient_ID, Appointment_Date, and Appointment_Timeslot.

Some patients will require multiple appointments, but it would be tedious to repeatedly put in the Doctor and Patient information for, say, six appointments. Is it possible to use one data entry form to input multiple records at once, whereby I could select the Doctor_ID and Patient_ID once, but input multiple separate dates and times, then save all of these records in one go?

If not, is there any way to auto-fill the fields of a new record with the information which was submitted into the last record saved? Eg, when I click "save appointment" a macro would both save the information for that record, and insert it into the fields of a new record, so all I would have to do is enter the date and time of the next appointment before saving that too.



It would be great to hear any solutions to this problem. Thanks in advance.

[Bob Fitz]

I think your table design might be improved. Google "access many to many" and have a read.

[AdiDoyle]

Hi Bob,

Thanks for the suggestion. I should have probably mentioned that the patient and doctor IDs are foreign keys to separate tables. I have the whole DB in 3NF as far as I can tell, and I don't think there's anywhere I need to add an assign table to deal with a many to many relationship.

Maybe I'm missing something, but how would an issue with a many to many relationship between my tables affect the functionality of a form?

[orange]

Post a jpg of your tables and relationships. Perhaps you could show readers some code to clarify the issue, or a sample of your data showing what you are dealing with; what you expect and what you are actually getting.

[AdiDoyle]

Hi Orange.

Here is a jpeg of my tables and their relationships. As you'll see it's a very simple database.



Here is a jpeg of my the form (also very rudimentary):



What I'm trying to get it to do is this. Say I enter values into each of the 6 fields and click save. Can I write a macro that will not only save the data to a record in the appointments table, but also open a new blank version of the form AND auto-fill the information I've just inputted for the last record into the first four fields (Patient, Eye, Doctor, Drug)? This would save me inputting this information every time I want to book a single appointment for the same patient.

[AdiDoyle]

Also, I just realised I misspelled the title of this thread. I meant to call it Data Entry Form... Mea Culpa

[orange]

Where does Eye fit into your business/model?

Would you not also have a dosage for the Drug and/or a prescription?
Sometimes it's easier to describe a typical day in the business in simple English to test/vet your data model.

There are generic data models as DatabaseAnswers.org

Here is one, and the business requirements/facts that may be of interest to you
http://www.databaseanswers.org/data_...ctor/index.htm

[AdiDoyle]

This is a basis for a database to be built for an ophthalmologist, so the eye is a short text field. It can be either left or right.

The dosage is particular to the type of drug being used.

I don't think the data model is the issue here. I realise it's simple, but it fits the purpose that was described to me. There is no need for a many-many relationship between the Drug and Appointment table, because there will only ever be one drug per appointment.

I really just need to know if there's a way to auto-fill fields in the data entry form using information from a previous record. I know I made a mess of explaining that, so my apologies for the confusion. And thanks for all your assistance so far.

[orange]

Well keeping things real simple, you might consider using a tempvar(s) to record the data you're concerned with. You put the data value(s) into the Tempvar(s) and you can use the Tempvar(s) to put the latest data into the table field.

If it's a common value for all records you could review this
http://access.mvps.org/access/forms/frm0012.htm

See this post and link by pbaldy re previous values
https://www.accessforums.net/showthr...0325#post50325

Good luck.

[AdiDoyle]

Tempvars were exactly what I was looking for.

Thanks very much!