More help on DMax+1

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

Tina

Now, when I click on the add button on get the error
message "type mismatch". Here's the code behind my form.
Am I missing something?????

Option Explicit
Option Compare Database
Private Sub CustomerID_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID] = '" & Me![CustomerID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
Dim res As Long
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




Private Sub Open_Service_Card_Click()
On Error GoTo Err_Open_Service_Card_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Service Cards"

stLinkCriteria = "[System_ID]=" & "'" & Me![System_ID]
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Service_Card_Click:
Exit Sub

Err_Open_Service_Card_Click:
MsgBox Err.Description
Resume Exit_Open_Service_Card_Click

End Sub
 
Tina,

When an object name contains spaces, you must enclose it in square brackets.
Try this:
res = DMax("System_ID", "[System Information]") + 1

Also, I would be inclined to consider the possibility that System_ID may be
null:
res = Nz(DMax("System_ID", "System Information"), 0) + 1

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
 
Since I don't see any prior information, I have to ask pretty basic
questions. What data type is Customer ID? If it's numeric, use just
rs.FindFirst "[CustID] = " & Me![CustomerID]

Of course, you could be having problems due to your use of Dim rs As Object.

Can you tell where the error is occurring?
 
I've included all of the background information
below....Any advice you can give, you would be much
appreciated. Customer ID is text (an example is AB0040).
When I place the code under the on click of the add
button, I get a type mismatch error. When I debugged and
compiled my code, it didn't highlight any errors. At one
point in my experimentation with the code, it had a
problem with the res of the code.
-----Original Message-----
Since I don't see any prior information, I have to ask pretty basic
questions. What data type is Customer ID? If it's numeric, use just
rs.FindFirst "[CustID] = " & Me![CustomerID]

Of course, you could be having problems due to your use of Dim rs As Object.

Can you tell where the error is occurring?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Now, when I click on the add button on get the error
message "type mismatch". Here's the code behind my form.
Am I missing something?????

Option Explicit
Option Compare Database
Private Sub CustomerID_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID] = '" & Me![CustomerID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
Dim res As Long
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




Private Sub Open_Service_Card_Click()
On Error GoTo Err_Open_Service_Card_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Service Cards"

stLinkCriteria = "[System_ID]=" & "'" & Me! [System_ID]
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Service_Card_Click:
Exit Sub

Err_Open_Service_Card_Click:
MsgBox Err.Description
Resume Exit_Open_Service_Card_Click

End Sub
Click to expand...


.
Click to expand...
 
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.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Tina said:
I've included all of the background information
below....Any advice you can give, you would be much
appreciated. Customer ID is text (an example is AB0040).
When I place the code under the on click of the add
button, I get a type mismatch error. When I debugged and
compiled my code, it didn't highlight any errors. At one
point in my experimentation with the code, it had a
problem with the res of the code.
-----Original Message-----
Since I don't see any prior information, I have to ask pretty basic
questions. What data type is Customer ID? If it's numeric, use just
rs.FindFirst "[CustID] = " & Me![CustomerID]

Of course, you could be having problems due to your use of Dim rs As Object.

Can you tell where the error is occurring?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Now, when I click on the add button on get the error
message "type mismatch". Here's the code behind my form.
Am I missing something?????

Option Explicit
Option Compare Database
Private Sub CustomerID_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID] = '" & Me![CustomerID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec
Dim res As Long
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




Private Sub Open_Service_Card_Click()
On Error GoTo Err_Open_Service_Card_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Service Cards"

stLinkCriteria = "[System_ID]=" & "'" & Me! [System_ID]
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Service_Card_Click:
Exit Sub

Err_Open_Service_Card_Click:
MsgBox Err.Description
Resume Exit_Open_Service_Card_Click

End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
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
 
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


Tina said:
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...
 
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...
 
Back
Top