Unanswered: HELP! VBA to validate wont work, too many days frustrated.
So let me start off with an apology if this is posted in the wrong place or if im not using proper forum etiquette, im a complete noobie.
A little background... ive been building an inventory database using access to track replacement components for a tooling shop. the database contains basic information on the tooling itself and the components that make up the tooling. Within the main table is a calculated field that concatenates the specifiC tool number, 3 letter component description, detail number, and revision level. (eg. "AU100.PBU.233.A1") This is what is used as the component ID. This would ordinarily be the primary key but for some reason MS Access wont allow for calculated fields to be primary keys.
I have a form built in order for employees to enter the information into the table appropriately with a few buttons programmed for basic functions (clear entries, save entry, and close form) The user interface needs to be as simple and bulletproof as possible since the end users aren't the most computer savvy.
On to the Problem.... the issue is with programming the "Save Entry" button. I have a VBA written that is intended to verify that the calculated "Component_ID" control on the form does not match an existing calculated "Component ID" field on the table. If they do match it is supposed to pop up a message box indicating that a duplicate entry is being attempted, and exit the sub. Here is the actual 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
MsgBox "Component already exists in database. Please double check entry, or add new component revision level.", vbOKOnly, "Duplicate Entry Error"
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
MsgBox MacroError.Description, vbOKOnly, ""
What is actually happening is that no matter what entry is input, the message box still appears. in addition, the entry is saved to the table once the form is closed, or reopened. I have tried multiple variations of the Dlookup function using different syntax and still ended up with the same result.
Right now im at the end of my rope since ive been trying to fix this button for 3 days. im currently looking into the calculated control on the form and questioning the way its interacting. any help will b greatly appreciated.