Update table while form is open?

[Recordsetclown]

I have a form whose recordsource is a table indexed on a numeric field where duplicates are possible. All records matching a given number are therefore grouped together in the table.

My form has code that loops through a clone of the form recordset to find matching numbers and then writes the matches to another table. To speed up the search, the code stops looking when the last matching record is passed; however, if a new record is added before a search is done on the same number, the new record is the last in the table and therefore not included. Is there a way to force Access to insert the new record into its proper position in the table immediately after a new record is created?

Thanks,

Jeff

 

[Paddyo]

Hi

Maybe this will work.

Why not base the form on a query, which is based on the table.

In the query set the sort options as required.

When you have saved the new record you may need to requery the form .

good luck
Paul

 

[thornmastr]

Jeff,

A couple of points here. Since you are most interested in a record set based on duplicate key values, the collated sequence of your table is not related to this index. If this was youre your primary index and not allowing duplicates, then yes, I would agree with you; for all intents you do have the record order pretty much under control; but with your index you do not. What you do have is an ordered page of index values and their appropriate addresses. Nothing of any direct value for your quest…….but……From what I see your primary concern is, has your tables been updated in such a way that your group totals are now no longer accurate?

You might want to consider not basing the underlying record source directly on the table but based on a query that is based on your duplicate key structure.When your form first opens, you might want to get the current record count which will tell you how many records you are dealing with at the start of your process.

When you have reached the end of your processing, requery your record source and get the record count. If that number is not the same as the previous number, you know your table has been updated, and you will probably want to repeat your processing cycle again.

I do like that name.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Recordsetclown]

Thanks guys, very much, for your input. I've been using this rather backhanded process in order to preserve the main form bookmarks so I could go back to the correct record when the user selected it from a pop up listing - since there is not currently anything unique to a single record. Maybe I should just add an autoincrement field in the table as a unique record identifier and go about this more conventionally? Thanks again for your thoughts - and Robert for the name comment.

Jeff