INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

CODE

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

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

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

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

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

(OP)
TY! I will try that. Thanks, Duane!

misscrf

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

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

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

CODE

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/getfile.aspx?folder=2...
http://files.engineering.com/getfile.aspx?folder=f...
http://files.engineering.com/getfile.aspx?folder=6...

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close