Problem updateing form after insert

[johndoomed]

Hi again. One other problem I got in my database is updating forms after inserting new data.

Example: I've made a system for sending invoices. Connected to the invoice is serveral order-lines which is shown in a subform (I use access in norwegian so I'm not sure about that name).

I've made another form for inserting these order-lines. The problem occurs when I close the insert-form, with macro. I cant get the invoice form to update. I have to click on a update-buttom (Macro: update)...

Can this be fixed as well?

 

[llkhoutx]

On your subform on close event add the following

If me.dirty = true then docmd.RunCommand acCmdSaveRecord

 

[johndoomed]

Thanks for the answer!

Hmm, this did not work. Do you need more info?

 

[llkhoutx]

Part of your orignal post says

The problem occurs when I close the insert-form, with macro.

I suppose by insert-form you mean a mere bound form by which data is displayed/added/modified.

How does your macro get executed? That may be the source of your problem.

The macro may need additional instructions before the close command, i.e. setting focus to the for, then a save record command.

I'm not certain how to setfocus using a macro. I use them sparingly, I think VBA is much better.



Do you code in VBA or macros?

 

[johndoomed]

I will try to explain the db and problem better. I have uploaded/attached a picture with the relations. I have also tried to illustrate the forms in question and their relations with the database.

The MAIN FORM displays the invoice details and SUB FORM displays the lines connected to that specific invoice. This works perfect.

My problem is that when I add a line with the ADD FORM, and close that form (With a macro, se below), the new line will not show in the SUBFORM. I have to run a separate macro (Executed with a button), that macro only contains the line “Update”.

I suspect the problem is my “close macro” in which is executed by a button in the ADD FORM. This macro contains the following:

4 different “assign values” commands (This may be an incorrect translation of the command). Example:

Element: [Forms]![Faktura_ny Post]![Sum] 
Expression: [Forms]![Faktura_ny Post]![Sum2]

The macro also contains the ”close” command which obviously closes the ADD FORM. I’ve tried to add the command “update” after “close”, but this has no effect.

Is my problem that the macro does not update the right form?

Do you code in VBA or macros?

I do a bit of both, but I use macroes because I'm not that code with VBA.

 

[llkhoutx]

Apparently, your insert-form you really is a subform, not a form.

You say:

I’ve tried to add the command “update” after “close”...

It's too late to save a subform record after the close, the record's already gone. Think about it. Save the record immediately prior to your closing the form. I presume that you're closing the form which closes the subform and that you're not closing the subform directly. Before closing the form you have to setfocus on the subform to save it's dirty record. If the record isn't dirty, it hasn't been changed.

If you can't save a record with a macro (I don't use macros), have the macro call (execute) a function with the following code in it:

Public Function SaveSubFormRecord()

If Forms!YourMainFormName!sfrmYoursubformName. form.dirty = true then 
  
  'focus has to be on the subform for the "save" inside this "if" to work
  Forms!YourMainFormName!sfrmYoursubformName.form.setfocus
  docmd.RunCommand acCmdSaveRecord

end if

end function

Save the function in a Class Module.

 

[johndoomed]

Thanks for yet another good reply!

My insert form is a seperate form which opens in its own window, and is by my understanding not a sub-form - but we might have a language problem here?

The problem is not saving the posts, the problem is displaying the posts in my subfom after closing the insert-form.

BUT, I will try your function.

 

[llkhoutx]

Requery the subform on the insert -form (a separate form), on the OnClose event.

I think that I understand that you have a form with subform open (form A), you add data via another form (form B), close "form B" and want the data to appear in the subform on "form A."

Like I just said, requery the Form A subform, when form B closes.

I assume that the data is making it way into the underlying table, it's just not being displayed in the "form A subform."

I think that we've got it now.

 

[johndoomed]

Now we're cooking

I tried this code:

Private Sub Form_Close()

    Forms!Faktura_ny.Faktura_nyDelskjemaPoster.Form.Requery

End Sub

But I get this error-message:

Run-time error '2465':

Application-defined or object-defined error

Is there something wrong with the code or the form-names?

Thanks for all your great help!

 

[tkoh78]

Create an macro with "save" action.
Assign this macro to OnCurrent event of your invoice form or whatever you want your record to be updated. Hope this helps.

 

[llkhoutx]

Forms!Faktura_ny.Faktura_nyDelskjemaPoster.Form.Requery

should be

Forms!Faktura_ny[B]![/B]Faktura_nyDelskjemaPoster.Form.Requery

There's a difference between a bang(!) and a dot(.).

"Dots" are used for properties, etc.

"Bangs" are used for controls. A subform is a control on a form.

 

[johndoomed]

YES! Thats it! THANKS!