I am trying to get he max value from a table that I will be updating in a form...
I am using this max value as the unique identifer.
I was doing this in Access 2003 but now in Access 2007...its not working...
I am trying these two ways...maybe someone can shed some light on this\t\t
The table is a LINKED SQL Table in SQL Server
Hope someone can help here...
In this example.
Complaint Field Name
dbo_Complaints_Table Linked SQL table name
Complaint is the Textbox name in the form
ERROR: Run time error '6' OVERFLOW
'populate ID box with largest value + 1
Dim varID As Integer
varID = Val(DMax("Complaint", "dbo_Complaints_Table")) + 1
Complaint = varID
I am getting errors on the ADODB Dim statement
Dim rs As ADODB.Recordset, MyVal As Currency
Set rs = New ADODB.Recordset
Open a Connection to the Recordset
rs.Open "SELECT MAX(Complaint) from dbo_Complaints_Table", CurrentProject.Connection
MyVal = rs.Fields(0).Value
Me.Text1.Value = Format(MyVal, "#,##0.00")
Set rs = Nothing
VIEWS ON THIS POST
Monday 22nd October 2012