Match two fields in userform

[noobynoobynoob]

Hello!

I'm really super new to userforms and VBA. I've followed an online tutorial (my first mistake?) and am really happy with what I've achieved (my second mistake?).

I have a userform that allows a user to search for a single value (order number) in the table via a combobox and they are then able to edit just that row. I'm quite happy with this. However, each order number may have several lines/rows of individual parts. Several parts making up one order.

For example order A may contain parts 1, 2 and 3 and order B may contain parts 1,3 and 4 (this would be 6 rows in the table)

I need combobox1 in the userform to list all of the order numbers in the table (which I've already achieved). Then when that selection in combobox1 is made combobox2 is populated with only those part numbers that the order number relates to. The order numbers are generated when a new record is added to the table. They are not sequential or in anyway able to be known in advance and referenced from a different worksheet for instance.

So if the user selects order A in combobox1 then they are presented with parts 1,2 and 3 in combobox2. Whereas if the user selects order B in combobox1 then they are presented with parts 1,3 and 4 in combobox2. Then when the submit/edit command button is pressed the userform is populated with the data from the correct row in the table.

Each row contains an order number and a part number I just need to ensure that when searching that the row that contains that order number and part number is populated in to the userform so that the correct row is going to be edited.

I hope the above makes sense?

Thanks,
NoobyNoobyNoob

[MRUTOR]

Hey nooby,

here would a sample file be helpful because we do not know where exact your data are in the sheet. just a file with some dummy data as described in your post and with the userform including code.
Then it would be easier to help you.

[noobynoobynoob]

I hope this helps and that I've managed to attach it. I have made lots of comments in my code to help me understand and learn.

[torachan]

A couple of examples attached.
Both use arrays in different manners by splitting out unwanted data.
If I have time tomorrow I will work in the most suitable to your code to demo use.
Meanwhile see which you understand and give it a go within your app.
torachan.

[noobynoobynoob]

Hi,

many thanks for the examples it certainly does what I described and demonstrates the functionality I want to achieve, although I only need two comboboxes not three. However being a total noob
I can't work out how to put that code in to what I already have!

I need to 'pass' the row number that the user has selected (by making selections in the two comboboxes) to a variable named RecordSearch which then populates an 'edit' useform
with the data from just that row in the table.

I think I understand enough to know that what Torachan has kindly provided will/should replace the piece that I've (hopefully) put in bold below. I just don't know how to do this.

Private Sub CmdButSearchRecord_Click()'clicking the command button named "Search Record" carries out the below

Dim RecordSearch As String 'create a variable named RecordSearch to store a value in memory

RecordSearch = Application.WorksheetFunction.Match(ComboBox_OrderNumberSearch, Sheets("Datatable").Range("DynAnchorOrder"), 0)
'match the selection inputted in the combobox to a value in the datatable based on the range defined by the dynamic anchor.

Sheets("UserFormEngine").Range("D5").Value = RecordSearch

The dynamic anchor dictates the column to look in for the value (selected in the combobox) and the column that contains the row number. It then passes that row number to cell D5 on the sheet 'UserFormEngine'.
I then 'call' cell D5 to 'pull' the data from that row in the datatable to the 'edit' userform.


I hope that the above makes sense!

Thanks,
NoobyNoobyNoob

[torachan]

Did something similar a few months ago.
Where items had common grouping and filtered out on second or third field.
It was easier for me to cut this down as I always prefer to use tables - referencing is more efficient.
The one form approach is something I have adopted for a number of years - I find it cleaner.
Declare all your variables at the head of the UserForm module and then they are available to each Sub.
Such as the rowcounter 'rctr' established by the listbox and available to the 'update' and 'delete' subs.
Hope some of the techniques prove useful and time saving.
torachan.

[noobynoobynoob]

Hi all. This is very impressive Torachan. I can see that it clearly works. I won't pretend to understand how it does it as it's completely beyond me at the moment.

[noobynoobynoob]

I've been working through the code and variously trying to comprehend and understand arrays, loops and collections as I've gone along; with limited success. The code is really dense (as efficient code
should be I expect)!

Can someone point out to me where I've got this right and wrong? The code works and is excellent I just need to understand it!

''''''''''''''''''''''''
Option Explicit 'declares all variables before running the code
'''START OF VARIABLES'''
Dim ary1 As New Collection, ay1
Dim arr2 As New Collection, a2
Dim arr3 As New Collection, a3
'a collection is basically a sophisticated type of array. _
An array is a group of variables. It is (usually) a fixed area, can only contain data of the same type (integer, string etc) and _
a fixed set of values. Collections can be dynamically increased or decreased (they can hold more or fewer items on demand). _
A collection allows items within it to be added (.Add), found(.Item), counted(.Count(returns total amount of items in that _
collection)) and deleted(.Remove) from the collection.

Dim Aary() As Variant
Dim Bary() As Variant
Dim Cary() As Variant
Dim TEMP1ary() As Variant
Dim TEMP2ary() As Variant
Dim TEMP3ary() As Variant
' declaring the variable as a Variant type means that the value stored can be a number or text

Dim rowcount As Integer
Dim i As Integer 'row number
Dim j As Integer 'column number
Dim k As Integer
Dim z As Integer
'declaring the variables as Integer type (number only)
'''END OF VARIABLES'''

Private Sub cbo1_Change() 'when the object (combobox) is accessed/used/clicked
Me.cbo2.Clear ' Me is shorthand for 'this object' In this case 'this userform'. Saves having to rename all the references _
from UserForm1 etc if you change the name of the userform at somepoint (code won't run as it doesn't know the _
name of the object anymore). Also dead handy for copying code from one userform to another (I think)!
'telling cbo2 to empty what it has (essentially to reset)

rowcount = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row 'counts the number of rows in column A of Sheet1 and stores this as a _
integer value in the variable rowcount
'rowcount = Worksheets("NAME OF SHEET").Cells(Rows.Count, "COUNT NUMBER OF ROWS IN THIS COLUMN").End(xlUp).Row

TEMP1ary = Worksheets("Sheet1").Range("A2:C" & rowcount).Value 'declaring the size and shape of the array
'Setting the size of the array as Sheet1 A2:C(value of rowcount variable). Takes account of the header row in Cell A1
'SO TEMP1ary is the number of ROWS AND COLUMNS OF DATA in the table (minus the header row)

k = 1 ' variable k has a value of 1

For i = LBound(TEMP1ary) To UBound(TEMP1ary) 'controls the number of rows to loop
'For is declaring a loop and Next* is telling it to 'go around to the start' (loop) until the amount of times it has looped equals the UBound value
'LBound and UBound are the LOWEST (LBound) and HIGHEST (UBound (upper?)) values in the array named TEMP1ary
'TEMP1ary is Sheet1 A2:C(value of the rowcount variable)
'rowcount variable is the number of rows in coloumn A in Sheet1

'SO i controls the number of times to perform the loop
' the number of times to loop is set by the lowest and highest values in the variable TEMP1ary _
EVENTUALLY: Loop sequentially from the lowest to the highest value of the number of rows and columns in A:C of Sheet1 (less the header row)
If TEMP1ary(i, 1) <> Me.cbo1.Value Then
' put the value of the row number (held in variable i) into column 1 of TEMP1ary IF that value IS NOT equal to the value in combobox 1 then enter loop j
For j = 1 To 3 ' controls the number of columns to loop (3)
'**For is declaring a secondary** (nested) loop
TEMP1ary(i, j) = ""
' put the value into TEMP1ary (use the row loop (i) to determine which row to put it in, use the column loop (j) to _
determine which column to put it in) = blank value
Next j ' loop the next column ** This is the 'bottom' of the second (nested) loop
Else: 'otherwise (if the if statement is not true!)
k = k + 1 ' if the above is not true then add 1 to variable k (k was set with a value of 1 before the routine (above) began so k will now equal 2)
End If ' end the if statement
' is this whole thing just populating TEMP1ary with the values in Column A but without duplicates?

Next i '* This is the 'bottom' of the first loop
'For is declaring a loop and Next* is telling it to 'go around to the start' (loop)

ReDim TEMP2ary(1 To k, 1 To j) ' ReDim tells excel to change the size of the array
' size of the TEMP2ary is to be changed from 1 to k (what it currently is?) to 1 to j (WHY?)
''''''''''''''''''''''''''''''''''

Many, many thanks in advance!!
NoobyNoobyNoob

[torachan]

Nearly all right or near enough.
Except.

For j = 1 To 3 ' controls the number of columns to loop (3)
'**For is declaring a secondary** (nested) loop
TEMP1ary(i, j) = ""
' put the value into TEMP1ary (use the row loop (i) to determine which row to put it in, use the column loop (j) to _
determine which column to put it in) = blank value
Next j ' loop the next column ** This is the 'bottom' of the second (nested) loop

This is all the same array - what is happening is if the field does not equal the test field(combobox.value) all fields in the array row are filled with "".
k is not updated (only updated if row retained) this then used to resize the array after all the blank rows are removed.
This method has been supeceeded in recent year by using tables and advanced filters - I only use it because I am old and stuck in a rut.

[noobynoobynoob]

ok, I think I've made some good progress in understanding this.

I've changed the names of the variables (based on what I think they are and what values they are holding, for what purpose). Have also heavily indented to help me
concentrate on one bit at a time.

It might have taken me a while but I'm getting there. I think that I'll be ok with working out the next sub after this one as I think that it's essentially doing the
same thing as the one below (but for the second combobox). However, I am a bit stuck on what is going on with that collection (foot of the code block below).

As a recap I have 3 comboboxes in a userform that should list Order number, part number, quantity. I need them to 'cascade'. So selecting order number 1 in combobox1
then populates combobox2 with only those parts that are 'in' order number 1

Please Login or Register  to view this content.

I continue to be very grateful for any and all assistance.

Thanks,

NoobyNoobyNoob

[torachan]

Before proceeding further, I do not want you or I to suffer the wrath of the moderators.
Please read the forum rules, especially those related to encasing code within code tags i.e # #
Please edit each piece of code submitted.
torachan.

[noobynoobynoob]

And finally (hopefully!) I need some help with understanding how the data is 'flowing' through the initialize event:

Please Login or Register  to view this content.

I will get there and the principles of this code will be utilised many, many times with different datasets and userforms.

Thanks

[torachan]

It is good to see great progress and interest.
The basics of the initialise event > placing the content of column A into single array > this may well contain many duplicates.
Then taking that array and placing in a collection > this process does not allow duplicate keys therefore produces a list of uniques.
The .Count is a query you can make on the collection which gives you its size for the number of unique items to add to the combobox.
The links below are ones I frequently refer too as approaching my 'four score' I have forgotten more than I have learned.

https://www.snb-vba.eu/inhoud_en.html

https://www.thespreadsheetguru.com/b...t-excel-tables

happy coding.
torachan.