Answered by:
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?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?
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?
- Edited by system243trd Monday, November 9, 2015 11:13 AM
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