Finding Database Entries

[BruceVBA]

On the surface this seems like a relatively simple VBA code problem, but I cannot get my head round the concept of the 'Find' property and dealing with code that will define data entry based on two fields!

Any help on any of the points I have outlined below would be absolutely fantastic and enable to me to virtually complete the database I am currently trying to build. I would like to thank anyone in advance for providing any input into the problem I have outlined below:

I have managed to write a 'Do While' loop which updates a combobox selection from an open (variable) vertical data array. The array contains dates (mm-yy) in chronological order, and must correspond to another combobox selection containing 'project names' (chosen by the user) with an equivalent loop structure. Now I need to write some code which will:

1. Acknowledge that a selection from the list has been made (by the user) in a combobox.

2. Check to see whether this entry has been made before in the database which must also depend on an item selected from the other combobox (possibly using the 'Find' property?!!).

3. If the entry has not been made before, it must choose where to make the entry (i.e. by finding the correct row down in chronological order. e.g. if an entry for a date has previously been made say for April 2002 for a specific project name, then the code will need to search for this entry and insert a row and the corresponding entries of data below it if say you were entering data for May 2002 for that project).

As I said if anyone has a clue about producing code to solve any one of these ideas I would be most grateful.

Regards,

Bruce

 

[DaleWatson123321]

Bruce,

To get things started, can you first confirm that you're talking about EXCEL, and a datalist or database within Excel.

If this is the case, then I would suggest that your best route is to utilize Excel's powerful database functions, and database functionality.

Using "criteria" that is tied to your comboboxes, you can have a database formula tell you whether the item already exists in your database.

Regarding inserting a row so that the new item is in the proper chronological order... I would suggest that an easy option is to simply use a "sort routine" - i.e. have the entry entered at the bottom of the database, and then sorted into the proper location.

If this is Excel, and you'd like "specific" help, I would be open to having you email me your file - or a scaled down version - and I would insert the necessary database functions and code.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[BruceVBA]

Dale,

Yes this is an excel-based (v97) VBA problem. So you are suggesting to perhaps use a current region and row Counting function to input a data line at the bottom of the database and then use a 'sort routine' to sned this entry into the correct chrono order? Could you give me an example using an appropriate sort function? Unfortunately I cannot send the file for reasons of confidentiality but I could send you an equivalent example if you would be prepared to help me on that level. I will forward this to you soon. Thankyou for your help on this.

Regards

Bruce