• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Working with objects from recordsets.

Hello Everyone,

My Code:

    str_DynObj = _
        "SELECT tbx_DynamicObjects.Object, tbx_DynamicObjects.Pos1_TopTwips, " & _
            "tbx_DynamicObjects.Pos2_TopTwips, tbx_DynamicObjects.FormID, " & _
            "tbx_DynamicObjects.TabID, tbx_DynamicObjects.Active " & _
        "FROM tbx_DynamicObjects " & _
        "WHERE tbx_DynamicObjects.FormID = 22 " & _
            "AND tbx_DynamicObjects.TabID = 2 " & _
            "AND tbx_DynamicObjects.Active = TRUE "
    Set rs_DynObj = db.OpenRecordset(str_DynObj)
  
    While Not rs_DynObj.EOF
    
        MsgBox rs_DynObj.Fields(0) & "  |  " & rs_DynObj.Fields(1) & "  |  " & rs_DynObj.Fields(2)
        
        rs_DynObj.MoveNext
        
    Wend

Open in new window


What I Want to do:

rs_DynObj.Fields(0) is the name of my object.  Could be a text box or a lable or even a combo box.

rs_DynObj.Fields(1) is the Top value I want to set my object to.  

So in the database I could have:

Object         Top Value (in TWIPs)
TextBox A   1440
Lable A        1440
TxtBox B      2880
Label B         2880

The above recordset could be what my SQL returns.  

I have a loop going through each one of the records (objects) returned.  I want to dynamically reset the object's top value listed in the adjacent column.

What is the syntax for doing something like

value of rs_dynobj.fields(0) . top = rs_dynobj.fields(1)

Thanks in advance for your assistance.

Best Regards,

Wendee
str_DynObj = _
        "SELECT tbx_DynamicObjects.Object, tbx_DynamicObjects.Pos1_TopTwips, " & _
            "tbx_DynamicObjects.Pos2_TopTwips, tbx_DynamicObjects.FormID, " & _
            "tbx_DynamicObjects.TabID, tbx_DynamicObjects.Active " & _
        "FROM tbx_DynamicObjects " & _
        "WHERE tbx_DynamicObjects.FormID = 22 " & _
            "AND tbx_DynamicObjects.TabID = 2 " & _
            "AND tbx_DynamicObjects.Active = TRUE "
    Set rs_DynObj = db.OpenRecordset(str_DynObj)
    MsgBox rs_DynObj.RecordCount
    
    Dim i As Integer
    
    While Not rs_DynObj.EOF
    
        MsgBox rs_DynObj.Fields(0) & "  |  " & rs_DynObj.Fields(1) & "  |  " & rs_DynObj.Fields(2)
        
        rs_DynObj.MoveNext
        
    Wend

Open in new window

0
pwdells
Asked:
pwdells
1 Solution
 
Rey Obrero (Capricorn1)Commented:
are you trying to create controls in a form using VBA?

see this link
CreateControl Method
http://msdn.microsoft.com/en-us/library/aa221167%28office.11%29.aspx
0
 
GugroCommented:
I think what you are looking for is:
Forms(MyFormName).Controls( rs_dynobj.fields(0)). top = rs_dynobj.fields(1)

Or if this code run in the form itself:
Me.Controls( rs_dynobj.fields(0)). top = rs_dynobj.fields(1)
0
 
pwdellsAuthor Commented:
Thank you!  Your top suggestion worked!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now