Page 1 of 2 12 LastLast
Results 1 to 15 of 19

VBA To Move Through Recordset

  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    VBA To Move Through Recordset

    I have a query named addressinfo and I want to use the syntax
    Code:
    Set rs = db.OpenRecordset(addressInfo, dbOpenDynaset)
    but I am getting the error:
    Run-time error '3078'
    The Microsoft Access database engine cannot find the input table or


    query "". Make sure it exists and that its name is spelled correctly



    Yes the query exists and yes I am spelling it correctly. I have tried with and w/o
    Code:
    dbOpenDynaset

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,667
    Is addressInfo the name of a variable, or the name of your query?
    If it is the name of your query, it needs to be enclosed in quotes (otherwise, VBA is looking for a variable named addressinfo).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    @JoeM - that was it. I just needed the quotes.

    As my code progresses, I am attempting to assign variables to a textbook on my report, by using this syntax
    Code:
    If Not (rs.BOF And rs.EOF) Then
            Me.txtInfo.Text = vbavariable1 & rs![systeminfo]
    End If
    which gives me an error of
    Code:
    Run-time error '2185'
    You can't reference a property or method for a control unless the control has focus

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,513
    are you sure you want to set the Text property? The control must have focus if so, in which case you need to Me.txtInfo.SetFocus first.
    Otherwise, you probably want to set only the control value: Me.txtInfo = "something"
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by Micron View Post
    are you sure you want to set the Text property? The control must have focus if so, in which case you need to Me.txtInfo.SetFocus first.
    Otherwise, you probably want to set only the control value: Me.txtInfo = "something"
    I am wanting the text to appear I the textbook when the report opens. I added in your suggestion of
    Code:
    Me.txtInfo.SetFocus
    And If I use
    Code:
    Me.txtInfo = ....
    I get an error of
    Code:
    Run-time error '-2147352567'
    You can't assign a value to this object
    Iif I use
    Code:
    Me.txtInfo.Text
    I get an error of
    Code:
    Run-time error '2101' The setting you entered isn't valid for this property

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,513
    the new error suggests this is a calculated control. If it is, you can't set text or value properties in it. You would have to set the value for some other control and have a calculated control get it from there.

    not sure what this means
    appear I the textbook
    I'm guessing that's autocorrect on a phone, but even if I substitute in for "I", I still don't know where a textbook comes in to play here.

    you say you tried setting the focus, but in the correct order? Maybe post the code so that the order of lines can be certain rather than just snippets of it because the order is important. Also, this alone Me.txtInfo.Text is incomplete.

    When I asked about setting the text property of your control (as opposed to its default: .Value) I wasn't sure you knew the difference. Maybe check https://docs.microsoft.com/en-us/off...s.textbox.text to see if that is really what you want to do. I think that most times, just setting the value is the correct approach while getting rather than setting is when .Text comes in to play.

  7. #7
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by Micron View Post
    the new error suggests this is a calculated control. If it is, you can't set text or value properties in it. You would have to set the value for some other control and have a calculated control get it from there.

    not sure what this means I'm guessing that's autocorrect on a phone, but even if I substitute in for "I", I still don't know where a textbook comes in to play here.

    you say you tried setting the focus, but in the correct order? Maybe post the code so that the order of lines can be certain rather than just snippets of it because the order is important. Also, this alone Me.txtInfo.Text is incomplete.

    When I asked about setting the text property of your control (as opposed to its default: .Value) I wasn't sure you knew the difference. Maybe check https://docs.microsoft.com/en-us/off...s.textbox.text to see if that is really what you want to do. I think that most times, just setting the value is the correct approach while getting rather than setting is when .Text comes in to play.

    Sorry phone changed textbox to textbook -

    This is my full method, when my report opens I want the text box on the report called txtInfo to display what I am attempting to set to it in the VBA statement below

    Code:
    Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim vbavariable1 As String
    
    
    vbavariable1 = "Hi"
    Set db = CurrentDb
    Set rs = db.OpenRecordset("addressinfo")
    
    
    Me.txtInfo.setFocus
    
    
    If Not (rs.BOF And rs.EOF) Then
    	Me.txtInfo.Text = vbavariable1 & rs![systeminfo]
    	
    End If

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    434
    Try the code in the On Load event instead of the On Open event.

    As micron said you wont need .Text or .SetFocus

    Don't forget to close your rs object and set the rs and db variables to nothing when you're done:
    Code:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

  9. #9
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by kd2017 View Post
    Try the code in the On Load event instead of the On Open event.

    As micron said you wont need .Text or .SetFocus

    Don't forget to close your rs object and set the rs and db variables to nothing when you're done:
    Code:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If I move the code to the on Load event, adding in the .SetFocus and using Me.txtInfo.Text = will move through error free, but I have to click the report a few times before the text box gets updated with the values. Is there a way to have it immediately display?

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    434
    You really don't need the setfocus line and the .text
    What do you mean click the report? Refresh it or just clicking around on the report window? Is the textbox in the the details section of the report? It might be easier to build the textbox's text in its control source property using the expression builder and dlookup.

    It would help if you told us exactly what you're doing and provide FULL code (vba and sql queries).

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,513
    If this is one textbox that isn't part of the records (e.g. it's in the report header) then
    - why not use a calculated control in the report, or
    - use DLookup in that control, or
    - make the data part of the recordset that the report is based on (it appears that you can get it from your query so why not add it to the report)
    - pass the value to the report in the OpenArgs parameter of DoCmd.OpenReport method?

    I see no sense in opening a recordset to get only one value from it.
    Maybe it's time for you to fully explain what the goal is and post your db.

  12. #12
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by Micron View Post
    If this is one textbox that isn't part of the records (e.g. it's in the report header) then
    - why not use a calculated control in the report, or
    - use DLookup in that control, or
    - make the data part of the recordset that the report is based on (it appears that you can get it from your query so why not add it to the report)
    - pass the value to the report in the OpenArgs parameter of DoCmd.OpenReport method?

    I see no sense in opening a recordset to get only one value from it.
    Maybe it's time for you to fully explain what the goal is and post your db.
    Can DLookup() concat multiple fields together? -- I was using a recordset as there is the potential that more than 1 row could be returned.

    I am unable to post database -

    What I am doing is I have a form with 2 combo boxes and 1 text box and have a button on the form
    On the button press event of the form I take the value from the top combo box and build two queries with that value as the where clause
    **Here is where I am experiencing issues
    I am then trying to use VBA to set the text for two text boxes based on values in those SQL Queries (which is why I open a recordset) - each query has two fields I want to display.

    Does that help clarify?

  13. #13
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by kd2017 View Post
    You really don't need the setfocus line and the .text
    What do you mean click the report? Refresh it or just clicking around on the report window? Is the textbox in the the details section of the report? It might be easier to build the textbox's text in its control source property using the expression builder and dlookup.

    It would help if you told us exactly what you're doing and provide FULL code (vba and sql queries).
    if I do not use the set focus and the .text I get errors.

    And I mean click around on the report.

    This is full syntax for my Report
    Code:
    Private Sub Report_Load(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim vbavariable1 As String
    
    
    vbavariable1 = "Hi"
    Set db = CurrentDb
    Set rs = db.OpenRecordset("addressinfo")
    
    
    Me.txtInfo.setFocus
    
    
    If Not (rs.BOF And rs.EOF) Then
        Me.txtInfo.Text = vbavariable1 & vbCrLf & rs![field1] & vbCrLf & rs![field2]
        
    End If    
    
    
    rs.close
    Set rs = Nothing
    
    
    Me.txtGreet.SetFocus
    Me.txtGreet.Text = "Hello " & strNameFromForm
    
    
    Set rs1 = db.OpenRecordset("fullinfo")
    Me.txtfullinfo.SetFocus
    
    
    If Not (rs1.BOF And rs1.EOF) Then
        Me.txtfullinfo.Text = rs1![Information Systems].[MAC ID] & " - " & rs1![Information Systems].[Computer Name]
    End If
    
    
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing
    
    
    End Sub

  14. #14
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    434
    Did you manually change from Report_Open to Report_Load in the code editor? If so it's better to click the OnLoad event property on the properties window to generate the sub, otherwise you can run into problems.

    But you can just use dlookup in the control source of the textboxes instead. =Dlookup("'Hi' & vbCrLF & [field1]", "queryname", "where clause here")

  15. #15
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by kd2017 View Post
    Did you manually change from Report_Open to Report_Load in the code editor? If so it's better to click the OnLoad event property on the properties window to generate the sub, otherwise you can run into problems.

    But you can just use dlookup in the control source of the textboxes instead. =Dlookup("'Hi' & vbCrLF & [field1]", "queryname", "where clause here")
    No - I used the properties window to generate the sub.

    The Dlookup would work in this instance, but in the other two instances I am using recordset as I am returning potentially multiple values.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-11-2014, 10:26 AM
  2. Replies: 6
    Last Post: 12-03-2013, 10:14 PM
  3. How to Select second Recordset from first recordset
    By FrustratedAlso in forum Programming
    Replies: 28
    Last Post: 05-10-2012, 04:45 PM
  4. Replies: 2
    Last Post: 03-08-2012, 11:59 AM
  5. Replies: 1
    Last Post: 11-13-2009, 02:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums