Was WhereCondition provided when form opened?

[Seth Schrock]

Is it possible to determine if the WhereCondition was provided when the form was opened? What I'm wanting is that if I just open the form (no WhereCondition provided) it would go to a new record, but if the form was opened to a specific record (a WhereCondition was provided) then it would go to that record. Normally, I would put a DoCmd.GoToRecor d Record:=acNew in the form's OnLoad event to do the first option, but I think that would cancel any attempt to go to a specified record if one was provided. This is why I want to test if a WhereCondition was provided.

Right after I submitted the question an idea struck me. I can test for the Filter property on the Load event to see if it is blank or not to know if the WhereCondition was provided.

[Stewart Ross]

Hi Seth. You are right that you can test the form's Filter property to see whether a Where clause exists, but to know for sure whether the given filter is active you need to test the form's FilterOn property which will be set True if the filter is currently applied, False if not. It is possible to have a form's filter property set to some value but for the filter not to be applied at the time.

-Stewart

[NeoPa]

Indeed. I (very) recently discovered that changing the .RecordSource property of a form actually sets .FilterOn to False but leaves the .Filter as it was previously.

Generally it's more straightforward though. If .Filter is set then generally .FilterOn is True. Having said that, it's probably most sensible to test for it anyway.

[Seth Schrock]

That is a very good point. Thanks for sharing. Now I'm glad that I did post it so that I could get this insight.

[TheSmileyCoder]

While not exactly an answer to your question, I wonder if this info might help you anyway. With the open command, you can specify that the form opens in add mode. This means you can only add new records with the form.
Do open in add mode:

docmd.OpenForm "myFormName",,,,acFormAdd

The downside (or benefit depending on your point of view) is that if I recall correctly, you cannot view existing records with that form now. The recordset is basically empty, pending new records.
Do open "regularly" in edit mode:

docmd.OpenForm "myFormName",,,"RecordID=" & 5,acFormEdit

I prefer this method, over the filter, since the filter can be removed.

[Seth Schrock]

In this particular case, I need to be able to see existing records, but I just want it to open to a blank (new) record instead of the first record if the WhereCondition isn't provided.

I have never used the acFormEdit option, so I looked it up and found that acFormPropertyS ettings is the default option. What is the difference between these two options? I almost never specify this setting and I'm able to add new and edit existing records. And I'm pretty sure that I have removed the form's filter while using the default setting.

[TheSmileyCoder]

Well the acFormAdd and acFormEdit have SOME resemblance to the form settings for AllowAdditionsa nd AllowEdits. But with a regular form, the default is that AllowAdditions and AllowEdits are both enabled. If you provide the settings with acFormAdd/acFormEdit it will only be one or the other that is allowed. I haven't actually tested nor looked into how AllowDeletions is affected by this particular setting.

There are certain obvious cases where you don't want to allow additions, such as a user updating his own data. We don't want the user accidentally hitting page-down and starting a new record. However a HR employee should obviously be able to add new records (but might not be allowed to see existing). So using the acAdd and acEdit allows us to re-use the same form for multiple purposes, without having to add all sorts of custom logic and passing of arguments with the OpenArgs.

[Seth Schrock]

What is the difference between acFormPropertyS ettings and acFormEdit (the former being the default)?

[TheSmileyCoder]

acFormPropertyS ettings will use the AllowEdits, and AllowAdditions properties that the developer have set for the form, whereas acAddNew and AcFormEdit will override them.

As said, I am not 100% sure how AllowDeletions factor into this. Should be easy enough to test if important :)

[NeoPa]

Smiley:
I am not 100% sure how AllowDeletions factor into this

Here is a snippet of some of my code that answers that question :-)

    With Me
        .AllowDeletions = False
        'DataMode set to either acFormAdd or acFormEdit causes
        '  .AllowDeletions to be reset to True.

[TheSmileyCoder]

@NeoPa
Good to know. Personally I prefer to disable the "built-in" deletions and run my own, usually involving soft deletes.

[NeoPa]

That makes a lot of sense Smiley.

I have a large project with a lot of forms pre-built before I became involved. I've had various problems due to users deleting items they should not be able to delete. I changed the design of these to preclude any deleting, but this was overridden by the automatic change. Hence, I found I had to set the .AllowDeletions again to False in the Form_Open() procedure.

In my case they shouldn't have been allowed to delete records at all. That was all the control I needed ;-)