Information Message for Duplicate field

[net]

Hello,

How can I create an alert message to tell the user that there are an existing work order number in the table? I don't want it to restrict the entry, just inform the user that there is an existing work order number, do they want to continue.

Btw, the code will be attached to a combo box (cbowonum).

Appreciate the help.

 

[MarkK]

Check out the MsgBox() function, which pops up a modal form the user must clear.

 

[net]

Check out the MsgBox() function, which pops up a modal form the user must clear.

Hi MarkK,

I need for the function to perform a search to see if there are a work order number in the table before displaying the message.

Where can I find the MsgBox() function?

 

[MarkK]

In Access there are two help systems, Access help, and VBA help. To find more detailed help on functions you might want to run, use VBA help. To get there, open a code window, which you can do by hitting <Alt> + <F11>, and click Help on the main menu, and select "Microsoft Visual Basic Help," and then search for MsgBox(). This will show you detailed information about how to use the MsgBox function.

In respect to your other question, how to determine if a value already exists in a table, check out the DCount() function. With syntax like this . . .

DCount("*", "tblYourTable", "YourID = 1234")

. . . you can count how many records there are in a table that satisfy certain criteria. If there are more than zero, then a record already exists, so you could use code like . . .

If DCount("*", "tblOrder", "OrderID = " & Me.TestOrderNumber) > 0 Then
   MsgBox "The order number " & Me.TestOrderNumber & " already exists in the table."
End If

Hope this helps,

 

[net]

Hi MarkK,

I am trying to add the DCount code to locate a work order that is currently in the table and respond with a message if found.

I am using the following code, but I receive an Syntax error. What am I missing?

Code:
If DCount("*", "tbl_WorkOrder", "WorkOrd_Number = " & Me.Cbowonum) > 0 Then
MsgBox "The Work Order number " & Me.WorkOrd_Number& " already exists in the table."
End If

Error Message:
Syntax error (missing operator) in query expression “WorkOrd_Number = ‘

 

[MarkK]

What is the value of Me.Cbowonum when you run that code? Are you certain that there is a value there? Also, what is the data type of WorkOrd_Number? If is a string, you need to delimit as follows . . .

If DCount("*", "tbl_WorkOrder", "WorkOrd_Number = '" & Me.Cbowonum & "'") > 0 Then

. . . noting that the cbo's value is wrapped in single quotes.

Cheers,

 

[net]

What is the value of Me.Cbowonum when you run that code? Are you certain that there is a value there? Also, what is the data type of WorkOrd_Number? If is a string, you need to delimit as follows . . .

If DCount("*", "tbl_WorkOrder", "WorkOrd_Number = '" & Me.Cbowonum & "'") > 0 Then

. . . noting that the cbo's value is wrapped in single quotes.

Cheers,

The WorkOrd_Number is text data type. I have a combo box (Cbowonum) pulling the workorder numbers from a query. The user selects the workorder number from the combo box. I hope I answered your question.

 

[MarkK]

The WorkOrd_Number is text data type.

OK, so you need string delimiters, which are the single quotes I added in my last post.

Have you added those to your code? Does that change the outcome?

 

[net]

OK, so you need string delimiters, which are the single quotes I added in my last post.

Have you added those to your code? Does that change the outcome?

Yes it does work. But, when I test the data, I receive the message box whether I have a duplicate field or not. I need to to find the dup and inform the user that there are another work order number for that number. Otherwise, just record the new work order number if no dup exist.