Report source

[sync123]

Hi,

I have an unbound form that I use for data entry into a table called tblPatient. The table has fields like last name, first name,dob,phone ..

After the user fills the form they click the "save+print" button. When the "save+print" button is clicked ,I execute an sql insert statement which adds the new record into my table. After the record is inserted I generate a report which is based on that record that I just inserted.

The report gets its values from the form .


-Users have just recently requested a feature wherby they can view all records in my table and basically chose one record and generate the report based on that record. So in short user want the capability of viewing all records in table tblPatient and then chosing one on which the report will be based.

-I am a little confused on how to go about this. If I were to use a combo I believe I can bound it to my tblPatients .
I would like to show at least 3 fields from my table first name,last name,problem,time.So the combo box would have an items like

paul,white headache 12:00PM
sam,david heat exhaustion 1:00PM

After the user selects an item I want to generate the report based on that item .How can I set the report to get its values from a record?


Can someone help me out?Short on time.
Thanks
sync123

 

[scriverb]

Your ComboBox should not be Bound to the table. Rather, create a query using your tblPatients as the source and display your records in the field order that you would like and also the sort order. Now the only problem I see is being able to identify a particular record based upon these four fields. You probably should add an AutoNumber field with a name like RedCounter. This field would just be used to identify the record.

Select A.[RecCounter], A.[Last Name] & ", " A[First Name] as Name, A.[Problem], A.[Time]
FROM tblPatient as A
ORDER BY A.[Last Name], A.[First Name];

Now with this SQL as the RowSource set your Bound column to 1, Column Widths to 0;2;2;.5, Column Count to 4.

Now with the ComboBox being bound to the RecCounter value of the record selected you can now create a query for your report that references back to the value of the ComboBox.

Select A.*
FROM tblPatient as A
WHERE A.[RecCounter] = [FORMS]![frmYourFormName]![ComboBoxName];

If you have any further questions feel free to post back.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sync123]

Thanks for your help! I will try it and let you know how it goes
sync123