Dynamically created buttons

[opopanax666]

SOLVED: Dynamically created buttons

Hi everyone,

I am staring myself blind on a problem, I think, so maybe if I explain what I need, someone could give a fresh view on things.

- This form will be used for stockroom-employees to quickly input what product they just weighed by clicking a few buttons (and maybe later use with a touch-screen);
- We employ different nationalities, so I want the buttons to show logos, together with a caption below the button;
- We use 3 tiers in product identification: 6 main categories (fixed), divided in 20 subcategories (with a max. of 6 per main group)(fixed), which in turn contain a customizable number of groups (max. of 18 per subcategory);

So I have a table "Main" with just 6 IDs and groupnames, a table "Sub1" with 20 IDs, subnames, logopaths and main_ids (linked to Main), and a table "Sub2" with an autonumber-id, name, logopath, display (yes/no) and sub1_id (linked to Sub1).

On the form I would like to have three option-groups: one displaying the 6 "Main"-buttons, one to display the "Sub1"-buttons depending on which "Main"-button is pressed, and one to display the custom "Sub2"-buttons depending on which "Sub1"-button is pressed.
I would need to "create" the buttons in group 2 and 3, and that was where I got stuck. I tried to use the "visible"-property, but since I only knew how to do this by using the button name, I needed to create a query including a function to create a column with sequential numbering to be able to control the buttons. And I never got this to work...

Just to be complete: the chosen product_id will be saved to another table together with the weight (which was input before getting at this form), and the autonumber of this table would be used as a unique identifier for the product (barcode).

So maybe someone here knows of a different approach to get to the same result.

Any feedback would be greatly appreciated!

 

[stopher]

Consider using a Treeview control. It's ideal for this kind of hierarchical stuff.

Chris

 

[spikepl]

I think you were on the right path, considering your requirements for a workshop-floor suitable user interface.

Why did you give up?
If you have 3 sets of buttons (from what I understand 6 + 6 +18) it is easy to loop through one set and assign visibility and text to them.

Eg

Dim ctl As Control

For Each ctl In Me.Controls
       If TypeName(ctl) = "ToggleButton" Then
           MsgBox "aaa"
       End If
Next ctl

 

[opopanax666]

Consider using a Treeview control. It's ideal for this kind of hierarchical stuff.

I have no experience with Treeview whatsoever, but I'll have a look at it this weekend. Although I believe that for the people working here, this setup would look and feel a bit weird. Understand that we are a non-profit, employing people who, for whatever reason, can't find a job in regular employment. Most of them have never even used a computer... But I'll certainly have a look, thanks for the pointer!

I think you were on the right path, considering your requirements for a workshop-floor suitable user interface.

Where I got before is:

Form with option-groups, as you said, 6 + 6 + 18 buttons. Code for first option-group:

Private Sub optgrpHOOFD_AfterUpdate()

Dim nRecords As Integer
Dim Counter As Integer
Dim strButton As String
Dim strCounter As String
Dim varImage As Variant
Dim cCont As Control

Me.txtHoofd.Value = DLookup("Hoofdgroep_naam", "tblProduct_hoofd", "Hoofdgroep_ID =" & optgrpHoofd.Value)

nRecords = DCount("*", "AutoNumberQuery1")

Counter = 1

Do While Counter <= nRecords

    strCounter = CStr(Counter)

    strButton = "KnopA" & strCounter
    Controls(strButton).Visible = True
    Controls(strButton).Caption = DLookup("subgroep1_naam", "AutoNumberQuery1", "SRLNO =" & strCounter)
    varImage = DLookup("subgroep1_logo", "AutoNumberQuery1", "SRLNO =" & strCounter)
    If IsNull(varImage) = False Then
      If Len(Dir(LogoPath & varImage)) > 0 Then
        Controls(strButton).Picture = LogoPath & varImage
      End If
    End If

    Counter = Counter + 1

Loop

End Sub

and the "SRLNO"-field in AutoNumberQuery1" is created with this function:

Option Compare Database
Option Explicit

Dim varArray() As Variant, i As Long

Public Function QrySeq(ByVal fldvalue, ByVal fldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query in a new Column
'Author : a.p.r. pillai
'Date : Dec. 2009
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be unique Values from the Query
'2 : Column Name  - the Field Name from Unique Value Taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with a Unique Field Value
'            - as First Parameter
'            - Need to Save the Query after change before opening
'            - in normal View.
'-------------------------------------------------------------------
Dim k As Long
On Error GoTo QrySeq_Err

restart:
If i = 0 Or DCount("*", QryName) <> i Then
Dim j As Long, db As Database, qdf As QueryDef, rst As Recordset

i = DCount("*", QryName)
ReDim varArray(1 To i, 1 To 3) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
For j = 1 To i
    varArray(j, 1) = rst.Fields(fldName).Value
    varArray(j, 2) = j
    varArray(j, 3) = fldName
    rst.MoveNext
Next
rst.Close
End If

If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
    i = 0
    GoTo restart
End If

For k = 1 To i
If varArray(k, 1) = fldvalue Then
    QrySeq = varArray(k, 2)
    Exit Function
End If
Next

QrySeq_Exit:
Exit Function

QrySeq_Err:
MsgBox Err & " : " & Err.Description, , "QrySeqQ"
Resume QrySeq_Exit

End Function

but this always ended in an "error 3061", probably because the criteria for AutoNumberQuery1 ("Forms![frmProductgroep]![optgrpHOOFD]"), although working for the query, is taken literally as a string by the function. So I tried getting my head around Parameters and QueryDefs, but that didn't quite work out

So I thought maybe someone had an other perspective on the problem...

 

[spikepl]

What I don't get is why bother with sequence numbers? If your items are queried ordered by the producer/item name (I am assuming that is your Name mentioned in #1) then selecting one button in the option group will give you which item it is. You can run through the recordset generated by the query, and simply count how far you are, when setting up the buttons.

Besides - don't let a small obstruction like that knock you out.

 

[spikepl]

Your stuff is very complex, and I don't understand why.

1. Prepare query to get the data as in Sub1/sub2
2. Open the recordset
3. Loop through the recordset, while setting buttons and counting
4. When loop completed, loop over remaining buttons and set them invisible

 

[opopanax666]

You can run through the recordset generated by the query, and simply count how far you are, when setting up the buttons.

Your stuff is very complex, and I don't understand why.

1. Prepare query to get the data as in Sub1/sub2
2. Open the recordset
3. Loop through the recordset, while setting buttons and counting
4. When loop completed, loop over remaining buttons and set them invisible

First off, thanks for the replies! I realize this is a very contrived way to approach the problem, but I have no experience whatsoever with recordsets, so I had to scrape together what I could find
Do any of you know of examples of looping through a recordset (I'll do a search myself, of course)?

 

[opopanax666]

Okay, had a look around, and got this code working:

Private Sub optgrpHOOFD_AfterUpdate()

Dim rst As Object
Dim dbs As Database
Dim RecFil As Integer
Dim strSQL As String
Dim nCount As Integer
Dim Counter As Integer
Dim strButton As String
Dim cCont As Control

'-------------------------------------------------------------------------

Counter = 1
Me.optgrpSUB1 = Null
For Each cCont In Me.optgrpSUB1.Controls
    cCont.Visible = False
Next cCont

RecFil = optgrpHoofd.Value
strSQL = "SELECT * FROM AutoNumberQuery1 WHERE Hoofdgroep_id = " & RecFil
'-------------------------------------------------------------------------

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

On Error Resume Next
rst.MoveLast
On Error GoTo 0
nCount = rst.RecordCount

rst.MoveFirst

Do While Counter <= nCount
    strButton = "KnopA" & Counter
    Controls(strButton).Visible = True
    Controls(strButton).Caption = rst.Fields("Subgroep1_naam")
    rst.MoveNext
    Counter = Counter + 1
Loop

rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

This is just a working example, I think I can get the rest (logo's) to work.

But could someone check the code and tell me whether this looks okay, or whether I need some more error handling e.g. Thank you

 

[stopher]

Looks good.

You can condense the code to this:

Dim rst As Recordset
Dim dbs As Database
Dim RecFil As Integer
Dim strSQL As String
Dim cCont As Control

'-------------------------------------------------------------------------

Me.optgrpSUB1 = Null
For Each cCont In Me.optgrpSUB1.Controls
    cCont.Visible = False
Next cCont

RecFil = optgrpHOOFD.Value
strSQL = "SELECT * FROM AutoNumberQuery1 WHERE Hoofdgroep_id = " & RecFil
'-------------------------------------------------------------------------

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rst.EOF
    Me.optgrpSUB1.Controls(rst.AbsolutePosition + 1).Visible = True
    Me.optgrpSUB1.Controls(rst.AbsolutePosition + 1).Caption = rst.Fields("Subgroep1_naam")
    rst.MoveNext
Loop

rst.Close

Set rst = Nothing
Set dbs = Nothing

The point is, each button in an option group can be referenced by its index e.g.
Me.optgrpSUB1.Controls(1)

Also, you can iterate through recordsets by testing for eof (end of file). This saves having to mess about with counters.

Note that the absolute position value starts at zer o - hence +1.

As regards error trapping. All code should include some error trapping. See here.

hth
Chris

 

[opopanax666]

Looks good.
The point is, each button in an option group can be referenced by its index e.g.
Me.optgrpSUB1.Controls(1)

Also, you can iterate through recordsets by testing for eof (end of file). This saves having to mess about with counters.

Note that the absolute position value starts at zer o - hence +1.

As regards error trapping. All code should include some error trapping. See here.

Chris, cheers mate, and thanks to everyone else!