Add New Record From Pop-up Form, Reset RecordSource - Form Locked?

[misscrf]

so this is odd. I have ensured the recordsource is updateable. This was working prior, but who knows what of the 89097987 attempts I've made at getting this all to work, that was. I have a form for records. The detail is visible = false on load. The header shows with buttons to look up a record (pop-up form), add a new record (pop-up form), and go back to main menu. The add record needs a pop-up form, so that the user can choose a "contact" and/or "match", as the hierachy of relationships is Contact --> Match --> Record. I have the code below, to execute a stored procedure, which will add the new record and bring back the scope identity, so I can use that in setting a 1 record - record source on the main records form, when I return to it.

This all works fine, except when I am returned back to the records form, on this 1 record, the form is locked. I can't click on anything. The access ribbon has the views grayed out. When I right-click the title bar of the form, then everything is ok again, but I have no idea what that means. Why is my form frozen? Why can't I work in the form, now that I have a record source of the new record I've added? The form is set to allow edits, and the fields are not locked. I can't click on the different pages of a tab control on the form either. I am happy to debug different properties, but not sure which ones might be the culprit. Why does right-clicking the form's title bar unlock everything so it works again?

Here is the add new record code on the pop-up form:

Private Sub cmdStartNewMRecord_Click()
    Dim mid As Integer
    Dim cmd As New ADODB.Command
    Dim midparm As New ADODB.Parameter
    Dim MCID As New ADODB.Parameter
    Dim frmMCSQL As String
    
    
    If Nz(Me.cboChooseContact.Column(0), 0) = 0 Then
        MsgBox "No Contact has been selected. Records must be assigned to a valid Contact and Match.", vbCritical, "Must Choose a Contact and Match to Continue."
    ElseIf Nz(Me.cboChooseMatch.Column(0), 0) = 0 Then
        MsgBox "No Contact has been selected. Records must be assigned to a valid Contact and Match.", vbCritical, "Must Choose a Contact and Match to Continue."
    Else
        mid = Nz(Me.cboChooseMatch.Column(0), 0)
        With cmd
            .CommandText = "sp_AddNewMatchRecord"
            .CommandType = adCmdStoredProc
            .ActiveConnection = "DRIVER={SQL Server};SERVER=blahservername;DATABASE=blahdatabasename"
            Set midparm = .CreateParameter("@mid", adVarChar, adParamInput, 30, mid)
            .Parameters.Append midparm
            Set MCID = .CreateParameter("@NEWID", adCurrency, adParamOutput)
            .Parameters.Append MCID
            .Execute Options:=adExecuteNoRecords
            Set .ActiveConnection = Nothing
            Set midparm = Nothing
        End With
       Debug.Print MCID
    End If
    
    frmMCSQL = "SELECT * FROM tblMRecords WHERE ID = " & MCID
    Forms!frmMRecords.RecordSource = frmMCSQL
    Forms!frmMRecords.Visible = True
    Forms!frmMRecords.Detail.Visible = True
    Forms!frmMRecords.Refresh
    Call ShowRequirements(Nz(MCID, 0))
    Set MCID = Nothing
    Set cmd = Nothing
    DoCmd.Close acForm, "frmMRecords_AddNewRecord", acSaveNo
End Sub

Any help would be greatly appreciated!
Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would try comment out the refresh since it shouldn't be necessary.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[misscrf]

TY! I will try that. Thanks, Duane!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

No, unfortunately that did not do it. Here is all the code on the Main Record form:

Option Compare Database
Option Explicit
Private Sub Form_Load()
    Me.Detail.Visible = False
End Sub
Private Sub cmdNew_Click()
    DoCmd.OpenForm "frmMRecords_AddNewRecord"
    Me.Form.Visible = False
End Sub

Private Sub cmdFindMRecord_Click()
    DoCmd.OpenForm "frmMRecords_FindRecords"
    Me.Form.Visible = False
End Sub

Private Sub cmdQContacts_Click()
    MsgBox "Contacts listed are only those entered for this Contact." & vbCrLf & _
        "Please add Contacts in Contacts Section, " & vbCrLf & _
        "and then assign them to a type of contact here within a Record.", vbQuestion, "Assign Contacts to Types of Contacts Within Records"
End Sub

Private Sub cmdMainMenu_Click()
    Forms!frmMain.Visible = True
    DoCmd.Close acForm, "frmMRecords", acSaveYes
End Sub

Here are the properties of that main record form:

http://files.engineering.com/getfil...28b528370c&file=MainRecordOtherProperties.jpg

http://files.engineering.com/getfil...33018d82e&file=MainRecordFormatProperties.jpg

http://files.engineering.com/getfil...4a6d5156bc7&file=MainRecordDataProperties.jpg

I'm so close to getting this part of the development working, so I can move on to the rest!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I figured this out. The add new record form was set to pop up Yes and Modal Yes. Even though I close that form, it was not releasing control properly. Since I don't have any other visible forms, I turned those to No, and now it works! Yay! 3 Hours of sleep and I figured it out!!!

misscrf

It is never too late to become what you could have been ~ George Eliot