how to write VBA for populating specific form values ?

[RLehrbass]

How would you write the following in VBA?:



[frmPayment] has the following defaults: MemberID= [forms]![frmMember]![ID], PaymentDate= date() and the record source is [tblTransaction]

On opening [frmPayment],
run [qryBalanceReport] in background,
IF [qryBalanceReport].[SumOfDebt]= 0
THEN add (as a new record) the value of [tblMember].[totalDues] to [tblTransaction].[Debt],
AND set [frmPayment].[PaymentAmount] = [tblMember].[TotalDues].

ELSE [frmPayments].[PaymentAmount] = [qryBalanceReport].[Balance]

Note:
[qryBalanceReport] uses Totals to sum .[Credit] columns and .[Debt] columns respectively
and also returns .[Balance]: sum([Debt]-[Credit]).

[June7]

To get the value from query object, would have to open a recordset in VBA or use DLookup domain aggregate function.

Does form open to new record row or do you need to move to new record row if condition is met?

[RLehrbass]

moderator,
add new record to new record row if condition is met.

[June7]

I know you want to add new record.

Question concerns whether the form is already on a new record row when it initially opens. Or needs to move to new record row.

[RLehrbass]

frmPayment is already in addnew record
tblTransaction.debt is not open

[June7]

Should Debt be Debit?

Where is TotalDues coming from?
If it is not included in the form RecordSource, maybe need to do a DLookup. Why does TotalDues need to be saved into 2 other tables?

Then in the form OnCurrent event, try something like:

Dim dblBalance As Double
dblBalance = DLookup("SumOfDebt", "qryBalanceReport")
If dblBalance = 0 Then
'do something here
Else
'do this something here
Me!PaymentAmount = dblBalance
End If

[RLehrbass]

Should Debt be Debit?

Where is TotalDues coming from?
If it is not included in the form RecordSource, maybe need to do a DLookup. Why does TotalDues need to be saved into 2 other tables?

Then in the form OnCurrent event, try something like:

Dim dblBalance As Double
dblBalance = DLookup("SumOfDebt", "qryBalanceReport")
If dblBalance = 0 Then
'do something here
Else
'do this something here
Me!PaymentAmount = dblBalance
End If

Yes you are correct, it should be Debit not Debt, it is just a title right?

1) [TotalDues] is from [tblMembers] which the Record Source for [frmMember], which is currently open and the [frmPayments] is being opened from.
2) The value in [TotalDues] will be used as the value in [tblTransaction].[Debt] to issue a 'starting balance' if no previous debts have been recorded.
3) [TotalDues] will be used as the value for [PaymentAmount] as the default value, showing how much to expect as a payment. * This actually should be [qryBalanceReport].[Balance] but i thought it would require some additional code to requery the balance report after adding the new [Debt] record.

I believe I have attached the file for your review but not sure if it attached. I was in the middle of editing frmPayment but am having a brain fart and have the incorrect record sorce if it currently.??
Membership-database-construction5-26-15startingFile1.zip

[June7]

Well, debit and debt are different things.

qryPayment won't open and therefore frmPayment doesn't work.

[RLehrbass]

Ok, I have renamed Debt to Debit. I am having problem with finding all the instances of Debt to assure they are all changed in the queries.
Somehow I deleted the [tblPaymentInfo] and have reinstalled that table. so now the Payment form opens and I am working on correcting the associated queries. I'm getting 2 instances of not seeing SumOfDebit in aggragate functions.
then I will address the code you have offered.

[RLehrbass]

Moderator,
I ran into a problem. when opening [frmPayment], error reads" can not add record(s); Join key of tblTransaction not in recordset".
is this a simple fix?

[June7]

I've never seen that message. What code is triggering the error?

I use Rick Fisher's Find and Replace add-in when I have to do global design edits, like change a field name. Cost about $50. Saved my sanity more than once.

[RLehrbass]

Moderator,

I have performed a lot of cleanup. I have not addressed the original question but have gotten all the supporting tbl, frm and qry in order so I can now effectivally address the original question code. I will have to reword my question as I have renamed or replaced many objects to make thing correct. See if this is better!
Membership-database-construction6-15-15EndingFile.zip

Thank You for your Help and time. I do appreciate you!

[June7]

I am looking at frmPaymentMethod which has a subform frmPaymentMethodSubform. Both forms have the same RecordSource. This does not make sense.

Now there is no frmPayment in this db.

Where is the code for this issue?

[RLehrbass]

In my cleanup efforts, i replaced frmpayment with frmTransactionInput to read more correctly, i am actually using tblTransaction as the record source and feel the correct term would be Transaction Inputt Form to allow for the addition of debit as well as payments.
The frmPaymentMethodsubform is used on the the paymentMethod input form on the switchboard, from the view/edit other information. this is used to display all the options already available to the user on the input form.

[June7]

Still doesn't make sense to use a subform bound to the same data as main form. It seems to be emulating a split form. Consider a single form in Continuous view.

Not seeing any code like what I suggested.