Msgbox error message/recheck field value

Hello,
I've been searching for the answer to this all week, and I haven't been able
to find it. I'm new to building Access databases, but I've managed to do
pretty well with what I'm working on so far. The one problem that is holding
me up from being done with this is that I have an error check in place to
prevent users from entering numbers that are already in use, but once the
code runs the first time, it doesn't check to see if the value has been
changed. The msgbox keeps coming out even if an acceptable value is entered
after the original error. I have a query set up that updates a table with 2
fields: Category Numbers and the available Initiative Numbers for each
category. Then in the code for a data entry form that I've set up to input
data into a master table, I had this code, which I wanted to prevent the user
from saving the data if the Initiative Number is already use in the Master
Table:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If Not IsNull([CATEGORY #]) And Not IsNull(DLookup([INITIATIVE #], "2nd
Copy of Master", "[CATEGORY #] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

The problem as stated before is that once the user gets the initial msgbox
stating that the initiative number is not available for use, it doesn't
recheck to see if the value of the initiative number has been changed--if it
no longer violates my If statement. When that didn't work, I tried this code
to prevent the user from saving the data if the Initiative Number wasn't
found in the table that contained available initiative numbers for each
category:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If [INITIATIVE #] In ( (DLookup([Initiative Numbers], "Avail Init #",
"[Category Numbers] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

This doesn't work any better than the first code. Can someone help me
determine what I'm doing wrong, please? Thanks.

personally, I would change the design to have a sub lookup the last
initiative and autonumber it so the user wouldn't need to enter one. i would
also avoid spaces and # in my field names also to simplify things.

"mb" wrote:

Hello,
I've been searching for the answer to this all week, and I haven't been able
to find it. I'm new to building Access databases, but I've managed to do
pretty well with what I'm working on so far. The one problem that is holding
me up from being done with this is that I have an error check in place to
prevent users from entering numbers that are already in use, but once the
code runs the first time, it doesn't check to see if the value has been
changed. The msgbox keeps coming out even if an acceptable value is entered
after the original error. I have a query set up that updates a table with 2
fields: Category Numbers and the available Initiative Numbers for each
category. Then in the code for a data entry form that I've set up to input
data into a master table, I had this code, which I wanted to prevent the user
from saving the data if the Initiative Number is already use in the Master
Table:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If Not IsNull([CATEGORY #]) And Not IsNull(DLookup([INITIATIVE #], "2nd
Copy of Master", "[CATEGORY #] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

The problem as stated before is that once the user gets the initial msgbox
stating that the initiative number is not available for use, it doesn't
recheck to see if the value of the initiative number has been changed--if it
no longer violates my If statement. When that didn't work, I tried this code
to prevent the user from saving the data if the Initiative Number wasn't
found in the table that contained available initiative numbers for each
category:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If [INITIATIVE #] In ( (DLookup([Initiative Numbers], "Avail Init #",
"[Category Numbers] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

This doesn't work any better than the first code. Can someone help me
determine what I'm doing wrong, please? Thanks.

Thanks for your reply, but I need to allow users to select initiative numbers
out of sequence, otherwise this would work, I guess.

"mb" wrote:

Hello,
I've been searching for the answer to this all week, and I haven't been able
to find it. I'm new to building Access databases, but I've managed to do
pretty well with what I'm working on so far. The one problem that is holding
me up from being done with this is that I have an error check in place to
prevent users from entering numbers that are already in use, but once the
code runs the first time, it doesn't check to see if the value has been
changed. The msgbox keeps coming out even if an acceptable value is entered
after the original error. I have a query set up that updates a table with 2
fields: Category Numbers and the available Initiative Numbers for each
category. Then in the code for a data entry form that I've set up to input
data into a master table, I had this code, which I wanted to prevent the user
from saving the data if the Initiative Number is already use in the Master
Table:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If Not IsNull([CATEGORY #]) And Not IsNull(DLookup([INITIATIVE #], "2nd
Copy of Master", "[CATEGORY #] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

The problem as stated before is that once the user gets the initial msgbox
stating that the initiative number is not available for use, it doesn't
recheck to see if the value of the initiative number has been changed--if it
no longer violates my If statement. When that didn't work, I tried this code
to prevent the user from saving the data if the Initiative Number wasn't
found in the table that contained available initiative numbers for each
category:

Private Sub Save_and_Close_Click()
On Error GoTo Err_Save_and_Close_Click

If [INITIATIVE #] In ( (DLookup([Initiative Numbers], "Avail Init #",
"[Category Numbers] =" & [CATEGORY #])) Then
MsgBox "This Initiative # is already in use for the category you have
selected. Please assign a different Initiative number to this Initiative."
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Save_and_Close_Click:
Exit Sub

Err_Save_and_Close_Click:
MsgBox Err.Description
Resume Exit_Save_and_Close_Click

End Sub

This doesn't work any better than the first code. Can someone help me
determine what I'm doing wrong, please? Thanks.