Constructing Screen Array Pointers (1 Viewer)

[HitStrike]

Howdy,
I imagine there is a way to make an object out of screen objects, but I don't know how to do it.

My database is fundamentally all SQL calls. I use many unbound fields and read them into VB to use in SQL Inserts, Update, Queries Etc.

I read the SQL data, store them into Variable Arrays with VB and have to stuff the data into screen tagnames as such

Me.ScreenData1 = SQLData(1)
Me.ScreenData2 = SQLData(2)
Me.ScreenData3 = SQLData(3) etc ad infinitum

Can I make screen arrays objects or construct the screen tagname dynamically?

There has to be a more efficient methodology.

Your help is most appreciated

 

[Roku]

What do you mean by "screen objects"? Are you talking about open forms within Access, controls on those forms or something different?

If your names are as predictable as your example, then you can build a loop which populates the relevant Me.ScreenDataX from its corresponding SQLData (X). If this is what you want, I can post a sample for you.

If your field names are not predictable, then what is the correlation between the SQL and field? In other words, how would you know which "SreenData" field goes with which SQL source?

 

[HitStrike]

Good morning Roku,
By "Screen Objects" I mean Text / Combo Box Objects on an Access form

Yes, my screen object names are predictable as shown within my example. I did try to construct the screen object name concatenating a string but it was rejected by the compiler. So I would love to see your example as you seem to know exactly what my dilemma is.

The beauty of what your talking about, is that even my bound screen objects reference the underlying database tag name. If I could construct the Screen Object Tag names the I would only need one routine call to load my screen data on all of my screens by simply passing an array size variable from screen to screen.

That would be unbelievable. I have been a musician for the past ten years and just getting back into programming, so your help is most appreciated.

Thanks
Duncan Walters

 

[Roku]

Something like this should do what you want:

Option Compare Database: Option Explicit: Option Base 1
 
Private Const kScreenData As String = "ScreenData"
Private SQLData() As String
 
Private Sub loadSQL()
Rem #### assumes SQLData have been populated by some means ####
Dim ctl As Control
Rem step through the controls on the form
For Each ctl In Me.Controls
  Rem check whether control is of required type
  If ctl.ControlType = acTextBox Then
    Rem check whether the control name has prefix of interest
    If Left(ctl.Name, Len(kScreenData)) = kScreenData Then
      Rem got one
      Dim intItem As Integer
      intItem = CInt(Right(ctl.Name, Len(ctl.Name) - Len(kScreenData)))
      Rem check that an array entry exists (care on array bounds - 0- or 1-relative according to Option Base)
      If intItem <= UBound(SQLData) Then ctl = SQLData(intItem)
    End If
  End If
Next
End Sub
 
Private Sub cmdLoad_Click()
Dim i As Integer
ReDim SQLData(3)
For i = 1 To 3
  SQLData(i) = "SQL " & CStr(i )
Next
loadSQL
End Sub

I have used a command button to load the SQLData array in this case, prior to scanning the form controls.

Note the use of Option Base 1 to make the array 1-relative.

Hope this helps.

 

[HitStrike]

Thanks Roku,
It is a different way then I was hoping, but it did reduce the size of my program. I actually had some tags with the same beginning characters so I did this.

It would be really handy if I could have just constructed the screen object name directly and then vector right to the location without having to go through every part of the me.control object. But this way does work. Thanks again

Dim Ctl As Control
Dim i As Integer
Dim kScreenData As String

kScreenData = "TAG"
kLength = Len(kScreenData)

i = 0
For Each Ctl In Me.Controls
If UCase(Left(Ctl.Name, kLength)) = kScreenData Then
If IsNumeric(Right(Ctl.Name, (Len(Ctl.Name) - kLength))) Then
i = Int(Right(Ctl.Name, (Len(Ctl.Name) - kLength)))
Ctl = ISATag(i)
End If
End If
Next

ErrorResume:

 

[Roku]

Thanks Roku,
It would be really handy if I could have just constructed the screen object name directly and then vector right to the location without having to go through every part of the me.control object.

I don't understand what you mean by this? The control name is set at design time, so you have to find out what's there at run time by some kind of looping mechanism.

In your earlier post you said:

If I could construct the Screen Object Tag names the I would only need one routine call to load my screen data on all of my screens by simply passing an array size variable from screen to screen

As you probably know, controls have a Tag property - but I got the impression that you meant something different? Perhaps the control tag could be used for what you need, although I can't visualise an approach at present. Having said that, I'm thinking that your vector approach might be implemented by a table (a list of controls and corresponding locations)?

Also, the bit about passing from screen to screen puzzles me. What does "screen" mean in your terminology? To me, that is the physical display, but you imply the "object which is showing" (maybe? ). If you want to pass your array of objects between Access forms, that should be possible using the Forms collection. You could even pass between different applications, although that's a tad harder. The Window Handle property might be of help in that case (Hwnd).

Looking at your revised code, it appears that your control names have prefix 'Tag'? Are these a particular type of control (e.g. TextBox)? You mentioned Combo Boxes in an earlier post - do these follow the same naming convention? I'm interested in your numeric test on the name suffix - are there cases where the name is 'Tag' with alpha suffix? If so, why?

I'm also wondering why you want to use your approach? Is it ease of programming? Efficiency of execution? Something else? The answer will influence the solution (e.g. the control loop is not exactly efficient!).

If you want to pursue your original requirement, I'm happy to help further - I just need a clearer idea of what you envisage.

 

[HitStrike]

Boy, as I think about it, I realize that I am using Access very much different then standard, or so it may be.

I don't use record locators. I don't use direct screen to database tables functionality. Instead I load the unique identifying names of my data with different Filter Buttons and by selecting (dbl Clk) the field holder for that tag, the VB then loads in the associated data, which in many cases is in multiple tables through a series of Select calls. Then if the data needs to be duplicated, deleted or modified, there is a data record add and delete button which query the database to see if the point is being modified or deleted and take associated action.

As such, many of the built in feature of Access to populate a screen with control source etc are not being used. For my application this is the best way to address it. Multiple hundreds of Query Tables are just way to cumbersome.

So that is why if I could just in a single call without a loop, insert the SQl'd data on the screen it would be very handy.


As example, In my tables/database I name a data point BedTypes. On any screen, I name the Text or Combo or Button or Whatever Bedtypes.

When I SQL the data, I get rst.Fields![BedTypes], if at that point I could construct the screen tag name from the Database Tagname and have the screen data field update it would be very functionally significant.

Such that I could say
ScreenTag = Me.Bedtypes (derived from rst.Fields![Bedtype] wit string functions)
CTL.ScreenTag or
ScreenTag = rst.Fields![TagName] whatever would work for the object

That kind of thing.

As far as passing screen to screen, no that wasn't what I meant. I meant I could use the functionality of this feature on many already developed screens by mods to the VB code, which I have already done with the code that you helped me with.

Yes I have many combo, text, labels and a great of them started with Tag, so I needed to use the IsNumeric call to differentiate.

I'm also wondering why you want to use your approach? Is it ease of programming? Efficiency of execution? Something else? The answer will influence the solution (e.g. the control loop is not exactly efficient!).

The answer is both. I am a low level assembler style programmer and old habits die hard. But I hate to see tons of lines of, while efficient code, very cumbersome code. That is why I would be hesitant to loop the object to look for one tag. granted in the numerous 400 to 600 point screen arrays I had, it worked very well. But having to loop through the object structure on every tag would be contrary to my religion.

I do thank you and it reduced my code by probably three thousand lines of very ugly code.

Have a fantastic day and thanks again.

 

[Roku]

Now you tell me you are an Assembler programmer, I see the sense!

I cut my teeth on Assembler in 1972, using a CDC3300. I later went on to Burroughs B3500. COBOL took over after that. No such thing as a screen in them thar days