More help on DMax+1

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
 
G

Graham R Seach

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
 
D

Douglas J. Steele

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?
 
T

Tina

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...
 
D

Douglas J. Steele

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...
 
T

Tina

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
 
G

Graham R Seach

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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help 1
Command Button not working on form - HELP!!! 3
A little bit more help on DMax +1 4
Error 2237 8
Copy of record not copying all fields 4
Deleting record with primary key 2
Criteria when opening form 1
Clear a form 1

Top