A little bit more help on DMax +1

  • Thread starter Thread starter Tina
  • Start date Start date
T

Tina

When I changed the code in the first option, I got the
same Type Mismatch error when I clicked the add button....

I think the problem may be that res has not been
declared. How do I declare it and where do I declare
it???? As for the datatype of System_Information it is
text (i.e. AB0040). Does this make a difference and how?
Thank you again for all of your help!!! It is greatly
appreciated!!!
-----Original Message-----
Tina,

The first option gave you a syntax error, because you included part of the
Nz syntax. Use this instead:
res=DMax("System_ID", "[System Information]") +1

For the second option to return a Type Mismatch is strange. Where is res
declared, and what datatype is it?

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


I tried both codes. With the first one, when I pressed on
the add button, the code box opened with the whole line
highlighted and an error message that said "syntax
error". When I tried the second code and clicked the add
button, an error box appeared with the error message Type
Mismatch.


Here's what the codes looked like....Do I have it in the
right spot or am I leaving something out???? Thank you so
much for all of your help!! It is greatly appreciated!!!

----First Option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res=DMax("System_ID", "[System Information]"),0) +1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

----Or second option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = Nz(DMax("System_ID", "[System Information]"), 0) +
1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub
-----Original Message-----
As Grahan suggested, try

res = DMax("System_ID", "[System Information]") + 1

or even

res = Nz(DMax("System_ID", "[System
Click to expand...
Information]"),0)
+ 1
Please indicate what line of the code was causing the problem if that
doesn't solve it.
Click to expand...
Click to expand...
Click to expand...
 
Tina,

If res hasn't been declared, then you probably also don't have [Option
Explicit] set.

From any code module, select Tools --> Options, and tick [Require Variable
Declaration]. Then go into *ever* module in your database, and add the
following text directly beneath the "Option Compare Database" line.

Option Explicit

However, even if this is the case, res would be created as a Variant
datatype, which would accept the value you're trying to put into it. No, my
feeling is that System_ID is not numeric. Is that so?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Tina said:
When I changed the code in the first option, I got the
same Type Mismatch error when I clicked the add button....

I think the problem may be that res has not been
declared. How do I declare it and where do I declare
it???? As for the datatype of System_Information it is
text (i.e. AB0040). Does this make a difference and how?
Thank you again for all of your help!!! It is greatly
appreciated!!!
-----Original Message-----
Tina,

The first option gave you a syntax error, because you included part of the
Nz syntax. Use this instead:
res=DMax("System_ID", "[System Information]") +1

For the second option to return a Type Mismatch is strange. Where is res
declared, and what datatype is it?

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


I tried both codes. With the first one, when I pressed on
the add button, the code box opened with the whole line
highlighted and an error message that said "syntax
error". When I tried the second code and clicked the add
button, an error box appeared with the error message Type
Mismatch.


Here's what the codes looked like....Do I have it in the
right spot or am I leaving something out???? Thank you so
much for all of your help!! It is greatly appreciated!!!

----First Option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res=DMax("System_ID", "[System Information]"),0) +1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

----Or second option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = Nz(DMax("System_ID", "[System Information]"), 0) +
1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

-----Original Message-----
As Grahan suggested, try

res = DMax("System_ID", "[System Information]") + 1

or even

res = Nz(DMax("System_ID", "[System Information]"),0)
+ 1

Please indicate what line of the code was causing the
problem if that
doesn't solve it.
Click to expand...
Click to expand...
Click to expand...
 
I selected Require Variable Declaration under Options.
Then, I added Option Explicit directly beneath Option
Compare Database in every module in my database.

After reading the definitions of numeric and variant, I
think that System_ID is not numeric and is variant.

However, when I click the add button on my form, it
automatically opens the module with the error message
Compile Error: Variable Not Defined and has res
highlighted. WHen I walk through the debugger it
highlights the beginning of the Add_Record_Click Module.
Am I trying to add the code in the wrong place???? I've
included a copy of the code as I have entered it.

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = DMax("System_ID", "[System Information]") + 1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Thanks again for all of your help!!
-----Original Message-----
Tina,

If res hasn't been declared, then you probably also don't have [Option
Explicit] set.

From any code module, select Tools --> Options, and tick [Require Variable
Declaration]. Then go into *ever* module in your database, and add the
following text directly beneath the "Option Compare Database" line.

Option Explicit

However, even if this is the case, res would be created as a Variant
datatype, which would accept the value you're trying to put into it. No, my
feeling is that System_ID is not numeric. Is that so?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

When I changed the code in the first option, I got the
same Type Mismatch error when I clicked the add button....

I think the problem may be that res has not been
declared. How do I declare it and where do I declare
it???? As for the datatype of System_Information it is
text (i.e. AB0040). Does this make a difference and how?
Thank you again for all of your help!!! It is greatly
appreciated!!!
-----Original Message-----
Tina,

The first option gave you a syntax error, because you included part of the
Nz syntax. Use this instead:
res=DMax("System_ID", "[System Information]") +1

For the second option to return a Type Mismatch is strange. Where is res
declared, and what datatype is it?

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


I tried both codes. With the first one, when I
Click to expand...
pressed
on
the add button, the code box opened with the whole line
highlighted and an error message that said "syntax
error". When I tried the second code and clicked the add
button, an error box appeared with the error message Type
Mismatch.


Here's what the codes looked like....Do I have it in the
right spot or am I leaving something out???? Thank
Click to expand...
you
so
much for all of your help!! It is greatly appreciated!!!

----First Option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res=DMax("System_ID", "[System Information]"),0) +1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

----Or second option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = Nz(DMax("System_ID", "[System Information]"), 0) +
1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

-----Original Message-----
As Grahan suggested, try

res = DMax("System_ID", "[System Information]") + 1

or even

res = Nz(DMax("System_ID", "[System Information]"),0)
+ 1

Please indicate what line of the code was causing the
problem if that
doesn't solve it.
Click to expand...
Click to expand...


.
Click to expand...
 
For this code to work res needs to be the name of a control on your form that
you wish to be set to DMax + 1.

What is more of an issue is the data type of System_ID as you can only add one
to a number. If it is string (variant is not a valid data type for a column)
then you will have to extract the number from the string, add one and convert
it back to a string.

Incidentally, if you get in the habit of always compiling your code before you
save the edited form, it saves you from getting compile errors at run-time.
 
Tina,

You must declare res somewhere, and I suggest you declare it in the
procedure in which it is used:
Private Sub Add_Record_Click()
Dim rs As Long

On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec
res = DMax("System_ID", "[System Information]") + 1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click
End Sub

But you surely want to use the value that res will contain, so it might be
worthwhile to put that value into a textbox (or wherever you want to use
it). For example:
res = DMax("System_ID", "[System Information]") + 1
Me.txtMyTextBox = res

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Tina said:
I selected Require Variable Declaration under Options.
Then, I added Option Explicit directly beneath Option
Compare Database in every module in my database.

After reading the definitions of numeric and variant, I
think that System_ID is not numeric and is variant.

However, when I click the add button on my form, it
automatically opens the module with the error message
Compile Error: Variable Not Defined and has res
highlighted. WHen I walk through the debugger it
highlights the beginning of the Add_Record_Click Module.
Am I trying to add the code in the wrong place???? I've
included a copy of the code as I have entered it.

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = DMax("System_ID", "[System Information]") + 1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Thanks again for all of your help!!
-----Original Message-----
Tina,

If res hasn't been declared, then you probably also don't have [Option
Explicit] set.

From any code module, select Tools --> Options, and tick [Require Variable
Declaration]. Then go into *ever* module in your database, and add the
following text directly beneath the "Option Compare Database" line.

Option Explicit

However, even if this is the case, res would be created as a Variant
datatype, which would accept the value you're trying to put into it. No, my
feeling is that System_ID is not numeric. Is that so?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

When I changed the code in the first option, I got the
same Type Mismatch error when I clicked the add button....

I think the problem may be that res has not been
declared. How do I declare it and where do I declare
it???? As for the datatype of System_Information it is
text (i.e. AB0040). Does this make a difference and how?
Thank you again for all of your help!!! It is greatly
appreciated!!!
-----Original Message-----
Tina,

The first option gave you a syntax error, because you
included part of the
Nz syntax. Use this instead:
res=DMax("System_ID", "[System Information]") +1

For the second option to return a Type Mismatch is
strange. Where is res
declared, and what datatype is it?

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


message
I tried both codes. With the first one, when I pressed
on
the add button, the code box opened with the whole line
highlighted and an error message that said "syntax
error". When I tried the second code and clicked the
add
button, an error box appeared with the error message
Type
Mismatch.


Here's what the codes looked like....Do I have it in the
right spot or am I leaving something out???? Thank you
so
much for all of your help!! It is greatly appreciated!!!

----First Option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res=DMax("System_ID", "[System Information]"),0) +1
Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

----Or second option----
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
res = Nz(DMax("System_ID", "[System Information]"),
0) +
1

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

-----Original Message-----
As Grahan suggested, try

res = DMax("System_ID", "[System Information]") + 1

or even

res = Nz(DMax("System_ID", "[System
Information]"),0)
+ 1

Please indicate what line of the code was causing the
problem if that
doesn't solve it.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top