Subforms, relationships, and calculations

[hjg]

Hi,

I'm a new user here and new Access user, but have used this wonderful site as a resource.

My question concerns the structure of my database. I'm doing a simple db which collects data on clients, specifically visit and consult times. I have a main form with basic client info. I then have two sub-forms, linked to the master form by client number, with specific information on visits and consults. (a) The first subform concerns consult times (b) The second one contains a second set of times.

Before I go forward, I'm wondering if structurally, there is any reason to have two sub-forms rather than one.

I am also performing calculations in the sub-forms. In subform (b), they work fine (I used a string).

In subform (a), however, I need a piece of information from subform (b) to do the calculation. I don't know how to get that piece of information (a general date and time) over to subform (a). If I try to put in the control source equation =ElapsedTimeString([ConsultRequested],[OrdersWritten]) I get an error. The reason is because [ConsultRequested] is from subform (b).

If I try to put in instead something like [Conslt_Tblsubform]![ConsultRequested] it also doesn't work. (I'm sure that last bit is totally wrong.)

So, should I just have one sub-form, which would probably solve the problem?

Also, this is an unbound calculation. I did also try to write a SetValue macro which would save this to a table. However, if I need to run reports or make charts with averages, isn't it better to just do the calculations then and there, rather than save them to the table?


I realize that my questions may be a bit unclear and I apologize. This is a first for me.

Thanks in advance and please let me know if I need to clarify.

 

[pbaldy]

Welcome to the site. I'm not really clear on what you're doing, but here's a good reference on how to refer to forms and subforms, as your syntax is incorrect:

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

 

[hjg]

thank you

Hmm. I couldn't get that to work, either. I'll keep plugging at it. Thank you.

Let me briefly recapitulate, too. I'm looking at ER visits at the hospital. I want to look at medical record numbers, some other basic information, and then the wait times patients have in the ER. This database will then store info. on assorted wait times and be used to track these wait times by quarter, by hospital unit, etc. The assorted wait time parameters are things such as "time orders written," "time orders given," "time admitted," as well as "time consult requested" and "time consult performed."

I am calculating time differences between the parameters above, i.e. time between "time orders given" and "time admitted" or time between "consult requested" and "orders written." I have researched and successfully found how to calculate the time difference, which was no small feat for time periods greater than 24 hours.

In setting up the form, I originally had all the fields on the same page. It was suggested to me, however, that I use some sub-forms and link them to the master form by medical record number. I did this. I actually made two sub-forms (and I don't really have a logical explanation for why - this is sort of trial and error). The first one has "consult" parameters and the second sub-form has "orders" parameters. I suppose it was silly to make 2 sub-forms.

Anyway, I will keep at it and try out only 1 subform and I will experiment with the formulas on the reference page you linked to.

Thanks so much!

Geoff

 

[CEH]

Also, this is an unbound calculation. I did also try to write a SetValue macro which would save this to a table. However, if I need to run reports or make charts with averages, isn't it better to just do the calculations then and there, rather than save them to the table?

Your correct on not putting your calculations in a table. And if you are also using the calculations in a report... you will need the form open to reference the unbound textbox where the answer resides...... or do the calculation again on the report..... You might think about putting the calculation in the query.. kill two birds with one stone.... e.g.... Field... CalculatedAnswer: [Field1] *[Field2]

 

[hjg]

Good idea

Thank you Curtis,

I like the idea of the query.

Another question I have is regarding the sub-form layout. For looking up information the sub-form seems like a good idea. My sub-form spreads horizontally across the form. In fact, one has to use the scroll bar to get to the end of the sub-form fields.

However, for data entry purposes this seems unwieldy. Is there a way to make the data entry much simpler (even tabbing across is onerous). Perhaps I should use the 'switchboard' to do this? Any ideas?

I can zip and download my file if that is helpful.

Regards,
Geoff

 

[boblarson]

Another question I have is regarding the sub-form layout. For looking up information the sub-form seems like a good idea. My sub-form spreads horizontally across the form. In fact, one has to use the scroll bar to get to the end of the sub-form fields.

However, for data entry purposes this seems unwieldy. Is there a way to make the data entry much simpler (even tabbing across is onerous). Perhaps I should use the 'switchboard' to do this? Any ideas?

I would say you would be wise to explore the use of a tab control when "real-estate" is at a premium.

Just be sure you know that using a tab control doesn't require any special coding when referring to controls that exist on it. It's as if the tab control doesn't exist. So, you would refer to a control on it the same way as if it was directly on the form.


And this might help you with how to use subform syntax:

1. When you are dealing with subforms, you are actually dealing with two parts - the subform CONTAINER (that which houses the subform on the main form) and the subform itself.

2. The subform and subform container can be named the same, but are not necessarily so. You need to check before writing the code. If they are the same then it simplifies things but it doesn't really matter if it is, or isn't, because you just have to refer to the container.

3. When you are doing things, like setting the recordsource on the subform, you are not really requerying the container, as it doesn't have a requery method, but the subform itself does. So, when you are referring to a property, or method, on the actual subform (not the container), you need to have the subform container name and then .Form. between the container name and the method, or property, so Access knows you want to refer to the form's method or property and not the container's method or property.

 

[hjg]

okay

Okay, thank you for this information. I understand that using tabs will reduce clutter. A quick follow-up. I currently have a form and a sub-form. The sub-form appears as a text box within the form. The sub-form, however, is cluttered - therefore using tabs there will help, if I understand correctly.

Or, should I tab the main form along with the sub-forms, making a total of 3 tabs? I fear I'm not being clear... but does that question make sense?

Thank you to the kind people who have responded. I appreciate your time and informative answers.

Geoff

 

[boblarson]

therefore using tabs there will help, if I understand correctly.

It certainly can

Or, should I tab the main form along with the sub-forms, making a total of 3 tabs?

That all depends on your stuff and what you want to accomplish. I'm attaching a screenshot of a program I wrote a few years ago, for an example of what you can do, and it had forms, subforms and subforms on subforms and I used tab controls on each.

 

[hjg]

that's a great-looking db

Your db looks wonderful - I'm hoping to do a fraction of a fraction of what you've done, looking a fraction as nice.

Back to my basic questions: Bob, when you create tabs in your sub-forms, does it automatically format them as seen on your db screenshot? When I do it, the sourcing is fine, but the tabs do not show. Instead, there are numerous columns stretching out horizontally.

X X X X X X X X X X X X

Sort of like this, if the X represents a column (or field). I would prefer to have what you show on your screenshot, that is tabs, and then, inside each tab, various fields (columns).

On the main form, I can format the fields any which way I want. On the sub-forms, Access automatically creates columns in one row. I'm not sure why. Any way to format the sub-form, including the tabs, in the same manner one formats the main form?

[Also, I put a macro command in several places on my sub-form in order to perform a calculation. I decided to get rid of these After Event macros, but cannot seem to find one remaining, hidden macro. I actually deleted the macro, thinking I no longer needed it. However, now, when the form opens, it says it cannot find the macro - suggesting that there is still an After Event macro command somewhere. Any way to find this??]

Thanks so much, all, and especially Bob.

Geoff

 

[boblarson]

What you do is to create a form first and then you can add the tab and move the fields to the tabs. To do so, you select the control, click CUT and then click the tab you want it on and then click PASTE. But, if any have event procedures or macros that were attached to an event, you will likely need to go back to that event in the control's properties and select the drop down to assign to EVENT PROCEDURE or MACRO.

As for your error, somewhere you have referenced that macro. But, you will have to search through to find it. You might want to download, and install, the free V-Tools and use the "Total Deep Search" to find where the name is being referenced. You can get the tools here:
http://www.skrol29.com/dev/en_vtools.htm

 

[hjg]

To belabor the point, I'm trying to create a subform that is readable. Currently, it is not. It is in datasheet view, with 1 row and many columns, making both the readability and data entry unwieldy. I would like a non-spreadsheet view in the sub-form - much as on a typical form, wherein one can design the form however one likes. I'm unclear why this isn't (or why I can't figure out how it is) possible in sub-forms. I've tried switching to single form and continuous form, which did not work (I think those are not supposed to work in sub-forms?). I've read and re-read Bob's comment, but do not quite see an answer to my question in his last response - but have probably mis-read it.

A million thanks, again.

[NEVER MIND. IT WORKS NOW]

Geoff

 

[hjg]

My db

Here is the db, cleaned up and zipped. All data included is made up and fictitious.

The form is "EDForm." It is supposed to include the subform "ED_TmElpsd_Tbl subform" It doesn't appear as if the form is loading correctly (clearly, I made an error). You can, I suppose, look at the subform separately. What a mess. You can also see the form in design view.

Remember, I am just a beginner. I am just trying to make a workable, basic db that collects some data and will produce some reports. (Some of the stuff in the footer is not cleaned up. I'm unsure what I'll do with it.)

Any help or suggestions are appreciated.

Geoff

 

[boblarson]

Okay, I got it working for you.

Check out these changes:

1. changed the field that was the foreign key in the subform table
2. Changed your MR# to MRNum as you want to avoid using special characters and spaces within names of fields and objects. The # sign is an Access reserved character (putting something between two of those tells Access that you are giving it a date)
3. I changed your relationships - take a look at what I did and
4. look at the Master/Child links on the subform to see how they link together.
5. I put the FK field on your subform so you could see it in work but you can just set its visible property to NO so it doesn't show.
6. I changed your Edit Record button code on the main form, just to give you some more instruction as to how to use it.

I am attaching the database with the changes for you to look at. Be sure to ask questions about anything you don't understand.

 

[hjg]

thank you so very much

Bob,

Thank you thank you. I haven't actuallly had a chance to see the changes, as I'm at home and do not have Access on my laptop.

Nonetheless, I would like to thank you for what I can imagine are extraordinarily helpful changes. More importantly, as you mention, I think they will help me learn a little more about what the heck I'm doing in Access.

I will get back to you tomorrow once I've had a chance to look at and digest the changes.

Thank you kindly. What a pleasant forum.

Geoff

 

[hjg]

Some other questions...

1) I'm still having trouble making the "Unlock to Edit" button work in the sub-form.

2) Is there a more elegant way to enter the date/time than the 'general' format? I had to do it that way in order to capture wait times of over 24 hours.

3) How do you get a screenshot of the db? I've tried the "Print Screen" button, but nothing seems to happen.

4) I've put a command button that performs an OpenForm macro function - this opens a more general patient census form. However, because the primary form is in full screen (I've 'maximized' it with a Maximize macro), the secondary form cannot be seen underneath. I tried to put a Minimize function into the same macro as the OpenForm function. Any suggestions?

(EDForm is the primary form and frm_PF is the secondary one I wish to open)

 

[hjg]

Why with the 'unlock to edit' button does the code only work on some of the form's fields? I now only have a single form (I've gotten rid of the sub-form). When I click on the 'unlock to edit' button it unlocks the original main form's fields (about 9 fields).

But, it does not affect the other 10 or so fields on the lower half of the form (these are fields which were added to the main form and which used to be part of a sub-form placed on the main form). When I click on properties, it says that I am in the detail section of the form - yet this appears to be the same part of the form as the upper fields.

Any idea why this is not working?

Thanks in advance,
Geoff

 

[boblarson]

Why with the 'unlock to edit' button does the code only work on some of the form's fields? I now only have a single form (I've gotten rid of the sub-form). When I click on the 'unlock to edit' button it unlocks the original main form's fields (about 9 fields).

But, it does not affect the other 10 or so fields on the lower half of the form (these are fields which were added to the main form and which used to be part of a sub-form placed on the main form). When I click on properties, it says that I am in the detail section of the form - yet this appears to be the same part of the form as the upper fields.

Any idea why this is not working?

Thanks in advance,
Geoff

Geoff:

Probably because you didn't change the tag property on those other controls. I had put the word 'lock' I believe, in the tag property of those other controls and the code is looking for controls with that tag. I use that so I can selectively choose which to lock, and also to avoid trying to lock control types that can't be locked.

 

[hjg]

Bob,

I owe you a bunch of beers.

Thanks!

Geoff

 

[boblarson]

I'm glad it seems that you are getting things to where you want them to be.

 

[hjg]

I'm still having some issues, actually. I've attached the db, if anyone is interested in giving suggestions (no real data involved). My worries are:

I can't seem to build a PivotTable for PFGrandTbl because I don't have a Count column for my field totals. So I'm not sure what to add to the data part of the table. Perhaps I should have had a count column for each of the fields.

I'm having a heck of a time making a nice query with sub-queries built into them. On the EDForm I have some unbound textbox calculations which I'm using in queries to get average elapsed times. I'm having to do queries of queries, which means I have way too many queries.

I'm worried about buidling PivotCharts and Tables for EDForm as well.

Thanks again to Bob and others for suggestions. Best wishes,
Geoff