How to make sure all the fields in the form are filled before saving the record

[wasseypurian]

I have two forms to filled one after other
1. Project Form now referred as projform
2. Project Risk_Details Form now referred as projriskform

Now I want that all the fields to be filled in projform before I migrate to the projriskform. So I have tried to make the all the field "Required" in the table. But how can I modify the message that it displays?

Also where should I place the macro of opening the projriskform and I should display the message that entry has been stored in the table?

I have tried to place the openform macro after the saverecord(deve loped using the record operation of the form wizard) button but the problem is that the form migrates even when the form is not complete.

https://www.dropbox.com/s/kf4mgfgq6gl62vu/savemacro.jpg

I have even tried with after_insert event but that is not working.

I have tried after update thing as well but that also dose not seem to work as I have other things as well running in after update event

Have a look as code below

Option Compare Database

' Next is used to save value for correct confirmaton message.
Dim IsNewRec As Integer

Private Sub Sector_AfterUpdate()
'For the sub sector display
SubSector = Null
SubSector.Requery
SubSector = Me.SubSector.ItemData(0)

' Display confirmation Message.
If (IsNewRec = 1) Then
MsgBox "Order Added.", vbInformation, "Confirmation"
Else
MsgBox "Order Updated.", vbInformation, "Confirmation"
End If

End Sub

Private Sub Form_Current()
'For the sub sector display
SubSector.Requery

' set newrec switch for confirmation.
If (Me.NewRecord) Then
    IsNewRec = 1
Else
    IsNewRec = 0
End If

End Sub

Private Sub Form_Load()
If IsNull(Sector) Then
  Sector = Me.Sector.ItemData(0)
  Call Sector_AfterUpdate
End If

'If IsNull(Project_Name) Then
    'DoCmd.GoToRecord , , acNewRec
'End If

End Sub

Where shall I open the new form?

[twinnyfo]

wasseypurian,

There are two main ways I have seen what you want to do. neither way uses macros, and most on this forum would agree that in order to build a robust database, you need to use VBA instead of macros.

Option 1: Use the built in error trigger to require entry of the required data fields. I believe the validation rule is 2107. When the error triggers, see if the error is 2107, and then have the user correct the data entry error.

I prefer:

Option 2: Your VBA controls navigation options. Provide instructoins ont he form and in the ControlTipText property of your controls to say that certain values are required. After every control is updatedd, check to see that it is a valid entry. Once all the controls have valid entries, then enable the button that navigates to the next step in the operation. You could also do this after the fact, so that when the user clicks the button to go to projriskform, these validations are done through your VBA. If it meets all the required criteria (and no errors will fire, because you are not navigating to a new record), then open the projriskform.

Either way will work. Others here will have different preferences, but option 2, in my opinion would be preferred, because I don't like my db to generate errors, but I would rather exhaust all possible options before an error arises--this is not possible in all situations.

Work with these prionicples and let us know what you come up with and we can troubleshoot any additional details.

[wasseypurian]

Can you please give an example about how to use option 2. I am sorry if this question is dumbo to ask as I am new to access.

Though I have tried to fix the things by placing condition on each fields and it worked fine as [MacroError] fired up until all the required fields were filled up.

https://www.dropbox.com/s/0n11tl6sdx16bf5/mystratey.jpg

But using the above strategy is got stuck when in another form I have imposed primary key condition so that enties in two fields cannot be same to any of the previous entry in the form.

For example : If I have already have values for field 3 & 4 as Spain and Chile. And while filling the form user again chooses Spain and Chile then primary key violation will occur but I believe this does not passes the [MacroError] condition.

https://www.dropbox.com/s/5rhj4phhhmdaana/primarykeyproblem.jpg

How can I check this type of error.

[twinnyfo]

Not a dumbo question at all.

According to Option 2 above, you would have a command button that moves to the projriskform when it is clicked. However, in the command button's properties, you set its Enabled Property to False. This will display it on the Form, but no one can click it and navigate to the next section.

Now, let's say you have four text boxes (we will call them txtBox1, txtBox2, txtBox3 and txtBox4--just for example), all of which require data in them. You can have a label on the Form that tells the user these text boxes must contain data (so at least they know). You can also have a brief message in the ControlTipText of each text box. Then, when someone hovers the mouse over that text box, a small pop up will tell them they have to put something in there.

then, in the Text Box AfterUpdate events, add this, plus an additonal sub to evaluate your text boxes:

Private Sub txtBox1_AfterUpdate()
    ValidateData
End Sub
Private Sub txtBox2_AfterUpdate()
    ValidateData
End Sub
Private Sub txtBox3_AfterUpdate()
    ValidateData
End Sub
Private Sub txtBox4_AfterUpdate()
    ValidateData
End Sub
Private Sub ValidateData()
    If IsNull(Me.txtBox1) Or _
        IsNull(Me.txtBox2) Or _
        IsNull(Me.txtBox3) Or _
        IsNull(Me.txtBox4) Then
        Me.cmdProjRiskForm.Enabled = False
        'Add code to determine which text box
        'is blank and notify the user
    Else
        Me.cmdProjRiskForm.Enabled = True
    End If
End Function

This is only the basics, but I can't write your code for you. Use this sample as a strating block for building specifically to your needs.

Please note, that I did not include any error handling, which should also be included in all of your VBA.

[wasseypurian]

Thanks for your help :)
You just saved my life ;)

[twinnyfo]

Glad I could help! Let us know if we can help with anything else!