Exit db Then Re-Open On The Same Frm

[Groundrush]

Is it possible to close down a db then have it re open on the same form that it was originaly open on?

The reason why I am asking is because my code that I use for sending emails only fires once and the db has to be shut down and started up again before any more can be sent.
At the moment I can only send one email at a time.

I have tried refreshing my main form but that does not work

Any ideas?

Cheers

 

[noccy]

1. close the form
2. Open the form

 

[Groundrush]

1. close the form
2. Open the form

Already tried that.

It only works when the db is shut down completely and re opened again.
I only get one shot at it, you have to start the process each time you send an email.

 

[Groundrush]

Why won't the emails go without closing down Access?

Hi Pat

The email does go out ok without closing down access

The problem is that the user may have quite a few records to create but they have to close the db down and restart it again to email another record.

this is my code........I cant see what I am doing wrong
Private Sub cmdEmailJob_Click()
On Error Resume Next
DoCmd.SendObject acReport, "rptVinciInstructionToEngageSubCon", "RichTextFormat(*.rtf)", EMail, , , SubjectLine, _
"Please see attached document.", False
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSubConOrders SET tblSubConOrders.MailSent = -1 WHERE (((tblSubConOrders.MailSent)=0))"
DoCmd.OpenQuery "qryEmailSent", acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.GoToControl "JobNo"
DoCmd.GoToRecord , "", acNewRec
DoCmd.Close acForm, "frmSubConOrders"
DoCmd.OpenForm "frmSubConOrders", acNormal, "", "", , acNormal
Resume Next
End Sub

Thanks

 

[Groundrush]

- why did you set it up this way? It would be much more convenient if the user didn't have to exit the db to continue.

That is what I am trying to do.

I don't understand how you want this form to work. Do you want the form to close after the emails are sent?

No I dont want it to close, I only did that to try and reset the form so the email can work again on a new record without having to shut down.
the command button also updates a table to indicate that an email has been sent once it has been pressed

But what is the point of moving focus to a different control or going to an empty record?

I did that to prevent people from typing over the current record, thinking that it is saved.
it was supposed to go to new record after the email has been sent.

If the form is running a query to update multiple records, it doesn't make any sense that it be bound.

Pat I'm not to sure what you mean by that.

When you have a bound form, you should not be running queries behind the scenes to update records of the current recordset. You can easily run into sharing conflicts because you're fighting with yourself over control of the current record.

I don't know of a better solution but I think now that maybe this is where the problem is.

what can you suggest I do?

Thanks for your time

 

[Groundrush]

I have removed all the code but left this bit in:

Private Sub cmdEmailJob_Click()
DoCmd.SendObject acReport, "rptVinciInstructionToEngageSubCon", "RichTextFormat(*.rtf)", EMail, , , SubjectLine, _
"Please see attached document.", False
End Sub


And I still have the same problem

the email function can only be used once unless you close the db down re-open again

 

[Groundrush]

Thanks Pat

I'll try that first thing at work tomorrow


Wish me luck

 

[Groundrush]

It seems to work ok when I remove this part of the code
EMail, , , SubjectLine, _
"Please see attached document.", False





I can live with that....