If tickbox is ticked then must enter something into another field on a form (1 Viewer)

[j.smith1981]

How is the most efficient way to allow the following to happen:

What I want is for when the user clicks an add record button.

Is when a tick box named 'filed_bool' is ticked (I got the value back through a msgbox as being -1).

Then the 'location' value cant be null.

If the user forgets to enter a value, the macro wont let the user submit the entry to the database.

I have got sorted with how to do stuff like this with excel, but with access I pulled a blank.

Any advice is greatly appreciated.

Thanks,
Jeremy

 

[pbaldy]

I would use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

 

[j.smith1981]

Ok I have amended it to this:

Private Sub file_location_BeforeUpdate(Cancel As Integer)
If filed_bool.Value = -1 And Len(Me.file_location & vbNullString) = 0 Then
MsgBox "You must enter a 'File Cabinet Location"
Cancel = True
End If
End Sub

The idea is to make sure that filed_bool equals ticked (which is -1) then if it is, check that the file_location text box equals no character length, then dont allow it to be inputted to the data sheet.

I think it might be to do with addressing the filed_bool field (the variable name I have given it on the form). I thought it might be to do with Form.filed_bool but that doesnt work either.

Can someone help me please?

Thanks ever so much in advance,
Jeremy.

 

[pbaldy]

Again, I would use the update event of the form, not the textbox. The textbox update event will only fire if the user changes the value. You want it tested no matter what.

 

[j.smith1981]

Ok I have one that works, as a isNull value.

But this simply doesnt behave the way I want it to.

I have gone for the Len one aswell and that works the same as I have put with isNull (they both work on comparison).

But you have to go into the textbox for this to work obviously, but what if the user didnt go into that textbox and just click the add record button, how would I accommodate for this?

Thanks again,
Jeremy.

 

[j.smith1981]

Ok I have come to the conclusion this isnt what I was wanting.

I have therefore put this in place:

Private Sub Form_Load()
Me.filed_bool.Value = "0"
file_location.Visible = False
End Sub

This makes the form hide the file_location textbox.

Then:

Private Sub filed_bool_Click()
If (Me.filed_bool.Value = "-1") Then
file_location.Visible = True
Else
file_location.Visible = False
End If
End Sub

Show the 'file_location' textbox when filed_bool is checked.

But when it comes to the following which seems to work:

Private Sub file_location_BeforeUpdate(Cancel As Integer)
If IsNull(file_location) Then
MsgBox "The file location cannot be null if the Filed is checked!"
Cancel = True
End If
End Sub

But only if the user goes into the textbox, how can I make this work on the add_record button, that either updates the existing record or adds a new one?

This button was created when I went through a wizard like setup to add records to the database.

Again any help is greatly appreciated, thanks,
Jeremy.

 

[pbaldy]

I'm not sure how to say this any clearer. You don't want the before update of any individual textbox. You want the before update event of the form itself. That will catch all controls/fields, whether the user goes into them or not. The code will start with:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 

[boblarson]

Jeremy:

Listen to what Paul is telling you. He knows what he's talking about.

 

[j.smith1981]

Apologies Paul.

But I dont see why I cant reference to the name of a textbox and then apply if the filed_bool tickbox is ticked, if the textbox for the location ie filed_location or which ever it was above (cant remember off hand what its called).

But surely this should be possible, as the above you stated would be for the whole form wouldnt it?

Or am I being a bit (excuse the pun) being a bit of a dofus, sorry just not the best when it comes to VBA, PHP and MySQL are definately my strong points, with visual basic I tend to stay clear of this but I cant this time arraggggh lol.

Thanks in advance for any feed back, perhaps examples would help if you can please, thats probably the best way for me to understand it.

Regards,
Jeremy.

 

[pbaldy]

It isn't so much what you're doing as where you're doing it. You can reference the name of a textbox, but you keep using the update event of a textbox, and I'm telling you to use the update event of the form. Look at the form's properties, not the properties of an individual textbox.