Need some hint to validate form

[d_profesor]

Hello, I've some question about form validation.., hope you can help me.

I have form which contain a subform. Main form bound to tbl_Product and subform bound to tbl_Componen_Product. I need to create product number (part number) base on some textbox value from main form and subform..To do this, i create a command button and a new textbox on mainform. I place some vba code in command click event to generate that number and display it in new textbox named txtProductNumber that bound to a field in the tbl_Produk.

My question are quite simple:

1. I want to validate txtProductNumber so that it can't be null. and if user force to close form with empty product number, data won't be saved . Where should i place validation code? I've tried to place it in form beforeupdate, with doCmd.cancelevent method. But it didn't work, because focus has to move to subform when txtProductNumber is still in null value (the value for txtProductNumber generate after some textbox in subform filled. How do i solve it?

2. How do i disable access warning, and replace it with my custom message when a data won't be saved?

2. Is wise to store product number in table for this case? i know that product number can be stored in a query or retrieved with a function when i need it. What is the standard choice, store in table or not?

Thanks

 

[Rank Am]

The easiest way is to set the field in the main table deign view to required yes, or it could be the primary key. You cannot save records in a query there has to be a field in a table.
This would force access give a data validation error if the user tried to quit.
You could also enter an input mask in the fields design grid to prevent incorrectly formatted or incomplete data being entered.

To write code the easiest way is to create a procedure in the main forms module with a variable to update the txtProductNumber

Private sub UpdateProdNumber ()
OnError Goto DealWithError
Dim strPROD as String
      'Reference mainform controls by
      'ME.{controlname]
     'Subform controls by
     'ME.{subform_name}.FORM.{controlname}
strPROD = ME.{controlname} & ME.{subform_name}.FORM.{contolname}&.....
me.txtProductNumber = strPROD

DealWithError:
docmd.setwarnings = false 'Switch off default warnings
msgbox  'Enter some text etc to prompt user
       'Some code such as SetFocus to perform an action
docmd.setwarnings = true

In the main forms BeforeUpdate and command button click event enter the following code

Call UpdateProdNumber

 

[Dairy Farmer]

New record, Field1 cannot be null/empty. Prompt as field looses focus.

Private Sub [COLOR="Blue"]Field1 [/COLOR]_Exit(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString

 If Me.NewRecord And Len(Me.[COLOR="Blue"]Field1 [/COLOR]& vbNullString) = 0 Then

    Msg = ("You have not entered [COLOR="blue"]Field1[/COLOR]." & vbNewLine & _
    "Do you want to discard this entry?")
    Style = vbYesNo + vbExclamation + vbDefaultButton2
    Title = "Missing Data"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
       MyString = "Yes"
       Cancel = True
       DoCmd.RunCommand acCmdRecordsGoToLast

    Else

        MyString = "No"
        Cancel = True
    End If
    End If
End Sub

 

[Dairy Farmer]

Check for duplicates

Private Sub [COLOR="Blue"]Field1[/COLOR]_BeforeUpdate(Cancel As Integer)
    If IsNull(DLookup("[COLOR="blue"][Field1][/COLOR]", _
        "[COLOR="Blue"]Table1[/COLOR]", _
        "[COLOR="blue"][Field1][/COLOR] = """ & Me.[COLOR="blue"]Field1[/COLOR] & """")) = False Then
        Cancel = True
        MsgBox ("The [COLOR="blue"]number[/COLOR] " & Me.[COLOR="blue"]Field1[/COLOR] & " already exists." & vbNewLine & _
        "Please select a new [COLOR="blue"]number[/COLOR].", vbExclamation, "Dulicate [COLOR="blue"]number[/COLOR]")
        Me.[COLOR="blue"]Field1[/COLOR].Undo
    End If
End Sub

 

[Dairy Farmer]

On save button or form exit button.

Dim Cancel As Integer

    If Len(Me.[COLOR="Blue"]Field1[/COLOR] & vbNullString) = 0 Then
        MsgBox "A [COLOR="blue"]Number[/COLOR] is required", vbExclamation, "Missing Data"
        Cancel = True
        Me.[COLOR="blue"]Field1[/COLOR].SetFocus

    Else
[COLOR="SeaGreen"]'choose Save or Quit depending on the button[/COLOR]
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Quit
    End If

Validate more than one field:

Dim Cancel As Integer

    If Len(Me.[COLOR="Blue"]Field1[/COLOR] & vbNullString) = 0 Then
        MsgBox "A [COLOR="blue"]Number[/COLOR] is required", vbExclamation, "Missing Data"
        Cancel = True
        Me.[COLOR="blue"]Field1[/COLOR].SetFocus

    ElseIf Len(Me.[COLOR="blue"]Field2[/COLOR] & vbNullString) = 0 Then
        MsgBox "A [COLOR="blue"]number[/COLOR] is a required field", vbExclamation, "Missing Data"
        Cancel = True
        Me.[COLOR="blue"]Field2[/COLOR].SetFocus
        
    Else
[COLOR="seagreen"]'choose Save or Quit depending on the button[/COLOR]
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Quit
    End If

 

[d_profesor]

The easiest way is to set the field in the main table deign view to required yes, or it could be the primary key. You cannot save records in a query there has to be a field in a table.
This would force access give a data validation error if the user tried to quit.
You could also enter an input mask in the fields design grid to prevent incorrectly formatted or incomplete data being entered.

To write code the easiest way is to create a procedure in the main forms module with a variable to update the txtProductNumber

Private sub UpdateProdNumber ()
OnError Goto DealWithError
Dim strPROD as String
      'Reference mainform controls by
      'ME.{controlname]
     'Subform controls by
     'ME.{subform_name}.FORM.{controlname}
strPROD = ME.{controlname} & ME.{subform_name}.FORM.{contolname}&.....
me.txtProductNumber = strPROD

DealWithError:
docmd.setwarnings = false 'Switch off default warnings
msgbox  'Enter some text etc to prompt user
       'Some code such as SetFocus to perform an action
docmd.setwarnings = true

In the main forms BeforeUpdate and command button click event enter the following code

Call UpdateProdNumber

I'm sory, i don't mean that i store record in query. I mean through function and query, product number can be generated. So actually, i've an option, store product number in a table or just retrieve it from function and query. But that's not the problem now. Btw, if i set required property=true for product number field, that will prevent focus to move to
subform..and in this case, since the product number need values from some control in the subform, that means it is not possible to do...

 

[d_profesor]

New record, Field1 cannot be null/empty. Prompt as field looses focus.

Private Sub [COLOR=Blue]Field1 [/COLOR]_Exit(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString

 If Me.NewRecord And Len(Me.[COLOR=Blue]Field1 [/COLOR]& vbNullString) = 0 Then

    Msg = ("You have not entered [COLOR=blue]Field1[/COLOR]." & vbNewLine & _
    "Do you want to discard this entry?")
    Style = vbYesNo + vbExclamation + vbDefaultButton2
    Title = "Missing Data"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
       MyString = "Yes"
       Cancel = True
       DoCmd.RunCommand acCmdRecordsGoToLast

    Else

        MyString = "No"
        Cancel = True
    End If
    End If
End Sub

The situation is after some field in main form is filled, focus must move to subform.Because product number need values from sub form before it generate through procedur and function. And if it happen, that's mean property me.newrecord will be False..and also will run beforeupdate event... and checking for null value will be invalid here because product number is not generated yet (null value is correct value in this situation)

so here the proses:

add field values in main form -------> add field values in subform---->create produk number

me.newproperty will set to false and beforeupdate event occur when focus move from main form to subform...

is it the case that i have to perform delete operation to undo the data? Help please...

Thanks...

 

[Rank Am]

Btw, if i set required property=true for product number field, that will prevent focus to move to
subform..

No it won't, when a form looses focus it does not update the tbl unless you write some code to tell it to. Also a subform is a control on the main form so the main form Onlostfocus event doesn't even happen when moving between main form and a subform.
If the product number is going to be used alot then I would definately store it in a table. Why validate it if it is not a key data field

 

[John Big Booty]

No it won't, when a form looses focus it does not update the tbl unless you write some code to tell it to. Also a subform is a control on the main form so the main form Onlostfocus event doesn't even happen when moving between main form and a subform.
If the product number is going to be used alot then I would definately store it in a table. Why validate it if it is not a key data field

Sorry I don't believe that to be entirely correct. If there is a required filed on the main form that is blank, you will not be able to shift focus to your sub-form without a message advising that a field with a required property is blank. Unless of course your Sub-form is not linked to your main form.

 

[d_profesor]

No it won't, when a form looses focus it does not update the tbl unless you write some code to tell it to. Also a subform is a control on the main form so the main form Onlostfocus event doesn't even happen when moving between main form and a subform.
If the product number is going to be used alot then I would definately store it in a table. Why validate it if it is not a key data field

Yes, it will be used frequently. I' ve create code so that it will be uniqe. Later, it will be printed and stamped on the product. And maybe someday it will be use for creating bar code. That's the plan..

In my case, like John said, form is linked to the subform. and setting required field property=true prevent focus move to the subform. I've tried it

 

[d_profesor]

Sorry I don't believe that to be entirely correct. If there is a required filed on the main form that is blank, you will not be able to shift focus to your sub-form without a message advising that a field with a required property is blank. Unless of course your Sub-form is not linked to your main form.

yes, that's the case..