Update a form control on form open event

[longhair]

afternoon all,
hopefully, someone can point me in the best dircetion to do this in vba.
i have a form with numerous text fields - most have their control source as a record set passed from a query. there are a few, however, that are unbound. these need to be updated / changed / etc based upon different apsects of the record set or calcutlations based on the record set.
what i'm atempting to do is use vba to update these text fields on the on open event. the form is a continous form that can vary from 2 entries (records) to theoretically an infinate number of entries.
what i have so far:

Private Sub Form_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim intRecCount1 As Integer

rstPostDtl.MoveLast
rstPostDtl.MoveFirst

Set dbs = CurrentDb
Set Forms("VendDtl2").Recordset = rstPostDtl

Me.Text0 = txtVend
Me.Text2 = txtName

intRecCount1 = rstPostDtl.RecordCount
n_due = 0
With rstPostDtl
Do While Not rstPostDtl.EOF
    For i = 1 To intRecCount1
       If .Fields(0) = "V" Then
            n_due = .Fields(6)
       Else: n_due = n_due + .Fields(5)
       End If
       'Me.Net_Due.Value = n_due <-issue is here?
       MsgBox (n_due)
       .MoveNext
    Next i
Loop
End With
End Sub

i am having trouble setting the field [Net_Due] to contain the appropriate data. the messagebox that shows n_due shows that n_due contains the correct data - but no matter what i do the field [Net_Due] does not show the correct data. seems to only show the first instance of n_due or the last instance (based upon how i change my code). probably missing something simple here - do i need to make n_due an array and then somehow loop through the form - setting each instance of [Net_Due]?
hope this makes sense.
any suggestions appreciated.
i've already been down the road trying to use calculated fields in the query and am not able to accomplish what is needed here there. i cannot change any thing in the underlying tables nor add new ones.
thanks in advance.
regards,
longhair

 

[RoyVidar]

This is how a continuous form behaves. What you see, isn't many controls, but multiple instances of the same control - so what you do programatically to one instance, you do to all instances.

Only way I know of to overcome this, is to bind the controls.

Roy-Vidar

 

[longhair]

RoyVidar,

thanks. is it possible to bind [Net_Due] to n_due while other controls are bound to rstPostDtl (the recordset)?
or could one unbind the form from the recordset and code each seperate control : for example
with rst
me.text1 = .field(0)
me.text3 = .field(1)
etc
end with

regards,
longhair

 

[RoyVidar]

I'm sorry, my impression is that
1 - all the controls must be bound
2 - they must be bound to fields in the same recordsource/recordset
3 - binding means each controls controlsource consists of the name of one of the fields in the recordsource/recordset

I must confess I've accepted what others have said, that this is how continuous forms behave. What little I've tested, confirms it - what one assigns programatically to one instance of a control, will most likely occur/display in all instances of it. One exception being conditional formatting.

I'd love to be proven wrong, though.

Roy-Vidar

 

[IanAble]

There is one inelegant way of doing this. Bind your previously unbound controls to a User Defined Form Function which receives an input.

If I read you right, there is a direct link between the Record Field Values and the results you want to display in the unbound controls (similar to using a DLookUp function). Pass that value as you would to any other calculated function.

If you're using the AbsolutePosition of the record that's a little more tricky. You'll have to include a summing field in your query.

In the end it would be easier, to use a similar Function in the RecordSource query and make the calculated field the ControlSource of your previously unbound Control.

Either way such functions would have make use of static variables (e.g. to RecordSet) to reduce the memory overhead. Even then things will move more slowly.

 

[longhair]

IanAble,

thanks. perhaps i'm a bit thick skulled - and the fact that i do not work with vba all that much, anymore, and using sql very infrequently. but i thought that the following (in the query) would equate to my vba:

Net Due: IIf([aptrxp].[type]="V",(Sum([vch-hdr].[inv-amt])+([aptrxp].[amt-paid])),(Sum([Net Due])+([aptrxp].[amt-paid])))

i may have the 'sum' in the incorrect place but i would think that this logic is correct. however, access throws back an error: "circular reference caused by alias 'Net Due' in query definition's SELECT list".
any suggestions?
regards,
longhair

 

[RoyVidar]

You seem to call this "new field" the same as the field you're referencing as the last part/false part of the IIF statement.

But I'm not sure this would work either (the aggregates). I think, for someone to be able to assist, there would be a need for a wee bit more info.

Perhaps subqueries would do?

Roy-Vidar

 

[NvrSaDi]

If you are working with a continuous form, following formula works for me, if the form recordsource is a table or updatabable query.

Since the table is locked when it is the record source to an open table, one way to update the form and the table is through a form attached to the table.

In this setup, I have a continuous form attached to a table with 99 records which have only an ID field filled in for all 99 records.

In this scenario, if I have filled in a field named Description, the program counts the records, saves the count to an integer variable named RecCount and then sets the value for a checkbox on the same form. If the Description field is empty, the program exits the subroutine.

For n = 1 To 99

DoCmd.GoToRecord acDataForm, "frmRenewal", acGoTo, n
If Me.Description = "" Or IsNull(Me.Description) Then
RecCount = n 'saves record count for future use
Exit Sub 'exit procedure if Description field is empty
End If
Me.InUse.Value = 0 'sets value for checkbox
Me.Repaint
Next n

Also, if you are getting a circular in a calculated field in a query, it is because you are using the field name twice - in the field name and in the calculation in the same query field.

 

[longhair]

AccessDB,

i'll take a look at your suggestion in a bit. i understand why i'm getting the circular reference error - but the fact remains that 'Net Due' is calculated by taking the original invoice amount and subtracting the payment from it - if there is more than 1 payment then the 'Net Due' is equal to the previous 'Net Due' minus the current payment.

RoyVidar,
for a little more info. this is going to be a historical db. it will contain records that were purged out of our actual mrp db (which is a totally different rdbms). the powers that be want the screens to match our mrp system as exactly as possible. this is an a/p details screen. the users will get here after selecting a record on the previous form (a/p summary screen). i then use the voucher from the previous form as the parameter for my select query.
the form displays the following:
field note
type straight from table
voucher straight from table
po calculated
due/paid calculated
invoice/check# calculated
amount calculated (net effect on g/l)
net due calculated
payment total calculated

there are 2 types of 'type' - 'v' for voucher 'p' for payment. based upon the 'type' i display (or do not display) and may do some calculations.
for example the field 'po' is only shown when the 'type' is 'v'. i accomplish this with the following in my query

PO: IIf([aptrxp].[type]="V",[aptrxp].[po-num],"")

based upon he 'type' i need to do calculations to show the correct 'net due'.
for example:
we have a voucher number - there are a total of 4 records, one of type 'v' (the inital record) and 3 of type 'p'.
the 'net due' of the 'v' record is the initial invoice amount(from another table). the 'net due' of any subsequent records is the previous 'net due' plus the 'amount' represented as such:
voucher type net due payment total amount
123456 v 559.68 0.00 559.68
123456 p 0.00 559.68 - 559.68
123456 p 559.68 - 559.68 559.68
123465 p 0.00 559.68 - 559.68
an instance such as this occurs when a check is voided (the 2nd instance of a 'type' 'p' record and then a new check is cut, but will also happen when you have numerous payments for the same voucher - this will depend on the terms we have with the vendor.
i hope this explains the issue a bit better. i'm open to any suggestions.

regards,
longhair