 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

January 12th, 2005, 02:50 PM
|
Registered User
|
|
Join Date: Jan 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
New to Code...Help!
I am new to using code in Access, always used features in design mode. I have managed to pull a couple things off and make my way through some of this code, but one thing is holding me up. I have a form "frmISMci" that I have inserted a "add new record button" on. This has the effect of saving the current record and adding a new record. The only thing is, before the record is saved and a new record is added I would like an event procedure that would require certain fields to be "is not null" before the record is saved and in the case that certain fields are null and the record cannot yet be saved a message box esplain to the user that they must input a value for a given field. So for the save button there needs to be an event procedure "On click" that essentially tells the user the must fill out the given fields before a record can be added and the current saved. I realize I can use the required option for the fields, but that will only tell the user "you cannot go to the specified record" if left blank. Can anyone point me in the right direction. Thanks so much. -Justin
|

January 12th, 2005, 02:55 PM
|
Registered User
|
|
Join Date: Jan 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Here is an example of what I have so far. This has an effect of telling the user that the sum of all the appoprtiation fields must = 100%, but it doesnt prevent the user from saving the record. It simply pops the message box up and then saves the record. This is the code for the on click event procedure of my add new record button:
Private Sub Command168_Click()
If ([GAppropriation] + [AAppropriation] + [LAppropriation] + [IAppropriation] + [SAppropriation] + [WAppropriation] + [OAppropriation]) <> 100 Then
MsgBox "Total % Appropriation must equal 100%"
Exit Sub
End If
On Error GoTo Err_Command168_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command168_Click:
Exit Sub
Err_Command168_Click:
MsgBox Err.Description
Resume Exit_Command168_Click
End Sub
|

January 12th, 2005, 08:42 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Hi Justin,
Here's a version of the null check piece.
The cmdSave_Click event calls a function called ValidateControls. ValidateControls passes the name of all the textboxes on your form, plus a "friendly" name (the name that will appear in a message box) to a function called CheckForNulls using a parameter array. The parameter array allows you to send a variable number of controls to be checked for null. CheckForNulls then checks to see if one or multiple text boxes contain a null value, and returns the appropriate message to the Validate controls function, which in turn returns the message to the cmdSave_Click event, where a message box is displayed. If all the text boxes have been filled in appropriately, cmdSave_Click simply advances your form to a new record.
Just replace the textbox names (e.g., "txtName") and "friendly" names (e.g., "Name") in the ValidateControls function with the names of your text boxes and a user-friendly name for them (possibly your label caption). The ValidateControls function and the CheckForNulls function get pasted right in your form module along with the cmdSave_Click event procedure. You don't need to change anything in the CheckForNulls function.
Private Sub cmdSave_Click()
' Check for null values
strOK = ValidateControls
If Len(strOK) > 0 Then
MsgBox strOK, vbInformation, "Required Information Missing"
Exit Sub
End If
'Do your percentage check...
If ([GAppropriation] + etc...
' If it all cecks out, save record
DoCmd.GoToRecord , , acNewRec
End Sub
Private Function ValidateControls() As String
Dim strMsg As String
' Text box name, user-friendly name
strMsg = CheckForNulls(Me, _
"txtName", "Name", _
"txtAddress", "Address", _
"txtPhone", "Phone")
ValidateControls = strMsg
End Function
Public Function CheckForNulls(frm As Form, _
ParamArray varListOfControlsAndLabels()) As String
Dim intI As Integer
Dim ctl As Access.Control
Dim strMsg As String
Dim strFirstNullControl As String
Dim fNullFound As Boolean
Dim fMultipleNullsFound As Boolean
For intI = LBound(varListOfControlsAndLabels) _
To UBound(varListOfControlsAndLabels) Step 2
Set ctl = frm(varListOfControlsAndLabels(intI))
If ctl.ControlType = acTextBox Then
fNullFound = IsNull(ctl)
End If
If fNullFound Then
strMsg = strMsg & varListOfControlsAndLabels(intI + 1) & vbCrLf
'Store the first null Control, to set focus to it later.
If Len(strFirstNullControl) = 0 Then
strFirstNullControl = varListOfControlsAndLabels(intI)
Else
'More than one null was found
fMultipleNullsFound = True
End If
End If
Next intI
If Len(strMsg) > 0 Then 'at least one null was found
If Not fMultipleNullsFound Then
'There was only one null Control found.
'Use single case in message. Trim trailing CrLf.
strMsg = Left(strMsg, Len(strMsg) - 2)
strMsg = """" & strMsg & """ is a required entry. " _
& "Please fill in a value for this item."
Else
'There was more than one null Control found.
' Use plural case in message.
strMsg = "The following required entries were left blank:" _
& vbCrLf & vbCrLf & strMsg & vbCrLf _
& "Please fill in values for these items."
End If
frm(strFirstNullControl).SetFocus
End If
CheckForNulls = strMsg
End Function
|

January 12th, 2005, 08:51 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Or for a simpler approach you could go with this sort of thing:
Private Sub cmdSave_Click()
If IsNull(Me.[GAppropriation]) Or _
Len(Me.([GAppropriation]) = 0 Then
MsgBox "[GAppropriation]Must be Filled In Before Proceeding"
[GAppropriation].SetFocus
Exit Sub
End if
DoCmd.GoToRecord , , acNewRec
End Sub
HTH,
Bob
|

January 13th, 2005, 08:54 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
For the simplest approach, design your tables so that those fields are required.
mmcdonal
|

January 13th, 2005, 09:03 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
If you still want to avoid code you can open the table in design view. For each field you do not want blank, set the following properties:
Required: YES
Allow Zero Length: NO
Now when the user tries to save a record and move on, Access won't let them. The good news is that no coding is required. The bad news is that Access's built-in error messages are very cryptic. Your users may not understand exactly what the error message is telling them.
Try it.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|

January 13th, 2005, 03:36 PM
|
Registered User
|
|
Join Date: Jan 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Bob, Thank you so much for your reply. This seems like some great code and I am going to move forward with trying to implement it. Thanks for taking the time to help. Greg, I had already set the fields to required, but like you said the reason I need this code is because the error message is in fact very cryptic. I needed to tell the user exactly what they had done wrong. Also, do you all hard code your buttons or use button wizards. Just curious how the gurus do it. I guess I will pick up a VB book. Thank you all for your help and suggestions.
-Justin
|

January 13th, 2005, 03:57 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
I usually use the button wizard to create the button and insert the "seed code" then I alter the code to do exactly what I want. It saves a few keystrokes. For buttons that do things "out of the ordinary," I code it myself from scratch without the Wizard.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|

January 13th, 2005, 07:25 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Hi Justin,
I don't use the Wizards, but then I have a lot of boiler plate code I've accumulated in code libraries over time that I just reference or cut and paste a lot. The CheckForNulls routine, for example, is a function that I keep in a library .mda add-in and can reference from any application I'm working on.
Since you're book shopping, however, I wanted to make a recommendation. Check out http://www.developershandbook.com/. Best Access VBA book around with tons of sample code. You'll love it if the coding bug has bit, as it appears it has.
Best,
Bob
|
|
 |