Writing a "required field" Macro using a linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help with writing a macro that will force form users to enter data in
required fields before the form is updated or can exit. The problem I'm
having is the table being used is a 'linked' table, which restricts any
modification to the table. Therefore I can't set validations or required
options. I've attempted to write the macro without satisfied results. Any
help is appreciated.
 
Ms,

The macro would look something along these lines:

Condition: [NameOfControl] Is Null Or [NameOfOtherControl] Is Null
Action: MsgBox
Message Text: "No data in required fields"
Condition: ...
Action: CancelEvent

Assign this macro on the Before Update event of the form.
 
To be more Specific, I have 12 fields on a form, and I would like to run the
condition macro on the Before Update proerty, such that if the field is
"Null", a MsgBox pops up requiring data be entered. Where I run into a
problem is setting up a macro that can run on each field without having to
refer to each field
individually, and thus create 12 macros, one for each field on the control.
Essentially, I am looking to write 1 macro with one condition in this fashion:
[fieldname] is not Null MsgBox
where [fieldname] refers to the current field with the focus of the user.
 
CORRECTION TO THE LAST POST:

[fieldname] is Null MsgBox
where [fieldname] refers to the current field with the focus of the user.
Also some were in the action I need to Cancel the update Event if the field
is not populated on exit.

Ms SDB said:
To be more Specific, I have 12 fields on a form, and I would like to run the
condition macro on the Before Update proerty, such that if the field is
"Null", a MsgBox pops up requiring data be entered. Where I run into a
problem is setting up a macro that can run on each field without having to
refer to each field
individually, and thus create 12 macros, one for each field on the control.
Essentially, I am looking to write 1 macro with one condition in this fashion:
[fieldname] is not Null MsgBox
where [fieldname] refers to the current field with the focus of the user.


Ms SDB said:
Help with writing a macro that will force form users to enter data in
required fields before the form is updated or can exit. The problem I'm
having is the table being used is a 'linked' table, which restricts any
modification to the table. Therefore I can't set validations or required
options. I've attempted to write the macro without satisfied results. Any
help is appreciated.
Click to expand...
Click to expand...
 
I tried that but it doesn't work. Here is the closest I results so for:

Condition - [Forms]![frmMain]![ControlSource] Is Null Action - CancelEvent
Condition - ... Action - MsgBox "No data in required fields"
Condition - ... Action - GoTo [ControlSource]

My problem is: If the Null [ControlSouce] is populated first, the MsgBox
will not appear for any other Null fields, although the CancelEvent is still
running. If I set a MsgBox for each [ControlSource] the box pops up
continously for each Null field before the GoTo runs.


Steve Schapel said:
Ms,

The macro would look something along these lines:

Condition: [NameOfControl] Is Null Or [NameOfOtherControl] Is Null
Action: MsgBox
Message Text: "No data in required fields"
Condition: ...
Action: CancelEvent

Assign this macro on the Before Update event of the form.

--
Steve Schapel, Microsoft Access MVP

Ms said:
Help with writing a macro that will force form users to enter data in
required fields before the form is updated or can exit. The problem I'm
having is the table being used is a 'linked' table, which restricts any
modification to the table. Therefore I can't set validations or required
options. I've attempted to write the macro without satisfied results. Any
help is appreciated.
Click to expand...
Click to expand...
 
Ms,

This macro only bears a faint resemblance to the one I gave you.

Is ControlSource really the name of a field? If so, I suggest you
change it.
 
Ms,

Ah, ok. Well, you can't use the Before Update or After Update event of
the control(s) in question, because if there is no data entered, these
events don't happen. You could, I suppose use the Exit event of each
control, if we can assume that the user is tabbing through all controls
on the form... but this is a dubious assumption in most cases, I imagine.

You could write a macro similar to what I showed you at first, listing
all 12 controls (drop the "[Forms]![frmMain]!" stuff). But that will
only tell the user that at least one of the required fields has not been
completed, but not specify which one. If you want to specifiy the
field, you don't have to write 12 separate macros, but you would need 12
separate actions in the one macro. Something like this...

Condition: [FirstControl] Is Null
Action: MsgBox
Message Text: ="No data in FirstControl."
Condition: ...
Action: CancelEvent
Condition: ...
Action: GoToControl
Control: [FirstControl]
Condition: ...
Action: StopMacro
Condition: [SecondControl] Is Null
Action: MsgBox
Message Text: ="No data in SecondControl."
Condition: ...
Action: CancelEvent
Condition: ...
Action: GoToControl
Control: [SecondControl]
Condition: ...
Action: StopMacro
Condition: [ThirdControl] Is Null
Action: MsgBox
Message Text: ="No data in ThirdControl."
Condition: ...
Action: CancelEvent
Condition: ...
Action: GoToControl
Control: [ThirdControl]
Condition: ...
Action: StopMacro
... etc

A more elegant (though not necessarily more effective) solution would be
available by using a VBA procedure.
 
I'll try this, I think the only difference is you have the CancelEvent in a
different position, and have added a StopAction condition (which I didn't
think about). I think this just might work! And no "ControlSouce" is not the
name of the field I was just using it in my example. I will let you know if
it works! Thanks

Steve Schapel said:
Ms,

This macro only bears a faint resemblance to the one I gave you.

Is ControlSource really the name of a field? If so, I suggest you
change it.

--
Steve Schapel, Microsoft Access MVP

Ms said:
I tried that but it doesn't work. Here is the closest I results so for:

Condition - [Forms]![frmMain]![ControlSource] Is Null Action - CancelEvent
Condition - ... Action - MsgBox "No data in required fields"
Condition - ... Action - GoTo [ControlSource]

My problem is: If the Null [ControlSouce] is populated first, the MsgBox
will not appear for any other Null fields, although the CancelEvent is still
running. If I set a MsgBox for each [ControlSource] the box pops up
continously for each Null field before the GoTo runs.
Click to expand...
Click to expand...
 
Back
Top