Access VBA- refer to table field.

[farmerscott]

Hi Everybody,

I have the following code and it works.

Private Sub Amount_AfterUpdate()
Me.Recalc

If Me.Controls![Income/Expense] = "Income" Then

Me.Controls![Credit] = Me.Controls![Amount]

Else

Me.Controls![Debit] = Me.Controls![Amount]

End If

End Sub

I would like to adjust it so that I don't have 2 invisible controls on my form.

Details-

1. I have a subform called "frmCashdissection" (many side) and a main form called "frmCashtransaction" (one side).
2. I have a table called "tblCashdissection" with two fields called "Credit" and "Debit"
3. When "income/expenses" is chosen from a combo box ("frmCashdissection") and an "Amount" is entered I would like the result to go direct to the table and not via the relevant control on the form.

So my question is- How to I refer to a table and field within VBA?


Further any general comments in taking this further, to mimic a bank statement with a running balance. I can get the debits and credits into their respective fields. But can I use an append query/DSum to get the running balance and put it into 'tblCashdissection".

thanks,

FarmerScott

 

[Legacy 456155]

If "Amount" is indeed a field in your recordsource, all you need is:
Me.Controls![Credit] = Amount
Most Access devs rename their controls to provide a clear distinction between fields and the controls sourced to each field.
So if a textbox sources to a field name "Amount", it will typically be renamed to txtAmount.
In any case, Access will implicitly determine which you are referring to by syntax.

 

[welshgasman]

If the transaction is a debit, I multiply the amount by -1. I only use one field for amount.

 

[Legacy 456155]

I'm sorry, but I don't think that I am understanding you here.

 

[farmerscott]

If "Amount" is indeed a field in your recordsource, all you need is:
Me.Controls![Credit] = Amount
Most Access devs rename their controls to provide a clear distinction between fields and the controls sourced to each field.
So if a textbox sources to a field name "Amount", it will typically be renamed to txtAmount.
In any case, Access will implicitly determine which you are referring to by syntax.

Hi Dataluver,

Yes "Amount" is a field on my form and in the source table (where the data is being stored).

What i want to do is 'bypass' the need to have extra controls (thou invisible) on my form. I was hoping I could do something like-

If Me.Controls![Income/Expense] = "Income" Then

Table("Cashdissection").Field name ("Credit") = Me.Controls![Amount]

Else

Table("Cashdissection").Field name ("Debit") = Me.Controls![Amount]

End If

So what i need is the VBA equivalent to Table("Cashdissection").Field name ("Debit")
thanks,

Farmerscott

 

[farmerscott]

If the transaction is a debit, I multiply the amount by -1. I only use one field for amount.
[/QUOTE

Thanks Welshgasman- will do.

 

[JonXL]

Does this work?

If Me.Controls("Income/Expense").Value = "Income" Then

    Me.Credit = Me.Controls("Amount").Value

Else

    Me.Debit = Me.Controls("Amount").Value

End If

 

[farmerscott]

Hi JonXL,

Your code looks to be working.

The only downside is that the values are not written back to the table until after the next record on the main form is active. This is not a big issue.

thanks for the help,

FarmerScott

 

[JonXL]

The only downside is that the values are not written back to the table until after the next record on the main form is active. This is not a big issue.

I think that is usual behavior - until you commit the record, it is in 'edit' mode and can be backed out of.

If you want to make sure the record is committed after updating this field, adding If Me.Dirty Then Me.Dirty = False will force the record to save after setting the control's value.

If Me.Controls("Income/Expense").Value = "Income" Then

    Me.Credit = Me.Controls("Amount").Value

Else

    Me.Debit = Me.Controls("Amount").Value

End If

If Me.Dirty Then Me.Dirty = False