423,846 Members | 2,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,846 IT Pros & Developers. It's quick & easy.

unbound form

P: 28
I have an unbound form that I need to insert several calculated fields data into a unbound text boxes. I have all the calculated queries all done I just need to insert the values into the text boxes. I thought of using Dllookup but I get an error ?name when I put the Dlookup on there controls property of the unbound text box.
Please help.

Attached Images
File Type: jpg form 1.jpg (168.4 KB, 132 views)
1 Week Ago #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,121
The obvious question must surely be why the form is unbound?
1 Week Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,703
And.... Are you referencing values on this unbound form in your Queries?
1 Week Ago #3

P: 28
Yes the values come from several different queries.
1 Week Ago #4

twinnyfo
Expert Mod 2.5K+
P: 2,703
Referring to controls on a Form within your queries can work, but sometimes, depending on how they are referenced, it can cause errors. Here is an example:

I have a Form named frmPeople. On that form, there is a text box named txtPersonID. The Value of that text box is 81404, as this is the unique identifier for that personís record in my DB.

If I want to find this personís address in a Query, I may do this:

Expand|Select|Wrap|Line Numbers
  1. SELECT StreetAddress, City, State, ZIP 
  2. FROM tblAddresses 
  3. WHERE PersonID = [Forms]![frmPeople].[txtPersonID];
When that query is executed, what happens is that the query ought to interpret [Forms]![frmPeople].[txtPersonID] as 81404. If this query were all by itself, it would probably run fine. However, if you had another query, using the values of this query, it may throw an error. Tertiary and quaternary references will almost always throw an error.

Without actually seeing all the queries that you are using, I cannot confirm that this is what is causing your errors. However, one way to avoid this error is to generate your queries using VBA on the Form, using the values on the form (and/or) just use DLookup()s exclusively to determine your other calculated values. An example of creating your VBA would be this:

Expand|Select|Wrap|Line Numbers
  1. StrSQL = _
  2.     "SELECT StreetAddress, City, State, ZIP " & _
  3.     "FROM tblAddresses " & _
  4.     "WHERE PersonID = " & Me.txtPersonID & ";"
Although this looks similar to the previous code, but this will never have issues when run, because the Value of the string is now:

Expand|Select|Wrap|Line Numbers
  1. SELECT StreetAddress, City, State, ZIP FROM tblAddresses WHERE PersonID = 81404;
Hope this hepps!
1 Week Ago #5

P: 28
I think i'm going to use subforms for each query and attach it to the main form . since i'm really having a issues with getting the query's data inside the unbound text boxes on the form that is unbound.
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,121
Without knowing all your details as well as you do, it certainly sounds like bound forms make more sense.
1 Week Ago #7

P: 28
i van send you a copy of the db im not sure how i can get all the forms to layout the way i want them to
You can download the database from address: http://s000.tinyupload.com/?file_id=...95908091720321
1 Week Ago #8

twinnyfo
Expert Mod 2.5K+
P: 2,703
Unfortunately, most of us cannot access files posted elsewhere from work. You can use the advanced button to post a reply and add a zipped file, but this should not be necessary to even post your DB (unless a mod specifically asks for it).

I htink your DB has a few other challenges that we might need to look at first.

Is there a specific reason why this form cannot be bound to an underlying dataset?
1 Week Ago #9

P: 28
because the data i need display come from several queries from the table.
1 Week Ago #10

twinnyfo
Expert Mod 2.5K+
P: 2,703
You may be able to combine those queries into one.

Would you be so pleasant as to post the SQL statements for those queries?

Perhaps we can find a way to use one query to display all your data. This would certainly be a better solution than the direction this form's structure is currently taking.
1 Week Ago #11

P: 28
70 queries i would have to send you
1 Week Ago #12

twinnyfo
Expert Mod 2.5K+
P: 2,703
Again, sounds more like a structural re-work than anything else.

It might be more helpful for you to provide us some explanations about your form, first. What do the various fields mean, and how are they calculated from the table? Post #10 implies that these 70 queries are based upon one table, correct?

My initial thought, based upon a quick review of your form, is that if we can narrow down how to calculate one of your text boxes, we can apply that same priniciple to the others.

For this step, can you explain the "Cheese" column, what the different fields represent and how the values are calculated? You could provide a coupl of the qureies, also, just to give us an idea of the calculations you are using.

This might take a while for us to understand what you are doing, but if you are willing to work with us, we are willing to work with you.

Thanks again.
1 Week Ago #13

P: 28
cheese field departmentb fields for pM COMPLIANCE the calculation for each of the departments thefist query

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(MaximoReport.WorkOrder) AS [CountOfWork Order] 
  2. FROM MaximoReport 
  3. WHERE (((MaximoReport.WorkType)="PMINS" 
  4.     Or (MaximoReport.WorkType)="PMOR" 
  5.     Or (MaximoReport.WorkType)="PMPDM" 
  6.     Or (MaximoReport.WorkType)="PMREG" 
  7.     Or (MaximoReport.WorkType)="PMRT") 
  8.     AND ((MaximoReport.Status)="COMP" 
  9.     Or (MaximoReport.Status)="FCOMP") 
  10.     AND (([MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour])>=DateAdd("h",-1,[Enter the Start Date]) 
  11.     And ([MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour])<DateAdd("h",23,[Enter the End Date])) 
  12.     AND ((MaximoReport.ActualLaborHours)<>"00:00" 
  13.     And (MaximoReport.ActualLaborHours)>"0") 
  14.     AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,"08/19/2018") 
  15.     And (MaximoReport.ActualStartDate)<DateAdd("h",23,"08/25/2018")));
then i divide that query out put with this query

Expand|Select|Wrap|Line Numbers
  1. SELECT [MaximoReport].[WorkOrder], 
  2.        [MaximoReport].[WorkType], 
  3.        [MaximoReport].Status, 
  4.        [MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour] AS Expr1 
  5. FROM MaximoReport 
  6. WHERE ((([MaximoReport].[WorkType])="PMINS" 
  7.     Or ([MaximoReport].[WorkType])="PMOR" 
  8.     Or ([MaximoReport].[WorkType])="PMPDM" 
  9.     Or ([MaximoReport].[WorkType])="PMREG" 
  10.     Or ([MaximoReport].[WorkType])="PMRT") 
  11.     And (([MaximoReport].Status)<>"CAN") 
  12.     And (([MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour])>=DateAdd("h",-1,"08/19/2018") 
  13.     And ([MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour])<DateAdd("h",23,"08/25/2018")));
output. each text box is a percentage od PM completed workorder for each department
1 Week Ago #14

twinnyfo
Expert Mod 2.5K+
P: 2,703
A confusion, a couple observations, a couple improvements:

A confusion:
In your first query, because it is an aggregate query and all you are doing is counting the number of work orders, you are returning one field ([CountOfWork Order]), which has a numerical value. The second query returns four fields, none of which is a true numerical value. Exactly how are you dividing one by the other?

A couple observations:
How do we know that this has anything to do with "Cheese"? It may be perfectly clear to you, but not to the outsiders.

The expression [icode][MaximoReport].[Target Start]+[MaximoReport].[TargetStartHour][i/CODE] appears to be a Date/Time value that you are returning. If this is the case, you can easily set the [Target Start] field to be a Date/Time field, using the General Date/Time format, which allows you to capture the date and the time in one field. This is a more effective way to capture this value.

The first query uses a user input for the target start and stop dates, the second uses a hard date. Is this intentional? Also, the first query looks to be quite restrictive, as the user can enter whatever target start and stop dates they wish, but the actual start and stop dates are fixed.

What is the purpose of subtracting an hour from the start date and adding 23 hours to the stop date?

The field [MaximoReport.ActualLaborHours], according to your query appears to be some form of date, but it is being expressed as a text string ([MaximoReport.ActualLaborHours]<>"00:00"). If this is a true date field, with a default value of 0, you should be able to evaluate a value as simply <> 0, which is a lot simpler and more straightforward.

A couple improvements:
Evaluating the same field against many criteria can get quite burdensome, and wears out the fingers. If you know the values, you can use the In() clause in your query. Using some of these principles, let's see what your first Query could look like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(MaximoReport.WorkOrder) AS [CountOfWork Order] 
  2. FROM MaximoReport 
  3. WHERE MaximoReport.WorkType In ("PMINS", "PMOR", "PMPDM", "PMREG", "PMRT") 
  4.     AND MaximoReport.Status In ("COMP", "FCOMP") 
  5.     AND [MaximoReport].[TargetStartDateTime]>=DateAdd("h",-1,[Enter the Start Date]) 
  6.     AND [MaximoReport].[TargetStartDateTime]<DateAdd("h",23,[Enter the End Date]) 
  7.     AND MaximoReport.ActualLaborHours>0 
  8.     AND MaximoReport.ActualStartDate>=DateAdd("h",-11.8,"08/19/2018") 
  9.     AND MaximoReport.ActualStartDate<DateAdd("h",23,"08/25/2018");
I recognize this does not give you an overall solution, but helps you understand some things a bit better.

Could you provide the structure of your Table MaximoReport?

I think we can still do this more easily than having 70 queries calculating things.

Thanks for being patient with us as we strive to work through htis with you--and hopefully we will all understand the issues better.
1 Week Ago #15

Post your reply

Sign in to post your reply or Sign up for a free account.