DoCmd.OpenForm Modality Problem

G

Guest

The The form called by DoCmd.OpenForm should stay focused until the user
closes that form via the X button after entering all proper Utility Names.
However, it immediately loses focus and the MsgBox pronmpt appears on top of
the open form. The MsgBox prompt has focus.

The form's Modal is "Yes". Allow Edits is "Yes"

Here is the entire procedure:

Private Sub CmdUpdUtilFld_Click()
' Create adodb recordset, connection and command here
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

' Try the following adodb connection
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
' Select Distinct Utility Field to create table Old-NewUtil
containing fields OldUtility and NewUtility
.CommandText = "SELECT DISTINCT [Utility] AS OldUtility, Space(60)
AS NewUtility INTO [Old-NewUtil] FROM [QA Follow-Up]"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

'--- Here is the problem source code
'--- This DataSheet form has two columns. 1. A list of Utility Names, 2. An
empty
'--- field for entering the proper Utility Name. These are the fields from
'--- "Old-NewUtil" created in the above SQL.
'Open "Input New Utility Names" form in datasheet view and Edit mode in
order to input proper values into NewUtility field.
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit

' Prompt to Update QA Follow-Up
Dim Prompt As String, Title As String, Response As Variant
Title = "Preparing to Update QA Follow-Up"
Prompt = "Do you want to Update the QA Follow-Up.Utility Field?"
'--- This MsgBox appears immediately on top of the DataSheet form. The
MsgBox has focus. It should not happen until the user closes the DataSheet
form.
Response = MsgBox(Prompt, vbInformation + vbYesNo, Title)
If Response = vbNo Then
Exit Sub
End If

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
'Update QA Follow-Up.Utility Field with new value
.CommandText = "UPDATE [QA Follow-Up] INNER JOIN [Old-NewUtil] ON
[QA Follow-Up].[Utility]=[Old-NewUtil].[oldUtility] SET [QA
Follow-Up].Utility = [Old-NewUtil].[NEWUtility];"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

End Sub
 
G

Gijs Beukenoot

GaryZ formuleerde de vraag :
The The form called by DoCmd.OpenForm should stay focused until the user
closes that form via the X button after entering all proper Utility Names.
However, it immediately loses focus and the MsgBox pronmpt appears on top of
the open form. The MsgBox prompt has focus.

The form's Modal is "Yes". Allow Edits is "Yes"

Here is the entire procedure:

Private Sub CmdUpdUtilFld_Click()
' Create adodb recordset, connection and command here
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

' Try the following adodb connection
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
' Select Distinct Utility Field to create table Old-NewUtil
containing fields OldUtility and NewUtility
.CommandText = "SELECT DISTINCT [Utility] AS OldUtility, Space(60)
AS NewUtility INTO [Old-NewUtil] FROM [QA Follow-Up]"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

'--- Here is the problem source code
'--- This DataSheet form has two columns. 1. A list of Utility Names, 2. An
empty
'--- field for entering the proper Utility Name. These are the fields from
'--- "Old-NewUtil" created in the above SQL.
'Open "Input New Utility Names" form in datasheet view and Edit mode in
order to input proper values into NewUtility field.
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit

' Prompt to Update QA Follow-Up
Dim Prompt As String, Title As String, Response As Variant
Title = "Preparing to Update QA Follow-Up"
Prompt = "Do you want to Update the QA Follow-Up.Utility Field?"
'--- This MsgBox appears immediately on top of the DataSheet form. The
MsgBox has focus. It should not happen until the user closes the DataSheet
form.
Response = MsgBox(Prompt, vbInformation + vbYesNo, Title)
If Response = vbNo Then
Exit Sub
End If

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
'Update QA Follow-Up.Utility Field with new value
.CommandText = "UPDATE [QA Follow-Up] INNER JOIN [Old-NewUtil] ON
[QA Follow-Up].[Utility]=[Old-NewUtil].[oldUtility] SET [QA
Follow-Up].Utility = [Old-NewUtil].[NEWUtility];"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

End Sub
Click to expand...

Change
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit
to
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog
 
G

Guest

Thanks,

I added the acDialog as you said. No change. The form still appears with the
MsgBox immediately on top. Could it be something in the form itself? Should I
post this in the Access.Forms group ?

Gary Z.

Gijs Beukenoot said:
GaryZ formuleerde de vraag :
The The form called by DoCmd.OpenForm should stay focused until the user
closes that form via the X button after entering all proper Utility Names.
However, it immediately loses focus and the MsgBox pronmpt appears on top of
the open form. The MsgBox prompt has focus.

The form's Modal is "Yes". Allow Edits is "Yes"

Here is the entire procedure:

Private Sub CmdUpdUtilFld_Click()
' Create adodb recordset, connection and command here
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

' Try the following adodb connection
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
' Select Distinct Utility Field to create table Old-NewUtil
containing fields OldUtility and NewUtility
.CommandText = "SELECT DISTINCT [Utility] AS OldUtility, Space(60)
AS NewUtility INTO [Old-NewUtil] FROM [QA Follow-Up]"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

'--- Here is the problem source code
'--- This DataSheet form has two columns. 1. A list of Utility Names, 2. An
empty
'--- field for entering the proper Utility Name. These are the fields from
'--- "Old-NewUtil" created in the above SQL.
'Open "Input New Utility Names" form in datasheet view and Edit mode in
order to input proper values into NewUtility field.
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit

' Prompt to Update QA Follow-Up
Dim Prompt As String, Title As String, Response As Variant
Title = "Preparing to Update QA Follow-Up"
Prompt = "Do you want to Update the QA Follow-Up.Utility Field?"
'--- This MsgBox appears immediately on top of the DataSheet form. The
MsgBox has focus. It should not happen until the user closes the DataSheet
form.
Response = MsgBox(Prompt, vbInformation + vbYesNo, Title)
If Response = vbNo Then
Exit Sub
End If

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
'Update QA Follow-Up.Utility Field with new value
.CommandText = "UPDATE [QA Follow-Up] INNER JOIN [Old-NewUtil] ON
[QA Follow-Up].[Utility]=[Old-NewUtil].[oldUtility] SET [QA
Follow-Up].Utility = [Old-NewUtil].[NEWUtility];"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

End Sub
Click to expand...

Change
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit
to
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Click to expand...
 
D

Dirk Goldgar

GaryZ said:
Thanks,

I added the acDialog as you said. No change. The form still appears
with the MsgBox immediately on top. Could it be something in the form
itself? Should I post this in the Access.Forms group ?
Click to expand...

Did you add the acDialog argument on the same line as the rest of the
DoCmd.OpenForm statement? Although it was wrapped to this:
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog
Click to expand...

by the newsreader, it should all be on one line in your code.
 
G

Gijs Beukenoot

GaryZ heeft uiteengezet op 29-11-2004 :
Thanks,

I added the acDialog as you said. No change. The form still appears with the
MsgBox immediately on top. Could it be something in the form itself? Should I
post this in the Access.Forms group ?

Gary Z.

Gijs Beukenoot said:
GaryZ formuleerde de vraag :
The The form called by DoCmd.OpenForm should stay focused until the user
closes that form via the X button after entering all proper Utility Names.
However, it immediately loses focus and the MsgBox pronmpt appears on top
of the open form. The MsgBox prompt has focus.

The form's Modal is "Yes". Allow Edits is "Yes"

Here is the entire procedure:

Private Sub CmdUpdUtilFld_Click()
' Create adodb recordset, connection and command here
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

' Try the following adodb connection
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
' Select Distinct Utility Field to create table Old-NewUtil
containing fields OldUtility and NewUtility
.CommandText = "SELECT DISTINCT [Utility] AS OldUtility, Space(60)
AS NewUtility INTO [Old-NewUtil] FROM [QA Follow-Up]"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

'--- Here is the problem source code
'--- This DataSheet form has two columns. 1. A list of Utility Names, 2. An
empty
'--- field for entering the proper Utility Name. These are the fields from
'--- "Old-NewUtil" created in the above SQL.
'Open "Input New Utility Names" form in datasheet view and Edit mode in
order to input proper values into NewUtility field.
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit

' Prompt to Update QA Follow-Up
Dim Prompt As String, Title As String, Response As Variant
Title = "Preparing to Update QA Follow-Up"
Prompt = "Do you want to Update the QA Follow-Up.Utility Field?"
'--- This MsgBox appears immediately on top of the DataSheet form. The
MsgBox has focus. It should not happen until the user closes the DataSheet
form.
Response = MsgBox(Prompt, vbInformation + vbYesNo, Title)
If Response = vbNo Then
Exit Sub
End If

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
'Update QA Follow-Up.Utility Field with new value
.CommandText = "UPDATE [QA Follow-Up] INNER JOIN [Old-NewUtil] ON
[QA Follow-Up].[Utility]=[Old-NewUtil].[oldUtility] SET [QA
Follow-Up].Utility = [Old-NewUtil].[NEWUtility];"
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing

End Sub
Click to expand...

Change
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit
to
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Click to expand...
Click to expand...

Strange... This is a quote from the helpfile: "Microsoft Access
suspends code execution until the dialog form is closed". What version
of Access are you using? I think in previous versions it was A_DIALOG.

Another thing that might cause this is an event that fires when you
open the form "Input New Utility Names", You could test that by adding
a msgbox immediately below the line where you open the form:

DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog
MsgBox "We're here now"
 
G

Guest

Dirk Goldgar said:
Did you add the acDialog argument on the same line as the rest of the
DoCmd.OpenForm statement? Although it was wrapped to this:


by the newsreader, it should all be on one line in your code.
Click to expand...

Yes it is on the same line.
 
G

Guest

Gijs Beukenoot said:
Strange... This is a quote from the helpfile: "Microsoft Access
suspends code execution until the dialog form is closed". What version
of Access are you using? I think in previous versions it was A_DIALOG.
Click to expand...

I'm using Access 2000. In fact, when I typed the comma after acFormEdit, the
popup showed acDialog as one of the options, and I chose that.
Another thing that might cause this is an event that fires when you
open the form "Input New Utility Names", You could test that by adding
a msgbox immediately below the line where you open the form:

DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog
MsgBox "We're here now"
Click to expand...

I'll give that a try and get back to you.
 
G

Guest

Gijs Beukenoot said:
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit,
acDialog
MsgBox "We're here now"
Click to expand...

Just tried this and as expected, it went through the DoCmd and put the
MsgBox("We're here now") on top. However, there is no code in any of the
Form level Events as seen in the Form Properties.
 
R

Rick Brandt

GaryZ said:
Just tried this and as expected, it went through the DoCmd and put the
MsgBox("We're here now") on top. However, there is no code in any of the
Form level Events as seen in the Form Properties.
Click to expand...

A datasheet form cannot be modal. Why? I have no idea.
 
D

Dirk Goldgar

Rick Brandt said:
A datasheet form cannot be modal. Why? I have no idea.
Click to expand...

Wow! I didn't know that. How strange! Thanks, Rick.

Gary, it sounds like your best workaround would be to display the form
in Continuous Forms view, formatted if necessary to look like a
datasheet. If that absolutely won't do, you can open the form in
datasheet view and put a wait loop in the code following, to wait until
the form is no longer open. If you do that latter -- which I don't
really recommend -- you must be sure to put a DoEvents statement inside
the loop, so that Access can respond to the user's interactions with the
form.
 
G

Guest

Rick,

Thanks for your reply. I guess that ends this topic. Look for the
upcoming new topic: "Is there a way to simulate a Modal Datasheet?"
 
D

Dirk Goldgar

GaryZ said:
Rick,

Thanks for your reply. I guess that ends this topic. Look for
the upcoming new topic: "Is there a way to simulate a Modal
Datasheet?"
Click to expand...

<g> See my reply to Rick.
 
G

Guest

Dirk Goldgar said:
Wow! I didn't know that. How strange! Thanks, Rick.

Gary, it sounds like your best workaround would be to display the form
in Continuous Forms view, formatted if necessary to look like a
datasheet.
Click to expand...

That looks to be working. I'm entering the data now. I'll get back if any
further probs.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Re-Post - DoCmd.OpenForm Modality Problem - Form's Modal = "Yes" 3
Form doesn't wait for input 1
Is there a way to simulate a Modal Datasheet? 1
Getting error please help with this code.?? 1
Form with Disconnected Recordset will not allow edits. 1
Filling in a listbox from a table in SQL Server from Stored Procedure 2
How to add more than 1 row in a combo box in VB? 2
=> function to return a date value 1

Top