How to simplify a repetitive code

[epardo87]

Hello there,
I've never used VBA as much as I'd like, but now i'm building something and I'm trying to figure out how to do things. in this case, I already figured out how to write what I need the code to do but I realise that I will have to write a bunch of repetitive code and I want to know if there's a way to reduce it some how, here's the deal:

I'm creating a form that will be used on touchscreen, so I will have more than a 100 buttons, each one represents a product, but I have 2 different prices depending on the type of purchase, so the code is written under the after update event of the "SaleType" Combobox, type "Local" will show "PriceL" and the rest "PriceP".

I've figure out a code that works just fine but I see that I would have to write it over and over again changing the PCode value and the button name, below is the code with only two buttons made (PN2001Btn and PN2003Btn), I suppose there should be a way to declare the formulas or something so that I only need to call them below the PCode...

Thanks in advance for your time and help!

Private Sub SaleType_AfterUpdate()    
    Dim LPrice As Variant
    Dim PPrice As Variant
    Dim ProdName As Variant
    Dim PCode As Integer
    
    PCode = 2001
    LPrice = DLookup("[PrecioL]", "[Precios]", "[PN]=" & PCode)
    PPrice = DLookup("[PrecioP]", "[Precios]", "[PN]=" & PCode)
    ProdName = DLookup("[Producto]", "[Products]", "[PN]=" & PCode)
    
    If Me.SaleType = "Local" Then
        PN2001Btn.Caption = ProdName & " $" & LPrice
    Else
        PN2001Btn.Caption = ProdName & " $" & PPrice
    End If
    
    PCode = 2003
    LPrice = DLookup("[PrecioL]", "[Precios]", "[PN]=" & PCode)
    PPrice = DLookup("[PrecioP]", "[Precios]", "[PN]=" & PCode)
    ProdName = DLookup("[Producto]", "[Products]", "[PN]=" & PCode)
    
    If Me.SaleType = "Local" Then
        PN2003Btn.Caption = ProdName & " $" & LPrice
    Else
        PN2003Btn.Caption = ProdName & " $" & PPrice
    End If
    
    
End Sub

[pbaldy]

My first thought is opening a recordset on the Products table, assuming you'd want all of them, and have buttons for all of them. Within a loop of that recordset your code becomes:

    PCode = rs!PN
    LPrice = DLookup("[PrecioL]", "[Precios]", "[PN]=" & PCode)
    PPrice = DLookup("[PrecioP]", "[Precios]", "[PN]=" & PCode)
    ProdName = rs!Producto
    
    If Me.SaleType = "Local" Then
        Me("PN" & rs!PN & "Btn").Caption = ProdName & " $" & LPrice
    Else
        Me("PN" & rs!PN & "Btn").Caption = ProdName & " $" & PPrice
    End If

I don't like having fields for the different prices. At some point you add a third price level and your structure changes. Look at database normalization.

[orange]

epardo87,

100 buttons on a screen/form seems very excessive. Perhaps you could tell us more - in simple terms - about the proposed application and this specific form. Who would use it? What business process would this form support? Also, could you show us the design of your tables?

I agree with Paul that if your business changes and other prices eg. Clearance Sale or Loyalty Program arise, then your design has to be restructured.

Also you may wish to research BeforeUpdate vs AfterUpdate and consider the difference for use in your design.

Further, I recommend you research Functions when solving issues of repetitive code.

[epardo87]

epardo87,

100 buttons on a screen/form seems very excessive. Perhaps you could tell us more - in simple terms - about the proposed application and this specific form. Who would use it? What business process would this form support? Also, could you show us the design of your tables?

I agree with Paul that if your business changes and other prices eg. Clearance Sale or Loyalty Program arise, then your design has to be restructured.

Also you may wish to research BeforeUpdate vs AfterUpdate and consider the difference for use in your design.

Further, I recommend you research Functions when solving issues of repetitive code.

Thanks for your answer,

this is a custom point of sale, a restaurant cashier would be using it, and the buttons are going to be within tabs so you may only see about 10 buttons per tab, you can see what i have so far in the picture below, the buttons fill up the subform at the left and when they select the sale type, each button will show the name of the product and the price. Btw thanks for the video, I will use that a lot. I'm thinking that going for the recordset option is what I need but I'm investigating how that works

[epardo87]

My first thought is opening a recordset on the Products table, assuming you'd want all of them, and have buttons for all of them. Within a loop of that recordset your code becomes:

    PCode = rs!PN
    LPrice = DLookup("[PrecioL]", "[Precios]", "[PN]=" & PCode)
    PPrice = DLookup("[PrecioP]", "[Precios]", "[PN]=" & PCode)
    ProdName = rs!Producto
    
    If Me.SaleType = "Local" Then
        Me("PN" & rs!PN & "Btn").Caption = ProdName & " $" & LPrice
    Else
        Me("PN" & rs!PN & "Btn").Caption = ProdName & " $" & PPrice
    End If

I don't like having fields for the different prices. At some point you add a third price level and your structure changes. Look at database normalization.

Thanks for your Answer,
I want to go with your suggestion but I'm struggling finding out how does recordset works (I'm a total rookie in this) as I'm understanding, before using the code you suggested I have to create the recordset with all the values right? is there any material you suggest to read or see to understand exactly what I must do to properly use the recordset?

[pbaldy]

Not sure what you mean by "create the recordset with all the values". I would expect the values to be in the products table, and the recordset would get them from there. Here's my template code for a DAO recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbseechanges)

  Do While Not rs.EOF
    'your code here
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

[epardo87]

Not sure what you mean by "create the recordset with all the values". I would expect the values to be in the products table, and the recordset would get them from there. Here's my template code for a DAO recordset loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbseechanges)

  Do While Not rs.EOF
    'your code here
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

That was it,
I just went through a few tutorials and finally understood how to use this, I managed to create the recordset through the Products table and used your suggested code, I had to add an On Error Resume Next to ignore all buttons not created yet, but that was it

Thanks again for your support, I really appreciate it

[moke123]

Another possibility is using dynamic buttons rather than writing code for 100 buttons. for instance instead of tabs for different product types use a combo along with your price type combo.
The after update of the combos will cause the corresponding number of buttons to appear with the appropriate captions. I have 12 buttons in the example but you can have more or less. Other info is written to the controls tag property to be written to the table on click.

This is a really basic, ugly example but I think it demonstrates the idea.