Form doesn't wait for input

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code: Find '--- in the code for my question.

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

'Open Old-NewUtil table in datasheet view in order to input proper
values into NewUtility field.
DoCmd.OpenForm "Input New Utility Names", acFormDS, , , acFormEdit

'--- The above DoCmd.OpenForm doesn't stop to allow input of new utility
names.
'--- The following MsgBox prompt immediately appears on top of the form
waiting
'--- for a responce. Do I need some kind of Read between the two ?

' 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?"
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
 
Back
Top