CLOSE AND RE OPEN FORM FROM COMMAND BUTTON 1

[jadams0173]

I think I'm making this harder than it should be. I have form with a command button on it. When the button is pressed after some other events take place I want to close the form with the command button and the reopen that form.

The reason is there is a dynamic make table that I bind the form to. After the other events run and the records are updated I need to reopen the form so the dynamic maketable will run so I can bind the updated table to the form.

Currently I have in the command button on click

    DoCmd.Close acForm, "FRMMAIN"
    DoCmd.OpenForm "frmmain"

But of course that won't work because the form hasn't complete unloaded and this keeps the table which I need to recreate locked. Any ideas?

 

[ThomasLafferty]

Do this by passing control to a Macro in the Click event of your command button.

Your code will look like this:

Docmd.RunMacro "FRMMAIN_CloseOpen"

Create a Macro and save it as FRMMAIN_CloseOpen.

In the action dropdown of the first line, select Close. In the object type select Form and in Object Name, select the name of your form, presumably FRMMAIN.
In the next action dropdown, select OpenForm. In the Object Name select the name of yoru form.

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[Eupher]

Hi, jadams0173,

I think Tom's suggestion will work. However, your question makes me wonder... Not saying your approach is wrong, just trying to understand what you are doing. At first glance it seems like a long way around. Can you explain a bit more?

Ken S.

 

[jadams0173]

Thomas,

I tried you suggestion but still got the table locked error.
Here is the code I added:

If Me!chMORELEASED = -1 Then
    Screen.MousePointer = 0
    DoCmd.RunMacro "SEND_REPORT"
    DoCmd.OpenReport "RPT_MAIN", acViewNormal
    DoCmd.RunMacro "FRMMAIN_CloseOpen"
End If

The first line of the macro is to close frmMain and the second line is to open frmMain.

Eupher,

I'll be glad to explain more. This is the only form in the DB. On form load I create a temporary table and bind a continuous form to it. The reason I use a temporary is there could be as many as 4 users changing there respective data at the same time and I think I read that an unbound control in a continuous form shows the same data for each record. All of the data comes from the same set of tables that are linked to the DB(I've split the DB). When the UPDATE command button is pushed, I run a series of update and append queries to move the data to different tables, generate a text file and run a batch file to upload the text file to a server que for processing, send some emails and lastly print a report and also open it to be viewed.

This is still all taking place in the same button push....
This part is where my problem is. After all of this happens, I need to close the form and reopen it. This will generate a new temp table for that user but will omit the records that were just edited or updated and will add any new records that have been added to the backend tables which update every 30 minutes 24/7.

I will post code if you want to see it and I am most certainly open to suggestions on how to do this more effeciantly.

 

[Eupher]

Hmm... Why not just unbind the form, recreate the temp table, then re-bind the form on your button click?

Ken S.

 

[jadams0173]

Sounds like a plan to me. Would it be something like...

Forms!frmmain.RecordSource = ""
<remake the tables>
Forms!frmmain.RecordSource = TempTable

 

[Eupher]

Yes, that's exactly what I was thinking. Don't forget the quotes around the name of your temp table.

Ken S.

 

[jadams0173]

I will give it a go here shortly and let you know how it turned out. BTW TempTable is a string variable. Guess I should get back in the habit of strTempTable so it would be more clear. ;-)

 

[jadams0173]

Ken,

I worked GREAT! Thanks for the tip!