MS Access VBA Code field validation - before update

[AMarvelFan]

I'm really , 'REALY' new to vba code... learning on my own. I've successfully written some small stuff on my own, but now need help. I've created a MS Acces user form with two combo boxes. It has several fields 3 of which need to be validated before the user exits (one of them being a combo box).

[OrderRes, [CompDt]and [CompBy]

If the [OrderRes] = “5 DAY LETTER” (ID 16). User should not be able to add a [CompDt] and or [CompBy]

If a user selects a [Order Result] other than “ 5 DAY LETTER” (ID 16), [CompDt] and [CompBy] are required.

If [OrderRes], [CompBy] and [CompDate] are null okay to [exit]

How would I write this code? Can some please provide a example?

This is what I've done thus far... not sure if it will work, but I don't know how to include all the scenarios:

If IsNull (Me.OrderRes] And IsNull(Me.CompDt) And IsNull(Me.CompBy) then
    DoCmd.Close
ElseIf IsNull(Me.CompDt) = False or IsNull(Me.CompBy) = False then
    If Me.OderRes = “5 DAY LETTER” THEN
        DoCmd.Close
    Else
        MsgBox "Remove Completion updates"
    End If
End If

Thanks in advance for you help,
Anthony

[twinnyfo]

AMarvelFan,

Weclome to Bytes!

Please know that we do want to help you on this forum. However, we typically do not get into the habit of writing solutions for our users. We will gladly troubleshoot irksome code that doesn't execute properly and guide you toward a solution.

That being said, based upon what you have described (including your level of expertise), I want to encourage you that you are much closer to a solution than you might think.

If you know a little bit about VBA, consider these questions:

1. When would you want to check these data points?
2. How would one go about checking the validity of the Text Boxes?
3. What other options might be available for this solution?

Again, I think you are very close (conceptually) to your solution. We will stand available to work through a solution for you--but you must put forth an initial effort of your own.

[AMarvelFan]

twinnyfo,

thanks...

This is what I had come up with... not sure if it will even work but.. I don't know how to include one of the scenarios:

If IsNull (Me.OrderRes] And IsNull(Me.CompDt) And IsNull(Me.CompBy) then
    DoCmd.Close
ElseIf IsNull(Me.CompDt) = False or IsNull(Me.CompBy) = False then
    If Me.OderRes = “5 DAY LETTER” THEN
        DoCmd.Close
    Else
        MsgBox "Remove Completion updates"
    End If
End If

[twinnyfo]

Anthony,

First, please use the Code tags when entering code (the "[CODE/]" button in your text editor). I also changed the formatting a bit to make it easier to follow and took the liberty of changing the last ElseIf to an End If.

Second, See how close you are to a solution? I can....

The first question is, when do you want to make such checks? You have three fields that can be updated: OrderRes, CompDt and CompBy. What if someone enters CompDt and CompBy and then selects OrderRes of 16? Do you want those other values cleared?

So, will this code execute after a Command Button or after the update of a Text/Combo box?

From a learning perspective, these are the things you will want to consider as you develop your projects.

I also assume that this is a Form with an underlying Table that is being updated, yes?

I might be away from my computer a bit over the next few days, but I (or another user) will try to help you with this....

[NeoPa]

To follow on from Twinny's good advice I would also warn that using a word processor for code is an easy way to get things wrong. It may be that you only used one in order to post your code here but either way never use a word processor as it can change your important quote (" & ') characters into other ones that look similar but simply don't work. Just as you have in your posted code. The correct ones should always show as vertical. Never as specifically opening or closing quotes of any form.