Modules not working or Its impossible to do the task

  • Thread starter Thread starter Sondra
  • Start date Start date
S

Sondra

I have addressed this question before and here I am again.
I went back to some old posts and tried to make this work,
but ran into problems. Here is my requirements:

1. User Form with auto-update field that sequentially puts
the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)
DocumentNumber
DocumentTitle
ChangeLead

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCEntry", "CCYear=" & Year
(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the
field is empty. When I enter the first field Document
Number the field stays blank. When I try to save the
record (field is still empty), it give me an error that
the form has an error. I've only changed the form by
adding the code above. When the form was autonumber it
worked fine; however, i had to create another database for
the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.
 
Sondra said:
I have addressed this question before and here I am again.
I went back to some old posts and tried to make this work,
but ran into problems. Here is my requirements:

1. User Form with auto-update field that sequentially puts
the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)
DocumentNumber
DocumentTitle
ChangeLead

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCEntry", "CCYear=" & Year
(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the
field is empty. When I enter the first field Document
Number the field stays blank. When I try to save the
record (field is still empty), it give me an error that
the form has an error. I've only changed the form by
adding the code above. When the form was autonumber it
worked fine; however, i had to create another database for
the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.
Click to expand...

So what is the exact error message you're getting?
 
I have a add new record on the form. When I press the
button I get:

Run Time error '3075'

Syntax error in query expression 'Max(CC#

Thanks
-----Original Message-----
Sondra said:
I have addressed this question before and here I am again.
I went back to some old posts and tried to make this work,
but ran into problems. Here is my requirements:

1. User Form with auto-update field that sequentially puts
the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)
DocumentNumber
DocumentTitle
ChangeLead

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCEntry", "CCYear=" & Year
(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the
field is empty. When I enter the first field Document
Number the field stays blank. When I try to save the
record (field is still empty), it give me an error that
the form has an error. I've only changed the form by
adding the code above. When the form was autonumber it
worked fine; however, i had to create another database for
the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.
Click to expand...

So what is the exact error message you're getting?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Sondra said:
I have a add new record on the form. When I press the
button I get:

Run Time error '3075'

Syntax error in query expression 'Max(CC#

Thanks
-----Original Message-----
Sondra said:
I have addressed this question before and here I am again.
I went back to some old posts and tried to make this work,
but ran into problems. Here is my requirements:

1. User Form with auto-update field that sequentially puts
the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)
DocumentNumber
DocumentTitle
ChangeLead

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCEntry", "CCYear=" & Year
(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the
field is empty. When I enter the first field Document
Number the field stays blank. When I try to save the
record (field is still empty), it give me an error that
the form has an error. I've only changed the form by
adding the code above. When the form was autonumber it
worked fine; however, i had to create another database for
the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.
Click to expand...

So what is the exact error message you're getting?
Click to expand...
Click to expand...

Change your DMax expression to put square brackets around "CC#", which
is not being intepreted properly because it contains the invalid
character "#". Your function should now look like this:

'----- start of revised code -----
Public Function GetNextCC()

GetNextCC = _
Nz(DMax("[CC#]", "CCEntry", "CCYear=" & Year(Date)), 0) _
+ 1

End Function
'----- end of revised code -----
 
Sondr said:
It still isn't working. I am having nooooooo luck.
Click to expand...

More information, please. "It isn't working" doesn't tell us much. Are
you getting the same error message? A different one? No message, but
not the result you want? What?

Please copy the code you're using now and paste it into a reply.

You said before that the error appeared when you clicked a button.
Please post the code behind that button, and any other relevant code.
 
Sondr said:
It still isn't working. I am having nooooooo luck.
Click to expand...

Sondr,

Your code IS working, it is just not working the way you want it to!

In your table, you have [CC#] defined as a long integer. So when you add a
new record, Access, by default, enters a 0 (zero) in the field EVEN IF you do
not have a default value defined for the field (or control)!

The Form_BeforeUpdate() code has

If IsNull([CC#]) Then

but CC# is not null because there is a 0 in the field. Therefore, the field
in not changed.

Since you would never have a negative number (right?) in the CC# field,
change the code to

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IMe![CC#] < 1 Then
Me![CC#] = GetNextCC()
End If
End Sub


Make sure the CC# in the DMax() function has brackets around it >> "[CC#]"

HTH,

Steve
 
I'm sorry, Steve, but in the interest of accuracy and to avoid confusion I
have to say that this is incorrect. Access does not do that. What Access
will do, and this may be what misled you, is that when you add a numeric
field using the UI, it will automatically give that field a default value of
zero. But that is not the same thing as 'entering a zero even if you do not
have a default value defined'. If you remove the automatically added default
value, then the default value will be Null.

To illustrate this, try running the following code ...

Public Sub TestIt()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
db.Execute "CREATE TABLE MyTestTable (MyTestLong LONG, MyTestText TEXT)"
db.Execute "INSERT INTO MyTestTable (MyTestText) VALUES ('one')"
Set rst = db.OpenRecordset("MyTestTable")
Debug.Print IsNull(rst.Fields("MyTestLong"))
rst.Close

End Sub

Result of running the above code in the Immediate window ...

testit
True

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


SteveS said:
Sondr said:
It still isn't working. I am having nooooooo luck.
Click to expand...

Sondr,

Your code IS working, it is just not working the way you want it to!

In your table, you have [CC#] defined as a long integer. So when you add a
new record, Access, by default, enters a 0 (zero) in the field EVEN IF you
do
not have a default value defined for the field (or control)!

The Form_BeforeUpdate() code has

If IsNull([CC#]) Then

but CC# is not null because there is a 0 in the field. Therefore, the
field
in not changed.

Since you would never have a negative number (right?) in the CC# field,
change the code to

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IMe![CC#] < 1 Then
Me![CC#] = GetNextCC()
End If
End Sub


Make sure the CC# in the DMax() function has brackets around it >>
"[CC#]"

HTH,

Steve
Click to expand...
 
Oops.... I was going from memory.... When I added a text box to a form, I
checked the default value - nothing was there. What I didn't check is the
default value for the field in the table. There *was* a default value of zero
there! I guess I need to get off the Pepsi and get back on Jolt to wake me up.

Thanks, Brendan. I had the idea (kinda), but you explained it better.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Brendan Reynolds said:
I'm sorry, Steve, but in the interest of accuracy and to avoid confusion I
have to say that this is incorrect. Access does not do that. What Access
will do, and this may be what misled you, is that when you add a numeric
field using the UI, it will automatically give that field a default value of
zero. But that is not the same thing as 'entering a zero even if you do not
have a default value defined'. If you remove the automatically added default
value, then the default value will be Null.

To illustrate this, try running the following code ...

Public Sub TestIt()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
db.Execute "CREATE TABLE MyTestTable (MyTestLong LONG, MyTestText TEXT)"
db.Execute "INSERT INTO MyTestTable (MyTestText) VALUES ('one')"
Set rst = db.OpenRecordset("MyTestTable")
Debug.Print IsNull(rst.Fields("MyTestLong"))
rst.Close

End Sub

Result of running the above code in the Immediate window ...

testit
True

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


SteveS said:
Sondr said:
It still isn't working. I am having nooooooo luck.
Click to expand...

Sondr,

Your code IS working, it is just not working the way you want it to!

In your table, you have [CC#] defined as a long integer. So when you add a
new record, Access, by default, enters a 0 (zero) in the field EVEN IF you
do
not have a default value defined for the field (or control)!

The Form_BeforeUpdate() code has

If IsNull([CC#]) Then

but CC# is not null because there is a 0 in the field. Therefore, the
field
in not changed.

Since you would never have a negative number (right?) in the CC# field,
change the code to

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IMe![CC#] < 1 Then
Me![CC#] = GetNextCC()
End If
End Sub


Make sure the CC# in the DMax() function has brackets around it >>
"[CC#]"

HTH,

Steve
Click to expand...
Click to expand...
 
SteveS said:
Oops.... I was going from memory.... When I added a text box to a
form, I checked the default value - nothing was there. What I didn't
check is the default value for the field in the table. There *was* a
default value of zero there! I guess I need to get off the Pepsi and
get back on Jolt to wake me up.
Click to expand...

The presence of a default value for the field, wherever it comes from,
could indeed be the reason the function isn't working for Sondra. But
we won't know until she tells us in what way it isn't working.
 
Sorry for all the confusion:

ModNumber***********

Public Function GetNextCC()
GetNextCC = Nz(DMax("[cc#]",[BioMedEntry], "YearNumber=" &
Year(Date)), 0) + 1
End Function

Form**************
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

Form Command Buttons**************
Add Additional Record Button =
Macro BioMedAdditional Steps:
Close Form BioMedEntry
Open Form BioMed Entry in Add Mode

Done Button =
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click


DoCmd.Close

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub

Please let me know if you need more information.

Thanks for all your help.
 
Sondra,

After reading Brendan's and Dirk's replies about default values for numeric
fields (and my goof), try this code; it takes into account a default value of
0 or a null:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me![CC#] < 1 or IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub


Steve
 
Back
Top