Displaying subform information in datasheet AND normal view

[pmp1]

Hi all,
I am using Access 2003 and remain a novice with Access, but improving.
I have a main form (customer details) with most of the customer's details displayed on Tab Pages. I also have a subform on the first Page of the Tab displaying visit details. The subform is displayed as a Datasheet and includes all fields in the Visits table.

The main form and subform all work fine, however, I wish to change the layout of the Visits information as follows:
I only want the Subform Datasheet to display the visit date and visit time, to which I know I can do this.

My problem however, is that I wish the other fields associated with the Visits to be displayed on the same Page of the Tab as normal bounded text boxes displaying the visit information (e.g. service type, cost of visit and amount paid) associated with the "selected" visit date/time.

I would appreciate knowing how (or if) I can do this.

Thanking you

Peter

 

[Remou]

Have a look at the Customer Orders form of the Northwind sample database that ships with all versions of Access (or download

http://www.microsoft.com/downloadS/...-8dbe-422b-8676-c632d66c529c&displaylang=en).

The form shows how to synchronise two subforms.

 

[pmp1]

Remou,

Thanks for replying. I haven't made myself clear enough, as I don't need to syncronise two subforms.

I only have one subform which currnelty lists all the visits for each customer. The subform (created using the form wizard) works perfectly well.

However, for asthetic reasons I wish to display a list of visit dates and times only in a list (i.e. the subform) and to the side of this list I wish to display the patient's visit details (i.e. service provided, cost of visit, amount paid etc) depending upon the visit record selected from the subform. That is to say only one visit can be viewed at once. I want the visit information to displayed using bound text boxes etc beside the list of visit dates/times.

I have a jpg (60kb) of the form I am working on which may make it more clear, but I do not know how to attatch it to this thread.

Whilst my subform has all the relevant fields in it, I only wish to see the first two columns (i.e. visit date and time) which the user can select.

The remaining fields I wish to have on the form as normal bound text boxes (e.g. cost of visit) which shows the relevant visit information depending upon which record (i.e. date /time) has been selected.

I hope I have made myself more clear?

Regards

Peter

 

[Remou]

I fear I did not make myself clear. The form I suggested shows additional details in subform two when a row is selected in subform one. According to your statement "I wish to display the patient's visit details (i.e. service provided, cost of visit, amount paid etc) depending upon the visit record selected from the subform", that is what you wish to do.

 

[MajP]

Remou gave you the correct answer but maybe you are not seeing it.

Lets say you have tblPatients and tblVisitDetails
1)Now build a subform in continous form view with the fields visitDate and visitTime on the form from tblVisitDetails. Now put this subform on the mainform and I assume link them by "patientID"

2)Now build another subform from the same tblVisitDetails, but put this one in single form view. Get rid of navigation buttons, record selectors, and dividing lines. Format it to look like it is part of the main form. Put the other fields (cost, service provided, amount paid) on this form

3)now set the child master relationship. The continous subform and this subform share the same table so they are probably related by [visitID] or something.
Now if you look at Remou's cited NW example you will see how they link the two subforms
something like
child:[visitID]
master:[subformcontrolName].form![visitID]

on the on current event of the continous form requery the other subform as shown in the NW example.

Now as you cycle the main form you change the Patient. The first subform shows time and dates for that patient. The other subform shows the details for the visit (but looks like it is on the main form if you format correctly)

 

[pmp1]

MajP and Remou,

Firstly sorry to Remou, it did not occur to me to have two subforms linked to the same table.

Thankyou to MajP for clearing up my missunderstanding.

I shall give this a go today and will advise of my success (or otherwise) tomorrow.

Thanking you both for your advice & patience.

Regards

Peter

 

[MajP]

You could also do this with a listbox and a subform.

1)Like the previous example bind the mainform to the patients table
2)Add a listbox (lstBxVisit) with a rowsource something like

select visitID, visitDate, visitTime, patientID from tblVisitsDetails where patientID = forms!mainForm!patientID

3)link the subform
child: visitID
parent: [listBxVisit)

4) on the mainforms oncurrent
lstBxVisit.requery

 

[pmp1]

Well I haven't quite master this yet but have progressed and have learnt a couple of new things on the way. I will continue to try and resolve this myself at this stage switching between Northwind and my application.

I will call for help in a couple of days if I still can't resolve it.

Thanks Peter

 

[pmp1]

MajP and Remou,

Thankyou for your help - I have resolved my problem. It took longer than expected due to my poor nameing conventions (using spaces inconsistently).

Can someone please advise how I can close this thread.

Thanking you again

Peter

 

[Remou]

That's it, just say so, and it is closed

Welcome to Tek-Tips, have fun, and please read the FAQs from time to time.

Here are some tips:
Naming convention:

http://www.xoc.net/standards/rvbanc.asp

Jet SQL:

http://msdn.microsoft.com/en-us/library/aa140015.aspx