Help!!!! Problem with form in Access.

[Elhan]

Hey guys. I'm an A level ICT student and I am currently working on a patient management database in Access as a project for the A2. So far all has been going well. The problem lies within one of my forms. The form (called frmTreatment) is a form with a subform. The main form contains patient and treatment data, whilst the subform allows for costs to be added to the system. The problem is that when making a combo box to select a patient by the option "Find a record on...combo box", the combo box does absolutely nothing. It brings up an empty list, whereas it should bring up Patient ID, Patient Name and Patient Surname. I suspect its a problem with the relationships. What the combo box should do is bring up patients from tblPatients and subsequently present data on them in the rest of the form's fields.

The second problem is with the subform. I want to add a combo box that allows the user to select the cost from the tblCosts, much in the same way with the afforementioned Patients combo box. Yet subforms won't allow new objects to be added and I can't see any way of making Cost ID perform such a function.

Can someone please help me by having a look at my project, and in particular the frmTreatment? I can't seem to pinpoint the error out and everything seems fine. I've done normalisation and everything already. The project is due after the Xmas holidays, plus its difficult to get in touch with my ICT instructor now. So any help will be appreciated.

Elh

PS: If you're willing to help give me your e-mail so I can send a zipped version of the project to you in order for you to help.

PPS: Cut down my requests

 

[Ukraine82]

I can't really answer all your questions but I did manage to get you a sample database for cascading combo box.

hth,
Michael

 

[Elhan]

Thanks, but it still doesn't help. I need someone to have a look at the program itself.

 

[Ukraine82]

From reading your first post it seems to me that you're having trouble with cascading combo box.

Are you trying to select a record from a combo box and have the form show that record?

Michael

 

[Elhan]

Yes. Its exactly that. If I can solve that half the problem is solved. The matter of the subform remains though.

BTW I tried the attachment you sent and it doesn't seem to work.

 

[Ukraine82]

If you want try making a wizard combo box...select the third option which is Find a record on my form based....select the field that you wish to input in a drop down box...keep going till you get to finish.

hth,

Michael

 

[Elhan]

That is what I have been doing all this time. It just doesn't work. Which is why I am trying to find someone to have a look at it first.

 

[Ukraine82]

Post your database. Besure to compact & repair and winzip the file.

Michael

 

[Elhan]

Do you have an e-mail I could send it to? Its too big to fit in an attachment here.

 

[Elhan]

lol can someone please help? I really need help with this and fast.

 

[IfButOnly]

What is in the Row Source, Column Count and Column Widths Properties of your combo box?

 

[Elhan]

Row Source: SELECT qryPatient_Treatment.PatientID, qryPatient_Treatment.[Patient Name], qryPatient_Treatment.[Patient Surname] FROM qryPatient_Treatment;

(I tried changing patient_treatment.patientID to patient_treatment.[patientID] but this makes no change)

Column Count: 3

Column Width: 2.54cm;2.54cm;2.54cm

I don't see how this will help you in understanding what the problem is but whatever. If someone could have a look at the project itself perhaps they could spot the problem. I believe its in the relationships but I like the expertise to know what.

PS : Shortened my original post.

 

[vengsiva]

Data source of form and subform can be set to table rather than query.Then set the linkfields,childfields properties in the subform and ensure that the wizard generated find combo is in the header.I can send you an extract by email if you want(It is more than 100 kb
Vengsiva

 

[IfButOnly]

Elhan, you're welcome to send it to me at peter_rallings@dodo.com.au and I will see if I can help.

 

[Elhan]

Thanks!!! Sent it

Ok Vengsiva I tried what you told me and managed to get the combo box to allow you to select patients. Yet it still has no effect on the form. I trust the subform's combo box would work in a similar manner. Problem is I don't know how to set the childsfield and linksfield properties :S Hopefully you could help me there Peter?

 

[vengsiva]

Go to subform properties.Click tab data.Choose "linkchildfields".From the pulldown selct the relevant field.Please see attached db which I did for a similar project.This is only an extract of that project

 

[IfButOnly]

Couldn't unzip your project - can you make a copy with less data and remove unnecessary forms etc, rezip and send. By the way I am using A2k.

It is bedtime for me here so I will not be able to look at it till morning.

Some things for you to try.

I assume the RecordSource for your main form is tblPatient_Treatment (or whatever) and is keyed by PatientID.

In the forms event of your form you will enter PatientID in the 'Link Master Field'. If your subform is keyed on PatientID as well then you will enter that in the 'Link Child Field'. hence you build the relationship - if the forms PatientID changes then the subform will reflect that data. Check the help file if this is not clear.

In the After_Update event of your combo box, you will need something like_

Private Sub cboPatients_AfterUpdate()
me.PatientID.setfocus
DoCmd.FindRecord cboPatients
End Sub

This will move you to the selected record and affect the subform as well.

-----

I am not sure what you are wanting to achieve with the cost combo box in the subform. If the subform is a datasheet showing items and costs for the treatment then, in the subform, select the cost field, then FORMAT, then CHANGE TO and select Combo Box. Then do as you have done before to build the rowsourse with appropriate costs for the Patient/Treatment mix.

Regards... Peter

 

[Elhan]

Ok I will send you the file in two versions...one not compressed and one slightly compressed. I will send them via my email elh4n2005@yahoo.com Hopefully you will be able to see what is the error. I removed any unnecessary data. Its still a large file :S With the cost combo box I want the user to be able to select costs and add them to the cost subform, which will in turn make calculations to calculate total cost. Sort of like an invoice when finished.

PS: I looked but I cant find the Link Master or Link Chield fields

 

[vengsiva]

Elhan.View the form with subform in design view.click the subform and go to properties.Please see attachment
vengsiva

 

[Elhan]

Okay Peter sent you two files. What is your email account's max storage capacity btw? The one is about 1000 kb, the other a little more.

Vengsiva I checked my subform for those properties and it appears they aren't there. Keep in mind I generated the form using a form wizard. Its based on two queries, one for the main form and the other for the costs. You see there are too many tables so I had to use queries. Could this make a difference?

Just for clarification purposes, the Cost ID field on the Subform has to be replaced by a combo box allowing you to select a cost. This way you can add multiple costs to the subform. Problem is I don't know how to transform a text box into a combo box, and even less how to add a combo box to a subform which would perform such a task.