Help On Validation In Forms!

L

lakattack

Registered User.
Local time
Today, 22:53
Joined
Apr 17, 2007
Messages
35
Hello.

I need to know some validation coding for my database.

In my subform of my form I have a text box in which you can enter data but you must enter data in that textbox before closing it.

What would the coding be for this?

Thanks
 
If you have a 'close form' button on the form, you could add coding along the lines of:

If [Forms]![formname]![textboxname] > "" Then
DoCmd.Close acForm, "formname"
Else
MsgBox ("Enter something in textbox")
End If
 
PaulJR said:
If you have a 'close form' button on the form, you could add coding along the lines of:

If [Forms]![formname]![textboxname] > "" Then
DoCmd.Close acForm, "formname"
Else
MsgBox ("Enter something in textbox")
End If
Click to expand...

I just typed in:

If [frmEquipment]![frmEquipmentSub]![Name] > "" Then
DoCmd.Close acForm, "frmEquipmentSub"
Else
MsgBox ("Enter Name")

And I just got an error message saying:

'Microsoft Access can'e find the field '|' refered to your expression'

What should I do?

frmEquipment = main form
frmEquipmentSub = subform
Name = name of textbox
 
I haven't actually ever used a Subform, but I think the first line of your code should be changed to this:

If [Forms]![frmEquipmentSub]![Name] > "" Then

Hope this helps.
 
PaulJR said:
I haven't actually ever used a Subform, but I think the first line of your code should be changed to this:

If [Forms]![frmEquipmentSub]![Name] > "" Then

Hope this helps.
Click to expand...


I typed in what you said and the message box still comes up as:

'Microsoft Access can't find the form 'frmEquipmentSub' refered to in a macro expression or Visual Basic code'

Any more ideas please!!!???
 
I can look tomorrow, but just a thought, do you have the option of changing your table properties for this field (name) to 'Required'? This is an alternative way of ensuring data gets entered.
 
Are you coding this in the main form then, and not the subform? If so, maybe you can try Me!frmEquipmentSub.Form!Name to see if that works.

Otherwise, here is a document that was posted ages ago by Mile-O that I still find useful:
 

Attachments

MrsGorilla said:
Are you coding this in the main form then, and not the subform? If so, maybe you can try Me!frmEquipmentSub.Form!Name to see if that works.

Otherwise, here is a document that was posted ages ago by Mile-O that I still find useful:
Click to expand...

This kind of works...

My code is now:

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
End If

If I click the exit button on the form and nothing is typed in the textbox then the message box comes up and closes

If I click the exit button on the form and the textbox has text inside it then it will close normally.

I do not want the form to close if the textbox is empty but still want that message box to come up.

How do I do that???
 
I think you have to cancel the event (the exit command) that caused this to run. You might try adding in a DoCmd.CancelEvent to see if that stops the form from closing.
 
MrsGorilla said:
I think you have to cancel the event (the exit command) that caused this to run. You might try adding in a DoCmd.CancelEvent to see if that stops the form from closing.
Click to expand...

Where would I add that to this code:

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
End If

Thanks!!
 
Sometimes you have to play with it a little to get it into a format that Access likes. I would probably try putting it here:

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
DoCmd.CancelEvent
End If

Let me know if that works or not. You might also try coding in a SetFocus statement to specifically set focus to the field you want entered (the Name field, in this example).
 
lakattack said:
Where would I add that to this code:

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
End If

Thanks!!
Click to expand...

Your code needs to be in the form's Before Update Event.

Here is an example using the Northwind.mdb's Customer form

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

   Cancel = False


' perform data validation
If IsNull(Me.CompanyName) Then

   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   Me.CompanyName.BorderColor = vbRed
   DoCmd.GoToControl "CompanyName"
      
   Cancel = True

End If


If Not Cancel Then
  ' passed the validation process

    If Me.NewRecord Then
        If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
            ' run code for new record before saving
        
        End If
    
    
    Else
        If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
           ' run code before an existing record is saved
           ' example: update date last modified
            
        End If
    End If

End If


' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

    If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
        Me.Undo

    End If
    
End If

End Sub
 
MrsGorilla said:
Sometimes you have to play with it a little to get it into a format that Access likes. I would probably try putting it here:

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
DoCmd.CancelEvent
End If

Let me know if that works or not. You might also try coding in a SetFocus statement to specifically set focus to the field you want entered (the Name field, in this example).
Click to expand...

No this does not work! What should I do now? I was thinking of using setfocus but did not know if this would work! What should I type into my coding?
 
HiTechCoach said:
Your code needs to be in the form's Before Update Event.

Here is an example using the Northwind.mdb's Customer form

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   Cancel = False
 
 
' perform data validation
If IsNull(Me.CompanyName) Then
 
   MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
   Me.CompanyName.BorderColor = vbRed
   DoCmd.GoToControl "CompanyName"
 
   Cancel = True
 
End If
 
 
If Not Cancel Then
  ' passed the validation process
 
    If Me.NewRecord Then
        If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
            ' run code for new record before saving
 
        End If
 
 
    Else
        If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
            Cancel = True
        Else
           ' run code before an existing record is saved
           ' example: update date last modified
 
        End If
    End If
 
End If
 
 
' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then
 
    If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
        Me.Undo
 
    End If
 
End If
 
End Sub
Click to expand...

I tried using this in my main form and subform and stil it does not work. Do you have that database that you can send me? I do not want the form to close but it closes even if there is no text in the textbox but the message box still pops up.

Thanks
 
Where do you have the following code right now (what event)?

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
End If
 
lakattack said:
I tried using this in my main form and subform and stil it does not work. Do you have that database that you can send me? I do not want the form to close but it closes even if there is no text in the textbox but the message box still pops up.

Thanks
Click to expand...

Are you putting the code in this event:

Code: 
Private Sub Form_[B]BeforeUpdate[/B](Cancel As Integer)

' code here

End Sub



I will also throw together an example for you.
 
MrsGorilla said:
Where do you have the following code right now (what event)?

If Me!frmEquipmentSubform.Form!Name > "" Then
DoCmd.Close acForm, "frmEquipmentSubform"
Else
MsgBox ("Enter Name")
End If
Click to expand...

I want the code to be in the part where if the Name field has no text and the user wants to close the form then the message box will pop up and it will not close the form until the Name field has been filled in.

Is that possible?

Thanks
 
lakattack said:
I want the code to be in the part where if the Name field has no text and the user wants to close the form then the message box will pop up and it will not close the form until the Name field has been filled in.

Is that possible?

Thanks
Click to expand...

But where is the code residing? You have it in the main form, correct? What event triggers it? Is it the On Close event of the form?
 
MrsGorilla said:
But where is the code residing? You have it in the main form, correct? What event triggers it? Is it the On Close event of the form?
Click to expand...

Yes it is in the main form. The coding is in the close form button. It is not on the On Close event. Does this help?
 
You must log in or register to reply here.

Similar threads

Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
552
Mike Krailo
Mike Krailo
M
Solved Password Validation sub
2
Replies
31
Views
1,363
mikenyby
M
D
Solved Entering Data in Subform creates a OLE Server or Active X Control Error
Replies
5
Views
346
theDBguy
theDBguy
A
Tab Index of fields NOT reacting in Order
Replies
5
Views
361
The_Doc_Man
The_Doc_Man
B
Form writing to wrong table
Replies
7
Views
274
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom