how to write VBA for populating specific form values ?

[RLehrbass]

You will find the same application with FrmCommitteeName, frmCommitteePosition, and frmMemberType. If you go to the switchboard, edit/view other Information and open each of the forms, you will see what I am attempting to convey for the user. As a novice, I am not familiar with all my options but this truly gave the visual information I want to convey to the user at a single glance.


On another subject. I am not sure how to get the Balance information to display on the frmMember in the financial area though. I tried all types of combinations of the forms and queries but it just says Name?? all the time.

As I said in the former reply, I haven't started to apply the code you gave me because the DB was so dirty (I am sure you will agree). Now that I did some cleanup (as you see it now) I can now move to working with the code you suggested.

[June7]

Instead of a long form that must be scrolled, consider arranging controls on a Tab control.

Since the Debit and Credit fields are not included in frmMember RecordSource, they are not available for calculation. If you want to pull values from qryTransactionBalance, try DLookup() domain aggregate function in textbox ControlSource:

=DLookup("Balance", "qryTransactionBalance", "MemberID=" & ID)

[RLehrbass]

That works good!
Just so you know, this DB is a highly modified version of the "Membership Template".

[June7]

Use formatting in textbox on form and report.

[RLehrbass]

Yea, I figured out to set the Format to currency on the format tab of the property sheet.
Did i mention that the DB started as the "Membership Template"?

[RLehrbass]

I considered using a tabbed form, but decided to give it page buttons instead. This allows you to jump to the section of interest without leaving the actual page. However, it still has the draw back of needing to click the section button to move to the next section while doing input. This is very annoying to me as a user. but changing to another page is annoying too. I want the user to be able to easily scroll through all the info while viewing or jump to point of interest.

[June7]

I guess it is a matter of what is least annoying.

Issues resolved?

[RLehrbass]

I'm Still trying to figure out how to state this one line of code for frmTransactionInput. (from the original question)

'Dim dblSumOfDebit As Double
'Dim dblTotalDues As Double
'Dim dblBalance As Double
'dblSumOfDebit = DLookup("SumOfDebt", "qryTransactionReport")
'dblTotalDues = DLookup("TotalDues"' "[Forms]![frmMember].[totalDues])
'dblBalance = DLookup("Balance", "qryTransactionReport")
'If dblSumOfDebit = 0
'Then make a new record [tblTransaction].[Debit] = dblTotalDues, (with todays date and $0. on credit.)
'And set field Me!Credit = dblTotalDues
'Else Me!Credit = dblBalance
End If

[June7]

Are you following my example from post 6?

If focus is on a new record row (new record will be initiated as soon as a field is populated) and the fields are part of the form's RecordSource:

If dblSumOfDebit = 0 Then
Me!Debit = dblTotalDues
Me!Datefield = Date()
Me!Credit = dblTotalDues
Else
Me!Credit = dblBalance
End If

Now uncomment each line so code will execute.

[RLehrbass]

I believe this should be my last question on this issue. I am going one step further in my code, i want to add a Debit = to [TotalDues] if renewal Date is => then today.
Dim CurrentRenewalDate As Date
Dim dblSumOfDebit As Double
Dim dblTotalDues As Double
Dim dblBalance As Double
CurrentRenewalDate = DLookup("RenewalDate", Forms.Member.RenewalDate)
dblSumOfDebit = DLookup("SumOfDebt", "qryTransactionReport")
dblTotalDues = DLookup("TotalDues", "Forms!frmMember!totalDues")
dblBalance = DLookup("Balance", "qryTransactionReport")

If Date()=> CurrentRenewalDate Then
Me!Debit = dblTotalDues
Me!Datefield = Date
'Here I want to accept the value in debit as a separate transaction and refresh form to accept next transaction of credit
RenewalDate = DateAdd,("yyyy,1)
qry.TransactionReport.Requery
If dblSumOfDebit = 0 Then
Me!Credit = dblTotalDues
Me!Datefield = Date

Else
Me!Credit = dblBalance
End If

[RLehrbass]

I may have written the lower part of the question incorrectly. looks like some code is missing...

[RLehrbass]

I believe this is what I meant to ask you!
Dim CurrentRenewalDate As Date
Dim dblSumOfDebit As Double
Dim dblTotalDues As Double
Dim dblBalance As Double
CurrentRenewalDate = DLookup("RenewalDate", Forms.Member.RenewalDate)
dblSumOfDebit = DLookup("SumOfDebt", "qryTransactionReport")
dblTotalDues = DLookup("TotalDues", "Forms!frmMember!totalDues")
dblBalance = DLookup("Balance", "qryTransactionReport")

If Date()=> CurrentRenewalDate Then
Me!Debit = dblTotalDues
Me!Datefield = Date
Me!PaymentMethodID = 5
'Here I want to accept the value in debit as a seperate transaction and refresh form to accept next transaction of credit
forms.frmMember.RenewalDate = DateAdd,("yyyy,1)
qry.TransactionReport.Requery
End If
If dblSumOfDebit = 0 Then
Me!Debit = dblTotalDues
Me!Credit = dblTotalDues
Me!Datefield = Date

Else
Me!Credit = dblBalance
End If

[June7]

You are use DateAdd() function incorrectly. Suggest you do some research on the syntax.

Why are you trying to 'requery' a query? You should requery the form's RecordSource with simply: Me.Requery, however, not sure why even need to do that. Maybe need to move to new record row?

[RLehrbass]

I have looked at the date function a lot on google searches but nothing applies it the way I need. I simply need to increase the date in a field by one year. As for the requeary, i need fresh data after updating the debit or credit fields, Is this not necessary to do?

[RLehrbass]

Will this line of code work?
CurrentRenewalDate = DateAdd(yyyy, 1, "RenewalDate", "Forms!frmMember!RenewalDate")