By Richard Rost
3/26/2008 9:57:41 PM
Here are some random questions sent to me about topics we covered in my Access classes:
Question: Richard: When you set up the ProductT in AC302 you said about the 'picture field' you would 'play with that later' or something to that effect. Also, I looked at AC313 and 'ProductT picture field' still doesn't have a picture.
Next in AC310 when you moved the graphics from the forms, reports and a couple of letters the size of the database dropped from something like 11MB down to 2 MB (if I remember correctly). Also in one of the courses about the productT you said we might have something like 5,000 records (pictures). Here's my Question: If that is the case and we have one logo removed and it drops the database from 11MB to 2MB - how are we going to store 5,000 pictures without MS Access being 'bloated' or even to large to use? ""A large database is a slow database"" as you always say. With 5,000 (or more) pictures would MS Access even be able to be usable?
- - -
Good question. The problem with storing pictures in your database is that Access doesn't really do a good job of compressing images down. Now, Access 2007 has added some nice new features to help with this. You can add pictures as ATTACHMENTS to your tables which cuts down on the size problem. However, here's what I usually do if I need to store a lot of pictures.
Let's say I'm storing employee pictures. I've got 1000 employees and they all have pictures. I don't want to store that information in my EmployeeT because then any time I try to do any lookups with EmployeeT it's going to be slow. So I'll make an EmployeePictureT which I'll actually put in a separate LINKED database MDB file (linked on EmployeeID). The only thing in this table is the EmployeeID and a Picture field. Now, if I need to get access to those pictures, I can just link to that table (query, form, whatever) and it works fine yet it DOESN'T slow the rest of my database down.
Richard: How about a list box course which would have a multi-list box which would add (say) products to the order subform when an item is clicked on. For example, say, PCResale.net is running a special on 'whatever' and you would have a small option group or list box and the user could choose several items off the list and it would automatically add these to the subform instead of using a drop down combo box? I'm guessing the problem might be how would we add the quantity of '101 keyboard, wireless mouse or whatever' was chosen? I might want to purchase 2 of the wireless mice or 3 of the keyboards because they are on Special Sale this month. I don't know if you would want to use an option group or a list box, but back in AC203 you talked about a multi-entry list box.
- - -
I am going to cover multi-select listboxes in a future lesson. They're not really easy to use because you have to get their values using some convoluted VBA code. I'll show them to you later.
Suggestions: Maybe you want the sales between two different dates - for example compare this year sales in, say, June to June of this year. Or something like: I want to see the sales to Customer # 1 (Amicron Computing) and Customer # 5 (Pharmacon).
- - -
You should be able to do this already with basic QUERIES that we've already learned. Come on now... I know you can. Just use your parameters and calculated fields like I've shown you.
Question: Richard: In AC310 we have the PaymentT. Let's suppose we have someone (say Anna Picore - we haven't seen her in awhile) come in to make a payment on an item she purchased. Now from the PaymentF - can we print her a receipt? Would you show how to do that?
- - -
A: Not really... the Payment form is just to enter the payment in. If she wants a receipt, just close the payment form, go back to the order form, and click on the PRINT PRODUCT INVOICE button. That will print out a receipt for her.
- - -
Q: Also, after Anna leaves Benny Sperduti comes in and makes a payment. Next Anna visited the ice cream shop next to PCResale.net and has some money left over; comes back in to make another payment and says ""Richard - just email me a copy of my receipt."" That could introduce the ""Send Method Object"" (it's called something like that). So, let's say we want to send her an email with the copy of what she has just paid us. I, personally, think this would be a great topic for a new lesson. Anna isn't concerned about the invoice total - just wants a receipt for what she paid today. Let's make a button for that: but since she would probably be in Contacts instead of the main part of MS Outlook how would we get to that for the email.
- - -
Yes, I'm going to cover sending email using Access in a future lesson. I'm actually going to show you how to generate your own HTML file that can be sent directly using Outlook.
Q: in a ComboBox is it possible after the item is dropped down to enlarge it? For example: the combo box is, say, 20 characters wide, but because of how it is placed on the form it only shows 10 characters. Can we enlarge it to show the full 20 characters in VB code or some other way. An example would be: Item = 101 Keyboard (that's 12 characters). Item = 101 Keyboard Wireless (that's 20 characters).
- - -
A: Good question. It is possible to change the LIST WIDTH and COLUMN WIDTHS properties to do this - no VB required. The size of the combo box when it's CLOSED is based on your design-view resizing of it (click and drag) but if you adjust those two properties you change the shape of the box when it's OPENED.
Question: Dynamic SQL in AC311.
Rick: How about doing a new video with with Date Ranges in them and a prompt, ie [Enter Start Date] and [Enter End Date] of the sales you wanted to look up? After that then on to the Sort: Combo box? I got it to work with what I'm doing, but then I want to look up something else besides (in my own database I have departments, etc that I may want to show).
- - -
A: I showed you how to create those parameter prompts way back in Access 102 (I think). As far as sorting a combo box, we also covered that - either make a sorted query for the box, or write your own SQL statement:
SELECT * FROM MyTableT ORDER BY Fieldname