Delete a record, but the table hasn't updated yet?

[blakerrr]

Hi All,
I have a strange situation that I can't figure out. The task is quite simple, delete a record from a table. Here is my situation:

I have a form called Order Create which cycles through the parts in my database, run off of a table called Master BOM. In this form there is a subform called Order Data (not linked to the main form) which has a field on it called OrderID. This subform is run off of a table called Order Data, which has OrderID as the primary key (autonumbered). In this subform is a nested subform called Order Details which shows all of the parts contained in that order. The idea is to sort through the parts in the main form and pick some to add to the order. This all works fine, just thought I would paint the picture.

Here is the problem. When I open up the Order Create form, I have it create a new record in the Order Data table (ie. a new order number via having the Data Entry property set to yes for the Order Data subform). Now, I have a button on the main form called Submit, which closes the main form, then checks to see if the order is empty (this works fine so far), and then if it is empty, it is supposed to delete the new record in the Order Data table. The problem is here, it doesn't delete the record. I have a module to accomplish the task, and the code to find and delete the record WORKS, I know it does because I have tried it in the immediate window it I know it works. For some reason though, the Order Data table hasn't added the new record yet, so my code isn't finding it. Is there some sort of Refresh command I can use? I even had the Order Data table open, then opened the Order Create form, which showed the new OrderID number in the Order Data subform, but yet the new record in the Order Data table had not appeared until I closed the main form, closed the Order Data table, and then re-opened the Order Data table.

What is up with this? I thought the task would be easy, and I certainly wasn't expected to run into this. Help me please!!!

[DThreadgill]

Hi All,
I have a strange situation that I can't figure out. The task is quite simple, delete a record from a table. Here is my situation:

I have a form called Order Create which cycles through the parts in my database, run off of a table called Master BOM. In this form there is a subform called Order Data (not linked to the main form) which has a field on it called OrderID. This subform is run off of a table called Order Data, which has OrderID as the primary key (autonumbered). In this subform is a nested subform called Order Details which shows all of the parts contained in that order. The idea is to sort through the parts in the main form and pick some to add to the order. This all works fine, just thought I would paint the picture.

Here is the problem. When I open up the Order Create form, I have it create a new record in the Order Data table (ie. a new order number via having the Data Entry property set to yes for the Order Data subform). Now, I have a button on the main form called Submit, which closes the main form, then checks to see if the order is empty (this works fine so far), and then if it is empty, it is supposed to delete the new record in the Order Data table. The problem is here, it doesn't delete the record. I have a module to accomplish the task, and the code to find and delete the record WORKS, I know it does because I have tried it in the immediate window it I know it works. For some reason though, the Order Data table hasn't added the new record yet, so my code isn't finding it. Is there some sort of Refresh command I can use? I even had the Order Data table open, then opened the Order Create form, which showed the new OrderID number in the Order Data subform, but yet the new record in the Order Data table had not appeared until I closed the main form, closed the Order Data table, and then re-opened the Order Data table.

What is up with this? I thought the task would be easy, and I certainly wasn't expected to run into this. Help me please!!!

After adding the new record in your code, add Me.Requery to display new records, or remove deleted records. Be sure to do it after you delete a record as well.

[blakerrr]

After adding the new record in your code, add Me.Requery to display new records, or remove deleted records. Be sure to do it after you delete a record as well.

Ok, I think this will work, the only thing is that my Order Data subform goes to the first record after the requery, and I need it to stick on the last. Any suggestions?

[blakerrr]

Ok, I think this will work, the only thing is that my Order Data subform goes to the first record after the requery, and I need it to stick on the last. Any suggestions?

Never mind! It works just fine now. Thanks :)