Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Though I agree with Mehow's comment, as a direct answer, you would use a class to handle the events raised by the buttons:</p> <p><strong>Class cBtnClick</strong>:</p> <pre><code>Public WithEvents btn As msforms.CommandButton Private Sub btn_Click() MsgBox "Delete Button" End Sub </code></pre> <p><strong>Userform Code</strong>:</p> <pre><code>Dim BtnCollection As Collection Private Sub UserForm_Initialize() Dim ctl As Object Dim clsBtn As cBtnClick Set BtnCollection = New Collection For Each ctl In Me.Controls If TypeOf ctl Is msforms.CommandButton Then If UCase(ctl.Caption) = "DELETE" Then Set clsBtn = New cBtnClick Set clsBtn.btn = ctl BtnCollection.Add clsBtn End If End If Next ctl End Sub </code></pre> <p>Further to this however, I suggest you look at the structure of your code, you shouldn't really need 17 delete buttons to do the same thing, one should suffice. If each button clears it's respective textbox, then I would consider adding the controls dynamically and encapsulating both the textbox and button in a single class object.</p> <p>Going from your further comments, have a look at something like this:</p> <p><strong>Class cInputClear:</strong></p> <pre><code>Private WithEvents p_tb As MSForms.TextBox Private WithEvents p_btn As MSForms.CommandButton Public Sub Add(Parent As Object, top As Long, left As Long, width As Long) Set p_tb = Parent.Controls.Add("Forms.Textbox.1") With p_tb .top = top .left = left .width = width End With Set p_btn = Parent.Controls.Add("Forms.CommandButton.1") With p_btn .top = top .left = left + width + 5 '5 for padding .Height = p_tb.Height .Caption = "Delete" End With End Sub Private Sub p_btn_Click() p_tb.value = "" End Sub Public Property Get TextBoxValue() As String TextBoxValue = p_tb.value End Property </code></pre> <p><strong>Userform:</strong></p> <pre><code>Private cCollection As Collection Private Sub UserForm_initialize() Dim x As Long Dim octrl As cInputClear Set cCollection = New Collection For x = 1 To 5 Set octrl = New cInputClear octrl.Add Me, 20 * x, 5, 60 cCollection.Add octrl Next x End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Dim octrl As cInputClear For Each octrl In cCollection Debug.Print octrl.TextBoxValue Next octrl End Sub </code></pre> <p>Suggested approach, re your comment about structure. I think I'd approach it like this if it helps any - this assumes that you only want to edit the description rather than the code as well, but you should get the idea:</p> <p>N.B - You'll need to put a frame on the userform</p> <p><strong>Class cData:</strong></p> <pre><code>Public Code As String Public Description As String </code></pre> <p><strong>Class cInputClear:</strong></p> <pre><code>Option Explicit Private WithEvents p_tb As MSForms.TextBox Private WithEvents p_btn As MSForms.CommandButton Private WithEvents p_lbl As MSForms.Label Private p_LabelKey As String Private p_Parent As Object Public Sub Add(Parent As Object, ByVal LabelKey As String, ByVal TextBoxValue As String, top As Long, left As Long, width As Long) Set p_Parent = Parent Set p_lbl = Parent.Controls.Add("Forms.label.1") With p_lbl .top = top .left = left .Caption = LabelKey End With Set p_tb = Parent.Controls.Add("Forms.Textbox.1") With p_tb .top = top .left = left + p_lbl.width + 5 .width = width .Value = TextBoxValue End With Set p_btn = Parent.Controls.Add("Forms.CommandButton.1") With p_btn .top = top .left = left + width + p_lbl.width + 5 '5 for padding .Height = p_tb.Height .Caption = "Delete" End With p_LabelKey = LabelKey End Sub Private Sub p_btn_Click() p_Parent.Parent.Delete Me.LabelKey End Sub Public Property Get TextBoxValue() As String TextBoxValue = p_tb.Value End Property Public Property Get LabelKey() As String LabelKey = p_LabelKey End Property Private Sub p_tb_Change() p_Parent.Parent.Update Me.LabelKey, Me.TextBoxValue End Sub </code></pre> <p><strong>UserForm Code:</strong></p> <pre><code>Option Explicit Private cCollection As Collection Private MyCollection As Collection Private EnableEvents As Boolean Private Sub Frame1_Click() End Sub Private Sub UserForm_initialize() Dim x As Long Dim myData As cData '//////// Just build dummy data for an example Set MyCollection = New Collection For x = 1 To 5 Set myData = New cData myData.Code = "ABC123" &amp; x myData.Description = "Description Of Data" MyCollection.Add myData, myData.Code Next x '/////////////////////////////////////////////// BindData End Sub Private Sub BindData() Dim x As Long Dim InvoiceItem As cData Dim oCtrl As cInputClear EnableEvents = False Set cCollection = New Collection x = 1 For Each InvoiceItem In MyCollection Set oCtrl = New cInputClear oCtrl.Add Me.Frame1, _ InvoiceItem.Code, _ InvoiceItem.Description, _ top:=20 * x, _ left:=5, _ width:=150 cCollection.Add oCtrl, oCtrl.LabelKey x = x + 1 Next InvoiceItem EnableEvents = True Me.Frame1.ScrollBars = fmScrollBarsVertical Me.Frame1.ScrollHeight = x * 20 End Sub Public Sub Update(Key As String, ByVal Value As String) If EnableEvents Then 'Update the datasource MyCollection(Key).Description = Value End If End Sub Public Sub Delete(Key As String) MyCollection.Remove (Key) 'Update our datasource '// Remove all controls from the frame Me.Frame1.Controls.Clear BindData End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POVBA get button clicks other than CommandButton_Click()
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSWa
    1. USSWa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POVBA get button clicks other than CommandButton_Click()
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you!! this actually solves my problem... but also raises concern over my structure.
      singulars
      1. PO
      1. USScott
    2. COI've updated my answer re the structure if it helps any
      singulars
      1. PO
      1. USSWa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload