Dmax or Stored Procedure?

[lukeargent]

I have a problem when using ADP connected to SQL Server 2000 that works fine when using a plain and simple MDB file.

I have a field that I want to increment based on the last value in the table but matching the linking field between the 2 tables.
I.E. a DVD could have 10 reviews of which it would appear as DVD_ID = 1, ReviewID = 1, 2, 3, 4 etc etc, and then DVD_ID = 2, ReviewID = 1, 2, 3, 4 etc etc. As you can see the ReviewID needs to revert back to 1 for every DVD_ID.

When using the MDB file, I create a button called btnNewID and give the following code on the "On_click" event;

me.ReviewID = nz(Dmax("ReviewID", "tbl_Review", "DVD_ID= " & Me.[DVD_ID]), 0) + 1

This works perfectly, but when I try to run whilst connected to the SQL server, I get error;

"invalid syntax near ')'."

Everything I've read suggests that the code doesn't support SQL Server and people are saying use a Stored Proc instead, but I'm new to SQL so I don't know anyway of creating this SP, and then calling it from a button on the form.

Has anyone been in a similar situation or at least know a way I can get around this? Help is much appriciated, I've lost a lot of hair the last couple of days!!

Kind Regards,
Luke Argent
IT Support Technician

 

[PaulBricker]

SQL Server doesn't recognize Access built in functions like DMax, but I think you can do this in a VBA module which should recognize the function. What you would do, is write your expression inside a function and then set the value of your field from that function. Then just call the function from your button click.

Paul

 

[lukeargent]

Hi Paul,

Sorry my post must of come across wrong - it's already in a VBA module on the "ON Click" event of the button. It's a private sub - but are you suggesting to use a private function instead?

Thanks
Luke

 

[PaulBricker]

No, you did make it clear I just missed it, but you should still be able to do what you want, just the way you have it set up. I reproduced the expression in the NorthwindCS.adp database using XP and SQL Server 2000 and didn't have any problem. So now I'm not sure what to tell you except you should be able to do it. Your syntax is for a numeric field, which I assume it should be because of the incrementing so that shouldn't be an issue, but try the full syntax

me.ReviewID = nz(Dmax("ReviewID", "tbl_Review", "DVD_ID= " & Me.[DVD_ID]& ""), 0) + 1

Sorry, wish I could suggest more.

Paul

 

[cmmrfrds]

Send the SQL you want over the current project connection to sql server.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, yourid as integer

Set cn = CurrentProject.Connection
sql1 = "select max(id) as myid from yourtable"
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly
Yourid = rs(0)
'-- rs(0) is the first field position and there is only 1 field being returned. This is short hand. You could reference by name. i.e.
Yourid = rs!myid

rs.Close
Set rs = Nothing

 

[lukeargent]

Thanks Paul, still no luck!!!

I tried the new code and still get the error...

The above I have provided is an example of what I'm trying to do.
I'm actually using different fields and tables but the context is the same, I even renamed my second table from DWR_Sequence to DWRSequence in case it was that it didn't like the _

CMMRFRDS - I will try that at some point, I'm awaiting for a couple of books to come through as I'm considering moving the application into VB.Net, ADO.Net.

Thanks for all you help so far guys,
Luke