Re-Post - DoCmd.OpenForm Modality Problem - Form's Modal = "Yes"

[Guest]

The The form called by DoCmd.OpenForm should wait for a user to input proper
Utility Names, but doesn't. The form appears, but immediately the MsgBox
prompt appears on top of the form. The prompt has focus. This prompt should
not appear until the user leaves the other form.

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 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 displayed form. The
MsgBox has focus.
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

 

[Steve Schapel]

Gary,

As far as I can tell, the code is doing exactly what I would expect.

Do you mean that you want the [Input New Utility Names] form to be
opened, and then, after the user has entered some data into this form,
for the Message Box to prompt at that point for confirmation to run the
Update? If so, it seems to me that the code should be on an event,
maybe the After Update, of the applicable control on the [Input New
Utility Names] form, or the After Update event of the form itself.

Your question does not relate to macros, which is the focus of this
forum. If the above suggestion doesn't help, maybe a post to the
microsoft.public.access.adp or microsoft.public.access.formscoding
newsgroups might yield a better response.

--
Steve Schapel, Microsoft Access MVP

The The form called by DoCmd.OpenForm should wait for a user to input proper
Utility Names, but doesn't. The form appears, but immediately the MsgBox
prompt appears on top of the form. The prompt has focus. This prompt should
not appear until the user leaves the other form.

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 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 displayed form. The
MsgBox has focus.
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

 

[Guest]

Thanks Steve,

a post to the microsoft.public.access.adp or microsoft.public.access.formscoding
newsgroups might yield a better response.

Just did so in microsoft.public.Access.FormsCoding. Could not find
microsoft.public.Access.ADP or microsoft.public.Access.ADO.

Also worded the question better. MsgBox should not appear until after user
closes the form via the X button. Instead MsgBox appears and gets focus
immediatly after the form is opened. As if the DoCmd.OpenForm displays the
form, but doesn't gain focus.

Thanks anyway.

 

[Gijs Beukenoot]

GaryZ had uiteengezet :

Thanks Steve,


Just did so in microsoft.public.Access.FormsCoding. Could not find
microsoft.public.Access.ADP or microsoft.public.Access.ADO.

Also worded the question better. MsgBox should not appear until after user
closes the form via the X button. Instead MsgBox appears and gets focus
immediatly after the form is opened. As if the DoCmd.OpenForm displays the
form, but doesn't gain focus.

Thanks anyway.

See answer in microsoft.public.Access.FormsCoding