Solved: Complete beginner with the use of VBA needs help.

[Christofu]

Sorry if I'm asking too much. But I'm terribly in need of some VBA help. You see, i'm doing a database for my Computer Studies Project. Its basically a database for a clinic. Has 3 tables, "In Patient"(Patients admitted to the ward), "Out Patients"(Patients seen in the day clinic) and an "Appointments" table.

There are two parts i need help with but aid with just either one is fine.:bow:

1st Part
Note that the In Patient and Out Patient table have 4 identical fields (Registered Number, Patient's Name, and Sex) and the other fields are not indentical. A procedure that often happens is that Out patients become In Patients(Getting admitted to the ward after a clinic check). So i need to have a button on the Out Patient form that immediately creates a new record in the In Patient form and copies the four items of data over from the Out Patient table to in the In Patient table. The user then fills in the other fields which are not indentical of the In and Out Patient tables. How do i produce this button?

2nd part
For the Appointments form, i need a button which immediately displays the current appointment. Appointments are in half an hour sessions. So let's say the time now is 4:30pm. An appointment was made for 4:00pm under person A's name and the next recorded appointment would be at 5:00pm under person B's name. With a click of a button on the form, how do i make Person A's name come up on the form?

Thanks so much for you help!!!
Chris.

[OBP]

Chris, did your tutor tell you to do it this way?
As Copying data from one table to another table is a Relational Database Taboo.
On the second question, why do you need to display appointments One at a Time?
If they have to be one at a time have you considered using a Combo box to select the Appointment that you want to view.

[Christofu]

Ah, no my tutor didnt tell me to do it that way but she told me to go ahead with it when i suggested it. Urmm no mention of "relational database taboo" whatsoever. But it would be great if it can be done. Can it? like a macro or something? I don't know. My knowledge on this is very limited i'm afraid. So i'm seeking help. :)
Ahh and appointments, is it possible to display the whole lot at once in a form rather than record after record? How does a combo box work, a pull down menu? Whatever is most viable i'll take. Teach me how!


> Could i use an update query of some sort or something? I'm really desperate, if you or anyone could give me a link which roughly tells me how to do part 1 or 2 that would make my day.

[CreganTur]

Welcome to the forum- it's always good to have new members. Also, thank you for letting us know this is homework.

1) One suggestion- instead of duplicating the first four fields in both tables, you could have a Patient table that contains just those four fields- add in a primary key field if needed. Then you can use the PK as a foreign key in your In-Patient and Out-Patient tables. this means you can easily add a new record to either the In or Out Patient table by adding in the primary key (see Append Query for how to add a new record to an existing table).


relational database taboo
What Tony means is that the foundation of a relational database is that you should not be repeating data within your tables- your tables should be related in such a way that repeated data is very rare, or eliminated all together. As an example: Say you have a Customers Order table. Instead of writing in the customer's Name, Address, and Phone Number for every single order you would create a Customer's table in which you create this data once. Then you replace your Customers Order table with an Orders table. Then you just need to reference the PK from the Customer's table for each order the customer makes to have everything related. This greatly reduces the data you would have repeated.

----------
2) One idea is that you could have two comboboxes- one that is a list of all the available appointment times. The second would have all of the dates. You could use the selected values from these two comboboxes as parameters as a part of a DoCmd.OpenForm method to open another form that would open with the selected appoitnment information.

This is one idea out of many. I think it would be the simplest for you, since you are new to VBA and Access.

A few housekeeping/best practice tips:
DO NOT use spaces in your table names or field names. InPatient and In_Patient are okay, but In Patient is not. The reason for this is that it adds unneccessary complexity issues. If you create SQL statements by hand you'll have to remember to wrap object names with spaces in brackets []. Also it's a very bad habit if you plan to ever work in another database program... like MySQL.

REMEMBER to create relationships and referential integrity where needed- this will make things easier for sustainability of your application.

Let us know if you need any more help in the future!

HTH:thumb

[Christofu]

Cool! You guys are awesome! I'm just trying to swallow what you've just said.

1) About the Patient records. The thing is, the Out Patient table has a field on it-Consultation Fee-which is also in In Patient table which will have a different value.
i.e. Pay for the day consult and pay for the in-ward consult. One of the purposes of these tables are to verify a patient has paid.
I have made a checkbox for the receptionist to tick off manually once the reciept is seen. (My mom actually does this but on a much simpler scale in Excel)
So, will it show a whole seperate record in In Patient and then a whole seperate record in Out Patient although the key field, sex, age and name has the same values?

Also, not all patients who are in patients were out patients before.
i.e. some have been referred or admitted to the ward immediately after an emergency.
Can a new record be made in In Patient Table and NOT in Out Patient table with RN Number, Name, Sex and Age present while following your idea of omitting those fields and then relating it? Please let me know.

2)So combo box the thing. How will it handle the many many dates though? But Ah kay i will do that! Is it possible to have the current appointment displayed in a click? And oh, do i like seperate the time from the date field and have a validation rule like:
#10:00:00AM# OR #10：30：00AM# OR #11:00... etc. (all the half an hour time slots in a workday)
Waa confused. Really sorry to be asking so much！ Sigh i should get a course or something!:confused2

You made my day man!

And thanks for your great help and advice, man. I'll make my tables single-word-named.

PS:Oh no! Does homework mean its a bad thing?! :doh: I don't mean to waste your time but its more of like exam work! :blush

[CreganTur]

PS:Oh no! Does homework mean its a bad thing?! :doh: I don't mean to waste your time but its more of like exam work! :blush

No, no- don't worry. We just have specific rules regarding homework- basically stating that we will provide guidance and code snippets to help specific homework questions, but we won't do the homework for anyone (not saying that you're asking for that:thumb)

Honestly, I would highly reccomend picking up a good Access book- it will answer a lot of the questions you have about the way Access itself works (the front-end). I would suggest: Access 2003 : Your visual blueprint for creating and maintaining real-world databases (http://www.amazon.com/Access-2003-blueprint-maintaining-real-world/dp/0764540815/ref=sr_1_1?ie=UTF8&s=books&qid=1229438573&sr=8-1), which is pretty good, or Microsoft? Office Access 2003 Step by Step (http://www.amazon.com/Microsoft%C2%AE-Office-Access-2003-Microsoft/dp/0735615179/ref=sr_1_1?ie=UTF8&s=books&qid=1229438625&sr=1-1). Step By Step is a guide for the Microsoft Office Specialist Access 2003 exam. There are a lot of other good books out there too.

----------

1) My sugggestions is that your Sex, Name, and Age fields should all go into a Patients table, and add a Patient_ID field. Patient_ID will be your primary key- it will be a unique identifier for that specific patient. Then in your In_Patient and Out_Patient tables you would have all of the other fields that you need, along with a Patient_ID field- this is the table's foreign key. It would have a many to one relationship with your Patients table. This means that one patient can have many in or out patient records.

2) you can use a SELECT DISTINCT query on your table(s) to get a list of all of the dates that exist in your table(s). This would be your combobox's row source.

In your Appointments table I would suggest having 2 different fields: an ApptDate field and ApptTime field. ApptDate is the date of the appointment, and ApptTime is the specified half-hour time when the appointment starts. Then you would search for the ApptDate AND the ApptTime.

[Christofu]

Cool you rock man you mysterious programmer IT guy! Indeed i have a good front end book for Access specifically for ICT Projects. And i have to hand it to you for solving another problem in my head which i didnt mention. A patient may visit twice, be admitted twice and thus need more that one record! Thas great man. I shall go ahead with your idea.

Could i ask anymore? Well do i make a form for patient, in-patient and out-patient tables? Or i make a form displaying the query for in patient and the query for out patient? And could you please please elaborate on the SELECT DISTINCT query? Or at least link me? I'm totally blank on the codes. Thank you!

PS: Name is reserved word. Can i still use it?

[CreganTur]

And i have to hand it to you for solving another problem in my head which i didnt mention.
I highly reccomend writing out on a white-board or piece of paper all of the tables and fields for each table that you are going to use, and also draw in your relationships. This will help you solve a lot of other possible problems before you start building.


Well do i make a form for patient, in-patient and out-patient tables?
That really depends on you, but here is a suggestion for you to consider:
I would create 2 queries: qryInPatient and qryOutPatient. The qryInPatient should use both the Patient and In_Patient tables and it should pull in the fields you want. Do the same for qryOutPatient.

I would build a form for each of these queries: you'll have an In Patient Admission form, and an Out Patient Admission form. See if that does what you want it to.


And could you please please elaborate on the SELECT DISTINCT query?
DISTINCT is a SQL keyword that tells your SQL SELECT statement to only show original values- basically no duplicates will show.

Go to the Row Source for your combobox and click on the build button- this will bring up a query design view that you should be used to. Build a query to pull your dates, and then open the query in SQL view. Add the DISTINCT keyword directly after SELECT. Save and exit the query- this should setup your combobox to show unique values for all of the dates in your table(s).

And yes, I am being vague in some areas because it's an exam project, but I think you'll be able to fill in the gaps :thumb


PS: Name is reserved word. Can i still use it?
That was bad on my part. Use something like FName or FullName or Patient_Name... whatever you want.

[Christofu]

awesome! And I did it successfully! Alright, tho i still have some doubts I think i've gotten the best out of this thread already. Thanks CreganTur, problem solved! :thumb