Hi Everyone

I am trying to ensure that If the user adds a New Record then it is mandatory that they fill in a specific Control

I am using this Event

Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.Tag = "*" And IsNull(Ctrl) Then
MsgBox "Post Zone is not filled in - please check fields with * are filled in"
Cancel = True
Ctrl.SetFocus
Exit For
End If
Next Ctrl

On the Post Zone control I have place the * on the Tag

When I add a Bew Record and fail to fill in the Post Zone then the Message Prompt appears as expected But
I also get this error:-

The Method you tried to Invoke on an Object Failed

Thoughts??

Your help appreciated

Hi Mike,

I'm not sure if that asterisk is being read as a wildcard character? I don't recall if that only works with LIKE or with = also...

That said, I usually do this on a form by form basis and give a hard list of controls to check instead of looping them all... it's a bit quicker performance-wise as well.

If you want a private module for the form, something like this:



You can make a public procedure and pass in a string of control names to check, using a delimiter such as a semicolon:



and in a standard module:




I know it doesn't answer your question directly, but I thought maybe a different approach may give you something to think on.

Cheers,

Hi Mike,

Assuming you placed the code in the Form's Before Update event, I just set up a similar form and the code works perfectly (on A2003).

Peter Hibbs.

Hi Mike,

If I understand well what you need:



HTH's

Hi

Still the same error

Is it to do with how I place the * in the Tab field of the Form ?

Is it supposed to a straight * or is it "*" ??

Hi Mike,

Just to make sure the code is working first, why not just plug in the name of the control (didn't you say it's just "one specific" control that you're checking?) in the code. For example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.ControlName & "") = 0 Then
Cancel = True
Me.ControlName.SetFocus
MsgBox "Please..."
End If
End Sub

Then, if that works, try replacing your Tag with an actual word. For example: Required

Then, you can modify the code to something like:

If ctl.Tag = "Required" Then...

Just my 2 cents...

hi theDbguy

Get the prompt and as previously mentioned I also get the error message about the object??

Then, it's not the code. As Peter mentioned earlier, it should work just fine.

It can't be the asterisk either if you took it out of the code logic. It must be something about the control.

What type of control is it, and is it Enabled or Locked?

Just my 2 cents...

Hi theDbGuy

This is the full code on the Before Update of the Form

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.MemNo = Nz(DMax("MemNo", "tblMembershipList"), 359) + 1

If Len(Me.CBOzONE & "") = 0 Then
Cancel = True
Me.CBOzONE.SetFocus
MsgBox "Please fill in the Post Zone"
End If


End Sub

This gives the Message Prompt and the original error about the Object

I commented out the Me.MemNo line and the error message I am getting now is:-

The setting you entered isn't valid for this property??

The control cboZone is a Combobox

Okay, if you comment out the MemNo line and get the invalid property error, which line gets highlighted?

If you leave in the MemNo line and comment out the SetFocus part, do you still get any error?

What is the SQL for the RowSource of the Combobox? What is the Bound Column? Is it Enabled or Locked?

Hi theDb

It does not take me to the VB Editor at all

It just displays the Message - then displays the Error about the property and then
sets the Focus to the cboZone control

the SQL for the Combobox is this:-

SELECT tbluPostZone.PostZoneID, tbluPostZone.PostZone, tbluPostZone.Zone
FROM tbluPostZone;

It is Enabled and Not Locked

Hi theDbGuy

I left in the Me.MemNo line and commented out the SetFocus

Got the correct Message prompt but the error is :-

The method you tried to invoke on an object failed

???

Try stepping through the code to see where the error is coming from.



Are there any other code that might be running at the same time?

Just my 2 cents...

hi theDbguy

I have attached the Db with no data in it so you can see my error

This is a project in progress so you may find lots of things wrong

The Main Form that opens - select Libraries , Subs & Exchanges

Then click the Add New Button

Then enter any random Business Name, select a Membershipo Type and add an Address

Now Close the Form and you will see what I am getting

Click to view attachment

Hi Mike,

Try doing a C&R. I just opened your db in 2010 and didn't get any error.

Just my 2 cents...

Hi theDbGuy

I have done both a C & R and a Compile

But i get the error ??

Okay, I think we'll have to wait and see if the others who download your demo also get the same error.

Just my 2 cents...

Hi theDbGuy

Can I do something on the Close Button

I tried this :-

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

If Len(Me.CBOzONE & "") = 0 Then

Me.CBOzONE.SetFocus

MsgBox "Please fill in the Post Zone"

Else
End If
DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

It gives the message prompt but does not set focus to the cboZone Control

It closes the Form

Yeah, the Close event is too late for what you need. The BeforeUpdate event is the best place for it. Like I said, the code works for me. I'm just curious to see if others has the same issue as you or not.

Hi,

I'm using 2007 version and I also get error.

This solved the problem.



HTH's

Hi

I tested, no error!
I am using access 2010

M

Hi Tiesto X

Perfect


I have no idea why I would get that error

Yet again Access baffles

I'm going to guess that you'll see the problem again! My first thought, after reading the three pages of posts, was that you were dealing with a Control that was corrupted. We think of Forms and entire Databases becoming corrupted, but Controls can become corrupted, as well.

Downloaded your file and ran it, in v2007, and got the same error message. Took down the particulars on the Combobox (Name, Control Source, Row Source) and deleted it. Then re-created it and it now runs without a problem!

Corruption is a funny thing, and can sometimes cause problems, and sometimes not, which would account for why some people had no problem and some did.

Also, this kind of code really belongs in the Form_BeforeUpdate event, not behind a custom Close button. If you use the Close Button for this, you have to disable the native Form Close Button, Access Close Button, the Close Database and Close Access options from the dropdown menu and the Navigation Buttons; using any of these while CBOzONE is empty will result in the Record being saved, with the CBOzONE being populated!

You need to delete then recreate the Combobox and keep your code in the Form_BeforeUpdate event.

Linq ;0)>

Hi Ling

I did as you suggested and deleted the cboZone

Recreated and placed the following on the Before Update of the Form

Private Sub Form_BeforeUpdate(cancel As Integer)

If Len(Me.CBOzONE & "") = 0 Then
cancel = True
Me.CBOzONE.SetFocus
MsgBox "Please fill in the Post Zone"
End If

End Sub

Now If I dont fill in a Zone then when I click the Close button I get the Message correctly and when I
click OK it does not set focus to the Combobox, it just closes the form ??

How would I correct this??

It has long been recommended that the code

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a form because of a quirk in Access. When DoCmd.Close is used, Access closes the form regardless of whether or not a PK field or other required field has been left blank or validation rule has been violated! If one of these things occur, Access will simply dump the Record, close the Form, and not tell the user that the Record has been dumped!

If Me.Dirty Then Me.Dirty = False forces Access to attempt to save the Record, and if a violation has occurred, will throw up a warning message allowing correction to be made before closing the Form.

You need to change your

DoCmd.Close

to

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

and have the Validation Code in the Form_BeforeUpdate event.

Linq ;0)>

Hi again Mike,

i forgot about this code:



and for the close form button its still the same code.


HTH's