Using DMAX field in Access Project (XP) connected to SQL 2000

[lukeargent]

Hi All,

I have come across some rather weird error that I can only assume is
something to do with my ADP file connecting to SQL Server 2000.
I'm using Access XP as my front end.

In simple terms:
I have a database which is in design for a new Development Request
system. The main 2 tables are linked one to many, by in this instance
say DWR_ID.
The main input screen is a tabbed form with a subform on the second
tab. The subform looks at tbl_Revisions, whilst the main form overlays
tbl_DWR. DWR_ID is shown on the form on the first tab, but is hidden
on the subform.
A field called RevisionID in tbl_Revisions, which is the main part of
the subform, needs to increment by 1.
A revision of 1 will exist for every single DWR that exists in the
database, so RevisionID is set to Duplicates OK. When a new revision
is created, I have a button that "On-Click" event - and when clicked
should look at the DWR_ID, look up any RevisionID's for that DWR_ID,
take the highest value and increment by 1.

The code for doing this in VBA is;
me.RevisionID = nz(DMAX("RevisionID", "tbl_revision", "DWR_ID= " &
Me.[DWR_ID]), 0) + 1

The above code works if I create a MDB file in the same way, but when
using my ADP file connected to the SQL Server, when the button is
pressed I get an error of;

Invalid Syntax near ')'.

Everywhere I've looked has suggested that the problem exists within the
code for the Criteria part of the DMAX command.

Has anybody got any ideas on this, I'm pretty good when it comes to
Access, but now I've got to the stage of trying to use SQL as the
back-end, I'm coming across more and more things I haven't got a clue
about!!

Help is much appriciated,

Luke Argent
IT Technical Support.

##no bits or bytes were harmed during the filming of this programme##

[MGFoster]

lukeargent wrote:

Hi All,

I have come across some rather weird error that I can only assume is
something to do with my ADP file connecting to SQL Server 2000.
I'm using Access XP as my front end.

In simple terms:
I have a database which is in design for a new Development Request
system. The main 2 tables are linked one to many, by in this instance
say DWR_ID.
The main input screen is a tabbed form with a subform on the second
tab. The subform looks at tbl_Revisions, whilst the main form overlays
tbl_DWR. DWR_ID is shown on the form on the first tab, but is hidden
on the subform.
A field called RevisionID in tbl_Revisions, which is the main part of
the subform, needs to increment by 1.
A revision of 1 will exist for every single DWR that exists in the
database, so RevisionID is set to Duplicates OK. When a new revision
is created, I have a button that "On-Click" event - and when clicked
should look at the DWR_ID, look up any RevisionID's for that DWR_ID,
take the highest value and increment by 1.

The code for doing this in VBA is;
me.RevisionID = nz(DMAX("RevisionID", "tbl_revision", "DWR_ID= " &
Me.[DWR_ID]), 0) + 1

The above code works if I create a MDB file in the same way, but when
using my ADP file connected to the SQL Server, when the button is
pressed I get an error of;

Invalid Syntax near ')'.

Everywhere I've looked has suggested that the problem exists within the
code for the Criteria part of the DMAX command.

Has anybody got any ideas on this, I'm pretty good when it comes to
Access, but now I've got to the stage of trying to use SQL as the
back-end, I'm coming across more and more things I haven't got a clue
about!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is the DWR_ID a string? If so, you have to delimit it with quotes (in
this case single-quotes):

nz(DMAX("RevisionID", "tbl_revision", _
"DWR_ID='" & Me.[DWR_ID] & "'"), 0) + 1

It may also be the reference to the Control "DWR_ID" (I'm assuming that
Me.DWR_ID is a reference to a control?). Perhaps if you put the value
of the control in a variable.

strDWRID = Me!DWR_ID
nz(DMAX("RevisionID", "tbl_revision", _
"DWR_ID='" & strDWRID & "'"), 0) + 1

Or, if you used the bang (!) indicator instead of the period indicator:

Me!DWR_ID instead of Me.DWR_ID.

If all else fails, try using a stored procedure.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiSu+4echKqOuFEgEQLALwCfT0bYppGY0SSwAs4vnEkypK 1RU8cAoOtS
7iiyCd6FedWFMaYwDaPEUqTg
=Od7g
-----END PGP SIGNATURE-----

[J. Clay]

Luke,

In my adp, I have found that is is faster to run a Stored procedure to get
data rather than using the DMax/Min/etc functions.

Just a suggestion.
J. Clay

"lukeargent" <lu*********@yahoo.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi All,

I have come across some rather weird error that I can only assume is
something to do with my ADP file connecting to SQL Server 2000.
I'm using Access XP as my front end.

In simple terms:
I have a database which is in design for a new Development Request
system. The main 2 tables are linked one to many, by in this instance
say DWR_ID.
The main input screen is a tabbed form with a subform on the second
tab. The subform looks at tbl_Revisions, whilst the main form overlays
tbl_DWR. DWR_ID is shown on the form on the first tab, but is hidden
on the subform.
A field called RevisionID in tbl_Revisions, which is the main part of
the subform, needs to increment by 1.
A revision of 1 will exist for every single DWR that exists in the
database, so RevisionID is set to Duplicates OK. When a new revision
is created, I have a button that "On-Click" event - and when clicked
should look at the DWR_ID, look up any RevisionID's for that DWR_ID,
take the highest value and increment by 1.

The code for doing this in VBA is;
me.RevisionID = nz(DMAX("RevisionID", "tbl_revision", "DWR_ID= " &
Me.[DWR_ID]), 0) + 1

The above code works if I create a MDB file in the same way, but when
using my ADP file connected to the SQL Server, when the button is
pressed I get an error of;

Invalid Syntax near ')'.

Everywhere I've looked has suggested that the problem exists within the
code for the Criteria part of the DMAX command.

Has anybody got any ideas on this, I'm pretty good when it comes to
Access, but now I've got to the stage of trying to use SQL as the
back-end, I'm coming across more and more things I haven't got a clue
about!!

Help is much appriciated,

Luke Argent
IT Technical Support.

##no bits or bytes were harmed during the filming of this programme##

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[lukeargent]

Thanks for the help, I have tried all of your suggestions but I still
keep getting the same error message back.

I even tried them in my test MDB file and they worked in there so I
know it wasn't a case of syntax as they are exactly the same in my MDB
and ADP files.

I'm going to look into a stored procedure, I haven't used one yet so
I'm going to try that this morning.

Cheers
Luke

[Ronald W. Roberts]

lukeargent wrote:

Thanks for the help, I have tried all of your suggestions but I still
keep getting the same error message back.

I even tried them in my test MDB file and they worked in there so I
know it wasn't a case of syntax as they are exactly the same in my MDB
and ADP files.

I'm going to look into a stored procedure, I haven't used one yet so
I'm going to try that this morning.

Cheers
Luke

Have you tried a select Top 1 ordered by?

Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com