Sub form query on main form

[rosscortb]

Hello,



I have created a 1 to many database. One employee table with personal details and then tables reflecting costs like, flights, accommodation, visa costs etc.

I then created a query(total costs) to add up the total of those tables and then a subform that I inserted in the main form that just display the total over cost.

When I click, new record, everything goes blank which is perfect for me to input a new record but the total of the 1st record( i only have one record at the moment) remains the same. How do I over come that please? see attachment if need be.

Thanks

Ross

[John_G]

The value stays the same because that control is not bound to a table field.

You need a bit of code in the form's On Current event to check for a new record, and set the value to zero:


if Me.NewRecord then Me![yourcontrolname] = 0.0

John

[rosscortb]

Thanks John.

Not my stregth, do you mean the main form or the subform?

Cheers

Ross

[John_G]

Sorry -

You put this in the On Current event of the main form.

The On Current event fires every time you move to a new record, either through form navigation controls or through VBA code.

In the line of code I showed you, Me.Newrecord will be true whenever you go to a new record - in other words, the "current" record is the new record.

So, whenever the "current" record is new, the text box is set back to 0.

- Open the main form in design view
- Display the properties sheet
- Click the "Event" tab
- for the On Current event, select "[Even Procedure] from the dropdown
- click the ... button to open the code window
- enter the line of code I showed you, replacing yourcontrolname with the name of your textbox
- close the code window and save the form

I apologize if you already know most of this!!

John

[rosscortb]

John,

Must be doing something wrong as it doesn't seem to like it. I have put

Private Sub Form_Current()
If Me.NewRecord Then Me.Overall_Cost = 0

End Sub


Thanks

Ross

[John_G]

What's the error message you get? The only thing I can think of is that Overall_Cost is not the name of the control. You could try Me!Overall_Cost = 0 -
Access sometimes gets picky about the difference between Me.Overall_Cost = 0 and Me!Overall_Cost = 0

[rosscortb]

Run-time error '438':

Object doesn't support this property or method

Private Sub Form_Current()
If Me.NewRecord Then Me.Overall_Cost = 0 (from Me to 0 is in yellow)


End Sub

Thanks

Ross

[John_G]

OK - you probably do have to use Me!Overall_Cost = 0, with "!" instead of "."

Is Overall_Cost (with an underscore) the actual name of the control, or is it Overall Cost (with a blank)? If the latter, with a blank, use Me![Overall Cost] = 0

[amrut]

I then created a query(total costs) to add up the total of those tables and then a subform that I inserted in the main form that just display the total over cost.

The control source of Overall Cost is a query (field). How can the value be set in code ?
Further, it is not clear if this is total for all records or the record currently displayed. In any case (except if the forms are not linked Master-Child) I think you need to requery the subform in Main form's Current Event.
Does this make sense ?