Find Record

[Delta]

Hi All,

I am quite new to Visual Basic and was wondering if someone could help me out.

I have a User Form to input information onto an Excel spreadsheet. This interface should ultimately make processes more user-friendly.

I would like to have a Find Record command button that I could click on, enter the search criteria and the first record found will appear on the User Form.
Users should have the option to click on a Find Next button if the first found record is not what they are looking for.

Any advice is welcome, as I'm not sure where to start!

Thank you in advance.
Delta

[lifeson]

How many criteria are you looking for?

[Delta]

The criteria is user specific. For example, some users may want to search by forename, and some may want to search by surname, or both names. Another example, perhaps the only information someone has is the order number and they want to find out details about that order.

Thank you.

[lifeson]

I assume order numbers would be unique but what if there are duplicate fore or surnames?

[Delta]

If there are duplicate names, there should be an option to to find the next record until all have been found. At the end, there would have to be a message to say, "No more records found", or something similar.

[mdmackillop]

I would suggest a combobox which would contain all available names to be searched, and a listbox to display all names found. You could add an option button to choose between forenames/surnames.

[Delta]

I don't think I can use a ComboBox as it restricts what someone can search by.

Perhaps I didn't explain myself clearly. I would like a Popup Box where I can enter any search criteria and it will search my spreadsheet and populate my User Form with the result of the search. I also want something which would enable me to find the next record if there are any.

Thanks for your ideas so far.

[mdmackillop]

Can you post a sample of your data, and also indicate which fields are to be included in the result?

[BackJack]

Delta,
I am faced with the same challenge at my work currently. I am looking into using the find() method in a loop that "counts" the rows in a specific column to see if it sees the requested text.

Just curious if you would like to compare notes and collaborate on this task.
-BackJack-

[BackJack]

Delta,
Today I worked on a very similar search function to your question. I have included the first version of the search for your review.

I have a user interface that collects personal information such as firstname, middlename, lastname, address, etc. On save, the personal data is stored on an Excel spreadsheet. Firstname is column 9, middlename on column 10, and lastname on column 11. The user can enter any number of records into the spreadsheet they wish. On this user interface I have a textbox, (txtSearch), which the user can enter a name that they want to search for. There is a command button named cmdSearchFind that the user presses to find the next record in the database

So here you go. This is version 1. Private Sub cmdSearchFind_Click()
If Len(txtSearch.Text) > 0 Then
Dim iRow As Long, iRowMax As Long
Dim iCol As Integer, iColMax As Integer
Dim tmpSearchValue
Dim Whoa As Boolean, ChkAll As Boolean
Dim ReChk
iRowMax = 65536
iColMax = 11
iRow = txtRowNumber.Value + 1
iCol = 11
Whoa = False
ChkAll = False

tmpSearchValue = txtSearch.Text

Do Until UCase(Sheets("Sheet1").Cells(iRow, iCol).Text) = UCase(tmpSearchValue) Or Whoa
iRow = iRow + 1
If iRow > LocateLastRow Then
ReChk = MsgBox("You have reached the last record." & vbCrLf & _
"Do you want to recheck from the beginning?", vbYesNo + vbQuestion, "Search Option")
If ReChk = 6 Then
iRow = 2
Whoa = False
Else
Whoa = True
txtSearch.Text = ""
End If
Else
Whoa = False
End If
Loop

If iRow < LocateLastRow Then
PopulateInitialForm (iRow)
Else
PopulateInitialForm (LocateLastRow)
End If

End If
End Sub

Please tell me what you think for the first version. Version 2 will search all three columns and version 3 will take partial data and return the remainder, ie: Hol would return Holmes, Holland, Hollbrook.

[mdmackillop]

What is this code?
PopulateInitialForm (iRow)

[BackJack]

Dr KillOp,
The user interface that I was referring to was named frmInitialForm. Whenever I need to read data from the spreadsheet and place it on the user interface, (name frmInitialForm), I call the method PopulateInitialForm. The passed in attribute of iRow is the row number of the spreadsheet that contains the personal information for the target.
Conversely I use SaveInitialForm whenever I want to save personal information to the spreadsheet.
I hope that clears up any questions that you may have. As you see, I try to use meaningful names for variables and methods.

[mdmackillop]

Without seeing the code, it's hard to evaluate your solution. To be honest, I would look at a Find solution. Checking 60k cells or so one at a time will be a bit slow.

[BackJack]

I don't understand why you are evaluating my solution to another person? I am just offering to help someone working on a similar task. I offer this information for free and disclose it fully. I am not doing this for a grade or for money. I am just trying to help another developer find a solution while I garner more experience and knowledge. Maybe I would be better off assisting others on another forum!

[mdmackillop]

I don't understand why you are evaluating my solution to another person?
Because this is generally seen as a co-operative forum. In this case the OP had not responded to my last post and your "solution" is being seen by any who visit this thread. It seems reasonable that this should be workable for all. It also seems reasonable that the solution presented should be the best possible.

[BackJack]

It is obvious to me that you see yourself as this website police and the proctor governing this site. I noticed that you were not the person asking for assistance, nor the person who provided any help. Just seems that you interceed on every postiing and tell everyone what is acceptable or not. I don't care if you like my offering. Many times great ideas come from the stimulus provided by others eventhough they don't provide the final solution.
Is this site only successful because you are the POLICE or because so many people offer so much help to so many. Try helping, instead of being the Barney Fife!

[mdmackillop]

I look forward to your solution.

[BackJack]

Instead of sitting there waiting for everyone else to provide a solution, why don't you develop a solution? You are capable of coding in VBA aren't you? We all look forward in anticipation to your solution. I have already posted an offering. Lets see what you have????!!!!!!

[lucas]

BackJack, I am going to ask you nicely once and only once to back of the attitude. I am the police and your behaviour is unwelcome, unproductive and will not be tolerated for long.

Malcolm commented on obvious problems with your code. I agree, without the other procedure it is worthless. He points out that it would be slow and he responded to your questioning of his authority in a civil manner.

You, my friend are out of line.

We pride ourselves on having a friendly forum. You are welcome to participate as long as you are civil.

Malcolm is the police as you call it here. I will handle it if he doesn't want to because you made it personal with him. Please check under his avatar and compare it to yours. Malcolm is a well respected person in this forum. You are a complete unknown until now. So far you haven't given us anything except negative's to judge you by. It's your choice.

[mdmackillop]

We all look forward in anticipation to your solution
I don't recall your election/appointment to Site Spokesperson, that said, I refer you to my previous post.

[lucas]

After continuing his same behaviour on another thread, backjack has been banned. I don't take this lightly and I don't like doing it but sometimes it just has to be.

[lucas]

After following backjack to another thread and finding the same behavior I banned him. Malcolm has pointed out that he had posted in that thread before I intervened in this thread and at Malcolm's request backjack is being given one last chance.

[Delta]

Thank you to those who have replied so far.

I have found an example, of what I am trying to achieve, on the following website.

www.excel-it.com/vba_examples.htm

The user inputs their search criteria in any text box which they have information for. For example, the user may only have the Order Number at hand, so they type that in the relevant box. Another user may only have Supplier details, but since it is not unique like an order number, there would be an option to find the next record, and so on.

It seems all very complicated to me and I don't know where to start, apart from try to edit the example from the website.

Thank you for your time.
Delta

[lucas]

Which of the examples on that link specifically Delta?

[lucas]

How about something like this. You could theoretically have several texboxes, each to search a different column.

Just start typing one of the names into the textbox and when you get one you like hit the command button to close the userform and select the row....many things you can do from there but thought I would offer it as an option.

[teodormircea]

Just read this post and gave me some ideas,

Some improvements, like ad a new field for one more criteria, with the choice to use it ore not.
Can also see the headers in the listbox.
If you know how to do it it will be great , :beerchug: