VB books (2002)

Topic

New Reply

It appears that nearly every issue addressed in this forum has to do with VB – which I know absolutely nothing about. I want to go to the next step in developement, but the creative process has hit the “Wizards” wall. Can anyone refer me to a good source for self-learning? There are about a million books on Amazon concerning VB and I’m not wealthy. I don’t mind spending money for value though – I just can’t afford to be wasting it.

There are two sources that have caught my eye, but I don’t know if they would be over my head as a beginner:

1.Access 2002 Developer’s Handbook Set
2.Access 2002 VBA Handbook

These two books can be bought as a set for $112.00 from amazon. That’s a lot of money, but, as I’ve said, I don’t mind spending the money on something I will have for some considerable period of time. I just don’t want to end up with something that is beyond the beginners ability to understand.

Little Help?

Reply | Quote

Replies

While those are excellent books, they might not be what you need now. When you are just getting started, you needed more of a tutorial to teach what you need to know in an organized way. Some years ago, I got “Beginning Access 97 VBA” by Smith and Sussman; I’m sure there is a more current version available now. They start with a simple database, then every chapter builds on that same database as it introduces new concepts for you to learn. And yes, I also have the Developer’s Handbook set, and wouldn’t be without it! It is an excellent reference set, for when I want to know the details about a specific topic.

Reply | Quote

Thanks for your input Mark. I will look for an updated copy. May I assume that any books on VBA will apply to ANY version? By that I mean to say, will a book concerning VB in Access 97 have the same usefulness in Access 2002?

Thanks Again,
Bryan

Reply | Quote

In terms of learning VBA, I don’t think it would much matter which version of a VBA book you got, as long it was at least Access97. You wouldn’t want such a book from Access 2.0 (which actually wasn’t VBA). There is certainly nothing in Access97 VBA not supported in later versions. If you were going to invest in a reference book, you ought to get one consistent with the version of Access you will be using.

Reply | Quote

Hi again Bryan,
Mark’s suggestion is an excellent book – I own it – but I would get the 2000 version. It doesn’t appear based on a web search that WROX has published a 2002 version, but the differences in VBA are pretty small between 2002 and 2002. They are a fair bit larger between 97 and 2000. Another thing you might consider is one of the step by step or 24 hour books in getting started. They can often get you past the very basic parts of programming, and then a detailed tutorial book like the Sussman book makes lots more sense. The books you mentioned are excellent resources if you are a professional programmer, but for getting started I would wait on them. Hope this helps.

Reply | Quote

Thanks and a tip of the hat to both Mark and Wendell for the input! Just ordered Beginning Access 97 VBA Programming and 2002 Step by Step. I figured between the two I could determine some basic understanding and let either my satisfaction or frustration guide me to whatever level. I do have to say, despite the frustrations, that this has been a very rewarding experiance. I must say that with all the rubish, and demented freaks, out there in cyber-world; it is most gratifying to find selfless individuals who are willing and able to assist and mentor. I am, indeed, humbled.

Bryan

P.S. While typing this message I was just approached by an operator wanting to know if there was a way to make an entry (as opposed to a selection) in a combo box appear from the drop-down on the next visit. This is NOT another request for help (although it will probably become one). I just have to laugh! We (or rather you) just solved a problem, the keyboard hasn’t even cooled off yet, and here they are asking for more! Is THIS what I have to look forward to in the future?!! LOL

Reply | Quote

______________________
P.S. While typing this message I was just approached by an operator wanting to know if there was a way to make an entry (as opposed to a selection) in a combo box appear from the drop-down on the next visit.
______________________

This is a fairly common question, and can be done in a couple of different ways, and since you asked the question, what the heck. If you are storing the combo box text string directly in the table, one solution is to make the contents of the drop-down the unique entries in the table, and involves no code. The more common scenario stores a number in the table, and links to another table which contains the description for that numeric coe. In that case, code is typically written to deal with the Not In List event to prompt the user as to whether they want to add the new entry to the linked table, and presuming they do, DAO or ADO code to create the new record is executed. I’ll let you explore the details, but you can tell the operator it is possible.

Reply | Quote

Thanks for your interest. I should first probably say that I am not the origunal creator of this DB and that I am learning new things about it each time an issue comes up. The combo box in question simply displays a customer name from a look-up table linked to the main form table. However, dbl-clk on this cell and it displays driver directions to this customer from a seperate table. This customer name also appears on some other forms and reports and I’m not quite certain how this all works together, need to do some more research.

My first consideration is: Why would the creator have made a look-up table for the name (there are no other entries in that table) and a seperate table for the directions, which has multiple information cells with names that are common to both tables? Maybe the directions table was an add-in after the fact? I don’t know, but it seems redundant.

Like I said, I think I need to look into the ramifications of any changes to that combo-box before I proceed. I will get back to you once I have a clearer understanding.

I’m really loving this mentoring situation! Really look forward to creative efforts with you!

Bryan

Reply | Quote

Well, let’s see if I’ve got this straight:

There are six combo boxes on the Order form with the same function. Each one of these have a seperate look-up table with common names for shippers or consignees. The only link I can see between these tables is from the seperate look-ups to the Order form table. I don’t really know why.

Just above these combo boxes for the shipper/consignee name is another combo box with only two choises: “shipper” or “consignee”. These are really nothing more than lables as far as I can see. This first one defaults to “shipper” and the other five default to “consignee”. In the real world any of these names selected could be either a shipper or consignee, though obviously at least one of them must be a shipper and one of them must be a consignee. Looking at the need for six combo boxes I have discovered that there may be multiple shipper locations (pickups) as well as multiple consignees (receivers). No one seems to know what would happen if you exceeded six designations on a single order. (Shut down and go home I guess).

Once a name has been selected from the customer name combo box you can dbl-clk this cell to have a form open with the directions, contact, phone number, etc for that customer. That information comes from a seperate table called ‘Directions’. I noted that the directions form may open with more than one record. This is because it searches for the customer by name only. There may be a customer with more than one facility – so it recalls all records for that customer name regardless of the number of facilities. The operator must use the record selection buttons to scroll through to find the correct location (city). There are 2,317 records in the directions table. The only duplications are the customer names. The reason for that is because there are several locations (cities) for the same customer name.

Much of this seems a bit redundant to me, but I’m new at this so maybe there’s a good reason. This isn’t the speediest DB I’ve ever seen. While all the operators want is a way to enter a new customer name without having to go to the underlying table to enter it, I think there is a larger issue here that needs to be addressed. The customer name fields in the Order form table, as well as the Directions form tables (shipper or consignee), have nothing to do with the “customer” who gets billed (there is yet another combo box for the party billed – which may or may not be the same as one of the customer names in the shipper/consignee combo boxes). Much of what is accomplished by the existance of these shipper/consignee boxes is nothing more than screen information and has nothing to do with billing because all of this information is extracted by reports to be manually entered into a seperate accounting program. Talk about inefficient!
My thought is that, although the order entry form is based on its own table, the shipper/consignee combo boxes should have a single source for thier look-up results (that being the Directions table) as the customer name in these boxes is common to all other boxes (i.e. you could have one name for a shipper, but you could also have the same name for up to five consignees with different city locations).
Boy, I bet you’re sorry you offered your help now! I’m still looking at how all the pieces fit together, but I think I will probably be trying to link the Directions table to the Order Form table and re-assigning the combo boxes source to Directions table rather than the seperate L/U tables for each of the boxes. At that point I suppose I could just delete those excess tables after testing. With 2,317 records in the Directions table I sure hope I don’t do an “Oooops”! Anyway, I would like your input on making these changes (if my thinking is right) before even addressing the NotInField issue. What do you think?

Thanks,
Bryan

Reply | Quote

I’ve been chewing on this for a couple of days while I’ve been trying to catch up after being gone for 3 weeks, so my apologies for the delay.

In any event, I think your analysis is pretty much correct in that it doesn’t make a whole lot of sense to have 6 combo boxes to let you pick one entry from the table. I have seen an occasion where we used two linked combos to first choose an organization, and then choose from several items associated with the selected organization. In that case we had separate tables that were also linked. The way we accomplished that was to update the rowsource for the second combo box based on the organization selected in the first combo box using the AfterUpdate event for the first combo. Then we allowed data entry staff to enter new organizations using the Not In List, and to also add new details associated with a particular organization using the NotInList event of the second combo box. However even this can get tedious if you have several hundred or even thousands of entries associated with one of the organizations.

In that case, we often resort to a tree view, although programming to use one gets to be a sizeable task. It’s advantage is that you can break things down into multiple levels so you aren’t scanning through lots of data to find what you are after. Microsoft has a treeview that comes as a part of the developer edition of Access, and some other companies offer more sophisticated ones. If your interested, post and I forward some details, but be warned that you need a pretty good grasp of VBA before you tackle it. Hope this helps some.

Reply | Quote

Thanks, once again, for the input. I think we both know where I am as far as VBA goes (I haven’t even received the books yet, let alone read any). I did manage to go back through and look at all the relationships and redundant info. I was able to eliminate five tables and improve the lookup funtions simply by removing the redundancies. I’m not sure how the unnecessary repetition affects the db, but the result is noticeably faster.

The only method I can see at this point for getting around the multiple combo boxes would be to use a subform, and I’m not so sure that would be any more efficient from either the operators position or even the application efficiancy. Need to do some more homework.

I have also discovered that there are 53 queries in this application and some (apparently) are not even being used. The same seems to be true of some of the machros. I think, before I go much further, that I should probably attempt to eliminate some of the “garbage” that has been left behind. From what I’ve been told, there have been several different people involved in this db over the years. Also, it was originally created in ’97 and converted to XP.

I do have a copy of ‘Mastering Access ’97” and must admit that just going through this db is a great learning experiance. I can see immediately how important it is to have a game plan before sitting down to create tables and queries particularly. I’m certainly not in a position to be judgemental of anyone else’s work, but I can see how easily one might become entangled in one’s own web if a fundamental plan is not established and adheared to. As I said, this is a great learning experiance.

Thanks again for your assist. I very much appreciate your interest in my developement in this application. Without being to presumptive, I should like to say that I view it as a mentored journey. If so – I certainly look forward to it.

Bryan

Reply | Quote

If you are going to try to do a clean-up of the database, you might consider investing in a tool called Speed Ferrett. It lets you find every ocurrance of a particular name, so you can begin to see where things are used and not used. It will also let you go in and rename objects so you can begin to have a consistent naming convention. It’s a fairly reasonable priced product – there is another more expensive product that we also use from a company called FMS called Total Access that does a comprehensive analysis of a database, and creates a used-on report as well as performance and design analysis. There are also some free tools available from Helen Feddma’s website, and from one run by Dev Ashish known as the “The Access Web” and you can see an example of TreeView technology from a company known as DRI on our website.

Reply | Quote