Recordset slow to refresh

[spinalwiz]

Im using an SQL 'INSERT INTO' command to add a record to a table. The form responsible for this is bound to this table.

The problem is that the form doesnt 'recognise' the new record until I do 'Form.Recordset.Requery'

Straight after the requery Im trying to move to the new record via a Combobox:

MyRS.MoveLast
Me![ComboBuilding] = MyRS.Fields("Building").Value

This however moves me to the 2nd last record, and Im guessing this is because the recordset takes so long to requery (the total number of records indicator in the navigation buttons takes a litte while before it updates to accmodate for the new record).

Any ideas?

Thanks

 

[scriverb]

If the form is bound to the table, why are you using additional SQL to insert the current entry on the form into the table. Why not just allow ADDs and let ACCESS add the record with either moving to another record or execute a command button VBA with:

DoCmd.RunCommand acCmdSaveRecord

When this happens your form will be pointing to the last record just entered.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[spinalwiz]

I'm not sure how to answer this properly but I think the short answer is that the user navigates the records through 2 comboboxes. One of these comboboxes displays a list of buildings in use for a particular event. And if they want to add a new building (and thus a new record) they click an 'Add New Buiding' button which allows them to select a bulding from a dialog box listing all the available buildings.

Having selected a building Im then adding a new record (using the SQL INSTERT INTO).

So the reason I cant do what you suggested is because the ComboBox only lists the Buildings currently in use and therefore the user wouldnt be able to add new bulidings?

Oh, and the combobox isnt filled directly from a table but from a query that joins the table of buildings with the table of bookings (this table).

Im not sure if this is clear. Please let me know . . .

Thanks