Help Please

[DeanH]

I have a table with various data input boxes. I have created a button (using the button wizard) which adds the record. However the only problem with this is, if some fields are left blank by the user and the button is pressed the record is still added. Does anyone have an idea how i can stop this happening so the record will only be saved if there is some data is in all the input boxes.

Validation Method?

I hope i have explained this clearly enough - i think i need sleep

Thanks in Advance

 

[bjackson]

insert some code before the save operation that checks
the text boxes that you want values in to see if they have values

eg

if isnull(me![txtboxname1]) then
beep
msgbox "put your message here",vbokonly,"Required information"
me![txtboxname].setfocus
exit sub
end if

 

[neileg]

Mark the fields as 'required' in the table design?

 

[DeanH]

How do i make the fields 'required'?

 

[DeanH]

Thank you all its fixed now. When i click the button it still says its added the record but unless data is entered in all fields it doesnt actually submit the record to the table.

 

[directormac]

Dean - It's great that you're making progress, but that last line of yours makes me a little twitchy... if your DB says "record added" but doesn't, how does the user know that (1) they're doing it wrong and (2) the info they're trying to save hasn't been?

--Cautious Mac

 

[bjackson]

Dean

is Your form bound to any table or are
you saving records from an unbound form with unbound text boxes

 

[DeanH]

OK, The Form is bound to a table named 'TblBookings'. The reason it says record added when the user clicks the 'Add Record' Button is because i created a message box to do this The code i have used in the 'Add Record' button is shown below,

Private Sub addrecord_Click()
On Error GoTo Err_addrecord_Click

MsgBox "Booking Details Added", vbExclamation, "Addrecord"
DoCmd.GoToRecord , , acNewRec
DoCmd.Close
DoCmd.OpenForm "welcome screen"
DoCmd.Maximize

Exit_addrecord_Click:
Exit Sub

You are right though MAC this could cause confusion because the user / receptionist could think they have added the booking when in reality it hasn't.

I have no idea on how to stop this however except to use method descibed earlier by bjackson.

Any Ideas?

 

[directormac]

If you've already got the checks in place to make sure that a required field isn't blank, how about a different message box that says something to the effect of "hey, ya left required fields blank--fill 'em in and try again..."

--Feedback Mac

 

[neileg]

Your message box appears before the record is added! You use the New Record function to force the save. Why don't you just save the record, and then pop-up your message. If it fails, then it will switch to your error handling and not display the message.

 

[DeanH]

Can someone explain the following? This is the coding i have now used for the button, yet when it is pressed it STILL enters data even though in theory it shouldnt.

Private Sub addrecord_Click()
On Error GoTo Err_addrecord_Click

If IsNull(Me![Customer ID]) Then
Beep
MsgBox "Please enter the Customer ID", vbOKOnly, "Required information"
Me![Customer ID].SetFocus
Exit Sub
End If

If IsNull(Me![Date Of Departure]) Then
Beep
MsgBox "Please enter the Date of Departure", vbOKOnly, "Required information"
Me![Date Of Departure].SetFocus
Exit Sub
End If

If IsNull(Me![Room Number]) Then
Beep
MsgBox "Please enter the Room Number", vbOKOnly, "Required information"
Me![Room Number].SetFocus
Exit Sub
End If

If IsNull(Me![Hotel ID]) Then
Beep
MsgBox "Please enter the Hotel ID", vbOKOnly, "Required information"
Me![Hotel ID].SetFocus
Exit Sub
End If

DoCmd.GoToRecord , , acNewRec
MsgBox "Booking Details Added", vbExclamation, "Addrecord"
DoCmd.Close
DoCmd.OpenForm "welcome screen"
DoCmd.Maximize

Exit_addrecord_Click:
Exit Sub

Err_addrecord_Click:
MsgBox Err.Description
Resume Exit_addrecord_Click

End Sub

Now to my understanding this shouldn't submit the record if data is missing but for some reason it still is :confused

Is there another line of coding i must put in to stop it adding the record if one of the fields is needed?

Please help before i find a bridge

 

[DeanH]

P.S I tried using the 'Required' property but for it kept bringing up errors.

 

[directormac]

Are you getting records added after the various error message boxes you have coded appear? Or are the error boxes not appearing in spite of blank fields?

The reason I ask is that Access distinguishes (often is seemingly bizarre ways) between a field with 0 value, a field that is
"blank" (value ="") and a Null value. If the controls in question receive focus at some point before you call your sub, it may be that Access is reading them as blanks and assigning "" to the underlying fields, which might then allow them to pass your IsNull testing. Check Access help for the Nz function, you might find it useful.

--Nullish Mac

 

[DeanH]

The error boxes are coming up promting the user to type the data in the correct box. However the record is still being added with the blank data.

 

[bjackson]

Dean
have you tried stepping through your code to see if it is
behaving how you think it should

if the form is closed no matter what code goes in your
save button the record will still be saved as is
because it is bound,that is why you need to set
the required property in the table


i use a method where the users only exit from the form
is through a command button and unless they use that
button the form remains open

 

[bjackson]

Dean

a suggestion that might fit what you want to do

1.Put a command button on your form that is the only way of closing the form
2.Set its Caption to "Close"
3.Change Its Caption To "Close And Save" as soon as the form
becomes dirty
4.put your addrecord code on this button adding

if me![commandbutton].caption="Close" then
'no data has been added
OkToClose=true
docmd.closeyour form
else

run your code in here
OkToClose=true
docmd.closeyour form
end if

that way the user will have to fill in all boxes before the form
will close. and they wont get all the access error messages or
prompts,the down side is all the fields will have to be filled
in before the form will close so you have to hope the users
know what to fill the text boxes with

this code comes from developers handbook and it works
quite well,you have to disable the close x on your form and
also whether or not you want to allow the form to be
minimised

add a form module variable
dim OkToClose as boolean

in the load event for your form
OkToClose = False

in the unload event
Cancel = Not OkToClose

 

[neileg]

All this code is good, but there are two fundamental issues that you need to sort.

1) You need to understand the difference between nulls, zero length strings and blanks.

2) If setting the Required property on the fields causes you errors, you need to resolve the reason for these errors, not just revert to not required. Using the required property will make Access pop up error messages that replicate the validation you are trying to code.

Hard though it may be to accept, sometimes you have to stop solving your solution and go back to solving the original problem by a different method.

 

[DeanH]

Thank you all for your help and input - the problem is sorted now.