Data entry sheet problem

[mcchieftan]

I am a VBA noob so please tell me if what I am attempting is simply too complex for where I am right now!

I am a small-scale cultivator of gourmet mushrooms and I am building a database to keep track of my crop. I am an experienced excel user and have recently enjoyed discovering a lot of excel that I never knew about (e.g match+index lookups) In the attached spreadsheet there is an example of a incubation database that I put together on the sheet Inc.S.

I am currently trying to simplify data entry using the sheets D1-D4, (listed on SW2 menu) I knew nothing of VBA until I started this last week! I downloaded a sample form here http://www.contextures.com/xlForm02.html and managed to work out how to get the first form working to my satisfaction but I'm having trouble with the second: DE2

I want the user to be able to input/choose from dropdown the item ID (E8) and have the rest of the form return the information for the relevant item. The user then fills in the quantity used (I8) and hits 'save record' to update the database (DB).

Sheets D3 and D4 will have a similar mechanism so I think if I can get my head around D2 the others will be fine..

One other question was, is it possible to include a control in the tab order? For D1 I worked out that I could set only the data entry cells to be unlocked and then protect the worksheet so that the user would automatically tab through only these cells but then I had to click on "Save record" manually.

Thank you for your time! As I'm sure you were all n00bs at one stage I'm sure you know quite how frustrating it can be! I found a resource on excel VBA that I'm getting into but obviously its not as direct as being able to talk to other, more experienced users.

Finally, please tell me if I have included inadequate information and I will do my best to clarify.

Thanks,

 

[NARAYANK991]

Hi ,

I like what you have done so far , and I'd like to help out in taking this forward ; my main problem is a lack of time ; can you say what kind of a time frame you are looking at to complete this ?

Narayan

 

[Nebu]

Hi:

This is definitely doable, if you say [I want the user to be able to input/choose from dropdown the item ID (E8) and have the rest of the form return the information for the relevant item. The user then fills in the quantity used (I8) and hits 'save record' to update the database (DB).] what is the criteria for auto populating the values? is the values going to come from the DB if yes are you looking for the last instance of the ID in the DB to be updated...

Thanks

 

[Nebu]

I have worked a bit on your file , hope this roughly what you are looking for, I have also fixed the tab index for sheet DE1 and DE2

 

[mcchieftan]

@Narayan: Thanks for your offer of help. The project is not urgently required. The spreadsheet will be functional without the smart data entry forms and there is plently that I can work on otherwise. I am a member of a online community of gourmet mushroom producers and I wanted to provide a tool to other growers who have less time/enthusiasm for excel but face the same difficulties of keeping track of multiple crop cycles.

In the short term if someone could point me in the general direction of the right bits of code or someone elses spreadsheet where these things are already in use, that'd be enough to get me started.

@Nebu: Thanks for taking the time to have a look. ID is a unique identifier so for sheet DE2 I'd like the user to be able to input an ID (Or choose from a drop down list) and the form will return the only values associated with that ID

I downloaded the sheet that you sent. DE2 works great in pulling up the corresponding record for each ID but unfortunately creates a new record rather than updating the old when I click "Save record".

I tabbed through the fields on DE1 but I didn't see a difference. Perhaps I misunderstood the process and misphrased my request: Is it possible that the user can tab through each of the relevant fields and when they tab out of the last field the focus finished on the tab (so they can tab, tab, tab, enter - rather than having to do a mouse click?)

Thanks for taking the time guys Really appreciate it!

 

[Nebu]

Hi:

The tabs will work only if you start updating the values (for e.g.: if you change the value in the drop down cell "D7" in sheet DE1 it will tab to cell "D9" drop down automatically and so forth).

Regarding the updating the values find the workbook with modified code.

Here assuming that in sheet DE2 you will only update Quantity used and if you change the Quantity used in cell I8 of sheet DE2 that change will be captured in the DB sheet for the corresponding ID. Please go through the file and let me know with questions if any.

Thanks