VBA Code for Form Field - Microsoft Access Forum


	Back to Access Forum Comments List

VBA Code for Form Field

William Kennedy

4 years ago

I am developing a Form that enters some Data and brings data into it from other queries.
VBA Code is attached to the form field [ChkBalField]

Private Sub ChkBalField_Click()
     select Balance
    from 3qry_ChkAll
    where ChkDate = (Select Max(ChkDate) From [3qry_ChkAll])
End Sub

I would like to use this for other queries and fields into the form. Is their a way to make it usable in other form fields by passing parameters into it.

	William Kennedy OP @Reply 4 years ago

	William Kennedy OP @Reply 4 years ago

	William Kennedy OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
Create a Public Sub Routine in a Global Module.

	William Kennedy OP @Reply 4 years ago
Thanks Kevin, I'm just starting with VBA. Do not know how to do that.

William Kennedy OP @Reply

4 years ago

I fixed the previous issue but now. I use four DLookup calculations within a query.
example:
ChkBal: Format(DLookUp("[Balance]","3qry_ChkBalance"),"Currency")
They all work well.
But when I try to add the values together to get a total. The calculated value is a a number because I can The field becomes a String value instead of a currency. What did I do wrong? I've tried both these ways.

Total: [ChkBal]+[SavBal]+[LTDBal]+[CharBal]

Total: (Format(DLookUp("[Balance]","3qry_ChkBalance"),"Currency"))+( Format(DLookUp("[Balance]","3qry_SavingBalance"),"Currency"))+( Format(DLookUp("[Balance]","3qry_LTDBalance"),"Currency"))+( Format(DLookUp("[Balance]","3qry_CharityBalance"),"Currency"))

	Adam Schwanz @Reply 4 years ago
Have you tried CCur? CCur changes the data type, Format changes the appearance. CCur(DLookUp("[Balance]","3qry_ChkBalance"))+...

	William Kennedy OP @Reply 4 years ago
Thanks. I will try that.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen