none
SQL in VBA in Access 2013 Assigning value to text box RRS feed

  • Question

  • Thanks for being here. I am a newbie needing to know how to assign a variable with the integer answer to a query, then set a text box value with that variable value, and then set another text box value with the variable value + 1. Also, would like to have my code reviewed overall for correctness. Any help will be very much appreciated! Thanks!

    Option Compare Database

    Option Explicit

    _______________________

    Private Sub TextBox1_GotFocus ()

    Dim MyDB as DAO.Database

    Dim MyTable as DAO.Recordset

    Dim strSQL As String

    Dim iMyVariable as Integer

    On Error GoTo ErrorHandler

    Set MyDB = CurrentDb

    strSQL = “SELECT MAX(MyField) FROM MyTable”

     

    (Missing code to assign integer result of strSQL to iMyVariable)

    (Missing code to set TextBox1 = iMyVariable)

    (Missing code to set TextBox2 = TextBox1 + 1) (OR set TextBox2 = iMyVariable + 1)

     

    ErrorHandler:

       MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

    ‘CLEAN UP

    MyTable.Close

    MyDB.Close

    Set MyTable = Nothing

    Set MyDB = Nothing

    End Sub

    Monday, April 28, 2014 1:34 PM

Answers

  • No MyTable is a variable for the DAO recordset object. You only need to change the MyTable with your actual table name SQL.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    • Marked as answer by bglegan Monday, April 28, 2014 2:50 PM
    Monday, April 28, 2014 2:32 PM
  • Thanks so much. I got some errors as I posted here a few minutes ago but it was because I was not putting the Set command in front of the statement. Now it work like a charm. Thanks again for all your help. Have a great day! Also, thanks for the link to Recordsets for Beginners. That's where I discovered I was leaving out the Set.
    • Marked as answer by bglegan Monday, April 28, 2014 2:51 PM
    Monday, April 28, 2014 2:50 PM

All replies

  • Something like this,

    Private Sub TextBox1_GotFocus ()
    	Dim MyDB as DAO.Database, MyTable as DAO.Recordset
    	Dim strSQL As String, iMyVariable as Integer
    
    On Error GoTo ErrorHandler
    	Set MyDB = CurrentDb
    	strSQL = "SELECT MAX(MyField) FROM MyTable;"
    	
    	Set MyTable = MyDB.OpenRecordset(strSQL)
    	
    	If MyTable.Reocordcount > 0 Then
    		iMyVariable = MyTable.Fields(0)
    	Else
    		iMyVariable = 0
    	End If
    	
    	Me.TextBox1 = iMyVariable
    	Me.TextBox2 = iMyVariable + 1
    	
    	'CLEAN UP
    ErrorCleanUp:
    	MyTable.Close
    	MyDB.Close
    	Set MyTable = Nothing
    	Set MyDB = Nothing
    	Exit Sub
    ErrorHandler:
    	MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    	Resume ErrorCleanUp
    End Sub

    Here is a link on how to use Recordsets : Recordsets For beginners


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered




    Monday, April 28, 2014 1:41 PM
  • Wow, thank you so much for a quick reply! This is very, very helpful. Just one question: do I use the same table name in your code as I do in the Dim statement? Probably do, but have to ask the dumb question anyway. Thanks again!
    Monday, April 28, 2014 1:56 PM
  • No MyTable is a variable for the DAO recordset object. You only need to change the MyTable with your actual table name SQL.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    • Marked as answer by bglegan Monday, April 28, 2014 2:50 PM
    Monday, April 28, 2014 2:32 PM
  • When I use the name of my table in the line
    MyTable = MyDB.OpenRecordset(strSQL)
    I get an "Invalid use of property" error message. When I use tblTemp, I get a "Variable not defined" error message. What am I doing wrong?? Thanks.
    Monday, April 28, 2014 2:36 PM
  • No you need to change it,

    strSQL = "SELECT Max(yourFieldName) FROM yourActualTableName"
    
    MyTable = MyDB.OpenRecordset(strSQL)


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Monday, April 28, 2014 2:46 PM
  • Thanks so much. I got some errors as I posted here a few minutes ago but it was because I was not putting the Set command in front of the statement. Now it work like a charm. Thanks again for all your help. Have a great day! Also, thanks for the link to Recordsets for Beginners. That's where I discovered I was leaving out the Set.
    • Marked as answer by bglegan Monday, April 28, 2014 2:51 PM
    Monday, April 28, 2014 2:50 PM