none
Populate a field on a form from a max value in a SQL table RRS feed

  • 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?
    Sunday, November 8, 2015 5:12 PM

Answers

  • Hi,

    Try changing the following line:

    Text51.Value = sqlText

    to this:

    Text51.Value = arcRST.Fields(0)

    Hope that helps...

    • Marked as answer by system243trd Monday, November 9, 2015 7:32 PM
    Monday, November 9, 2015 4:00 PM

All replies

  • 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...

    Sunday, November 8, 2015 6:14 PM
  • Thanks. 

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

    Sunday, November 8, 2015 9:51 PM
  • 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.
    Sunday, November 8, 2015 11:17 PM
  • 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?


    Monday, November 9, 2015 11:13 AM
  • Hi,

    Try changing the following line:

    Text51.Value = sqlText

    to this:

    Text51.Value = arcRST.Fields(0)

    Hope that helps...

    • Marked as answer by system243trd Monday, November 9, 2015 7:32 PM
    Monday, November 9, 2015 4:00 PM