fed up...need help

[lynsey2]

hey there guy's! I've been trying to do this for ages and got so far now i dont have a clue! I have posted about this before but its kinda different so ill start over.

THE STORY

I need to be able to get the history of prescriptions for each patient, to do this i have a button on the create script form, this button takes you to a pop up form with 2 list boxes on it (the 1st so you can select the script e.g. the last one and the 2nd so you can select the drugs from the selected script one by one or select them all) after you have selected the drugs in the second list box i need them to fill out the fields in the create script form.

THE PROBLEM

list box 1 doesn't bring up scripts ONLY for the selected patient!
list box 2 has started having problems looking up drugs for the selected script!
when i select 1 drug from list box 2 it fills out the fields in create script but if i select another drug (say to prescribe 2 drugs) it overwrites the first one.

THE SET-UP

i have 3 tbls!

ARTScriptMaster

Ref:
Patient Number
Start Date
Doctor
Consultant
Stop Date
Weeks Surname Forename Address1 PostCode Dob drug1 form1 Frequency1 Strength1 drug2 form2 Frequency2 Strength2 drug3 form3 Frequency3 Strength3 drug4 form4 Frequency4 Strength4 drug5 form5 Frequency5 Strength5 drug6 form6 Frequency6 Strength6 drug7 form7 Frequency7 Strength7 otherdrug otherform otherfreq otherstrength otherdrug2 otherform2 otherfreq2 otherstrength2 otherdrug3 otherform3 otherfreq3 otherstrength3 Comments AttendGPsurgery FollowUp Been provided Are Recommended UsualARTchangedYES UsualARTchangedNO otherday1 otherday2 otherday3

his1

Ref Start Date Stop Date Doctor Consultant Comments


his2

Ref PatientNumber Drug Form Frequency Strength


list box 1 looks up his1 list box 2 looks up his2 depending on what is chosen in list box 1.


HERE IS THE CODE

Sub Reset_Text()
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
ctrl.Value = Null
End If
Next ctrl
End Sub
Private Sub close_hist_Click()
On Error GoTo Err_close_hist_Click


DoCmd.Close

Exit_close_hist_Click:
Exit Sub

Err_close_hist_Click:
msgbox Err.Description
Resume Exit_close_hist_Click

End Sub

Private Sub Form_Open(Cancel As Integer)
SelectDrugs.Enabled = False
SelectDrugs.RowSource = ""
SelectScript.DefaultValue = ""
End Sub


Private Sub SelectDrugs_Click()
Reset_Text
Dim Ref As Integer
Dim MyRS As DAO.Recordset
Dim MySearch As String
Set MyRS = CurrentDb.OpenRecordset("his2", dbOpenDynaset)
Ref = Me.SelectDrugs.Column(0)
MySearch = "[Ref]=" & Ref
MyRS.FindFirst MySearch
Forms!ARTScript!pickDrug1 = MyRS("Drug")
Forms!ARTScript!pickForm1 = MyRS("Form")
Forms!ARTScript!pickFrequency1 = MyRS("Frequency")
Forms!ARTScript!pickStrength1 = MyRS("Strength")
MyRS.Close
DoCmd.Close
End Sub

Private Sub SelectScript_Click()
Reset_Text

Me.SelectDrugs.RowSource = "SELECT DISTINCTROW his2.Ref, his2.PatientNumber, his2.Drug, his2.Form, his2.Frequency,his2.Strength FROM his2 WHERE (((his2.Ref)=" & SelectScript.Value & ")) ORDER BY his2.Ref DESC;"

Me.SelectDrugs.Requery

SelectDrugs.Enabled = True
End Sub

ANY HELP WILL BE MORE THAN APRECIATED REALLY REALLY APRECIATED THANKS IN ADVANCE!

 

[Hayley Baxter]

So you will only ever select one script from the first list box at any given time and you may select one or multiple drugs from the second list box. If you select one or more you need all of them to fill out on the next form, Did I get that right?

Can you maybe post up a dummy copy for us to look at Lyns?

 

[Rich]

I'm confused by your table structure, why so many fields, frequency1, frequency2 etc?

 

[lynsey2]

I'll try the dummy thing.

Table structure...in ARTScriptmaster I store the scripts so in the ARTScript form each combo box has a field in the table, there can be more than 1 drug with corresponding form, frequency and strength, hence drug1 form1 ect and drug2 form2 etc.

I now know this was a bad idea but I don’t know how to fix it so I am just going to leave it. I just want to get the database doing what they want and if I have time I will fix things.

Please note I didn’t know any better at the time! And didn't have anyone to ask advice, I am panicking about this now but don’t have time to fix it .

So what happens is everything whether it be 1 drug or 7 drugs goes into ARTScriptmaster as 1 record if its only 1 drug the rest of the fields are left empty.

I am also going to run into problems with his2 because of this as if the patient has 2 drugs I need to be able to pick them both up and put them in his2 but the table structure is different so I can see that coming but im driving myself crazy.

ill send the dummy copy!

 

[lynsey2]

file is too big even when ziped!

 

[Rich]

My advice to you is to stop before you go any further and sort out your table structure, your just digging yourself a deeper hole at the minute.
In very simple terms the patient details belong in a separate table to the drugs issued. It's a one to many relationship between Patient and Drugs.
I assume the drugs available are selected from a combo on the data entry form which looks up the drug from a lookup table, in which case you would only store the ID number associated with the drug in say the prescriptions table, the same principal applies to the strength and frequency fields.
You only need one field for each. You may find it easier to draw out the structure on a piece of paper first.
If you get that correct then working with the data becomes much easier

 

[lynsey2]

Im with you! This is a nightmare, I don’t even know if the rest of my tables are correct. I have 4 separate tables for my drug form frequency and strength.
Use cascading combos based on a qry that shows all the different combinations of the drugs.

Is that wrong too??

Know what I don’t even think my relationships are correct im somehow just plodding along

i cant start over as i finish this placement in june (THANK GOD) but what can i do to fix it, if i try and change one thing something somewhere else stops working and it seems to get me in an even bigger mess.

 

[lynsey2]

 

[mission2java_78]

What exactly are you having problems with? It seems to me I'd follow rich's suggestion..you need to sit down and speak your design out..before creating tables on the fly. You sound like me 3 years ago . Dont stress it out...tell us what is complicating the issue and we can help resolve.

Just say things like one customer can have many orders...so you create two tables a customer table and an orders table and you join them on a customerid. Your customerid becomes the foreign key in your orders table...because several different orders can have the same customer. And so on and so forth..

Give it time it will click ... if it clicked for me ... it clicks for anyone.

Jon