Full Version: i am having trouble adjusting a database.
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
i have attached the database in this post. i did not create the database but have adjusted it but have knw hit a wall.
That i want the database to do is:
I want to create an appointment creator. there is a patient information form which the command will be displayed in to access the appointment creator. you knw select a date, in this appointment creator you select a doctor which u need to set an appointment with. then the form updates showing the available times for that doctor, once u have selected a doctor u select one ofthe available times and enter the patient th appointment is for. once this is done you click ok and this appoinment is made and then when you go back to patient information in the next appoinment text box the appoinment is listed there with the doctor name.
also i want a complete seperate form opening up all the appointments for each doctor only in ONE form.
Has i am just changing this database i am having big problems.
i have changed the time to suit me which is from 8.30 to 5.00 in 30 minute intervals.
i have changed the doctor names and in the appointment form where it says 'estimator' well i need this to be 'select doctor'.
Area is not needed.
Look up states is not needed.
and some forms are not needed but i havent deleted the stuff not deleted because it might mess te database up.
i have hit a wall at the part where i have changed doctor names but when i open the form and select estimator it only gives me one doctor to choose.
will comeone look through the attachment and help..
Larry Larsen
Just had a quick look.. you have behind your combo a field called wTypeID and it's criteria is set at (1) so only (1) record will currently show as all your doctors have different numbers..?
here is your database back
made too many changes to mention -- deleted extra items down to just a skeleton
ie --> old name --> Child163
new name --> Appointments_Sub10
Odid NOT test very much; leaving that up to you now that is is simpler to work with.
Modified main forms and subforms for
1. Appointments
2. Doctors
Hi I wrote an appointments db which you can download from Glen Krugers Site you might find this more along the lines of what you need.
thanxs for changing the database for me i really appreciate it but there is one problem which i have come across in the appointments form you cant add an appointment for some reason errmm its probably just a slight glitch i will check it out and try to see whats rong.
hanx for u knw changing it.
the only thing i require to do knw with my appointment maker is add in the patients.
asically when an appointment is made for a patient i want the appointment and name of doctor to be added into the next appointment text box for the patient.
any ideas..
If you look at the LinkMasterFields and LinkChildFields, you will see that DrID MUST be filled out on the main form (just under the date) before you can add or view records to any of the subforms -- and DrID is NOT VISIBLE on the subforms...
If you want to add a combo to the subform to choose a PatientID, that is a very easy addition
1. add PatientID to the table structure
2. then, modify the Doctor_Appointments subform and the Appointments_Sub subform
add PatientID to your tblPatientData table
PatientID, autonumber
add PatientID to the Appointments table structure
PatientID, long integer, defaultvalue --> null (in case you are blocking out a time slot but there is no patient)
strPatientID is NOT a good field to use to link tables
THere is a post with information on setting up data efficiently. Although your data is already set up, once you understand Access better, you may decide to change what has been done to be more efficient.
A new guy figuring out how to search -- discussion on normalizing data, designing tables and forms

about halfway through the post, there is a lengthy discussion on data structure, naming, etc ... and about 1/3 from the end is instructions for documenting the relationships and table structures -- this is an invaluable reference as you are building your database

The whole thread is an excellent read -- ScottGem does an awesome job (as always) at opening up another perspective. You need to look at your database using other perspectives.

Access is tough, but it is tremendously easier when you understand the basics. You are constructing a complex system, you would be wise to take a day and review basics before continuing.
If you do NOT want to schedule for one doctor at a time on the main Appointments form, remove DrID from LinkMasterFields and LinkChildFields and collect DrID using a combobox on the subform.
Since I posted the original database you downloaded, I felt obligated to help you get to a simpler example and I spent a couple hours stripping out what you didn't need and changing the main appointment scheduler for Doctors instead of Customers.
...not quite accurate ... YOUR obligation, now, is to take the time to understand what you have been given -- I hope this will help.
steps for documenting database
If you do these steps on what I posted back for you -- and also what others have graciously given you, you will learn a tremendous amount and we will feel better that we have truly helped you understand -- no one here wants to give you information without understanding on your part.
Ocan see from what you created that, while you have an understanding of some things, you are struggling with others.
One of the most important things when you design forms, in my opinion, is to give EVERY control you create a LOGICAL name as you make it (labels are the only control types where I don't always worry about that).
Good luck to you
hey thanx,
I have changed strpatientID to PatientId and it is an autonumber knw.

What am i ment to be modifying in the Doctor_Appointments subform and the appointments_Sub subform.
why do i have to add the patient id to the appointment table structure. and how do i do this do i just paste the column in or is their some special way in which to do this.
you're welcome
Well, you only need to do this if you want to associate a patient with an appointment -- right now, there is just a Notes field and a DrID
To modify the table structure, go to the DESIGN view of the table you want to modify
1. type the fieldname
2. choose a data type
3. change other properties such as field size, format, default value, etc...
tips on learning more using Help
so i have used the look up wizard to get the values from the patient table the patient ID
Is this correct and what an i ment to modify in the sub forms
You need to take a day and read the links you have been given. Study and you will be rewarded with knowledge and understanding.
Forget about using the wizard. If you read the link I gave you on setting up data structures, you would have found instructions for creating the combobox
just saw a post you made on this topic in a previous thread
in which you stated, "anyone else with help apart from freakazeud"
I must say something, publicly, like you did...
Oliver (freakazeud) is one of the most knowledeable people in Access that I have ever had the good fortune to come across, and is one of the first names that come to my mind when I think about people who are true experts; his knowledge and patience is outstanding and some of the "standards" in my main library came directly from him. Everything I have ever read from him is solid and good. You owe him, and those, like me, that have great respect for him, an apology. Perhaps English is not your first language -- don't confuse the frustration of not understanding with the idea that you are not getting valuable help. It is up to you to ask SPECIFIC questions when you need more information.
I know that others on this forum feel as I do, Oliver is great and a wonderful asset to this forum.
why canti use wizard look up i think it is perfectly right
Suit yourself. Use the wizard, don't use the wizard. Learn something, don't learn anything. Read the links you've been offered, don't read the links. Follow the advice of the best developers in the world, ignore them. It is all your call.
It one point in another thread you made it clear that you are trying to complete an assignment for your coursework. It is now equally clear that you have no intention of actually learning anything in the process.
Here's the bottom line: Learning is a journey, not a destination. If you complete this assignment without acquiring any understanding of how or why you got there, you will have wasted your time, your instructor's time, and the money of whoever is paying for this coursework.
You've been given NUMEROUS chances to learn from several people who are the very best in this business. They've been remarkably patient and generous, but the final outcome is on your shoulders. Learn or don't learn. It's all up to you.
Have a Happy New Year.
George Hepworth
yes mate i have read the links and yes i am understanding it and yes mate i am learning
I agree with George on every point that he made -- well-stated, George wink.gif
..in my opinion, use of the wizards is for people that do not want to take the time to understand what is going on
if that is the route you choose, then good luck to you
When you set up a combobox yourself, you gain understanding about the properties that need to be set and you will be much more knowledgeable for it. It is not hard, but it does take an effort on your part as it will probably take some time to get it right.
THere are the basic properties you will need to set for a combobox
in the DESIGN view of the form, you can turn on the property sheet; from the menu -->
View, Properties
then, the properties of whatever object you have selected will be shown, so select the combobox
press F1 in any property you do not understand -- that will give you specific help and you can print that out and start making a notebook for yourself (that was in the link I gave you in learning more using help) ... you should READ the links you have been given. I took a couple hours to strip down the db for you -- at least spend that amount of time to understand the additional information that others have so generously given.
when you are in the RowSource property, you can click on the Builder button (...) to the right -- you will be taken to the SQL builder, which is like the query builder you probably are already familar with
yes thanx,
One problem i cannot delete appointments in the doctors form.
but there IS a button to delete appojnts in the Appoinments_sub form ... simply copy the button to the other form and then copy the [Event Procedure] code that is behind the form.
While I do not often advocate redundant code, it is more forgiven when people are learning
when you do copy the code, take 15 minutes to understand it and be sure to Debug, Compile
press F1 on each keyword in the module that you do not know -- help will pop up; you can print it out and add it to your notebook
If you are trying to create controls and fill the properties yourself, either
. turn off the Wizards
click the toggle button on the toolbox that looks like a magic wand with pixie dust
2. click CANCEL when the wizard comes up
im still trying to get the patient ID combo box and i am having trouble i am follow on of the links you gave me.
look up the value for each of the following properties for your combobox and post it back:
Name = PatientID
ControlSource = PatientID (is this from the doctors table or the patients table)
RowSource = Patient
ColumnCount = 2
ColumnHeads = No
Columnwidths = 2"
ListWidth = 2"
try changing these properties...
owSource -->
SELECT tblPatientData.PatientID, tblPatientData.strPatientID, tblPatientData.strSurname, tblPatientData.strForename
FROM tblPatientData
Orderng these properties...
owSource -->
SELECT tblPatientData.PatientID, tblPatientData.strPatientID, tblPatientData.strSurname, tblPatientData.strForename
FROM tblPatientData
ORDER BY tblPatientData.strPatientID;
ColumnCount --> 4
Columnwidths = 0;.75;1;1
ListWidth --> 2.75
still not working shall i select the row source from a query by clicking (...).
And shall i have patient surname and forename or just full name
ive got the names coming up but once i click a name it only shows the id number in the text box.
how can i hide the id number
and you knw when you click on a combo box and it drops down giving you all the data how can you put headings ontop of them
and i have seen in the database which u made when you click on a name in the combo box all the details are filled in next to it in the textboxes
dont worry about hiding the ID number i have worked it out.
and i now knw how to put in column heads in
i dont knw how to hide the patient ID i just clicked on the show in the sql builder.
On my appointments table i have done exactly as you have done for 'DrID' which i have done for 'PatientID' but when i put all tables in a query they are not linked.
Ocant do it..............
post a screen shot of your relationship diagram (JPG) laid out according to the instructions in the link on normalizing data, designing tables and forms
hey i will post the database tonight and you can have a quick look at it sorry for thhe delay ubti had to get working with my website.
When my website is finished i will email the database.
heres the database crystal.
no, I am not going to do this for you -- you are going to do it for yourself or I will not help you.
You haven't even laid out the relationships.
1. Read the notes on how a relationship diagram should be laid out
2. do it
3. press PrintScreen
4. paste to Paint
5. save as a JPG file
6. post back to this thread
ok i will do tommorow.
hey ive restarted now,
Am trying to put the patientID into Appointments table.
but i dont understand one of your instructions.
I have put it in caps and bold the bit which i dont understand:
Create an autonumber field in the Employee table -->
EmployeeID, autonumber
then, in the other tables...
EmployeeID, long, DefaultValue = Null
''''''''Then, when you want to put data in (which should be done from a form), you can set it up to pick names from a list but store the EmployeeID.''''''''
create a combobox control
Name --> EmployeeID
ControlSource --> EmployeeID
RowSource --> SELECT EmployeeID, LastName & " " & Firstname, HireDate AS EmpName FROM Employees ORDER BY LastName, Firstname
BoundColumn --> 1
ColumnCount --> 3
columnWidths --> 0;2;1 (etc for however many columns you have -- the ID column will be hidden)
ListWidth --> 3 (should add up to the sum of the column widths)
EmployeeID will be stored in the form RecordSource while showing you names from another table... a MUCH better and more reliable method.
If you want to show other information from your combobox in other controls, you can use calculated fields.
For instance
Name --> HireDate
ControlSource --> = EmployeeID.column(2)
The reason that column 2 is referenced instead of column 3 is that column indexes start with 0, not 1, in Access
i am sending a print screen of the design view table of appointments were i have put patient ID in.
hey errmm.
I was just mucking around in the queries and i sort of got patientID linked between the patient table and appointments table.
ive sent a print screen does it look right.
hey i am now sending another attachment linking all the tables in a queries.
Could you tell me if im on the right track.
You know the control source of the combo box d i get that from th tblPatient or the Appointments table.
for some odd reason the combo box is not working.
I am sending the database (NOT FOR YOU TO DO IT) but just look at it and see whats wrong and tell me ys.
thank you
for thiss combo box i have used the wizard.
i forgot to attach the file to that.
I'll attach it to this one.
i think there is a problem between th linking of patientID in Appointment table and Patients Table
hi you know the appointments sub-form there is only the appointments table in there. is there a way in which you can add another table into it.
hey i have done something to the sub-form.
I think i have messed it up. could you have a look at it.
I have got the combo box on it as well could you also check if that is rite.
yes crystal ive overcome those problems hteres no need to answer them
just downloaded your version of it and in the sub form you have got these boxes which take the value out of the combo box and hold it in that box.
recognise it..
errmm wel in my combo box i have the Patient ID, Fullname, Town, TelphoneNumber and PreviousAppointment.
could you tell me how to do this and my combo box width isvery small and i have to use the bottom scroll bar to see the other values. do u knw wots rong. do i have to set the 'LimittoList' to 'No'
dont worry about that one either. ive accomplished it.
rrmm il just send the database in this just so you can check it for me yes
the problem is that i have put the combo box in the sub form but in the main appointments ofrm it doesn't come up.
ill someone have a look at this attachment and tell me wot to do.
looks like crystals gone on holiday..
Hey Crystal.
need your help.
ive made the combo box and it all works now all i have to do is connect the next appointment slot together so when i make and appointment for a patient and then go into patient information the next appointment is there.
could you help me...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.