Opening a form without triggering AutoNum

[TESA0_4]

I have a sub form (datasheet view) where users can add and amend records.
They also have the option to double click a line in the subform to open a
Single Form view that allows for easier data entry if required. If the user
double clicks an existing record the form open to that record and if the user
double clicks the new record line the new form opens blank except that the
AutoNum has ticked over. What I want is for the new form to open without the
AutoNum ticking over until the user actually starts data entry. I'm finding
users open the new form but close without making any data entry which then
leads to dialogue messages because they haven't filled in mandatory
fields...... The code I am using to open the form is:

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.txtActionID) Then
DoCmd.OpenForm "frmHazActPers"
DoCmd.GoToRecord , , acNewRec
Else
strWhere = "ActionID = " & Me.txtActionID
DoCmd.OpenForm "frmHazActPers", WhereCondition:=strWhere
End If

Any suggestions would be appreciated.

Terry

 

[Maurice]

when closing the opened form without making any changes you should place
me.undo in the closing event of the form. Ofcourse you'd have to check to see
if the users haven't filled in any data in the form. Could be something like:

dim ctl as control
dim i as integer
i=0
for each ctl in me
if isnull(ctl) or ctl="" then
i=i
else
i=i+1
end if
next

if i=0 then me.undo '-> nothing has changed...

In this test you do have to check the kind of controls because a
commandbutton cannot have a null or "" as a value so trap that by setting a
value in the tag of the control so you can check those instead of all
controls.

hth

 

[Klatuu]

If Not Me.Dirty Then
Me.Undo
End If

Would be much simpler.

Also to the OP, It would sound like you are using the Autonumber for
something other than maintaing relationships between tables. You should
never do this. First, data should contain no intelligence. Second,
autonumbers should never be used for meaningful data. One of the main
reasons is just what you are experiencing. If you need to have sequential
numbers for records, there are other ways than using Autonumber fields.

 

[Maurice]

Does that also go when a user has typed in a value in a certain textfield and
erased this a couple of seconds later. That makes the form dirty no? If it
does work your are certainly right that would be much simpler... ;-)

 

[Klatuu]

Good question, Maurice. Hadn't considered that. I would have to do a test to
see.

 

[Dirk Goldgar]

I have a sub form (datasheet view) where users can add and amend records.
They also have the option to double click a line in the subform to open a
Single Form view that allows for easier data entry if required. If the
user
double clicks an existing record the form open to that record and if the
user
double clicks the new record line the new form opens blank except that the
AutoNum has ticked over. What I want is for the new form to open without
the
AutoNum ticking over until the user actually starts data entry. I'm
finding
users open the new form but close without making any data entry which then
leads to dialogue messages because they haven't filled in mandatory
fields...... The code I am using to open the form is:

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.txtActionID) Then
DoCmd.OpenForm "frmHazActPers"
DoCmd.GoToRecord , , acNewRec
Else
strWhere = "ActionID = " & Me.txtActionID
DoCmd.OpenForm "frmHazActPers", WhereCondition:=strWhere
End If

Any suggestions would be appreciated.

The only reason a new autonumber would be assigned when you first open the
form is that there is code (or a macro) in one of the form's events that
"dirties" the form. Maybe there's code to automatically set some values on
the form. If you can remove that code, you won't have this problem -- the
autonumber won't be set until the user actually dirties the form. If you
want the form to open with some "preset" values, you can set the
DefaultValue property of the relevant controls, rather than setting their
values.

 

[TESA0_4]

Hi to you all,
Thanks for your replies.
Dirk, you have answered one underlying puzzle and that is why the AutoNum is
triggering - I am defaulting information to the form from the parent form of
the subform on which my command button is located on. This defaulted
information is unique to the circumstance so I cannot set up a common default.
Klatuu, be assured that I am only using AutoNum to create a unique ID for
each record. Once the form is fully developed the users will not 'see' the
AutoNum value.
I don't have opportunity to test your suggestions now, that will have to
happen tomorrow.
Once again thanks to you all.
Regards,
Terry

 

[Dirk Goldgar]

Hi to you all,
Thanks for your replies.
Dirk, you have answered one underlying puzzle and that is why the AutoNum
is
triggering - I am defaulting information to the form from the parent form
of
the subform on which my command button is located on. This defaulted
information is unique to the circumstance so I cannot set up a common
default.

You don't have to. You can set the DefaultValue properties of various
controls on the fly, when you open the form. If this form is used only for
this purpose, you could have the code to do that in the form's Open event.
If not, you could have the code to do that in the Click event of the command
button that opens the form. Essentially, wherever you now have the code
that sets the "default" values of certain controls by executing statements
formed like this:

Me!SomeControlName = SomeValue

.... you replace that with statements formed like this:

Me!SomeControlName.DefaultValue = """" & SomeValue & """"

The elaborate quoting in the above statement isn't always needed, but has
the advantage that it should always work, regardless of the data type
involved.

 

[TESA0_4]

Dirk,

Thanks heaps! I am using a double click on a field in the subform to open
the new form. I changed the code as you suggested to .default and it worked
as your forecast. Also, thanks for the reference to the additional quote
marks - that anticipated a problem that I would have had if you had not
mentioned it.
So much to learn and so little time in which to do the learning!!
Regards,
Terry