Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2015
    Posts
    7

    Answered: sending query from VBA from a function

    ok, so i want to populate a bunch of text boxes on a form from a series of queries that are generated by VBA.

    I've created a function that can take in the variables/values i want for the query and then look a case structure and create a query string. I've used a COUNT function, so it will give me only one value. How would I get the text box to populate this query string result? I originally had this set up in excel (it bounced the info off the DB thru the ADODB connection), but now i want to just bring everything over to access. I used the case structure b/c the query structures will be the same for a lot of outputs, only the parameters/variables I put in will be different.

    So, I have it set up like this: FunctionName(var1, var2, var2, case)
    and the function will generate the correct query string with the variables based on the case number.

    so the function output will create the query string with all my parameters like : "SELECT...FROM..."

    I want to end up with something like this: Forms![FormName]![Text4].Value = <<query output from query string generated by function>> ... but not sure what code to put b/w << >>

    Could i use a Dlookup somehow? or OpenRecordSet? I would like to recycle the code I already have. Plus, I don't want to make like 50 different, separate queries in access.

    Super thanks in advance!!!

  2. Best Answer
    Posted by Sinndho

    "You extract a value from a query (or its SQL expression) using a Recordset object (either DAO.Recordset or ADODB.Recodset).

    If all or most controls in a form are data-bound to the same query, you can use the RecordSource property of the form (an Access form object includes a DAO.Recordset object), while you use the ControlSource property of the control to bind it to a column.
    Code:
    MyForm.RecordSource = FunctionName(var1, var2, var2, case)
    If not, you can also use your own Recordset:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT COUNT(*) FROM Tbl_Users;"    ' SQL expression of the query.
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)    ' Open the Recordset object.
        Me.Text_UsersCount.Value = rst.Fields(0).Value    ' Assign the value of the first column of the data set to the control.
        rst.Close    ' Close the Recordset object.
        Set rst = Nothing    ' Clean up.
        
    End Sub
    "


  3. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You extract a value from a query (or its SQL expression) using a Recordset object (either DAO.Recordset or ADODB.Recodset).

    If all or most controls in a form are data-bound to the same query, you can use the RecordSource property of the form (an Access form object includes a DAO.Recordset object), while you use the ControlSource property of the control to bind it to a column.
    Code:
    MyForm.RecordSource = FunctionName(var1, var2, var2, case)
    If not, you can also use your own Recordset:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT COUNT(*) FROM Tbl_Users;"    ' SQL expression of the query.
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)    ' Open the Recordset object.
        Me.Text_UsersCount.Value = rst.Fields(0).Value    ' Assign the value of the first column of the data set to the control.
        rst.Close    ' Close the Recordset object.
        Set rst = Nothing    ' Clean up.
        
    End Sub
    Have a nice day!

  4. #3
    Join Date
    Aug 2015
    Posts
    7
    That worked beautifully!

    I had to use the code and break pieces into the main sub and the function, but it worked like a charm. Thanks a ton!
    Last edited by Niki O; 08-12-15 at 11:36.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no current record is a pefectly valid response
    are there rows that match your new recordsource
    ..you can test for an emtpy recordset by seeign if both the eof and bof properties are 0... if so you have no rows.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Aug 2015
    Posts
    7
    healdem, i just figured that out myself after jacking with it a bit (i edited that post). That's precisely what the issue was. Once i changed it to a record I KNEW was there, it worked great.

    But real quick - if it doesn't pull any records (I'll check the properties first), how would i then tell that text box value to put in a 0 for that value? sorry for the rookie question. something like an if statement at the end, like: if rst is null, set the record value to 0.

    i tried:
    if rst.RecordCount=0 Then
    rst.edit
    rst("Fields(0)").Value = "0"
    End If

    but it doesn't like the rst.edit line. Telling me the data is read-only. is there an easy work around for this?
    Last edited by Niki O; 08-12-15 at 12:08.

  7. #6
    Join Date
    Aug 2015
    Posts
    7
    got it. I used:

    If rst.BOF And rst.EOF = True Then
    QueryValue = 0
    Else
    QueryValue = rst.Fields(0).Value
    End If

    Thanks you guys!

  8. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Niki O View Post
    healdem, i just figured that out myself after jacking with it a bit (i edited that post). That's precisely what the issue was. Once i changed it to a record I KNEW was there, it worked great.

    But real quick - if it doesn't pull any records (I'll check the properties first), how would i then tell that text box value to put in a 0 for that value? sorry for the rookie question. something like an if statement at the end, like: if rst is null, set the record value to 0.

    i tried:
    if rst.RecordCount=0 Then
    rst.edit
    rst("Fields(0)").Value = "0"
    End If

    but it doesn't like the rst.edit line. Telling me the data is read-only. is there an easy work around for this?
    You can test whether the query returned record(s) or not using the EOF (or BOF property of the recordset:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT COUNT(*) FROM Tbl_Users;"    ' SQL expression of the query.
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)    ' Open the Recordset object.
        If rst.EOF = True then    ' Test: Does the recordset contains something.
            Me.Text_UsersCount.Value = 0    ' --> No.
        Else
            Me.Text_UsersCount.Value = rst.Fields(0).Value    ' --> Yes: Assign the value of the first column of the data set to the control.
        End If
        rst.Close    ' Close the Recordset object.
        Set rst = Nothing    ' Clean up.
        
    End Sub
    Have a nice day!

  9. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need to check both the bof and eof properties to see if a recordset is empty. IIRC BOF is true if the recordset pointer is set to the first row, or start of the recordset 'list'. if eof is also true then it measn there is nothing int he recordset.

    the recordcount property isnt' set untill the recordset is fully populated (IIRC you need to use the movelast method to set the recordcount property (and then movefirst methd to reset the recordset pointer to the first row.

    the movelast method comes at a cost (of Accerss fidnign the last row in the recordset, and oin a big table that can be painful)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •