Help!! Entry Forms for Parent-Child Tables.

[Zorkmid]

Hi there,

I have a database with a parent-child relationship. Parent - Patient. Child - Individual visit. How can I create a form that will take user's input and put it into the correct table ?

-Z

Edit - For example, in the northwinds sample.....if there was a form for customers to use to make an order, how would it work?

 

[Zorkmid]

Here is where I am now. For Kryst51, and anyone else who can help.

 

[missinglinq]

This is the classic main form/subform scenario.

1. Create a form for entering Patient data, including a unique identifying field.
2. Create a form for entering Individual Visits, must include the same unique identifying field
3. Open the Patient form in Design View
4. Place a Subform control on it, selecting the Visits form when asked
5. If the identifying fields in both forms have the same names, Access will automatically connect the form/subform, and you're set.

 

[Zorkmid]

This is the classic main form/subform scenario.

1. Create a form for entering Patient data, including a unique identifying field.
2. Create a form for entering Individual Visits, must include the same unique identifying field
3. Open the Patient form in Design View
4. Place a Subform control on it, selecting the Visits form when asked
5. If the identifying fields in both forms have the same names, Access will automatically connect the form/subform, and you're set.

Thanks Missinglinq, sounds simple, but since I am simple......when you say place a subform control on it, does that mean dragging a "visits form" onto the "patient information" form?

Can the "same unique identifying fields" be something like a patient ID number? Or should it be an autonumber?

-Z

 

[Kryst51]

Hi Zorkmid,

The first thing I noticed on your entry form is that some of the controls are unbound. So that when you type info into them and click "Save changes" They don't save.

Specifically the start date and comments field.

Also your Case table should have it's own unique id, not the patient id, the patient id should be a foreign key in that table. All of you tables should have some unique identifier (Usually an auto number) that is specific to that table (Which at this time they don't). This number becomes the primary key. I suggest reading up some on normalization, Good database design starts from good forethought and then good tables structure. I know you are on a deadline, but these things need to be addressed. Databases are not things that can usually be constructed overnight, they involve time, willingness to learn, and patience as you will not always be able to accomplish what you want quickly. If you do not have time to devote to this, I suggest talking to your superiors about extending the time you have, or reassigning the project to someone who has the basic knowledge.

I'll continue to look at what you have and make suggestions here.

 

[Zorkmid]

I have read up on normalization, wouldnt adding autonumber fields actually hurt the database ? I thought that normalization was to reduce redundancy in the database.

-Z

 

[Kryst51]

I have read up on normalization, wouldnt adding autonumber fields actually hurt the database ? I thought that normalization was to reduce redundancy in the database.

-Z

Autonumbers create a unique ID for each record in your table. It isn't a redundant number however. For example:

Table: tblPatients
pkPatientsID - Autonumber, Primary Key
FirstName - Text
LastName - Text

Table: tblCases

pkCasesID - Autonumber, Primary Key
Infection - text
fkPatientsID - Number, long integer.

Then in the relationship table you will link the Primary key from the Patients table to the foreign key in the cases table. You can have patientID #5 linked with CaseID #7 but they would still relate because of the relationship that you have defined.

This will make building queries, forms and reports a lot easier.

 

[Kryst51]

Also, just noticed, don't use spaces or special symbols or numbers in your field or table names, these really don't work well later on when things get more complicated.

 

[Zorkmid]

Ok, let me work on this for a few minutes, and I'll post what I get.

-Z

 

[Zorkmid]

Ok, I think I've addressed all suggestions so far. What is next? I require form to enter cases for new patient, and cases for existing patients.

I also will need a way to look up (search by name, PatientID, location etc) and edit entries, mostly just to close a case (enter an end_date).

There are other issues as well, but I'm just concerned with the basics for my deadline.

Thanks so much posters, you're terriffic!

-Zork

 

[Kryst51]

OK, Here's mine, I took off your buttons. You might want to add something to you before update event for the case form that prompts for that, this site is sure to have some code that can achieve that. Or for a button too.

I didn't know if the reason table went with the patient or the case. I assumed case. so added that in to the form. Also take a look at the relationship screen. Don't use lookups at the table level. Always make the relationships on that screen, then build combo boxes for data entry on forms.

I suggest that you do yours first then compare with mine.

Edit: I posted this after yours. but didn't realize you had already posted.

 

[Kryst51]

Here it is with a lookup for you for the patient name. The combo box wizard can help you with those kinds of lookups, also you could create a separate search form which would open your main form to that specific record, but that would take more work, Unfortunately I don't have any more time to spend on it. But please continue to ask questions in a new thread if you have them. Good luck.

 

[Zorkmid]

Hi there,

I'm still struggling with this. I have uploaded where I am right now. What im working on is the code for the AddNew record button, as an unbound form. It throws me a runtime error when I try and use it, and Im not quite sure why. Any ideas anyone?

-Z

Ignore this atttachment, I just realized I didnt save ANY of my code updates......well actually. This is from another data entry form that does work. We can compare it against what I build and maybe we can figure out the error.... SOrry. Will post reply in a few minutes.

-Z

 

[Zorkmid]

Ok, here is the addnew code that doesnt seem to work. I've got all of the error messages to stop, but it doesn't seem to be adding anything new to the table. I really suck at this. Can anyone give me a hand?

I'm not opposed to using bound forms either. I just am having a hard time getting these basic input forms to work, and I can't find any resources online that adrress my problems.

-Z

 

[dcb]

      tblPatient.AddNew
           
           tblPatient!Patient_KNumber = txtK
           tblPatient!First_Name = txtFirstName
           tblPatient!Last_Name = txtLastName
           tblPatient!Comments = txtComments
            
     tblPatient.Update

txtK does not reference anything

It should be Me.txtK or Forms![frmName].[ctrlName]

Or for a more structured approach

Dim strK, strFN, strLN, strComments As String
 strK = Me.txtK 
 strFN = Me.txtFirstName

tblPatient!Comments = strComments

hope this helps

Out of interest does anyone know how AddNew handles "Davi't" or something like it?

 

[Zorkmid]

I have ran accross a post that says that unbound forms will not work for parent-child relationships....is this true?

http://www.access-programmers.co.uk/forums/showthread.php?t=59582

 

[Kryst51]

Why do you need it to be unbound? That seems a very difficult way to achieve what you want.

 

[Zorkmid]

I have decided just to use bound forms now. My Entry form works just fine for new patients. I now need another form to add a new case, or modify an exisitng case (for example to put in an End Date) to an existing patient, I'd also like to be able to cycle through all of the cases for a specific patient in this form.

So I guess I need a main form that will allow the user to select a patient (maybe narrowed down by criteria like unit, isolation type, date range). Any thoughts?

Sorry this is so slow, the site seems to be crawling for me

-Z

 

[Zorkmid]

Bump, Im in a slump

 

[Kryst51]

You can probably add a button to the subform to add new record. I removed all the navigation keys from mine, but your current setup should allow for a new case for the same customer. You need to think about the best way to get to the new record, either scroll, or on openevent. Or a button "add new".

You can use my customer selection example to add more criteria combo boxes. You should be able to apply that to a search form maybe. Do some research on cascading combo boxes as well.

This thread might give you some more clarification about how to do it. In my reply I attached some step by step jpgs on how to set them up.