Adding a new record in a subform using Code

[ArkRoyal]

I am the Treasurer for my local Church. I have 2 forms: CashBook, and
BankDetails. The BankDetails form has a SubForm "Cheque(Check)Details".

When I receive cheques(checks) from the congregation, I enter the quantity
and total Value in the cash book. I have used code in the afterupdate event
in the Quantity field to open the BankDetails Form so that I can record the
details of the Cheque. ie bank name, value, cheque serial number and date
received in the ChequeDetails subform.

I use the docmd.gotorecord method to select the name of the Bank. This is
within a select - next loop so that I can enter details of all Cheques
without going out of the BankDetails form

My problem now is what code do I use to jump to a new record in the subform,
so that the date received is entered automatically.

Thank you and Regards

 

[John W. Vinson]

I am the Treasurer for my local Church. I have 2 forms: CashBook, and
BankDetails. The BankDetails form has a SubForm "Cheque(Check)Details".

When I receive cheques(checks) from the congregation, I enter the quantity
and total Value in the cash book. I have used code in the afterupdate event
in the Quantity field to open the BankDetails Form so that I can record the
details of the Cheque. ie bank name, value, cheque serial number and date
received in the ChequeDetails subform.

I use the docmd.gotorecord method to select the name of the Bank. This is
within a select - next loop so that I can enter details of all Cheques
without going out of the BankDetails form

My problem now is what code do I use to jump to a new record in the subform,
so that the date received is entered automatically.

Thank you and Regards

I don't understand why you have two tables. A donation can come in the form of
a cheque or cash (or other means). You might want to consider cheque specific
details as just optional fields in a single donation table.

Wouldn't it also make more sense to just have a small table of banks, and use
a combo box to select the BankID on the form? You certainly should NOT
repetitively enter multiple details about the bank.

John W. Vinson [MVP]

 

[ArkRoyal]

I enter both cash and cheque values in the cash book. But as I stated, I only
enter the qty of cheques received, with the sum total of the cheques in the
cash book. I record the details of each cheque in the Bank/ChequeDetails
form/subform. I have a small table with just the bank name. This is linked
by a relationship to the Chequedetails table. The code I use to open the
BankDetails form when I've entered the qty of cheques is as follows:


Private Sub QtyChq_AfterUpdate()
Dim AddCheque As String, i As Long, CapID As Long
Dim CapQtyChq As Integer, CapDate As Date
Dim CapRID As Long
CapQtyChq = Forms![CashBook]![QtyChq]
CapDate = Forms![Cashbook]![SunDate]

DoCmd.OpenForm "BankDetails", acNormal, , , acFormEdit

For i = 1 To CapQtyChq
DoCmd.GoToRecord acDataForm, "BankDetails", acGoTo, InputBox("Enter
Bank ID")
**********
Next i
DoCmd.Close acForm, "BankDetails", acSaveYes

I need the code to enter CapDate to the Date field in a new record on the
subform,
I intend putting this in place of the ********** above. I will then enter
the rest of the rest of the cheque details in the appropriate fields.

I also have a combobox on the Bank details form so that I can jump to a
particular bank if I need to track a cheque in the future.