Create/Open a new form based on record selection from a continuous form

PACIFICAT2005
PACIFICAT2005 used Ask the Experts™
on
Let me tell you that it was a pain researching all this.  My process works and felt my conclusion should be available with review for improvement.  Thus my post.  Points will be shared based on improvement.   The validation routine was previously awarded points on EE from a post of mine and will not be considered in this post.

Concept:  Open a new form based on a record selected from a continuous form.

The new form must be have a design view of "Single Form" with the selected record from a "Continuous Form."
The new form must be created upon request via button press.  In this case it's called UPDATE.
The new form must be able to validate missing fields against NULL and "" (solved from a previous EE post of mine.)
The validation on the new form will use the UPDATE button.

What I did:

1.  I created the original form with a default view of "Continuous Form"  The form is locked as I don't want changes made here.  Form fields that required validation against NULL and "" used a TAG property of REQD.

2.  I removed the ability to close the original form via clicking "X" in the upper right corner of the form.

3.  I added a CLOSE button to the header of the form and an UPDATE button to the detail.  Both buttons are unbound with code added.  See below.  Each of these buttons perform dual purposes since in essence they are two separate forms.

4.  I created a subroutine to create a duplicate of the original form, modify its properties, save then opened the new form with the selected record.  This subroutine is CALLed when needed and applied to any form passed to it.

Note:  The examples of code are the CreateUpdatedForm subroutine, and form specific code for the CLOSE & UPDATE buttons and on load code.


CODE FOR THE UPDATE BUTTON:

Private Sub Command27_Click()

' This is the UPDATE Button with a dual purpose.  Since we made a copy we need greater functionality.
' This includes validation using TAGS on form fields.
    
    Dim MyFormNm As String
    Dim MyRecdNb As Long
    Dim NewFormName As String
    Dim OrgFormName As String
    Dim ctl As Control
    Dim bMissing As Boolean
    Dim iResponse As Integer
    
    MyFormNm = Me.Form.Name
    MyRecdNb = Me.Form.CurrentRecord

'  We are using the UPDATE button to create a copy of the form with a selected record for modification/validation.

    If Left$(MyFormNm, 8) <> "Updating" Then
    
        NewFormName = "Updating " & MyFormNm
        Call CreateUpdatedForm(MyFormNm, MyRecdNb)
        
    Else

'  We are using the UPDATE button now on the COPIED form for validation.    

        bMissing = False
        OrgFormName = Right$(Me.Form.Name, Len(Me.Form.Name) - 9)
                
        For Each ctl In Me.Controls
        
            If ctl.Tag = "REQD" Then            
                If Nz(ctl.Value, "") = "" Then                
                    ctl.BackColor = vbRed
                    ctl.ForeColor = vbWhite
                    bMissing = True
                Else
                    ctl.BackColor = vbWhite
                    ctl.ForeColor = vbBlack
                End If
            End If
            
        Next ctl
        
        If bMissing Then
        
            Select Case MsgBox("Fields marked in red are mandatory. Do you want to correct this?", vbYesNo)
                Case vbYes
                    Exit Sub
                Case vbNo
                    If Me.Dirty Then Me.Undo
            End Select
        Else
            MsgBox "All requied fields contain detial.  Press Ok to continue."
        End If

        DoCmd.Close        
        DoCmd.OpenForm OrgFormName
        
    End If
    
End Sub

CODE FOR THE CLOSE BUTTON:

Private Sub Exit_Button_Click()
    
    If Left$(Form.Name, 8) = "Updating" Then    
        MsgBox "Press the 'Update' button instead."
    Else
        DoCmd.Close
    End If

End Sub


CODE TO EFFECT CHANGES ON THE COPIED FORM WHEN LOADED:

Private Sub Form_Load()

    Dim NewFormName As String
    Dim CtArgs As Integer
    
    CtArgs = Val(Nz(Me.OpenArgs, 0))
    NewFormName = Form.Name
    
    If Left$(NewFormName, 8) = "Updating" Then
    
        If Len(Me.OpenArgs & "") > 0 Then
        
            CtArgs = Val(Nz(Me.OpenArgs, 0))
            DoCmd.GoToRecord acDataForm, NewFormName, acFirst, CtArgs
            
        End If
        
    End If
End Sub

Open in new window

Public Sub CreateUpdatedForm(frmName As String, recNbr As Long)

'  Create a duplicate form in "Single Form" view from a "Continuous Form" passed as frmName
'  Open the new form with the selected record number passed as recNbr
    
    Dim NewFrmName As String
    Dim FormNm As Form
    
    NewFrmName = "Updating " & frmName
        
    On Error Resume Next
    DoCmd.DeleteObject acForm, NewFrmName
    On Error GoTo 0
    
    DoCmd.Close acForm, frmName
    DoCmd.CopyObject , NewFrmName, acForm, frmName
    DoCmd.OpenForm NewFrmName, acDesign, , , acFormEdit, acHidden
    
    Set FormNm = Forms(NewFrmName)
    
    With FormNm
        .Caption = "Updating ~ " & .Caption
        .DefaultView = 0
        .NavigationButtons = False
        .PopUp = True
    End With
    
    Set FormNm = Nothing
    
    DoCmd.Close acForm, NewFrmName, acSaveYes
    DoCmd.OpenForm NewFrmName, acNormal
    DoCmd.GoToRecord acActiveDataObject, NewFrmName, acGoTo, recNbr
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I realize on my validation component should've been a docmd.close for the updated form

        If bMissing Then
       
            Select Case MsgBox("Fields marked in red are mandatory. Do you want to correct this?", vbYesNo)
                Case vbYes
                    Exit Sub
                Case vbNo
                    If Me.Dirty Then Me.Undo
            End Select
        Else
            MsgBox "All requied fields contain detial.  Press Ok to continue."
        End If
        DoCmd.Close
        DoCmd.OpenForm OrgFormName
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007
Commented:
Just a suggestion here.  This sort of falls under TMI and TMC ... Too Much Information and Too Much Code.  And in a case like this,  it's much better to post a sample db with a clear explanation of how to reproduce an issue, etc.

mx

Author

Commented:
Point taken.  I attached a sample database to demonstrate this post.
Sample4EE.mdb
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007
Commented:
ok. That's a good start.
I've read the Concept, and What I Did.
Now I have to ask ... umm, what is not working or what needs to be done.  It 'seems' to be working.
Click Update from Continuous Form
Update on single form, close. Return back and data is there.

?

mx
You seem to be making life very difficult for yourself for a simple CRUD form.  It is, after all, built-in functionality.  Creating copies of the form within the database?  Using tags for validation?

Difficult to suggest how to "improve" on this without sounding rude, sorry.

Author

Commented:
The intent of the post was to seek improvement AND have this question on EE.  Additionally, another pair of eyes may see potention problems that I may overlook.

I sort of agree with you davesgonebannanas but a guy at work created a DB long ago that is being used with 110 forms.  All are continuous in design.  I was asked to create validation on each.  At first I thought they were only talking about a couple of forms.  Thus this process.

The validation part is necessary.  If you check my post history (not much I might add) had the a working validation process that was improved using tags.  You will see that my solution was using acTextBox, etc in a select/end select.



Author

Commented:
The idea of the code was to be transportable to any Access DB in concept.  I had a need and found this routine not centrally located anywhere on the net.  Just my experience researching speaking.  This post brings value to EE and anyone seaking such a solution.
 I just want to reiterate my opening comment. "Let me tell you that it was a pain researching all this. My process works and felt my conclusion should be available with review for improvement."

I am seeking code improvement. Maybe it can't be improved, but I would never know without asking.  Offering advise is never rude and I'm open to it, but approach can win or lose the conversation.
I'm a paying member of EE because I like and use the site.  I don't have time to earn millions of points and am happy there are Experts out there.  So, if improvements can't be made then they can't be made.
The attached database was a sample I whipped up for this post.  Thus it's CRUDness... It certainly wasn't the database I was having problems with.  My employer probably would have a cow if their program was posted.
Thanks for the opportunity.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"I am seeking code improvement."
Well, that's a pretty wide open question, not to mention open ended.  It would be better if you ask a specific question that can be addressed, for a start.  And there might be multiple questions over time.

You have to remember that we are not really here to design the app for you and everyone's time is limited. So ... can you ask a specific question or two ?

mx

Author

Commented:
Fair enough.

I wrote the code using Access 2010 saved in 2003 mdb version.  At work they use 2003 and are migrating to 2007.  Do you think there will be any issues since the version of the resources selected in VBA, Tools, References will be different.

Thanks.
"I am seeking code improvement."

Oh I see, so you're just wasting your time then.  Fair enough.  Do you look back at a project after completion and say to yourself, 'I wish I'd coded that subroutine slightly differently'?

How about next time you ask if you could have designed the solution better?

Author

Commented:
There are some great minds here at EE.  Seeking help from any of them is never a waste of my time.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"Oh I see, so you're just wasting your time then.  "
Chill!

"and are migrating to 2007."
That's unfortunate, although not uncommon.  O2007 is 4+ years old now, and Q2010 is a much better product. Even our very LARGE company is fast forwarding to O2010 before the end of the year.

"Do you think there will be any issues since the version of the resources selected in VBA, Tools, References will be different."
Not quite sure what you mean, but ... for the most part, VBA and especially the IDE has not changed.  References can be a temporary issue when going to new versions, but unless you are try to do a dual install (not recommended), those are quickly resolved.

Thank you for using EE and please continue to ask questions.

mx

Author

Commented:
I understand where your coming from with the migration to 2007.  I work for the government and they like 80 softpacks applied ....

One database I made became corrupt even after a compress & repair.  I created it using 2003.  Then I snagged 2007 when it was available.  I made a change using 2007, ensured it saved in 2003 format, yet became dead on a 2003 version.  Yet, I can run it on 2007.  I figure it maybe an updated reference not available on a PC with 2003.

My last post had the same approach, but I achieved suggestions that proved valuable to me.  I believe you can't be told no unless you ask.  Never ask, and the only disappointment would be myself.  Thank you for your input mx.  I'm not going anywhere.

Author

Commented:
MX

I moved the Update button to the header since record selection can be done by clicking on any field.  This way it's cleaner in appearance yet functionality remains the same.  Take care.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Cool.

Author

Commented:
Another change:

The department that used my code on their forms ran into a snag where multiple users had problems with form/record use.  So I added some code to capture the Environ("Username") into a string and append it within the form name.  

This would not have been necessary if they had the DB in a Front End/Back End arrangement.  They don't want to do that.  

But, I thought I would share.  You can compare both attachments to this question.  Changes were made in the Update & close button as well as the module.  


Sample4EE.mdb
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"They don't want to do that."
That is *very* unfortunately and shows they are unaware of the ramifications of not doing so.

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial