If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Msgbox error message/recheck field value
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. |
#3
|
|||
|
|||
Msgbox error message/recheck field value
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. |
Thread Tools | |
Display Modes | |
|
|