Auto fill fields from table

[lynsey2]

I have got a button on one of my forms!

the button runs a qry!and displays the result in a table like format

i need to be able to select one of the records from this table.

i need the selected record to fill out fields in the form.

e.g. a patient has a history of prescriptions, click on the button and it matches the patient number and displays all the scripts they have had, the Doc wants to give the patient a script thats exactly the same as the last time obviously changing the start and stop date!so he/she clicks on the button sees last times script clicks on it and the form fields are filled out with all the details except the start and stop dates.

i have looked everywhere to find this with no luck!

 

[simongallop]

As an idea, how about getting the results of the query to populate a list box (or set the value of the listbox to display the query) and then set the on double click event of the list box to display the selected record.

HTH

 

[lynsey2]

Thanks for that! not really kind what i need! i really really need it to be as simple as anything so clicking on a button then selecting the record they want and the fields being filled out kinda has to be the way it's done. else it's to complicated for them and they wont bother with it, they will start just writing the scrips again...and we all know what Doctors writing is like!
Thanks though i really appreciate the reply.

i have it so that when the button is pushed the qry is run and the result is shown on a seperate table like format.
All i need now is for when the doc chooses from this table (say dbl clicks a record) the table thing closes and the prescription form is displayed with the fields filled out!

Any other clues??please!
Luv Lyns x

 

[simongallop]

Lynsey,

What I suggested seems to be what you want! When you press the button, instead of the query showing in a seperate table, get the table to show in a list box ( it will be viewed as the same). Then the doctor double clicks on the record that he / she wants. This can be done in a listbox but not in a table. Then code on the Double Click event will take the details from that record and fill out a form.

If you need help then reply and I will see if I can send you a sample

Harry

PS What version of Access are you using?

 

[Hayley Baxter]

Hello Lyns

Hope you're alright. I haven't used this before but thought this link may be of interest to you. It appears that this coding will give you what you want ie it would match your chosen ID number on the form and fill in the fields accordingly based on that value. It does say a text box on this example but I'm sure you can use Harry's suggestion and use a listbox instead.

http://www.mvps.org/access/forms/frm0009.htm

Harry I wouldn't mind your example I would be interested to see this if you have one to hand. I am using access 2000.

HaylBaxter@aol.com

Thanks
Hayley

 

[The_Doc_Man]

The approach I might use would be to build a parent/child form situation where the parent is the form to select the person and the child is the list of prescriptions for that person. The list of scrips could be linked to the person through the patient ID field. The subform wizard will ask the right questions to make this work for you, or it is the .LinkChildField & .LinkParentField (or something like that) on the subform control's property sheet.

Don't forget that a sub-form is a form, too. So when you select any row, set up perhaps a dblclick routine. Inside that routine, you can check for me.Parent to see if you are observing that child form in a parent/child context. If so, you can find your way back to the list of controls on the parent to populate the appropriate ones with what you want. Or, in this context, you could just write some code to take the selected record and create a new record that looks just like it.

 

[lynsey2]

A sample would be great! I get you now Harry im sorry im away with the fairy’s right now not paying enough attention! My email is
00067458@napier.ac.uk a sample would help a lot! thanks much,
Lyns.

Hay! How’s tricks thanks for replying! Hope ur ok! Ill call soon!

 

[lynsey2]

Got the sample ok thanks!
I have a hitch...when trying to populate listbox1 with the result of my qry it keep's telling me I cannot have more than 20 fields yet wont let me select any less than what the result is!

E.g. my qry result has approx 33 fields that I need as a patient can be on up to 7 drugs (with corresponding FREQ STRENGTH and FORM) then there are the patient name number and dob etc.

I got the error msg saying cannot have more than 20 fields so I thought "well I want to try it to see if this will work for what I need" so I tried just using patient number name start and stop dates and I got an error msg saying I don’t have all the required fields.

Im taking it that as I need the qry to produce all 33 or so fields yet im not allowed to have more than 20 fields in the listbox1 that I cant do this!

Any ideas?? I’m sorry im still not very good...ill do networking when I return to uni don’t panic ill not even try and get a job as a Database designer!

 

[simongallop]

You don't need to have all 33 fields in the listbox as it would be very hard to see it all and cumbersome to use. Suggest that you change the criteria to show only the important fields ie ID, name, drug, date and then when you double click on the record to fill the textboxes, you set the code to get the remaining data. If you need help you have my email.

HTH

 

[lynsey2]

I still cant fix this as I need at least 7 drugs each having a form, freq, strength
Hence ill have 7-drug names 7 forms 7 freq and 7 strength in some cases! It doesn’t wanna play and won’t let me use this amount of fields! Surely this is not a huge amount of fields compared with big companies is it????? There must be a way!!!

 

[lynsey2]

 

[simongallop]

Am getting a bit confused. If you want, send me the db and will have a look

 

[lynsey2]

whats your email? sorry im confused too thanx for taking the time your a star.

here is the story so far

basically the doc opens the db
goes into a form and selects a patient (on this form there are
buttons to take the doc to other forms-prescribing forms.)
say the doc goes into ART script and the patient is going on about feeling better on the new range of tablets the doc prescribed, instead of the doc filling out the drop down boxes on the form again i want to have a button that brings up a list of ALL of the patients ART scripts, if the doc chooses to re prescribe one he/she can double click or just click on it and the drop down boxes etc on the form will AUTOmaticaly show the drugs which have to be prescribed.
A patient can be on up to 7 different drugs these 7 drugs can have many different forms, frequencey's and strengths. The doc will have to see on the list from the history the drug with corrasponding form freq and strength as if he/she could only see the drugname then whats the point as the patient could have had the same drugs but with different stengths etc and all the doc would see is a list of scripts that look the same.

if that makes it any better!
Lyns.

 

[lynsey2]

know what im still stuck!

 

[simongallop]

Have you changed the structure?

 

[ColinEssex]

Lyns

Empty your mailbox !!!!

I can't send you a PM - its full up....... what are you like

Col

 

[lynsey2]

I have tried doing a subform and that doesnt work! I have tried getting the qry to only show the patient name start and stop date and that doesnt work i have tried code that i found on the internet and that really didnt work! i tried creating another form based on the qry and using a button from the main form linking it by patient number and that didnt work! I even tried linking that extra form to the ART script form in the relationships window manualy and that made no difference.

Deleted all my pms col!

Lyns

 

[lynsey2]

 

[lynsey2]

.

 

[simongallop]

Did you receive my email about suggested restructuring? Would seriously recommend carrying it out and then work on the forms.

It will work out a lot easier in the future and you won't be limited to 7 drugs