Compile Error using Dmax funtion

L

Little Penny

Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
 
R

Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData], "OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob
 
L

Little Penny

Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData], "OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little said:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
 
R

Rob van Gelder

Check that you have named ranges called tbl_OperatorLogJobData and OpLogJobDataID?

Cheers,
Rob



Little said:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData], "OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little said:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
L

Little Penny

Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









Check that you have named ranges called tbl_OperatorLogJobData and OpLogJobDataID?

Cheers,
Rob



Little said:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData], "OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
L

Little Penny

Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









Check that you have named ranges called tbl_OperatorLogJobData and OpLogJobDataID?

Cheers,
Rob



Little said:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData], "OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim



Little Penny said:
Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
L

Little Penny

Thanks Tim that did it.....




Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim



Little Penny said:
Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









On Wed, 09 Dec 2009 17:29:29 +1300, Rob van Gelder


Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

No problem, but remember this is *not* a good way to get the key for a
just-inserted record unless there's only ever one user accessing the
database at any time....

Tim

Little Penny said:
Thanks Tim that did it.....




Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim



Little Penny said:
Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










On Wed, 09 Dec 2009 08:11:35 -0500, Little Penny




Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









On Wed, 09 Dec 2009 17:29:29 +1300, Rob van Gelder


Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
L

Little Penny

Understood there will only be one. If I want to chanage to handle
multi users in the future what should I read up on?



No problem, but remember this is *not* a good way to get the key for a
just-inserted record unless there's only ever one user accessing the
database at any time....

Tim

Little Penny said:
Thanks Tim that did it.....




Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim





Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










On Wed, 09 Dec 2009 08:11:35 -0500, Little Penny




Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









On Wed, 09 Dec 2009 17:29:29 +1300, Rob van Gelder


Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

I posted a reply to one of your other posts outlining how you could do this.
Tim


'%%%%%%%%%%%%%%%%%%%%%%%%%%
I'm not that familiar with DAO (usually use ADO though I know there are some
advantages to using DAO with Access).


In ADO (same in DAO?) you can get the value of the id field (if it's an
autonumber) by reading it back from the recordset after running an insert.


So, if you had a table:
id (autonumber)
otherfield (eg. string)


you can do something like:


'*******************
dim pk


Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)
rs1.AddNew
rs1.Fields("otherfield").value="Blah"
rs1.Update
pk = rs1.Fields("id").value 'should be populated with the "autonumber" key
'*******************






Little Penny said:
Understood there will only be one. If I want to chanage to handle
multi users in the future what should I read up on?



No problem, but remember this is *not* a good way to get the key for a
just-inserted record unless there's only ever one user accessing the
database at any time....

Tim

Little Penny said:
Thanks Tim that did it.....




On Wed, 9 Dec 2009 19:22:52 -0800, "Tim Williams"

Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim





Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










On Wed, 09 Dec 2009 08:11:35 -0500, Little Penny




Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









On Wed, 09 Dec 2009 17:29:29 +1300, Rob van Gelder


Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as
you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error
when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated
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

Run-Time Error 3075 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Dsum and Dmax with structured references 1
DMax on existing record to increment serialized # fails 1
"Compile error in table level validation expression " after format 0
Outlook Add-in - Javascript based using React JS - VS Code + WebPack - Unable to call external Web Service 0
VSTO C# add in Outlook.Appointment error 0
Problem with SET & REF function 0

Top