Question Asked for a parameter on form open...why? (1 Viewer)

[severly]

I have designed a database for use in tracking our projects. I've made use of queries to perform calculations that I need and sub-forms and sub-reports to display these calculations. All of my routines and operations work perfectly except for this one strange detail.

When I first open my database and then open my main project form, I am asked to provide the Project ID parameter. I use this parameter to reference the main form for all of the sub-forms and queries, basically to tie the right information together. Since I'm opening the form and it should proceed to record number 1 which holds Project ID 1 in that record and all queries have this form as its criteria, I don't know why it is asking for this. If I hit the cancel button I end up getting to the opened form, but if I advance to the next record I go through this again.

Now here is where it get interesting...if I go to Design View, click any control that has an Event Procedure, go to the properties sheet and click the "..." button next to Event Procedure to open the VBA window, then all I have to do is close the VBA window (mind you I haven't changed anything), then go back to Form View and surprise surprise, everything works correctly. It will continue to work fine until I close the database. At this point I will be asked I would like to save the changes to the form. Mind you that I haven't made any changes to the form, but I've clicked both "Yes" and "No" to this question, but it doesn't change the fact that the next time I open my database I have to go through this again.

Granted, it isn't that big of a deal, but I'm interested in figuring out why this happens and if I can solve it, then I can go to using the switchboard like I want. I've tried the "Repair and Compact" feature but it doesn't help. I didn't know if this is a query issue or a form issue, so I just thought I would ask it here and see if anyone can point me in the right direction.

Thanks in advance!

 

[Lister]

Sounds like one of your procedures or queries is calling the [forms]![formname]![Field] before the form has finished loading.
Follow the open sequence step by step and you should hunt it down.

It's the little things.

 

[severly]

Ok, that sounds like it has potential. I have a couple subforms on this form and reference the Project ID field to know what to include in their related fields. So this might be a silly question, but how to I control the opening sequence of the form. Right now I am just double clicking the form from the list pane on the left.

 

[vbaInet]

I have designed a database for use in tracking our projects. I've made use of queries to perform calculations that I need and sub-forms and sub-reports to display these calculations. All of my routines and operations work perfectly except for this one strange detail.

When I first open my database and then open my main project form, I am asked to provide the Project ID parameter. I use this parameter to reference the main form for all of the sub-forms and queries, basically to tie the right information together. Since I'm opening the form and it should proceed to record number 1 which holds Project ID 1 in that record and all queries have this form as its criteria, I don't know why it is asking for this. If I hit the cancel button I end up getting to the opened form, but if I advance to the next record I go through this again.

Now here is where it get interesting...if I go to Design View, click any control that has an Event Procedure, go to the properties sheet and click the "..." button next to Event Procedure to open the VBA window, then all I have to do is close the VBA window (mind you I haven't changed anything), then go back to Form View and surprise surprise, everything works correctly. It will continue to work fine until I close the database. At this point I will be asked I would like to save the changes to the form. Mind you that I haven't made any changes to the form, but I've clicked both "Yes" and "No" to this question, but it doesn't change the fact that the next time I open my database I have to go through this again.

Granted, it isn't that big of a deal, but I'm interested in figuring out why this happens and if I can solve it, then I can go to using the switchboard like I want. I've tried the "Repair and Compact" feature but it doesn't help. I didn't know if this is a query issue or a form issue, so I just thought I would ask it here and see if anyone can point me in the right direction.

Thanks in advance!

Zip, attach and post your db and I'll have a quick look

 

[severly]

Thanks for taking a look at it for me vbaInet. I've attached the zipped db. I'm sure you will see the structure isn't the greatest, I'm a novice to say the least. I was actually pretty happy with what I was able to figure out.

 

[vbaInet]

When I first open my database and then open my main project form, I am asked to provide the Project ID parameter.

I've just opened up your database and it didn't ask for a project ID. Try my version and see if it works fine on yours. I did a Compact & Repair too just in case.

Now here is where it get interesting...if I go to Design View, click any control that has an Event Procedure, go to the properties sheet and click the "..." button next to Event Procedure to open the VBA window, then all I have to do is close the VBA window (mind you I haven't changed anything), then go back to Form View and surprise surprise, everything works correctly.

Sometimes when you click on the "..." it assumes you have made some changes to the design, so that's something everyone is conversant with

And yes I agree you've done a rather good job in your project that I've even gone the extra length to help you add a few design changes. Nothing major. Hope you like the layout

 

[severly]

vbaInet,

Thanks for taking a look. When I open my T - Project form I'm not getting asked for the parameter now, but did you notice that the Sum of Equipment price is WAY off now? The math should take the number of each unit and the amount of time we have shown for each unit and multiply it, then add all of the hours up and multiply by the Labor rate field. When I ran the query for Equipment, It was returning multiple instances of the same Unit Quantity and Hours field.

So for instance, there might only be 4 pieces of equipment on a project, but if you run the Equipment query directly I would get 23 records because each piece was shown 6 or 7 times. That is inflating the total equipment price. I'm not sure if this was because the Project ID field was moved into the header or what.

Any ideas?

 

[vbaInet]

Moving the Project ID to the header wouldn't affect it. I've just downloaded your original project and it's the same figure as the one I sent you. I will try to find out the problem.

 

[vbaInet]

Question: What is the link between the price of one equipment to the quantity per equipment? How is that referenced? What are the differences between Material and Equipment?

 

[vbaInet]

Have a look at the attached. Much more of a realistic figure?

 

[severly]

VbaInet,

We are getting closer, but still double the amount that we should have. Let's look at the last project, (Project ID = 9) On the T - Project sheet which is where all of the information is entered we see the following:

Unit Hours Quantity

RTU 50 2
Exhaust Fans 7 3
Zone Dampers Wired Stats 6 18
Zone Dampers Wireless Stats 5 2

This means that the total hours for the units are as follows:

RTU = 100 hrs
EF = 21 hrs
Wired Stats = 108 hrs
Wireless Stats = 10 hrs

Total hrs = 239

At $50/hrs = $ 11,950

The Q - Equipment query pulls the Equipment from the T- Equipment table that is entered for this project. It also performs calculations to arrive at the total hours per unit (hours * quantity), price per unit (hours * labor rate) and the total price per unit (hours * labor rate * quantity). I then use Q - Equipment Total to run the Sum of the Hours and the Sum of Total Unit Price. The Sum of Total Unit Price is the value that is shown to the right of the Equipment window on the T - Project form. I have that value set to requery On Exit of the Equipment subform.

I looked into the query operation and I saw that each piece of equipment was being shown twice. Everything was the same except the Labor Rate, each record was repeated with one Labor Rate being $50.00 and the other being $52.00. The additional records were causing the inflated values that we were seeing. I don't know why the query was returning two sets. If you opened the T- Equipment table that the query is based on, you would only see the (4) items that are shown on the form with Project ID being 9. However, if you run the Q - Equipment query, you would have (8) records returned, (4) with the $50.00 value and (4) with the $52.00. I don't know why it was functioning this way, but I entered:

[Forms]![T - Project]![Labor Rate] in the criteria field of the query and that knocked out the records that did not match the Labor Rate on my T - Project form. Again, I don't know where these extra records were being generated from, but it was easy to filter them out. I've attached the zipped database showing my changes so you can see how it operated.

 

[vbaInet]

Took me a while to find what was wrong. In your Q - Equipment query you are using two tables, but when you open it in design view you see only one (T - Equipment). This is because your other table (T - Project) is hiding "nicely" behind Equipment. Move the Equipment table, then join both tables via ProjectID, problem solved. You wouldn't even need to inject it with DISTINCT or filter. Can you see what I mean?

 

[severly]

I discovered that as well. So I think I'm pretty much at the point I was hoping to get to. We ended up tackling a couple new items along the way, but that is how we learn huh.

Thanks again for your help.

 

[vbaInet]

You're welcome. Just a few pointers. Avoid using spaces or hyphens or special characters when naming forms, reports, objects, fields etc.

A good naming convention is: tblEquipment or in your case T_Equipment (where T stands for Table). But I noticed you used T as part of the name of some of your forms. If you were coding in VBA you may fall into problems. Be careful if you decide to change it now, because you would need to change every object in your db.

Also try not to base one form on many queries. Do more vb programming

Have fun!