Pass data from a form to a new form to a table

[chancer1967]

I have a form/subform that lists outstanding invoices (frmOutstanding) for a selected customer

I want to be able to have a button on that form for writing off a balance.

Pressing the button opens a new form (frmAdjust), populated with invoice number and outstanding balance (both from frmOutstanding), a date field (entered by user, but defaulting to today) and a reason code (from a combo box linked to the reason code table)

The user can change the amount, the date and the reason and then press submit to add the data to the Adjustments table.

Thats the theory anyway


I originally tried to get a button next to each record in the subform but got nowhere, so I now have a button on the main form and have conditional formatting in the subform so it is clear which record is selected.

I have successfully created frmAdjust which has all the data I need but have the following problems:

- I am unable to edit the amount as it is bound to frmCustomerOutstanding
- I don't know how to make it create a record in the Adjustments table when a submit button is pressed. VBA?
- I want to be able to select a different record in frmCustomerOutstanding and press the adjust button again and it refresh the data in frmAdjust. The only way I know is for a macro close the form then open it again


thanks in advance!!

 

[WayneRyan]

Chancer,

frmAdjust should be brought up when you hit your button.

frmAdjust should be have form attribute: DataEntry = Yes


You can "move in" fields from frmCustomerOutstanding as needed.
frmAdjust can "move out" fields to frmCustomerOutstanding.
frmAdjust can have a cancel button to abort the transaction.

Your command button should have something like:

DoCmd.OpenForm "frmAdjust"
Forms![frmAdjust]![SomeField] = Me.SomeField

In its OnClose event frmAdjust can have:

Forms![frmCustomerOutstanding]![SomeField] = Me.SomeField

Wayne

 

[chancer1967]

That worked a treat.

What I did not explain properly is that the contents of frmAdjust then need to be added as a new record to the table Adjustments, using a submit button on the form.

Erm... how?