Userform Search Criteria

Does anyone know what code I can use to reflect the Find search to cater for multiple text lookup options in the userform?

For example, the current code (cmbFind_Click) caters for find lookup on one text box only (txtDesLcn), however I would also like the oppotunity to use the search criteria for "txtSerial" as well. e.g. tupe in the Serial number and click "Find" to display the rest of the fields in the userform from the Database.

I was thinking

Please Login or Register  to view this content.

This however doesn't work. Please refer to attached spreadsheet for code.

Thanks in advance for your help!!!

Originally Posted by Leith Ross

Hello timarcarze,

I updated your macro to find either the location or the serial number.

Hi Leith,

Thanks for your assistance. This seems to mainly work, however I noticed 2 bugs on two seperate searches:

(1) It fell over when I attempted to search a 'location' which had multiple copies of the location e.g. Test (type in "Test" in location and it brings across wrong data).

(2) I also populated a number in serial number, .e.g. 123 and then tried to search "123" in serial number text box. The search came back with "found 10 copies", however 123 was a string from other columns in the datacase rather than specifically looking for the number 123 in "Serial Number" column.

Hope you can help.

Originally Posted by Leith Ross

Hello timarcarze,

I have fixed the first issue and second one partially. The forward and backward buttons do not return the proper data from the Find list. I will have to look more closely at the code to find all the problems. Here is the updated macro which has been added to the attached workbook.

Thanks Leith, that's great! I see your point about the small error with the start and end buttons.

If you wouldn't mind, would be great if you could point out how to resolve the border creation on adding a new entry, per my other post: http://www.excelforum.com/excel-prog...row-added.html

Thanks again for spending the time. Is a real hand for us newbies coming to grips with VBA.

Originally Posted by Leith Ross

Hello timarcarze,

The attached workbook has been revised. Try it and let me know if any changes need to be made.

Hi Leith,

I have just uncovered one other bug with the "find" and "amend" functionality, and am unable to fix it.

You have set it up so that we can search ("Find") by 2 criteria, (1) Despatch Location and (2) Serial No. Despatch Location works fine when you search by Despatch Location, however the problem lies with the Serial No. The steps to trace the error are as follows:

1. Add a new record completing the fields up to and including the serial No, e.g. Serial No = 12345.
2. Exit Userform
3. Open userform and enter serial number 12345 and click Find.
4. It brings up the data ok, but when you complete the blank fields and click "Amend", the data is populated commencing at the serial no field and do not line up with their corresponding field names?

I presume this would need an If Function of some sort that would cover the 2 "Find" scenarios listed above. Appreciate if you could help me out on this one.

Originally Posted by timarcarze

Hi Leith,

I have just uncovered one other bug with the "find" and "amend" functionality, and am unable to fix it.

You have set it up so that we can search ("Find") by 2 criteria, (1) Despatch Location and (2) Serial No. Despatch Location works fine when you search by Despatch Location, however the problem lies with the Serial No. The steps to trace the error are as follows:

1. Add a new record completing the fields up to and including the serial No, e.g. Serial No = 12345.
2. Exit Userform
3. Open userform and enter serial number 12345 and click Find.
4. It brings up the data ok, but when you complete the blank fields and click "Amend", the data is populated commencing at the serial no field and do not line up with their corresponding field names?

I presume this would need an If Function of some sort that would cover the 2 "Find" scenarios listed above. Appreciate if you could help me out on this one.

Can anyone assist with the above query? I believe it has something to do with the "Activecell" reference in the Click_Amend Macro, however not sure how to fix it??? Help appreciated.

Guys,

I received some help from another site on this last part only (http://www.ozgrid.com/forum/showthre...goto=newpost**). The code received was:

Please Login or Register  to view this content.

This seems to have fixed the Amend/Find issue when searching via "Serial No".

Thanks for your help