Make requirement in form

[jmk909er]

I have a difficult one that is hard to explain but here goes.



When a new record is being created in "frmProjects" I want that upon closing the form it will require the person via pop up warning? to add a comment which is a button on the same form that opens "frmRemarks" other wise the record will not save.

Or it could upon attempting to close the frmProjects it could pop up the frmRemarks with a message box saying "Comment is required when creating new record" ???

Any ideas? Please note that I am a novice at VBA and pretty much need things spelled out in detail, but I don't give up!

Thanks, Joe

[jmk909er]

I should also note that frmProjects pulls fromtblProjects and that comments come from tblRemarks which is a subdata table of tblProjects

[viper]

Try this out, you can either put it in the Form Close area of your form or you can assign it go a cammand button. If you need more detail just ask

 
Private Sub Form_Close(Cancel As Integer)
        'Message box warning of Comment needed
        strMsg = "WARNING!!! Comment is required when creating a new record. Click ok to open the Remarks" 
         If MsgBox(strMsg, vbOKOnly + vbCritical, "Remarks Needed") = vbOK Then
 
            Dim stDocName As String
            Dim stLinkCriteria As String
 
    'Opens a different form with matching criteria
            stDocName = "frmRemarks"
     'The first brakets put the field name of your ID key that will match up to the frmRemarsk table. The second braket name the field of the ID key thats in your frmRemarsk table.
            stLinkCriteria = "[CurrentFormID]=" & Me![frmRemarks_IDfield]
 
            DoCmd.OpenForm stDocName, , , stLinkCriteria
     'Closes out the original form you work on
            DoCmd.Close acForm, "frmProjects"
 
   End If
End Sub

Regards Viper

[jmk909er]

Thanks Viper, I'm not sure that I am smart enough to know how to apply this. I have a close button that I can put this code in but how does it work with the existing assigned macro that closes the form now? Should I get rid of that? Also if this form is opened to view an existing record I just need it to close without launching the input requirement. Am I making sense??

Thanks, Joe

[viper]

Yup you are, this is a code that I used for one of my DB and how I set up mine was that once I had a status field change it launched a txt box stating that you needed to add comments to the status change. After clicking 'OK' it launched a different form matching the record that was active, to enter a comment for the status change. For your DB you could two different frmProject forms. One for adding new records only with the above code and one for updateing existing records. The forms look the same but they have different functions, then you can assign them to your switchboard for one to add new and one to update existing. Your users wont know the difference.

[jmk909er]

Viper, you have inspired me with some new ideas! I think that makes a lot of sense and it also gets me around another issue that I am having (too deep for my little mind to explain here) by using the same form to input and view records. I am going to mark this thread solved for now and try some things. Thanks, Joe

[viper]

Any time Joe, glad I could help.

Regards, Viper