Assign a default value to field using validation

[ezybusy]

Hi everyone
I have an access based form which aim at allowing users to enter records into my database.
Now I need to set some validation to the value entered in one of the fields.
I want the user to enter "3101 or 3102 or 3103 or 3104 or 3105", if anything else a part from the numeric values stated above is entered into that field, then the field should be assigned to a default value value of "0000".

Can someone help me out???

[ItsMe]

Why not use a CombBox Control to list the available choices? You can place the values in a table as records or you can type the values into a value list of the combo. Perhaps using the Wizard to help you create the combo would be helpful.

[orange]

Please see https://www.accessforums.net/access/...bie-56635.html for additional info.

[ezybusy]

@ItsMe, thanks for the quick reply.
Here is the full requirement for the work.

Note that the tables below have a one-to-many relationship.


Table Category
-------------------------------------
Cat_id Category_Title
------------ --------------------------
0000 Others
3101 Category1
3102 Category2
3103 Category3
3104 Category4
3105 Category5




Table Products
------------------------------------------------------------------
ProductID Fertilizer_Name Class_id
----------------- --------------------- ------------
3102100000 Product_1 3102


Now, looking at the record in the table products we easily understand that the product_1 belongs to the category "3102" simply because its ID is made up of (3102)and(100000).
Now on the form that i developed, when the user type in the ID for a new product, the field CATEGORY is automatically filled with the first 4 digits of the product ID. And i was able to perform that using the "Mid Function" which extracts the first 4 digits of the product_id.

Now what i want to do is to automatically assign all new products which ID don't match to "3101, 3102, 3103, 3104, 3105" to the category OTHERS which is identified by "0000".

[ItsMe]

Now what i want to do is to automatically assign all new products which ID don't match to "3101, 3102, 3103, 3104, 3105" to the category OTHERS which is identified by "0000".

I guess I understand. I would probably have the user choose an item from a table. For instance, maintain a list of available items in a table named tblItems or even the existing TableCategory. Then, have the user select from tblItems the items they want to add to Table Products. You could use a combobox for this purpose.

However, to answer your question ... maybe something like

Dim strCheck As String
Dim strResult As String
strCheck = Me.txtInput.Value
strResult = Left(strCheck, Len(strCheck) - 5)
Select Case strResult
    Case 3101, 3102, 3103, 3104, 3105
    strResult = strResult
    Case Else
    strResult = "00000"
    
End Select
MsgBox strResult

[ezybusy]

Hi ITSME.
The above code worked, however it does not achieve what I actually want.
I want the value in the field Category to be automatically changed to "0000" if the extracted value from the ID field is not equal to "3101, 3102, 3103, 3104, 3105".

[ItsMe]

...
I want the value in the field Category to be automatically changed to "0000" if the extracted value from the ID field is not equal to "3101, 3102, 3103, 3104, 3105".

Then change
strResult = "00000"
to
strResult = "0000"

[ezybusy]

Hi ItsMe,
Sorry for coming back with the same issue.
This is my first project with Access so please consider me as a beginner.
Kindly provide detailed explanations as to how i should use the code you provided.

I tried placing the code on a "ON CHANGE event" on the "Category" field, but all it does is popping up a window that displays "0000", which is not what i want to achieve.

Below is the "Mid function" that i used on the "ProductID" field on a "ON LOST FOCUS Event".

Form.[ClassCode] = Mid(Form.[FertilizerHS], 1, 4)

Note that "ClassCode" is my "Category Field". And "FertilizerHS" is my "ProductID" field.

[ItsMe]

I would not place the following in the OnChange event of a control. Instead, I would use AfterUpdate of the Control.
Form.[ClassCode] = Mid(Form.[FertilizerHS], 1, 4)

I revised the code in post #5 so it includes your Mid() function vs. my Left() function and included the revision below. Also, I assigned the variable strResult to your field. Another thing I did was add some code that you may want to uncomment. It is the If Else statement that acts as a gated check to make sure FertilizerHS has a value.

'if isnull(Form.[FertilizerHS]) then
'exit sub
'end if
'Dim strCheck As String
Dim strResult As String
'strCheck = Me.txtInput.Value
strResult = Mid(Form.[FertilizerHS], 1, 4)
Select Case strResult
    Case 3101, 3102, 3103, 3104, 3105
    strResult = strResult
    Case Else
    strResult = "0000"
End Select
Form.[ClassCode] = strResult

[ezybusy]

Woow ItsMe, many many thanks. it works. you just saved me.

[ItsMe]

Glad to hear and welcome to the forum.