Text Validation for a form written to two tables

[yhchen]

I have a form that will write data into a query which has two linked tables, call it Table A and Table B.

In table A, the “ID” field is a required field.
In table B, the “Name” field is a required field, this will only trigger if Field B is not null.

So for example, if a staff write a record without writing any data to Table B, the data will only write into Table A. If additional data (e.g. Field B) is not null, then I will need Name field to be not null.

All these have been set up but I want a validation message to warn all the users that Name field should not be null when they triggered Table B.

Apparently I cannot use macro and set it up in Before Update form property because this will apply to those records that weren’t written to Table B as well.

Would any body can help me to write an “IF” then warning message VBA code?

Many thanks in advance for any help you can provide.

 

[MSAccessRookie]

I have a form that will write data into a query which has two linked tables, call it Table A and Table B.

In table A, the “ID” field is a required field.
In table B, the “Name” field is a required field, this will only trigger if Field B is not null.

So for example, if a staff write a record without writing any data to Table B, the data will only write into Table A. If additional data (e.g. Field B) is not null, then I will need Name field to be not null.

All these have been set up but I want a validation message to warn all the users that Name field should not be null when they triggered Table B.

Apparently I cannot use macro and set it up in Before Update form property because this will apply to those records that weren’t written to Table B as well.

Would any body can help me to write an “IF” then warning message VBA code?

Many thanks in advance for any help you can provide.

I believe that the following VB code used in the Before Update Form Property (with appropriate changes of course), might get you what you are looking for:

    If ((Not(IsNull(Me.FieldB))) And (IsNull(Me.Name)) Then
        MsgBox {An Appropriate User Message}
    End If

As a matter of preference, I like Control names that are a little more descriptive (the more descriptive the better). Who knows, I may have to revisit a project again two years after I am finished with it, and I might need that extra information.

 

[yhchen]

I believe that the following VB code used in the Before Update Form Property (with apropriate changes of course), might get you what you are looking for:

    If ((Not(IsNull(Me.FieldB))) And (IsNull(Me.Name)) Then
        MsgBox {An Appropriate User Message}
    End If

As a matter of preference, I like Control names that are a little more descriptive (the more descriptive the better). Who knows, I may have to revisit a project again two years after I am finished with it, and I might need that extra information.

on dear! thanks for the light speed reponse!

will try to see how it goes!!

 

[yhchen]

Hi....no..it doesn't work...

it broke at Then showing "Compile Erroe, expected "

any idea??

Also, when I type alone, the system told me as MsgBox I should follow:

MsgBox(Prompt,[Button As VbMsgBoxStole=vbOKOnly,[Title],[HelFile],[Context]) As VbMsgBoxResult

uh??

Many thanks for all the help you can offer.

 

[georgedwilkinson]

That's because there are too many open parens and not enough close parens. You'll have to fix that.

 

[yhchen]

Hello guys

Thanks a lot!

I use macro:

1. Cancel Event
(have condition set to: (Not(IsNull(Me.FieldB))) And (IsNull(Me.Name))
2. MsgBox
3. Stop Macro
4. GoToControl

and it works!

Thanks!! You guys are the best!

the gotocontrol command somehow doesn't work though

 

[georgedwilkinson]

Glad it's working out for you.

Excuse my ignorance, but I don't know what GoToControl is. I guess I could just look it up.

 

[yhchen]

Glad it's working out for you.

Excuse my ignorance, but I don't know what GoToControl is. I guess I could just look it up.

GoToControl should make the focus go to the specific field (in this case, field Name) but it is not working....

If anybody knows why please let me know...

Many thanks.

 

[georgedwilkinson]

GoToControl should make the focus go to the specific field (in this case, field Name) but it is not working....

If anybody knows why please let me know...

Many thanks.

OK, I looked it up. Sure enough, it does what you said.

So, how do you know it's not working? Is there an error message? Does it briefly go to the control and then go somewhere else? Does it grab you by the throat and beat you? What?

Also, what happens when you set a breakpoint on that line and single step to the next line? Does it step to the next line? Does it take control to another event? What?

 

[boblarson]

By the way, you should not have NAME as a name of a field or object. It is an Access Reserved Word and has special meaning within the context of Me.Name (that returns the name of the current class object).

You may want to check this list to make sure you aren't using other reserved words. Plus change NAME to something else ASAP.
http://www.allenbrowne.com/AppIssueBadWord.html

 

[yhchen]

By the way, you should not have NAME as a name of a field or object. It is an Access Reserved Word and has special meaning within the context of Me.Name (that returns the name of the current class object).

You may want to check this list to make sure you aren't using other reserved words. Plus change NAME to something else ASAP.
http://www.allenbrowne.com/AppIssueBadWord.html

cheers mate. the actual field name is Student_Name

but the information you provided is very useful as I wasn't aware there is a list of reserved words

 

[yhchen]

OK, I looked it up. Sure enough, it does what you said.

So, how do you know it's not working? Is there an error message? Does it briefly go to the control and then go somewhere else? Does it grab you by the throat and beat you? What?

Also, what happens when you set a breakpoint on that line and single step to the next line? Does it step to the next line? Does it take control to another event? What?

no...nothing happened...no error message at all

it didn't go anywhere but stay at the bottom I clicked...

 

[georgedwilkinson]

OK, how do I know it's not working then? If I was sitting there with you right now, how would you demonstrate to me that it is not working? Would you show me that when I press this key, that doesn't happen like you expect? Would the hand reach out and grab me by the throat? It takes more than "nothing happened" to troubleshoot this stuff from 12,000 miles away. You have to be much more specific.

 

[yhchen]

OK, how do I know it's not working then? If I was sitting there with you right now, how would you demonstrate to me that it is not working? Would you show me that when I press this key, that doesn't happen like you expect? Would the hand reach out and grab me by the throat? It takes more than "nothing happened" to troubleshoot this stuff from 12,000 miles away. You have to be much more specific.

ah..

Right... when I tested it by missing inputing any text in the required field, and click "OK"

It pops up the message I created, so I click OK. It then pops up another general MS Access Message says "You cannot go to specific record". I clicked ok again.

Then, expectedly, with the GoToControl command in the macro, the focus now should stop in the required field... but no, the focus still stays in the OK bottom I clicked before the warning message pops up.

 

[georgedwilkinson]

Is this in a macro? No wonder it makes no sense to me.

I'm going to have to defer to my associates who have a clue. Sorry.

 

[yhchen]

Is this in a macro? No wonder it makes no sense to me.

I'm going to have to defer to my associates who have a clue. Sorry.

Thank you for trying though...

 

[georgedwilkinson]

Keep bumping it. Somebody who can figure it out will eventually come along.

Either that or just post your db with the symptoms and I'll take a look.