Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2014
    Posts
    4

    Unanswered: Help programming validation rules to button

    So I just wanted to start off by apologizing if this is posted in the wrong place or if im not using proper forum etiquette, im a complete noobie here.

    that being said, here's a little background. I am building a database using MS Access that is meant to track inventory for a tooling shop. the database will have basic information on specific tooling, and inventory levels for replacement components for the tooling. The main table has all the information and includes a calculated field that concatenates the Tool number, a 3 letter disruption, the detail number, and the revision level (eg. WY201.PBU.233.A1) This field is used as the specific component ID for each piece in the tooling assembly. Typically this would be indexed, or set as the primary key but since its a calculated field access wont allow it.

    In addition to the table is a form for end users to input new components into the main table. this form also has a calculated field for the Component ID based on the basic information on the form. I have also added a few buttons on the form for basic functionality such as "Clear entries", "save Entries", and "close form". Any part of the user interface, such as the form, needs to be absurdly simple and straight forward since the users are not very tech savvy.

    On to the issue at hand.... the problem im having is in the programming for the "save entry" button on the form. I have attempted to make the button validate that the information on the form does not create a duplicate entry into the main table. it does this by comparing the calculated control for the component ID on the form to the calculated field on the table for the component ID. if the entry already exists then a message box should appear indicating a duplicate entry, if not, the entry should be saved to the table and then clear the form for a second entry. here is the actual VBA code...

    Private Sub Save_Component_Entry_Click()
    On Error GoTo Save_Component_Entry_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="Close Form" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessservices/2009/11/forms">
    ' _AXL:<Statements><Action Name="CloseWindow"/></Statements></UserInterfaceMacro>
    On Error Resume Next
    If Not IsNull(DLookup("Component ID", "Die Component Master BOM", "& [Add Die Component to Master BOM].[Component_ID] = Component ID")) Then
    Beep
    MsgBox "Component already exists in database. Please double check entry, or add new component revision level.", vbOKOnly, "Duplicate Entry Error"
    DoCmd.CancelEvent
    Exit Sub
    Else
    DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "Tool_Number"
    If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
    End If


    the problem is that when I try and test the code, no matter what entry I attempt to use, whether it actually exists in the main table or not, the message box appears. Then once the form is closed or I click back to design view the entry is saved into the main table anyway.

    im at the end of my rope here since ive been working with this for 3 days now. so any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, this is the right place, but please do not post multiple, identical threads. This is against the rules on every forum I've ever visited; the duplicate thread has been deleted.

    Second, I have no idea where you got this code from, but it appears to be a mixture of VBA code and an embedded macro, which isn't done.

    Thirdly, you don't do this type of validation in the OnClick event of a Command Button; there is no canceling for that event. Normally you'd use the BeforeUpdate event for the Control into which the Component ID is entered, but since the Component ID is calculated from several Controls, you'll have to use the Form_BeforeUpdate, because it executes just prior to a Record being saved and it can be canceled, if the validation fails. Only use the 'save' button to start the save process or save and go to a new record, if that's your need; leave the validation to Form_BeforeUpdate!

    Finally, using DLookup to see if a Record exists with a certain Field (in the underlying Table) whose data matches the same Field in the New Record on a Form, really isn't logical; what you're saying, basically, is,

    'What is the Component ID when the Component ID equals the Component ID!'

    Better to use DCount for this kind of thing. The syntax, like that of DLookup(), varies by Datatype of the Field in the Criteria Parameter of the Function.

    For a Text Field:

    If DCount("*", "TableName", "[IdFieldInTable] = '" & Me.IdControlName & "'") > 0 Then

    For a Numeric Field:

    If DCount("*", "TableName", "[IdFieldInTable] = " & Me.IdControlName) > 0 Then

    Welcome to dBforums!

    Linq ;0)>
    Last edited by Missinglinq; 07-08-14 at 18:45.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2014
    Posts
    4
    thanks for the insight Linq. Sorry about the double post, I didn't realize the first one posted.

    as for the code, I started off trying to do a macro then converted it to VBA and started going from there. As I said, Complete Newbie, and I don't have much experience in VBA. I have now gotten about chest deep into VBA and im learning how to swim. so im sure i'll be spending a lot of time on this forum asking for some help (if I cant find the information elsewhere)

    your code seems to make a lot more sense than mine, I haven't gotten it to work just yet but I have made progress (been working on this database for over a week now). Getting this form to work the way I intend seems to be my biggest struggle. I have converted all my controls to unbound controls which has solved the issue of unwanted entries being saved into the table prematurely. My current issue is trying to establish a cascading combo box to update a list of available part numbers for a specific tool. here's my VBA

    Private Sub Tool_Number_AfterUpdate()
    Me!Forms![Add Component].Controls!Part_Number.RowSource = "SELECT TMPart_Number" & _
    "FROM ToolMatrix " & _
    "WHERE TMTool_Number = " & Nz(Me.Tool_Number) & _
    "ORDER BY Part_Number"
    End Sub

    I've tried "Me.cboPart_Number.RowSource =" and several variations in between, some of them wont even compile, while the others that do wont populate my combo box.

    any suggestions?

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    On the button click, I use a IsValidForm test to check the needed fields:

    Code:
    Private Function IsValidForm() As Boolean
    Dim vMsg
    Select Case True
       Case IsNull(txtFile)
          vMsg = "You must select an Import File"
       Case IsNull(cboPostType)
          vMsg = "You must select a Target Table"
    End Select
    
    If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
    IsValidForm = vMsg = ""
    End Function

  5. #5
    Join Date
    Jul 2014
    Posts
    4
    Progress!! I have gotten the cascading combo box to work properly! im not liking how all my posts have to be "approved" or whatever before they show. I keep posting things only to be stuck waiting for a full day for a response. Anyways...

    here's whats left... on my form I have roughly a dozen controls (all switched back to bound controls), a calculated control, and 3 buttons (Clear Contents, Save Entry, Close) the "clear contents" & "Close" buttons work wonderfully. The "save Entries" button is what im having issues with.

    here is what im struggling with....

    The calculated control concatenates several other controls to form our unique Component ID, or our "serial Number". This control needs to be saved to a text field on the main table when the save button is clicked, but only after it has been verified to not be a duplicate entry, and be a complete entry (none of the concatenating field be Null). Note: since all of my controls are bound, I have set a BeforeUpdate procedure to the form to verify that a Boolean variable "blnSave" is = True before saving. Here is the code.

    Dim blnSave As Boolean

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If blnSave = True Then
    blnSave = False
    Exit Sub
    End If
    End Sub

    I need some serious help establishing a function, execute upon the save entry button click, to verify the calculated control is not an existing entry in the table, and if so display a message box & exit the save. If not, then save the calculated control to its designated field on the table along with the remaining entries.


    Update:
    ive set up a series of Validation rules via If Then Else statements to prevent null values being used in the controls that make up the concatenated control. the only thing left is to verify the calculated control doesn't already exist in the table and if it doesn't, copy the value (as text) into the field. here's the code I have so far...

    Private Sub Save_Component_Entry_Click()
    Dim Count As Integer
    Dim lngRed As Long

    lngRed = RGB(255, 0, 0)
    If IsNull(Me.Tool_Number) Then
    Count = Count + 1
    Me.Tool_Number.BorderColor = lngRed
    Else
    End If
    If IsNull(Me.Component_Type) Then
    Count = Count + 1
    Me.Component_Type.BorderColor = lngRed
    Else
    End If
    If IsNull(Me.Detail_Number) Then
    Count = Count + 1
    Me.Detail_Number.BorderColor = lngRed
    Else
    End If
    If IsNull(Me.Rev_Level) Then
    Count = Count + 1
    Me.Rev_Level.BorderColor = lngRed
    Else
    End If
    If Count > 0 Then
    MsgBox "Please Complete All required Fields. (Tool Number, Component Type, Detail Number, & Rev Level"
    Count = 0
    Exit Sub
    ElseIf MsgBox("Are you sure you want to save?", vbQuestion + vbYesNo, "Save Confirmation") = vbYes Then
    blnSave = True
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "Tool_Number"
    DoCmd.Restore
    Else
    blnSave = False
    End If
    blnSave = False

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •