Populate a field on a form from a max value in a SQL table

Question

I have an application with two forms (form1 and form2), form1 contains a button which opens form2 and form2 contains a text box that is unbound.  I also have a table called table1 in SQLServer that contains a list of ID's.  I want to be able to add some code to the button in form1 so that it will look at the maximum value of the table1 ID and add it to the field in form2.  Can anyone help?

Answer 1

Hi,

If you're using Access and the SQL Server table is linked to it, then you should be able to use the DMax() function. As an example:

Private Sub ButtonName_Click()

Dim lngID As Long

lngID = Nz(DMax("ID", "TableName"),0)

DoCmd.OpenForm "Form2", OpenArgs:=lngID

End Sub

Then, in the Open event of Form2:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then

  Me.TextboxName = Me.OpenArgs

End If

End Sub

Hope that helps...

Answer 2

Thanks. 

How can I do this if my Access database is not linked to the SQL Server table? 

Answer 3

Thanks. 

How can I do this if my Access database is not linked to the SQL Server table? 

Are you saying you don't have a link to the SQL Server table? If your database is not linked to the SQL Server table, how do you expect to retrieve the information? Are you downloading a temporary copy of the table? Since we know nothing about your setup, you may have to provide additional information how the data in SQL Server will be accessible to your Access database.

Answer 4

It is a legacy system that I am trying to unpick.

It is linked to SQL but uses code when carrying out any selects, updates, etc.

I have tried the following code, to open the connection and assign textbox 502 the value of the SQL result but this isn’t working

Private Sub Commandanex_Click()

Dim arcRST As New ADODB.Recordset

Dim sqlText As String

 sqlText = "select max(anid) from table1"

arcRST.Open sqlText, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 Text51.Value = sqlText

arcRST.Close

End Sub

Can anyone help?

Answer 5

Hi,

Try changing the following line:

Text51.Value = sqlText

to this:

Text51.Value = arcRST.Fields(0)

Hope that helps...