why doesn't this work?

[chris davies]

hi folks,
I am designing an application for an assignment for college, all seems to be going ok except that I have to teach myself access from a book. When I update a record on a form and go to another page it works fine, but if I attempt the same action ( for example creating an appointment then moving to a new form to confirm they have arrived) a second time I am getting an error which tells me I can't save the record. I think it is to do with updating the recordset when the new form is opened. I have an update record button on the form (created by the wizard) but it wont update on the second attempt. Could someone please tell me the code to update a recordset on the formload procedure. Ive tried me.recordset.refresh and me.recordset.requery but I still can't solve the error. Please help I've got to hand this assignment in in 2 weeks. thanks in advance
Chris

 

[pafcpilgrim]

Chris is all the information in one table? and how many forms are you using exactly?

If you create a simple appointment booking system, i would have one form to enter the appointments in and another form to view and be able to confirm any appointments.

If you are editing a record as long as you save the record everything should be fine (when you said you created an update command button i'm guessing you mean a save record button?), the refresh and requery do check for updated information from the table, but i would need to know a bit more about how you have designed things to work.

You can email the db if you wish i can look at it not a problem.

Mike

 

[chris davies]

why doesnt this work

to be honest I have been dumped with this am I am really struggling. What is the size limit of emailing databases to the forum as so far it has reached about 3.5megs in size.

I have 8 tables with all primary and foreign keys and 1:M relationships all set. My lecturer says the database structure is good. I am having problems applying this in Access.

what I have, is supposed to be an appointment system for a dentist, and while the add patient form works (based on a table), and the appointment form works (also based on a table, works sometimes) but when I go to the next form to confirm arrival and I am using an append and update query to copy certain fields to another table and to set a flag so that the record can't be copied over a second time. I have gotten myself in a knot after taking advice from someone who told me to use append and update queries. As I am totally inexperienced with this I am floundering. This was supposed to be a team project but two of the students have dropped out of the course leaving just two of us to get this sorted out and we are running out of time.

 

[pafcpilgrim]

Chris,

You could email me the db file my email address is visible, but why do you want to post some of the field data to another table when confirming the appointment.

This is how i would structure it, i do appreciate your knew to this so am trying as best i can to help.

1: TblCustomers
2: TblAppointments

The two fields that create the relationship are the customer id's and they would be a one-many one customer many appointments. I would have a main form with the customers details on (TblCustomers) and then a subform from TblAppointments you could use a simple checkbox as the confirmation of appointment or arrival etc.

If you wanted to view all active appointments you could then have a seperate form that using a query only display all those where the confirmation criteria is false (not checked)

Chris what would help is if you let us know what the table names are and do and also what information you are copying across with these queries an why. Good Databases are based on relationships so there isn't a need to duplicate information every time as long as the struture is right you shouldn't need to copy information about a good query can help you link the information together nice an easily.

If your DB is very big in size you can compact the database using the Tools-Database Utilities-Compact and Repair on the menu.

I appreciate your skill level is not great but i will at least try and sort it out if i can for you.

Mike

 

[chris davies]

why doesnt this work

Hi, Your email isnt visible, or at least I cant find it the forum says you dont want to recieve emails.

my relationships are set correctly, I'm sure of it. I have quite a good understanding of this area. Here is my database setup:
tables:
Patient: PatientID as PK, Name etc......
Staff: StaffNo as PK , Name etc......
Timeslot: Timeslot as PK, AppTime
Appointment: StaffNo, Timeslot, Date as PK (concatented, to avoid double booking) PatientName as FK treatmentNo as FK
Invoice: InvoiceNo as PK extra fields....
Treatment: TreatmentNo as FK extra fields....
Visit: VisitNo as FK extra fields....
Archives: ArchiveNo as FK extra fields....
I have a patient screen where I can enter new patients, appointment screen to add appointments. What I am trying to do is have a Visit screen (based on a visit table, where I can confirm the patients arrival, and Invoice screen where I print out the invoices and an Archive table where I dump the old records based on a DateDiff Query.

I have a reminders query which runs on the reminders screen using DateDiff to print out 6 monthly reminders then sets a flag so the same reminders dont come up every day, this is working fine. so is the Appointments and Patient data, the problem I am having is with transferring to the Visit screen from the Appointments Screen, where the user can confirm arrival using a yes/no lookup and update the table.Same with the invoice and archive tables. I have been using an append query and an update query to copy only some data and set a flag, from the Appointment table to the Visit Table because (mainly due to bad advice) I dont want all the same data in the Visit table and the Visit table has some fields of its own.

The same problem lies with the invoice and archive tables.

It works for the first record I enter, but crashes for the second record, so I need to find out how to refresh the data when I switch from form to form.

I hope this clarifies matters a bit. And I really appreciate your help.
Chris