minimizing form resets a field

[Guest]

I have a form that auto populates a project number using the following code:

Private Sub Project_Number_Enter()
Me!ProjectNumber = Nz(DMax("ProjectNumber", "[Project Table]"), 0) + 1
End Sub

When a user is in the form and minimizes the form it resets the project
number to the next number. It appears to refresh this field and runs the
code again. It also resets all the project numbers in the table. Obviously,
I can't have this! Any suggestions?

 

[strive4peace]

Hi Gretchen,

how about...

'~~~~~~~~~~~~~~~~~~~~~
if nz(Me.ProjectNumber,0) = 0 then
Me.ProjectNumber = Nz(DMax("ProjectNumber", _
"[Project Table]"), 0) + 1
end if
'~~~~~~~~~~~~~~~~~~~~~

instead of launching the code as you are, it should probably be launched
on the form BeforeInsert event, which happens after the first keystroke
to create a new record -- then you do not need the IF statement...

"It also resets all the project numbers in the table"

really?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks for your help Crystal,
I tried this code on the "before update" and it did not work at all...I'm
not getting a project number assigned at any point in the form. Also, yes,
what is so strange about this form is that it will automatically reset all
the project numbers if I scroll through the records. It seems that my form
will create blank records if I hold the scroll bar down (at the bottom of the
form) and then it assigns different project numbers to records that are
already existing.

Hi Gretchen,

how about...

'~~~~~~~~~~~~~~~~~~~~~
if nz(Me.ProjectNumber,0) = 0 then
Me.ProjectNumber = Nz(DMax("ProjectNumber", _
"[Project Table]"), 0) + 1
end if
'~~~~~~~~~~~~~~~~~~~~~

instead of launching the code as you are, it should probably be launched
on the form BeforeInsert event, which happens after the first keystroke
to create a new record -- then you do not need the IF statement...

"It also resets all the project numbers in the table"

really?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a form that auto populates a project number using the following code:

Private Sub Project_Number_Enter()
Me!ProjectNumber = Nz(DMax("ProjectNumber", "[Project Table]"), 0) + 1
End Sub

When a user is in the form and minimizes the form it resets the project
number to the next number. It appears to refresh this field and runs the
code again. It also resets all the project numbers in the table. Obviously,
I can't have this! Any suggestions?

 

[strive4peace]

Hi Gretchen,

can you email your database to me and tell me what to enter so that I
can duplicate the problem? There must be something else going on...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your help Crystal,
I tried this code on the "before update" and it did not work at all...I'm
not getting a project number assigned at any point in the form. Also, yes,
what is so strange about this form is that it will automatically reset all
the project numbers if I scroll through the records. It seems that my form
will create blank records if I hold the scroll bar down (at the bottom of the
form) and then it assigns different project numbers to records that are
already existing.

Hi Gretchen,

how about...

'~~~~~~~~~~~~~~~~~~~~~
if nz(Me.ProjectNumber,0) = 0 then
Me.ProjectNumber = Nz(DMax("ProjectNumber", _
"[Project Table]"), 0) + 1
end if
'~~~~~~~~~~~~~~~~~~~~~

instead of launching the code as you are, it should probably be launched
on the form BeforeInsert event, which happens after the first keystroke
to create a new record -- then you do not need the IF statement...

"It also resets all the project numbers in the table"

really?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a form that auto populates a project number using the following code:

Private Sub Project_Number_Enter()
Me!ProjectNumber = Nz(DMax("ProjectNumber", "[Project Table]"), 0) + 1
End Sub

When a user is in the form and minimizes the form it resets the project
number to the next number. It appears to refresh this field and runs the
code again. It also resets all the project numbers in the table. Obviously,
I can't have this! Any suggestions?