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.

 

[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.

 

[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.

 

[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.

 

[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.