BeforeUpdate to Validate form

[simonmarkjones]

I want to validate my form using a BeforeUpdate event.

However now that i call my code with a beforeupdate it wont let me go
to next or previous records.

What code should i put in o allow me to do thi??

[simonmarkjones]

Ive tried putting in

DoCmd.GoToRecord , , acNewRec

If all the checks are complete

But i get a runtime error of 2105

Saying you can not go to the sepecified record

[Keith]

<si************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I want to validate my form using a BeforeUpdate event.

However now that i call my code with a beforeupdate it wont let me go
to next or previous records.

What code should i put in o allow me to do thi??

What precisely do you mean by "validate my form"?

Keith.

[simonmarkjones]

I am checking each text filed in turn to see if it has been filled out.

Once they have all been filled i create another value (a receipt
number) which i store in another textbox which is not being validated
in this.

All my code works except when i call it from BeforeUpdate it validates
creates the new value but doesnt go to the next form/record. And its
getting pretty anoying.

[Keith]

<si************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I am checking each text filed in turn to see if it has been filled out.

Once they have all been filled i create another value (a receipt
number) which i store in another textbox which is not being validated
in this.

All my code works except when i call it from BeforeUpdate it validates
creates the new value but doesnt go to the next form/record. And its
getting pretty anoying.

Well without having seen your code I'm guessing that you're trying to go to
a new/next record from the Before Update event but that is illogical
(captain) because you have to update the current record before you can go to
a new one. Have you tried your validation code in the Before Update event
(as you have now) but have your record navigation code in the After Update
event? Your record should update as a result of navigating away from it.

HTH - Keith.
www.keithwilby.com

[simonmarkjones]

Yeah i've put
DoCmd.GoToRecord , , acNext
and DoCmd.GoToRecord , , acNewRec
into afterupdate and all that happens is my form just sits there and
doesnt go to the new record.

Never done much programming before

[Keith]

<si************@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Yeah i've put
DoCmd.GoToRecord , , acNext
and DoCmd.GoToRecord , , acNewRec
into afterupdate and all that happens is my form just sits there and
doesnt go to the new record.

Never done much programming before

The event(s) must not be firing. How and where are you committing the
record?

[simonmarkjones]

I want to call a function which does this when the next record button
is pressed (calling it from before update)

if textboxes are empty then

Message box you must fill text box

else

comit the record and move to next record

[Keith]

<si************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I want to call a function which does this when the next record button
is pressed (calling it from before update)

I appreciate what you are trying to achieve but how about answering my
question, where and when are you committing the record? Starting a new
thread every time you get frustrated will just irritate people.

[simonmarkjones]

Sorry i didnt realise.

Only just started using newsgroups.

And thats what i cant do. Dont know how to commit the record. Or where
appart from what ive already stated.

[Keith]

<si************@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Sorry i didnt realise.

Only just started using newsgroups.

And thats what i cant do. Dont know how to commit the record. Or where
appart from what ive already stated.

It might be an idea to post your code. The record should commit when you
navigate away from it. There's something stopping this from happening. Why
don't you get this functionality for free by making the fields mandatory at
table level?

[simonmarkjones]

As im new to this whole programming and access stuff (Only started
wednesday i think) Im not sure if i know what im doing.
But dont think i want to do it at table level because i want to add
more to my form in certain textboxes provided the rest of the for has
been completed. And also want to tell the ser which bits they havent
filled out yet. The bit i need to be able to do is towards the bottom
of this pretty long winded hacked together function. Thankyou for your
time on this.

'************************************************* *********************************************
'Main function which checks each textbox in turn to see if the form has
been completed
'If a textbox hasn't been filled out the user is informed what is
wrong.
'Once form is complete then Receipt Number is created along with
Username and current date.
'Then move onto next form
Function CreateReceiptNo()
Dim templateReceiptNo, addToEnd, temporaryReceiptNo, newReceiptNo As
String
Dim templateReceiptNoLength, addToEndLength, newLength As Integer

Form_form1.PaymentMethod.SetFocus

If Form_form1.PaymentMethod.Text = "" Then
MsgBox "Payment Method is required", vbOKOnly, "Required Field"
Form_form1.PaymentMethod.SetFocus
Exit Function

Else
Form_form1.PaymentAmount.SetFocus
End If
If Form_form1.PaymentAmount.Text = "" Then
MsgBox "Payment Amount is required", vbOKOnly, "Required Field"
Form_form1.PaymentAmount.SetFocus
Exit Function
Else
Form_form1.CostCode.SetFocus
End If
If Form_form1.CostCode.Text = "" Then
MsgBox "Cost Code is required", vbOKOnly, "Required Field"
Form_form1.CostCode.SetFocus
Exit Function
Else
Form_form1.Address.SetFocus
End If
If Form_form1.Address.Text = "" Then
MsgBox "Address is required", vbOKOnly, "Required Field"
Form_form1.Address.SetFocus
Exit Function
Else '** All text boxes have data - create Receipt Numer, Username &
Date **

Form_form1.txtReceiptNo.Enabled = True
Form_form1.txtReceiptNo.SetFocus
If (Form_form1.txtReceiptNo.Text = "") Then ' If this record doesnt
have a receipt number then create one
templateReceiptNo = "C - 00000" ' Set the variable with Receipt
Number template
Form_form1.txtReceiptNo.SetFocus

addToEnd = DMax("[LastUsedNumber]", "tableLastUsedNumber")
templateReceiptNoLength = Len(templateReceiptNo)
addToEndLength = Len(addToEnd)
newLength = templateReceiptNoLength - addToEndLength
temporaryReceiptNo = Left(templateReceiptNo, newLength)
newReceiptNo = temporaryReceiptNo & addToEnd
Form_form1.txtReceiptNo.Text = newReceiptNo
Form_form1.txtReceiptNo.SetFocus

IncrementLastUsedNumber ' Call function to increment the Value to
be used for the next receipt number

Form_form1.PaymentMethod.SetFocus
Form_form1.txtReceiptNo.Enabled = False

GetUserName 'Call function to store the users logon name

'**Get the date today save in variable then output on the form**
todaysDate = Date
Form_form1.txtDateInserted.Enabled = True
Form_form1.txtDateInserted.SetFocus
Form_form1.txtDateInserted.Text = todaysDate
Form_form1.PaymentMethod.SetFocus
Form_form1.txtDateInserted.Enabled = False

'************* FORM IS NOW COMPLETE SO MOVE TO NEXT RECORD
??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)

Else ' Receipt number has been filled out already

Form_form1.PaymentMethod.SetFocus
Form_form1.txtReceiptNo.Enabled = False

'************* FORM MUST ALREADY BE COMPLETE SO MOVE TO NEXT
RECORD ??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)

End If ' Ends the if which checked there was a receipt number
End If ' Ends the if which checked the Address field was filled then
created Receipt Number, Username and Date
End Function

[simonmarkjones]

Since the last post i have modified my code slightly... It all works
fine when i put it on my own form button.
However if i use the standard access form navigation standard buttons
and call the code from BeforeUpdate then i checks the form outputs the
username, date and receipt number, but doest move to the next form.
(Note it does move to the next form if i use a button and call from on
click).......
'************************************************* *********************************************
'Main function which checks each textbox in turn to see if the form has
been completed
'If a textbox hasn't been filled out the user is informed what is
wrong.
'Once form is complete then Receipt Number is created along with
Username and current date.
'Then move onto next form
Function CreateReceiptNo()
If IsNull(Form_form1.PaymentMethod) Then
MsgBox "Payment Method is required", vbOKOnly, "Required Field"
Form_form1.PaymentMethod.SetFocus
Exit Function
ElseIf IsNull(Form_form1.PaymentAmount) Then
MsgBox "Payment Amount is required", vbOKOnly, "Required Field"
Form_form1.PaymentAmount.SetFocus
Exit Function
ElseIf IsNull(Form_form1.CostCode) Then
MsgBox "Cost Code is required", vbOKOnly, "Required Field"
Form_form1.CostCode.SetFocus
Exit Function
ElseIf IsNull(Form_form1.Address) Then
MsgBox "Address is required", vbOKOnly, "Required Field"
Form_form1.Address.SetFocus
Exit Function
Else
'** All text boxes have data - create Receipt Numer, Username & Date **
Form_form1.txtReceiptNo.Enabled = True 'Stop the textbox being greyed
out
If IsNull(Form_form1.txtReceiptNo) Then ' If this record doesnt
have a receipt number then create one

StoreReceiptNo ' Function
IncrementLastUsedNumber ' Call function to increment the Value to
be used for the next receipt number

Form_form1.PaymentMethod.SetFocus
Form_form1.txtReceiptNo.Enabled = False

GetUserName 'Call function to store the users logon name

'**Get the date today save in variable then output on the form**
todaysDate = Date
Form_form1.txtDateInserted.Enabled = True
Form_form1.txtDateInserted.SetFocus
Form_form1.txtDateInserted.Text = todaysDate
Form_form1.PaymentMethod.SetFocus
Form_form1.txtDateInserted.Enabled = False

'************* FORM IS NOW COMPLETE SO MOVE TO NEXT RECORD
??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)
MsgBox "Details Updated", vbOKOnly, "DONE"
CreateNextForm
Else ' Receipt number has been filled out already

Form_form1.PaymentMethod.SetFocus
Form_form1.txtReceiptNo.Enabled = False
MsgBox "Nothing Updated", vbOKOnly, "DONE"
'************* FORM MUST ALREADY BE COMPLETE SO MOVE TO NEXT
RECORD ??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)
'CreateNextForm
End If ' Ends the if which checked there was a receipt number
End If ' Ends the if which checked the Address field was filled then
created Receipt Number, Username and Date
End Function

'************************************************* *********************************************
'Function which looks at tableLastUsedNumber and adds 1 to this
value.
'Then saves over the original value.
'Used to increment the Receipt number eg C-00001,C-00002 etc.
'This code just increases the last digit.
Function IncrementLastUsedNumber()
Dim myNumber As Integer

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber") + 1
Dim strSQL As String
strSQL = "UPDATE tableLastUsedNumber SET LastUsedNumber = " &
myNumber & " "

'DoCmd.SetWarnings False
'DoCmd.RunSQL (strSQL)
'DoCmd.SetWarnings True

'Updates the database without having to swich the warnings off as
old code did above
CurrentDb.Execute strSQL

End Function
'************************************************* *********************************************
Function StoreReceiptNo()
Dim templateReceiptNo, addToEnd, temporaryReceiptNo, newReceiptNo
As String
Dim templateReceiptNoLength, addToEndLength, newLength As Integer

templateReceiptNo = "C - 00000" ' Set the variable with Receipt
Number template
Form_form1.txtReceiptNo.SetFocus

addToEnd = DMax("[LastUsedNumber]", "tableLastUsedNumber")
templateReceiptNoLength = Len(templateReceiptNo)
addToEndLength = Len(addToEnd)
newLength = templateReceiptNoLength - addToEndLength
temporaryReceiptNo = Left(templateReceiptNo, newLength)
newReceiptNo = temporaryReceiptNo & addToEnd
Form_form1.txtReceiptNo.Text = newReceiptNo
' DONT NEED Form_form1.txtReceiptNo.SetFocus

End Function
'************************************************* *********************************************
Function CreateNextForm()
MsgBox "Creating new record", vbOKOnly, "DONE"
DoCmd.GoToRecord , , acNext

End Function

[simonmarkjones]

Ok here is all my code and it still doesnt work :( Please help
'************************************************* *********************************************
' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long

Const NoError = 0 'The Function call was successful

Function GetUserName()
' Buffer size for the return string.
Const lpnLength As Integer = 255

' Get return buffer space.
Dim status As Integer

' For getting user information.
Dim lpName, lpUserName As String

' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)

' Get the log-on name of the person using product.
status = WNetGetUser(lpName, lpUserName, lpnLength)

' See whether error occurred.
If status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be
used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
' An error occurred.
MsgBox "Unable to get the name."
End
End If
' Display the name of the person logged on to the machine.
'MsgBox "The person logged on this machine is: " & lpUserName
'Return
If IsNull(Form_form1.lbName) Then
Form_form1.lbName.SetFocus
Form_form1.lbName.Text = lpUserName
Else
' Do Nothing (Don't put username in box)
End If
End Function

'************************************************* *********************************************
'Main function which checks each textbox in turn to see if the form has
been completed
'If a textbox hasn't been filled out the user is informed what is
wrong.
'Once form is complete then Receipt Number is created along with
Username and current date.
'Then move onto next form
'Function CreateReceiptNo()
Function checkFormIsComplete()

If IsNull(Form_form1.PaymentMethod) Then
MsgBox "Payment Method is required", vbOKOnly, "Required Field"
Form_form1.PaymentMethod.SetFocus
' Cancel = True
Exit Function
ElseIf IsNull(Form_form1.PaymentAmount) Then
MsgBox "Payment Amount is required", vbOKOnly, "Required Field"
Form_form1.PaymentAmount.SetFocus
Exit Function
ElseIf IsNull(Form_form1.CostCode) Then
MsgBox "Cost Code is required", vbOKOnly, "Required Field"
Form_form1.CostCode.SetFocus
Exit Function
ElseIf IsNull(Form_form1.Address) Then
MsgBox "Address is required", vbOKOnly, "Required Field"
Form_form1.Address.SetFocus
Exit Function
Else
'** All text boxes have data - create Receipt Numer, Username & Date **
enableTextBoxes ' Enable the 3 text boxes so that they can be updated
If IsNull(Form_form1.txtReceiptNo) Then ' If this record doesnt
have a receipt number then create one

StoreReceiptNo ' Function
IncrementLastUsedNumber ' Call function to increment the Value
to be used for the next receipt number

GetUserName 'Call function to store the users logon name

GetTodaysDate

'************* FORM IS NOW COMPLETE SO MOVE TO NEXT RECORD
??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)
MsgBox "Details Updated", vbOKOnly, "DONE"
'CreateNextForm
Else ' Receipt number has been filled out already

MsgBox "Nothing Updated", vbOKOnly, "DONE"
'************* FORM MUST ALREADY BE COMPLETE SO MOVE TO NEXT
RECORD ??????Don't know how yet
'********* Possible something like
'***** DoCmd.GoToRecord , , acNext ' go nxt record (Creates
new page)
'CreateNextForm
End If ' Ends the if which checked there was a receipt number
End If ' Ends the if which checked the Address field was filled then
created Receipt Number, Username and Date
dissableTextBoxes ' Dissable the 3 textboxes
End Function
'************************************************* *********************************************
'Function which looks at tableLastUsedNumber and adds 1 to this
value.
'Then saves over the original value.
'Used to increment the Receipt number eg C-00001,C-00002 etc.
'This code just increases the last digit.
Function IncrementLastUsedNumber()
Dim myNumber As Integer

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber") + 1
Dim strSQL As String
strSQL = "UPDATE tableLastUsedNumber SET LastUsedNumber = " &
myNumber & " "

'DoCmd.SetWarnings False
'DoCmd.RunSQL (strSQL)
'DoCmd.SetWarnings True

'Updates the database without having to swich the warnings off as
old code did above
CurrentDb.Execute strSQL

End Function
'************************************************* *********************************************
Function StoreReceiptNo()
Dim templateReceiptNo, addToEnd, temporaryReceiptNo, newReceiptNo
As String
Dim templateReceiptNoLength, addToEndLength, newLength As Integer

templateReceiptNo = "C - 00000" ' Set the variable with Receipt
Number template
Form_form1.txtReceiptNo.SetFocus

addToEnd = DMax("[LastUsedNumber]", "tableLastUsedNumber")
templateReceiptNoLength = Len(templateReceiptNo)
addToEndLength = Len(addToEnd)
newLength = templateReceiptNoLength - addToEndLength
temporaryReceiptNo = Left(templateReceiptNo, newLength)
newReceiptNo = temporaryReceiptNo & addToEnd
Form_form1.txtReceiptNo.Text = newReceiptNo
End Function
'************************************************* *********************************************
Function CreateNextForm()
MsgBox "Creating new record", vbOKOnly, "DONE"
DoCmd.GoToRecord , , acNext
End Function
'************************************************* *********************************************
'Function Enables all the textboxes
Function enableTextBoxes()
Form_form1.lbName.Enabled = True
Form_form1.txtReceiptNo.Enabled = True 'Stop the textbox being
greyed out
Form_form1.txtDateInserted.Enabled = True
End Function
'************************************************* *********************************************
'Function Dissables all the textboxes
Function dissableTextBoxes()
Form_form1.PaymentMethod.SetFocus ' Sets the focus back to the form
so textboxes can be dissabled
Form_form1.lbName.Enabled = False

Form_form1.txtReceiptNo.Enabled = False

Form_form1.txtDateInserted.Enabled = False
End Function
'************************************************* *********************************************
'Function creates todays date
Function GetTodaysDate()
todaysDate = Date
Form_form1.txtDateInserted.SetFocus
Form_form1.txtDateInserted.Text = todaysDate
End Function

[Keith]

si************@gmail.com wrote:

Ok here is all my code and it still doesnt work :( Please help

<snip>

This is *really* complicated for what you're trying to achieve. I'd be
inclined to use the built-in validation at table/field level. If the
user gets stuck in a "you did not fill in such and such" they can always
use the ESC key.

Sorry it's probably not the answer you wanted but I just can't see the
advantage in re-inventing this particular wheel.

Regards,
Keith.

[simonmarkjones]

Ok cool thanks.

Wasnt sure if i was doing the right thing or not as i've only just
started using acces last week.

Cheers