Updating a table from a form

[Grunners]

Hi all,

There is no doubt a very simple answer to this problem. This having been said I haven't a clue what it is!

I have a form that shows invoice details for clients. Most of the form is populated using a dynaset and this works just fine. All the boxes etc on the form are filled in and the table behind the form is also populated. (obviously the table is populated first and then the results display in the form)

One part of the form cannot be populated in this way and I've had to put an OnCurrent event in to fill in the details, as follows:

//////////////////////////////////
dim a as string

a = Nz(DLookup("[invoice_contact]", "tbl_invoice_source", "[clientref] = forms![frm_invoice1]![clientref]"), " ")

Forms![frm_invoice1]!Textbox = a
//////////////////////////////////

This is a shortened version as there are a few text boxes. It updates the form fine but does not seem to copy records in to the table immediatley. Thus if a query is run on the table it returns null values.

However if i cycle through the records using the navigation buttons the table updates and all is well. Thus my question is how can I do this automatically in code? I've tried the following but still no joy:

/////////////////////////////////
Set dbs = CurrentDb
Set rst = InvoiceTable
rst.MoveFirst
Do
rst.MoveNext
Loop Until rst.EOF
////////////////////////////////

Any ideas would be very much appreciated!

Many thanks
Grunners

 

[ghudson]

You need to save the record before you run the query. Moving from one record to another is saving the current record if it was dirty.

DoCmd.RunCommand acCmdSaveRecord

HTH

 

[Grunners]

using:

Private Sub Form_Load()
Recordset.MoveLast
Recordset.MoveFirst
End Sub

seems to work, however

>>>Moving from one record to another is saving the current record if it was dirty<<<

Something else I had overlooked.

Any how, thank you very much indeed. I'll do it your way!

Cheers
Grunners