Verifying fields have been entered for record to save

[hardhitter06]

Hi All,

Access '03.

I have an input form with 2 fields that I am requiring. The first field is a combobox where I select a Vendor (which is a lookup to another table) :

SELECT tblInputNewVendor.VendorNameID, tblInputNewVendor.VendorName, tblInputNewVendor.VendorFedID FROM tblInputNewVendor ORDER BY [VendorName], [VendorFedID];

The second field is a Bound Object box which requires a file.

Here is the code for my Before Update for my input form. This code runs upon hitting the save button:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Tag <> "skip" Then
   If ctrl.ControlType = acComboBox Or acBoundObjectFrame Then
      If IsNull(ctrl) Then
         MsgBox ctrl.Name & " Cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit Sub
      End If
   End If
End If
Next
MsgBox "Record Saved!"
End Sub

My problem is this:

When I don't select a Vendor from the combo list, but insert an image, the record saves. THIS IS WHAT I DON'T WANT.

When I select a Vendor but I don't insert an image, my code takes effect and says my Bound object field cannot be left empty. This is what I want.

So I'm just confused on why it isn't stopping me when I leave out a Vendor from that combobox list?? Does it have something to do with that type of object?

Any help would be great. Thank you!

 

[SOS]

Try changing this:

If IsNull(ctrl) Then

to this:

If IsNull(ctrl.Value) Then

ctrl has no default like a normal control as it handles all different types, even those without a .Value property.

 

[vbaInet]

Check the combobox first, and when you're checking check the ListIndex property, not IsNull. ListIndex will return -1 if a selection was not made.

 

[hardhitter06]

SOS, that caused an Access Error.

VBA...how would I alter my code to do this?

 

[SOS]

What error?

 

[vbaInet]

Something like this perhaps:

If ctrl.ControlType = acComboBox Then
      If ctrl.ListIndex = -1 Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   ElseIf ctrl.ControlType = acBoundObjectFrame Then
      If ctrl.value & "" = "" Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   End If

 

[hardhitter06]

VBA,

I'm getting a compire error: Exit For not within For...Next.

It is highlighting the first "Exit For" in your code.

I'm a noob when it comes to coding so I'm not quite sure what that means?

Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

If ctrl.ControlType = acComboBox Then
      If ctrl.ListIndex = -1 Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   ElseIf ctrl.ControlType = acBoundObjectFrame Then
      If ctrl.Value & "" = "" Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   End If
MsgBox "Record Saved!"
End Sub

 

[vbaInet]

That code wasn't a complete replacement of your code. It was code to replace the bits inside your For each block.

SOS would still like to know what the error message read?

 

[hardhitter06]

SOS,

Mic Visual Basic

Run-time error '438'
Object doesn't support this property or method.

Once I hit debug: It has your edit in yellow.

 

[vbaInet]

You still need FOR EACH... and NEXT

 

[SOS]

SOS,

Mic Visual Basic

Run-time error '438'
Object doesn't support this property or method.

Once I hit debug: It has your edit in yellow.

And that would be because you've hit on a control which doesn't have a .Value property.

 

[hardhitter06]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
  If ctrl.ControlType = acComboBox Then
      If ctrl.ListIndex = -1 Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   ElseIf ctrl.ControlType = acBoundObjectFrame Then
      If ctrl.Value & "" = "" Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit For
      End If
   End If
Next
MsgBox "Record Saved!"
End Sub

Ok it's still a little screwy, but I'm close.

I don't fill out a Vendor Name, but I insert an object.

I hit the save record command button.

Microsoft Office Access: VendorName cannot be left empty.. Good

Hit "OK"

Microsoft Office Access: Record Saved!...Bad

Hit "OK"

Microsoft Office Access: No current record....good

Hit "OK"

Where does the Record Saved piece of code have to move?

 

[SOS]

By the way, the way you have it now your Message Box with the Record Saved text will show up if it is saved or not saved. You would need to have it as an ELSE for the original IF.

 

[hardhitter06]

Can you show me the line of code so I know what IF your referring to? Original If I'm not following lol

 

[SOS]

I took the liberty of modifying your code to make it a little more robust. See the parts in red.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
[B][COLOR=red]Dim blnError As Boolean[/COLOR][/B]
[B][COLOR=#ff0000]Dim strMsg As String[/COLOR][/B]

For Each ctrl In Me.Controls
[COLOR=red][B]   Select Case ctrl.ControlType[/B][/COLOR]
  [B][COLOR=red]Case acComboBox[/COLOR][/B]
[B][COLOR=red]      If Len(ctrl.Value & "") = 0 Then[/COLOR][/B]
[B][COLOR=red]        strMsg = strMsg & ctrl.Name & vbCrlf[/COLOR][/B]
         blnError = True
      End If
[COLOR=red][B]   Case acBoundObjectFrame[/B][/COLOR]
      If [COLOR=red][B]Len(ctrl.Value & "") = 0[/B][/COLOR] Then
[B][COLOR=red]         blnError = True[/COLOR][/B]
[B][COLOR=#ff0000]         strMsg = strMsg & ctrl.Name & vbCrLf[/COLOR][/B]
      End If
[B][COLOR=red]   End Select[/COLOR][/B]
Next
[B][COLOR=red]If blnError Then[/COLOR][/B]
[B][COLOR=red]   MsgBox "You have left these controls blank and they need a selection:" & _[/COLOR][/B]
[B][COLOR=red]   strMsg & vbCrLf & "Please go enter values for them.", vbExclamation, "Error"[/COLOR][/B]
[B][COLOR=red]   Cancel = blnError[/COLOR][/B]
[B][COLOR=red]Else[/COLOR][/B]
   MsgBox "Record Saved!"
[B][COLOR=red]End If[/COLOR][/B]
End Sub

 

[hardhitter06]

That is not working.

I left out Vendor Name list on my form and hit save and it told me "Recird saved".

Then I got another access error about changes to the table were not sucessful because they woyld create duplicate values.

 

[SOS]

I think it may be time to actually have the database in hand here so we can see what's up.

 

[hardhitter06]

alright, ill send it over..just a min

 

[gemma-the-husky]

its just displaying the message "record saved", because the code falls through to that bit ... but I bet it isnt ACTUALLY saving, because the cancel is still set to true

but you need to exit sub, as soon as you get a validation failure

i'm not sure about the test either

I would test a combobox and a frame with if nz(mycombo,0)=0
probably the other one would be if nz(myframe,"")="", but i am not 100% sure of that syntax.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
  If ctrl.ControlType = acComboBox Then
      If ctrl.ListIndex = -1 Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
        [COLOR="Red"] exit SUB[/COLOR]
         Exit For
      End If
   ElseIf ctrl.ControlType = acBoundObjectFrame Then
      If ctrl.Value & "" = "" Then
         MsgBox ctrl.Name & " cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
        [COLOR="Red"] exit SUB[/COLOR]
         Exit For
      End If
   End If
Next

[COLOR="red"]'you still get here if you dont EXIT SUB[/COLOR]

MsgBox "Record Saved!"
End Sub

 

[hardhitter06]

Gemma is right.

I pasted his code in there...with the exit subs and it works perfect!

Thank you Gemma.

Thank you SOS as well. Since this form code is so small because it is only 2 fields, I don't need anything to elaborate so I'm happy with this.