Setting values in unbound control on bound form

[KeithWilliams]

Hi,

My first post!

I have a form which is initially unbound, but then I assign a record set to it in the Open event as follows:

    Set Me.Recordset = rst

2 of the fields in the form have names corresponding to the field names in the query referenced by rst. So I guess that makes it a bound form from that point on.

The form also includes an unbound control (with blank control source, and name which does not correspond to a field in the recordset).

The view of this form is Continuous Forms. It always displays 12 rows, one for each month of the year. I want to populate the unbound control with a different value for each month in the VBA code, but how do I do this? I want to say something like:

Me!UnboundField.Value[1] = JanuaryValue
Me!UnboundField.Value[2] = FebruaryValue

etc.

But I don't know how to reference the value in each row distinctly. If I assign a value to Me!UnboundField.Value then that value is assigned to all 12 rows!

I have a hideous workaround, based on setting the unbound field's control source to a CHOOSE statement based on the month, but I'm sure this isn't the "proper" way to do it.

Many thanks,
Keith.

 

[Mile-O]

Can you post an example with this form as I can't visualise it?

 

[KeithWilliams]

Hi,

I've attached an image of the form. The Control Source for the form is assigned dynamically in the form's Open event. That populates the values in Month and Target. I can't use a single query to populate all 3 fields, because it would be non-editable, and I need the Target to be editable.

So I want to populate the Actual values for each month with a value retrieved from the database by a separate query, also in the form's Open event.

Hope that clarifies it!

Many thanks,
Keith.

 

[KeithWilliams]

Can't see the attachment from my previous post, so trying again.

 

[Pat Hartman]

I can't use a single query to populate all 3 fields, because it would be non-editable

- Why would be non-editable?

Access maintains only a single value for unbound controls so there is no way to have a continuous form show different values in an unbound field on different rows. The unbound control will show, as you have seen, the same value on all rows.

 

[KeithWilliams]

Why would be non-editable?

Because Target is the value I want to be able to edit, and Actual is a field calculated as a sum of values from another table. If I try to join the sum of values with the table containing Target in a query, the query becomes non-updateable. I can't find a way to force it to be editable. The Help system lists the rules for determining whether a query will be editable. I guess I breach one of the rules with my query, although not sure which one. Is there a way to force a field in a query to be editable?

Thanks,
Keith.

 

[Pat Hartman]

Much as I hate to use domain functions in queries, this is a case where it is hard to come up with an alternative solution. Even a sub-select produces a non-updatable query. Try something like this:

SELECT p.PersonID, p.FirstName, p.LastName, p.Suffix, p.JobTitle, DSum("amt" , "table1", "PersonID =" & p.PersonID) AS SumOfamt
FROM tblPerson5 AS p;

 

[KeithWilliams]

Hi,

Thanks for the suggestion of using DSum. I had no luck with this. This is my query using DSum:

SELECT SAP_Month.MonthStartDate, 
SAP_Year_By_Month_Parameter_Query_Target.Target, 
DSum("1","SAP_Incident","Year = 2003 AND TimeOfCall< SAP_Month.MonthStartDate")
FROM (SAP_Month INNER JOIN 
SAP_Year_By_Month_Parameter_Query_Target ON 
SAP_Month.MonthStartDate = SAP_Year_By_Month_Parameter_Query_Target.MonthStartDate) 
;

Access won't allow my reference to SAP_Month.MonthStartDate in the criteria of the DSum. It won't even allow a reference to a column of the SELECT statement in the first argument of DSum. Since I need a different value for each month, I need a way for the DSum arguments to reference the containing query. I don't think Access supports this. The Help documentation says the columns referenced in the criteria must be in the table specified by the DSum, and I think the same is true for the first argument.

I am beginning to think this is where I need to take the plunge and build an unbound form, with 36 controls, that is 12 text boxes for the months, 12 for the Target values and 12 for the Actuals, and manage the retrieval and updates in code. Do you think this might be the case? (I am not looking forward to this labour-intensive approach!)

Thanks,
Keith.

 

[Pat Hartman]

I forgot that the letters SAP stand for Sucky Application Program. If you're looking for an example of good table design, don't look there.

In any event, the criteria part of the DSum() didn't work because you didn't follow the syntax in my example. The selected field is more of a problem since the SAP table is unnormalized. Am I reading this correctly, is there really a separate table for each month and does the amount field really have a different name in each table???? You are going to have to use a union query to normalize the Actual data. Then the DSum() can look up the correct value in the Union query instead of the SAP_Incident table.

And finally, a column named Year is going to cause you problems. Change it.

 

[KeithWilliams]

Hi Pat,

I forgot that the letters SAP stand for Sucky Application Program.

Actually, in this case SAP is the acronym for my application! ("Station Action Plans", in case you're interested). However, I won't dispute the accuracy of your comment!

You were right, by including the reference to SAP_Month.MonthStartDate in the quotes, I was invalidating my query. I didn't realize that DSum would dynamically calculate the value of SAP_Month.MonthStartDate in the DSum criteria for each row. I played with the query, and finally got it working thus:

SELECT SAP_Month.MonthStartDate, SAP_Year_By_Month_Parameter_Query_Target.Target,
DSum("1","SAP_Incident","Year = 2003 AND Month(TimeOfCall) = " & Month(SAP_Month.MonthStartDate)) AS Actual
FROM SAP_Month 
INNER JOIN SAP_Year_By_Month_Parameter_Query_Target 
ON SAP_Month.MonthStartDate=SAP_Year_By_Month_Parameter_Query_Target.MonthStartDate
;

I know you were disparaging about using DSum, but it does the job for me in this case. The Target value is editable in the result set, and the other fields are non-editable. I suspect your dislike of DSum is related to the fact that it seems to slow the query down to a snail's pace!

I was interested to find that I needed to use INNER JOIN to relate the table and the query, rather than a WHERE clause. Using a WHERE clause made the recordset non-editable. (I don't understand why, as the relationship is the same).

To clarify the table relationships, no, SAP_Month is one table with a row for each month of the year. This will allow me to use outer joins where there are no Actual values for a given month. (I tried the above query with a LEFT JOIN, and the Target value is still editable). The SAP_Year_By_Month_Parameter_Query_Target query is based on a table SAP_TARGET, and includes a number of parameters to restrict the result set. It returns one row for each month.

Thanks again for all your help and patience.

Cheers,
Keith.

 

[Pat Hartman]

Keith, I hope you have at least heard of the SAP application that I was referring to. It is a very complex application with many "modules" that corporations can buy and "connect" together. The problem with it is that in order to modularize it, the table structure is unnormalized to a large degree because of the overlap in data required for each "module". The application was originally developed many years ago for the mainframe and has been modified and converted many times over the years to use a different back end database and different front end interface.

I was interested to find that I needed to use INNER JOIN to relate the table and the query, rather than a WHERE clause. Using a WHERE clause made the recordset non-editable. (I don't understand why, as the relationship is the same).

- People who have used SQL in other environments frequently make this error. Jet uses the "newer" join syntax which specifies the join type. When you use a non-specific join, Jet assumes that you want a cartesian product (cross-join) rather than an Inner Join and by definition, cartesian products are not updatable.

You are correct in why I disaprove of using domain functions in queries. If you have more than a small number of rows, they are extremely slow. I just had another idea for how to solve your problem. If you only need to show 12 rows, you can create two subforms, one based on a query that sums the actuals and the other based on a query that gets the forcasts. The forcast query would then be updatable and you would still see the two amounts side-by-side.

If you can live with seeing the value for one month of actuals at a time, you can use a subform for the forcast and as the current record pointer is moved into a row, you can requery an unbound textbox on the main form that does a DSum() of the actuals for that month. Although this solution still relies on DSum() the DSum()'s run one at a time rather than all at once, so although they technically will take the same amount of time, it is distributed and so won't seem as bad.

 

[KeithWilliams]

Hi Pat,

Don't worry, I have come across SAP before, so wasn't offended!

I will try your suggested approach with 2 subforms, but I suspect I will run into limitations Of Access, because I will need to pass a number of parameter values to both queries.

But I do need to display an at-a-glance view of all 12 months, so your last suggestion won't be workable.

I must admit, having used the PowerBuilder 4GE for many years, Access does seem extremely cumbersome for developing interfaces of any sophistication. I'm sure to a large extent this is down to experience.

Cheers,
Keith.