Validating Entries VBA

[cmxulb]

Hi All,
Currently, no field can be empty, when I hit save. My question is, how can I make it so that when Primed is selected and any other fields are empty it would auto writes N/A on the database when I click save?

Function ValidateEntries() As Boolean

ValidateEntries = True

Dim iMaterialID As Variant

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Print")

iMaterialID = frmForm.txtMaterialID.Value

With frmForm

'Default Color

.txtMaterialID.BackColor = vbWhite
.cmbValve.BackColor = vbWhite
.txtintfineflow.BackColor = vbWhite
.txtintcoarseflow.BackColor = vbWhite
.txtFineFlow.BackColor = vbWhite
.txtCoarseFLow.BackColor = vbWhite
.txtComments.BackColor = vbWhite
'--------------------------------

If Trim(.txtMaterialID.Value) = "" Then

MsgBox "Please enter Material ID.", vbOKOnly + vbInformation, "Material ID"
ValidateEntries = False
.txtMaterialID.BackColor = vbRed
.txtMaterialID.SetFocus
Exit Function

End If

'Validating Duplicate Entries

If Not sh.Range("B:B").Find(what:=iMaterialID, lookat:=xlWhole) Is Nothing Then

MsgBox "Duplicate Material ID found.", vbOKOnly + vbInformation, "Material ID"
ValidateEntries = False
.txtMaterialID.BackColor = vbRed
.txtMaterialID.SetFocus
Exit Function

End If

If Trim(.txtComments.Value) = "" Then

MsgBox "Please enter Comments.", vbOKOnly + vbInformation, "Comments"
ValidateEntries = False
.txtComments.BackColor = vbRed
.txtComments.SetFocus
Exit Function

End If


'Validating Scale


If .optLarge.Value = False And .optSmall.Value = False And .optBoth.Value = False Then

MsgBox "Please select Scale.", vbOKOnly + vbInformation, "Scale"
ValidateEntries = False
Exit Function

End If


If .CheckYes.Value = False And .CheckNo.Value = False Then

MsgBox "Please select Locked Yes or No.", vbOKOnly + vbInformation, "Locked"
ValidateEntries = False
Exit Function

End If


If .CheckYess.Value = False And .CheckNoo.Value = False Then

MsgBox "Please select Locked Yes or No.", vbOKOnly + vbInformation, "Locked"
ValidateEntries = False
Exit Function

End If



If Trim(.cmbValve.Value) = "" Then

MsgBox "Please select Valve from drop-down.", vbOKOnly + vbInformation, "Valve"
ValidateEntries = False
.cmbValve.BackColor = vbRed
.cmbValve.SetFocus
Exit Function

End If


If Trim(.txtFineFlow.Value) = "" Then

MsgBox "Please enter Fine Flow.", vbOKOnly + vbInformation, "Fine Flow"
ValidateEntries = False
.txtFineFlow.BackColor = vbRed
.txtFineFlow.SetFocus
Exit Function

End If

If Trim(.txtCoarseFLow.Value) = "" Then

MsgBox "Please enter Coarse Flow.", vbOKOnly + vbInformation, "Coarse Flow"
ValidateEntries = False
.txtCoarseFLow.BackColor = vbRed
.txtCoarseFLow.SetFocus
Exit Function

End If


If Trim(.txtintcoarseflow.Value) = "" Then

MsgBox "Please enter Int Coarse Flow.", vbOKOnly + vbInformation, " Int Coarse Flow"
ValidateEntries = False
.txtintcoarseflow.BackColor = vbRed
.txtintcoarseflow.SetFocus
Exit Function

End If


If Trim(.txtintfineflow.Value) = "" Then

MsgBox "Please enter Int Fine Flow.", vbOKOnly + vbInformation, "Int Fine Flow"
ValidateEntries = False
.txtintfineflow.BackColor = vbRed
.txtintfineflow.SetFocus
Exit Function

End If
End With



End Function

 

[cmxulb]

Yes! nice thanks man!..Yea CheckYes is "Locked"

When checked Yes for primed Valve type switches to N/A, How would you do it, so that it data is already there, don't change to N/A?

 

[Gokhan Aycan]

Instead of this line:

Me.cmbValve.Value = "N/A"

Use:

If Me.cmbValve.ListIndex = -1 then Me.cmbValve.Value = "N/A"

 

[cmxulb]

Instead of this line:

Me.cmbValve.Value = "N/A"

Use:

If Me.cmbValve.ListIndex = -1 then Me.cmbValve.Value = "N/A"

It works! thanks. but Is it also doing it for small and large?

 

[Gokhan Aycan]

Do you mean if either Small or Large is checked do not check N/A (that was Both in the first image you posted)?

 

[cmxulb]

Do you mean if either Small or Large is checked do not check N/A (that was Both in the first image you posted)?

Yes

 

[Gokhan Aycan]

Instead of:

Me.optNA.Value = True

Use:

If Me.optLarge.Value = False And Me.optSmall.Value = False Then Me.optNA.Value = True

 

[cmxulb]

Instead of:

Me.optNA.Value = True

Use:

If Me.optLarge.Value = False And Me.optSmall.Value = False Then Me.optNA.Value = True

Seeing it work puts a smile on my face. Thanks, man!

 

[Gokhan Aycan]