Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Similar a Dlookup(), is there a way a way to read a whole record and then parse the fields in VBA?


    Just started with VBA and have played with Dlookup().
    Is there a similar way to read a whole record of a given ID from a table, and then easily parse the variables?
    I have 5 Dlookups, and it seems better to just read the record if possible.
    I could do SQL I suppose (if I new how and Access provided for it) but I have no idea how to initiate that then use that result in VBA code.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    "then easily parse the variables?"
    Do you mean the fields within the record?

    Perhaps a sample would be helpful.

  3. #3
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    I meant parsing into variables, or something based on the field names.
    I've got a control record in a control table. I need to read one record that affects control properties in a form or report.
    I want just that record and all the fields.

    See an example db in post #31 in this thread: Setting the PageHeader.BackColor property with color codes (accessforums.net)

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    See if this helps:
    Code:
    Public Sub example_sub()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qry As String
        
        Set db = CurrentDb
        
        qry = "SELECT field_1, field_2, field_3, field_4, field_5 FROM some_table WHERE id=666;"
        
        Set rs = db.OpenRecordset(qry, dbOpenSnapshot)
        'Refer to the docs on OpenRecordset:
        ' https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao
        ' use dbOpenDynaset instead of dbOpenSnapshot if you want to be able to edit records
        
        ' you can refer to fields by field name
        Debug.Print rs!field_1
        Debug.Print rs!field_2
        Debug.Print rs!field_3
        Debug.Print rs!field_4
        Debug.Print rs!field_5
        
        ' or like this
        Debug.Print rs.Fields("field_1")
        Debug.Print rs.Fields("field_2")
        Debug.Print rs.Fields("field_3")
        Debug.Print rs.Fields("field_4")
        Debug.Print rs.Fields("field_5")
        
        
        ' or refer to fields by a "column" number. First field starts at zero, not one.
        Debug.Print rs.Fields(0)
        Debug.Print rs.Fields(1)
        Debug.Print rs.Fields(2)
        Debug.Print rs.Fields(3)
        Debug.Print rs.Fields(4)
        
        
        ' Edit a field (Make sure to use the dbOpenDynaset option when opening the recordset if you want to edit fields or add records)
        rs.Edit
        rs!field_1 = "Chicken McNuggets"
        rs.Update
        
        
        ' add a new record
        rs.AddNew
        rs!field_1 = "Chicken McNuggets"
        rs!field_2 = 461
        rs!field_3 = Date
        rs!field_4 = 987.55
        rs!field_5 = "pink goo"
        rs.Update
        
        'always close stuff that you open when you're done with them
        rs.Close
        
    ExitHandler:
        'Anytime you 'Set' an object equal to something you need to reset it equal to nothing when you're done with it.
        'We previously 'Set' the db and rs variables, so now we need to 'unset' them.
        'And use error handling like this to ensure your objects get cleaned up even if you get errors, and you will get errors...
        Set rs = Nothing
        Set db = Nothing
    
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Sub

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have a routine that reviews and records properties of all controls(except label as I recall) on all forms into a table.
    You could run the routine, then use Dlookup as fits your requirement.

  6. #6
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Almost there then this problem

    Quote Originally Posted by kd2017 View Post
    See if this helps:
    Code:
    Public Sub example_sub()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qry As String
        
        Set db = CurrentDb
        
        qry = "SELECT field_1, field_2, field_3, field_4, field_5 FROM some_table WHERE id=666;"
        
    ...
    I may have just gotten too cute and painted myself into a corner.

    On report load a Private Sub Report_Load() runs.
    Which then calls a Public Function fcnChngBgColors(CtrlId As String, TypChng As Integer) As Integer
    In the public function I want to set some of the report properties like ReportHeader.BackColor = ColCode.

    Code:
    Public Function fcnChngBgColors(CtrlId As String, TypChng As Integer) As Integer
    On Error GoTo ErrHandler
        Dim db As DAO.Database: Dim rs As DAO.Recordset: Dim qryToDo As String
        Dim intSuccCnt As Integer
        Dim ColCode As Variant
        Set db = CurrentDb
        intSuccCnt = 0
        
        qryToDo = "SELECT * FROM tbl_Au_CntlLoc WHERE id = '" + CtrlId + "';"
        Set rs = db.OpenRecordset(qryToDo, dbOpenSnapshot)
        
        Select Case TypChng
        Case 21  'Change Report Colors for header, footer, alternating and copyright notice
          
          ColCode = fcnColorConvert(rs!Pt2) 'User color report header / page footer
          If ColCode = "" Then
            ColCode = fcnColorConvert(rs!Pt1)  'Default color report header / page footer
          End If
          If ColCode <> "" Then
            ReportHeader.BackColor = ColCode ' This is a report property
    {...more code}
    Is there a way in the Public Function to tell it that it's the current report that wants the property changed?
    Can we put something in front of ReportHead.property to specify the report is what we want to modify?
    Or pass the Report object somehow (I don't know)?
    Or are we just one sub too far removed to modify the Report properties?

    When run an error message says that ReportHeader.BackColor isn't defined when the first line of the function is run (compile error I guess).

    The calling line in the Private Sub Report_Load() looks like this:

    Code:
    intSucCnt = intSucCnt + fcnChngBgColors("Col_Template", 21) 'Change Report header,  page footer and alternating bar colors to default or custom based on user preference
    Now is a good time to say that I'm only wanting to change a temporary instance of the report as it runs, not change the actual properties in the design.

    Note: I worked around the problem by changing the routine from fnc to sub and passing an array. Then using the array in the calling sub to set the report properties.
    Despite that fix, I'd still like to know if what I originally asked is possible.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can pass report name or report object. I tested with name. Add an argument to the color function to receive report name as a string.

    Public Function fcnChngBgColors(CtrlId As String, TypChng As Integer, strRpt As String) As Integer...
    ...
    Reports(strRpt).ReportHeader.BackColor = ColCode ' This is a report property



    Call the function:

    fcnChngBgColors(something, something, Me.Name)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Thanks post #7 June 7
    I'm thinking I want the routines to work in forms too, so I don't want to hard code Reports(strRpt), just pass the entire object if possible. But, I don't want to gobble up a lot of memory doing it.
    I'll give it a try tomorrow after some Zzzzs.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can have another argument in the function to specify report or form.

    If strObject = "Report" Then
    Reports(strName).ReportHeader.BackColor = ColCode
    Else
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    A note about your concatenation method, using + ADDS the value, you are getting away with it because you are using strings as your criteria (Which is odd as it's called ID but....) for any string concatenation you should use & e.g.;

    Instead of this

    Code:
    qryToDo = "SELECT * FROM tbl_Au_CntlLoc WHERE id = '" + CtrlId + "';"
    You should be using this

    Code:
    qryToDo = "SELECT * FROM tbl_Au_CntlLoc WHERE id = '" & CtrlId & "'"
    You can omit the closing semicolon it's superfluous.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Thumbs up

    Quote Originally Posted by Minty View Post
    A note about your concatenation method, using + ADDS the value, you are getting away with it because you are using strings as your criteria (Which is odd as it's called ID but....) for any string concatenation you should use & e.g.;
    ...

    You can omit the closing semicolon it's superfluous.
    Thanks for those tips. Since I'm new to VBA, I am reviewing lots of code from others as I build each new line. Sometimes I scratch my head and go, okay, it's a Microsoft product ya' know. But, it's good to know the better way. My old BASIC used ":" to concatenate and ";" for continuation of a line with a new statement. Now, that seemed logical.

    As to the odd ID, here's what's behind the scenes:

    Click image for larger version. 

Name:	VbaColor6.jpg 
Views:	31 
Size:	37.6 KB 
ID:	48296

    BTW, I have to go clean up the currency formatting based on this thread: Get rid of the dang "$" in currency already! - Page 2 (accessforums.net) post #18

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't want to gobble up a lot of memory doing it
    When you pass an object as a parameter to a function or sub you can pass as byval or byref . Byref is the default and just passes a pointer to the location of the object. So within the function, changes made will be reflected in the ‘original’. So you won’t be gobbling up memory. Use byval and the function will create a new object local to itself and should dispose of it in an orderly manner when the function is exited to free up memory.

    Passing the form/report name should also refer to the original object if used as forms(frmname) so nothing to be gained but loss of some flexibility.

    If your parameter is declared as say ‘frmrep as object’ you can pass any object so within the context of what your function is doing if referring to the same subset of properties such as controls you do not need to know whether it is a form or report.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Unfortunately, form and report headers/footers do not have common naming for those sections. Reports have ReportHeader/ReportFooter and forms have FormHeader/FormFooter. Now if MS had assigned "Main" in place of "Report" and "Form", there would be a common name (like PageHeader and Detail). Then passing the object would probably work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Unfortunately, form and report headers/footers do not have common naming for those sections.
    However, they do have the same vb constant names and the same index numbers for the first five sections (when listed ascending by index number). Section(0) is acDetail for both forms and reports. Not sure if any of that would be of any help here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, Micron, that is just brilliant.

    Did a test.

    Sub SetColor(objName As Object)
    objName.Section(acHeader).BackColor = vbRed
    End Sub

    calling the procedure from form and report

    Private Sub Report_Load()
    SetColor Me
    End Sub

    Private Sub Form_Load()
    SetColor Me
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-06-2017, 02:45 AM
  2. Replies: 29
    Last Post: 05-02-2017, 04:38 PM
  3. Replies: 2
    Last Post: 03-06-2013, 11:37 PM
  4. Relating similar fields???
    By smoothlarryhughes in forum Access
    Replies: 10
    Last Post: 02-26-2013, 12:50 PM
  5. Replies: 5
    Last Post: 08-29-2011, 05:17 PM

Posting Permissions

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