Need help with record validation

[Jasonmayak]

Hi I'm new to access so this may be a quick fix for someone but not me. I need to setup this table so that if the "current status" field is set to Empty than both the "Material" and "Resource" fields are cleared out of any information. Right now the Material, resource and current status fields are all drop downs with the information being pulled from other tables setup just to standardize the data. My problem is that currently any bin ID can show material and resource in it while its status is empty and it throws off my queries. Thanks for any help.

[RuralGuy]

You're not using "Lookup" Fields are you? http://access.mvps.org/access/lookupfields.htm

[June7]

Do you want to actually delete the data from the fields? That can be done with an UPDATE sql action.

I am confused - the example shows the Material and Resource fields are empty when Status says empty. If the issue is that status can be changed to Empty from one of the other 3 values and therefore data needs to be removed from Material and Resource fields, that can be accomplished with code in Status combobox AfterUpdate event on form.

[Bob Fitz]

AFAIK a table has no events so this would need to be done in a form bound to the table. (Users should never be working directly with tables anyway.)
You would just need a bit of code in the After Update event of the control on the form that is bound to the "Current Status" field that evaluates it's own value and if that value is equal to "Empty" then set the value of the controls bound to "Material" and "Resource" to Null.
Post back if you need help with the actual code.

[June7]

Bob, just for reference - Access 2010 introduced Data Macros which allow event code to be associated with tables.

[Bob Fitz]

Bob, just for reference - Access 2010 introduced Data Macros which allow event code to be associated with tables.

Thanks for the info June. Sound like another bad idea to me though.

[Jasonmayak]

Bob,
That makes perfect sense to me I just do not get the coding yet if you have an example I would appreciate it.

Thanks,
JM

AFAIK a table has no events so this would need to be done in a form bound to the table. (Users should never be working directly with tables anyway.)
You would just need a bit of code in the After Update event of the control on the form that is bound to the "Current Status" field that evaluates it's own value and if that value is equal to "Empty" then set the value of the controls bound to "Material" and "Resource" to Null.
Post back if you need help with the actual code.

[June7]

In VBA, like:

If Me!Status = "Empty" Then
Me!Material = Null
Me!Resource = Null
End If

[Jasonmayak]

In VBA, like:

If Me!Status = "Empty" Then
Me!Material = Null
Me!Resource = Null
End If

I appreciate the help. I attempted to plug this code into an event procedure under my only form in my database, 1st on "After update" then on "on current" I don't get any error messages from the code but it simply does not update my table or form at all. And I am saving it after I copy the code into place so I know its there. Is there anyways I can accomplish this from the table directly by using a record validation? I would really like to see my form react by clearing the material and resource fields when empty is selected in the current status field. In the table that contains the options for the material and resource fields I have created an additional record with no data in it (blank) and if I could just get the form to recognize that empty is in the current status field and to automatically swap the data from the material and resource field over to these blank options that would be ideal.

[June7]

Use the combobox AfterUpdate event.

[Jasonmayak]

In that combo box is "Event Procedure" and a Macro called "Close report command" which I created to exit out of a report within the database. Should there be something else there? What I have done is use the combo box to select event procedure and then clicked the button with the 3 dots to the right and that that is where I copied that code into. Sorry if I'm making this more difficult than it has to be. I appreciate your help.

[Jasonmayak]

Also I am actually using access 2010 right now.

[June7]

If the combobox AfterUpdate event property says [Event Procedure] then you have VBA code. Maybe the procedure is calling a macro (I don't use macros) called "Close report command" but this is still a VBA procedure. Add the suggested code into the procedure behind the macro call.

However, I would think the form AfterUpdate event should work, which is what is shown in the image.

[Jasonmayak]

I'm not sure why this code is not working. I wonder if there is an issue with how these field get their information. I have a main table (injbinmanagement) with these fields setup there as a lookup field pulling data from a different table each. So Currentstatus has a table as well as Material and Resource.

If the combobox AfterUpdate event property says [Event Procedure] then you have VBA code. Maybe the procedure is calling a macro (I don't use macros) called "Close report command" but this is still a VBA procedure. Add the suggested code into the procedure behind the macro call.

However, I would think the form AfterUpdate event should work, which is what is shown in the image.

[June7]

But are you saving Material and Resource keys into injbinmanagement?

If you want to provide db for analysis, follow instructions at bottom of my post.