Course List - Access 202

Student Interaction: Microsoft Access 202

Richard on 1/1/2007: More command buttons, setting values across forms, sorted combo boxes, form/subforms, and more. 74 Minutes.

Richard Rost on 1/1/2007: ATTENTION ACCESS 2007 USERS: There is a BUG in the original version of Access which causes the command button to NOT work. See this post in my blog for details.

Richard on 1/2/2007: NOTE: If you add a new customer and IMMEDIATELY try to add a contact for him without first saving the customer record (moving off of it, or closing the customer form) you will find that the customer doesn't show up in the combo box on the contact form. This is because the customer record hasn't been saved to the table yet. You can fix this problem by creating a REFRESH button using the command button wizard: Form Operations > Refresh Form Data. You just have to click on this button before trying to make a contact. In my Access 300+ Advanced series when we get into programming, I'll teach you how you can add the refresh code (Me.Refresh) to the VBA of your OPEN CONTACTS button and do all of this in one step.

Josiah�Whitman on 11/19/2007: pls. disregard the last question as you have subsequently answered it. Basic question now: I have long since lost my Access CD and the subform lesson in 202 requires it. I need the CD but also have three additional computers in my company that don't currently have Access. Is there an enterprise license for Access or what do your recommend. Thanks in advance for your advice. JW

Richard Rost on 11/21/2007: Access might want to install the subform wizard by prompting you to insert your Access CD, but you really don't the subform wizard. Just drop a subform on your form and set the Master and Child properties to link the subform to your main form. However, to answer your question, there is an Access Runtime edition available if you only need to USE an Access database (and not design with it). It depends on what version of Access you have.

nigel�kearton on 2/10/2008: Richard, I'm trying to set a default value (a name)on a form which is taken from a combo box on another form. However, all I can get is the Unique ID, not the name. Can You help? Thanks,Nigel.

Richard Rost on 2/28/2008: If you just read the value of the combo box, you're getting column zero which is PROBABLY your ID field. If you need to get the name out of the second column, access it like this =ComboBoxName.Column(1) Remember the first column is column(0). We cover this in Access 302.

Judith�Koester on 4/18/2008: Where deo you get the full name of the box? It appears on the video that you click on Input Mask and Forms!CustomerF!CustomerID pops up. That doesn't happen for me.

Glyn�Hope on 4/19/2008: Reall can't get on with your new media player as it has no minimise button. Is there any way I can still access the videos through my normal media programme/

Richard Rost on 4/21/2008: Judith, I understand your confusion. This happens around time index 2:48. What you're seeing has nothing to do with the Input Mask field. That is a little screen tip that I placed in the video using my recording software. It just so happens that my cursor is blinking in the Input Mask property box. Instead of using NOTEPAD to illustrate the name, I just popped it on the screen using a "Screen Tip" from my video recording software. You just have to know the notation... Forms!FormName!FieldName. Now, you could use the Expression Builder to figure out the form name, but remembering the notation is just so much easier.

Richard Rost on 4/21/2008: You're correct. The new video player does not have a Minimize button. I should probably add one, eh? Yes, you can still access your videos the old fashioned way. If you click on the "EXTERNAL" checkbox in the player, the videos will open up in Windows Media Player, and you can resize, minimize, move them as you see fit. Or, you can always load up Windows Explorer or open My Computer and run the videos manually by going to your C:\Program Files\599CD\ folder.

Linda�Gatlin on 4/29/2008: Hi Richard, I need to have a data entry form that allows me to type in an employee's name/SSN and populate fields from the EmployeeT but store the data in TrainingT. I have approx 15,000 employees so a drop down list isn't practical and I want to keep my tables clean. Thanks, Linda Gatlin

Richard Rost on 5/24/2008: Linda, the best thing to do would be to use a DLOOKUP statement then. This is a VBA function that requires a little bit of programming. It would allow you to type in the SSN, for example, and have everything else populated automagically. Now, I cover the DLOOKUP function in depth in my Access 302 class. There is also a free mini-video on DLOOKUP in the Tips and Tricks section if you want to check that out first. Hope this helps.

Anthony on 6/25/2008: I set up a form like you show in 202 that automatically displays a table of info that is related to the record that is open on another form (like your contact form in relation to the customer form). My question is, what if there is no related record (i.e. a record in ContactsT does not exist for the current customer). If a contact doesn't exist yet, I'd like it to begin a new contact record for that customer. Instead it seems to bug out a little and just show any customer's contact record.

Richard Rost on 6/26/2008: Anthony, it shouldn't "bug out" at all. If you click on a customer who has no contact records, you should just see a blank form.

Isaac�Moses on 6/29/2008: When you delete a combo box do you delete all information you entered or automatically entered like SQL for this combo box or does that information reside in the database as garbage data?

Richard Rost on 7/1/2008: Isaac, when you delete a combo box, any SQL statement stored in the box's Control Source is deleted. Now, as with almost everything in computers, is that information REALLY deleted? Well, it's marked as DELETED, but as with files I'm sure a super-geek could resurrect it somehow. For the average user, however, it's gone.

Tonya on 4/2/2009: Is it possible to disable the ability to add a record to the subform?

Richard Rost on 4/2/2009: Tonya, you can set the AllowAdditions property of the subform to NO.

ADP on 7/13/2009: I have a subform on a main form. The subform has a button to open an external form. I would like to have a value from the subform be the default in the external form. I can not make this happen using the Default Value as shown in this lesson.

Richard Rost on 7/16/2009: Accessing the value of a field on a SUBFORM is tricky. Normally, getting a value off just one form is: =Forms!FormNameF!FieldName If you're dealing with a SUBFORM then it becomes: =Forms!FormNameF!SubFormName.Form!FieldName You can put this value in the Default Value property of your new form, OR it might be easier to go the other way. In the VBA code for the button that opens this new form, you might just want to set the value manually in the new form after opening it. DoCmd.OpenForm "NewFormNameF" Forms!NewFormNameF!FieldName = SomeValueOrField If the button is IN the subform of which you speak, then you wouldn't need to use any crazy notation. I cover this when we get into the 300-level series of Access courses and start learning VBA.

�Carolyn Quinn on 7/24/2009: Forms: Why does the fields in the forms automatically come in so long? I hate that. I want to resize them - i am sure i can do this somewhere in the properties - can you show me? Thanks

Richard Rost on 7/25/2009: Carolyn, I don't know of any way to change the default length of a new textbox when you add it to a form. Sorry! I'd love to know if you find it though.

David�Leech on 9/25/2009: Hmm is it just me or does Access 2003 not have the option to open a form and find specific data through the command button wizard... seems to skip that step

Richard Rost on 9/25/2009: David, it has that feature. Form Operations > Open Form. NEXT. Pick your form. NEXT. "Open the form and find specific data to display" on the next screen.

Ghassan�Baghdadi on 10/21/2009: Sir, I was trying so many times to open a form for a specific customer but it didn�t work with me. I don�t know what is the problem. I followed what you have said but nothing changed. So, what is your recommendation.

Richard Rost on 10/21/2009: Ghassan, I would need to know exactly what you're doing so I could tell you why it's not working. Are you following the lesson EXACTLY as it appears in the video? Are you using MY database or your own database? What version of Access are you using? Give me a step-by-step on exactly what you're doing. I need more information in order to help you.

Ghassan on 10/22/2009: Mr. Richard, 1-I was trying to make a contact button in the customer Form but it didn't work. Once I click on the button, filtration doesn't work. 2-Yes I am following what you are saying exactly in the lesson. 3-I am not using your database because I don't have it. I searched for it but I think it didn't download when I downloaded the lessons. 4-I am using Access 2007. Is there any difference between what you are using and 2007? 5-Step by Step ( I am doing now HR Database) -I made 2 forms (Employee form) and (Attendance Form) -I need to connect the Attendance Form with a specific Employee like what you did between (Customer and Contact) -I have opened my Employee Form in Design View and make a button (Form Operation > Open Form > EmployeeF> Pick Open the form and find a specific data to display> ID <> ID and then Finish. Save and open the form, click on the button I did, but didn't work. -Also, Sometimes, I got empty table in the Matching Field in the Left Hand Side. I dont Know Why? That What I was doing and Thank you for your great teaching. Ghassan

Richard Rost on 10/24/2009: Ghassan, it turns out that this is a bug in Access. I just posted about it in my blog here.

Judith�Koester on 12/16/2009: I am trying to make a combo box with state abbreviations. I get a message that "no more than selected". I created a table with the 50 state abbreviations. That just doesn't sound right to me. Is this an evil thing of 2007?, or what am I doing wrong? Seems pretty straight forward to me.

Judith�Koester on 12/16/2009: After trying the state abbreviations combo box about 20 times and asking for your help, I ditched the table and re-did it. My combo is working OK now. Sorry, but am not sure what was going on.

Richard Rost on 12/20/2009: Glad you got it working. That's always my 2nd rule of thumb: delete the object and re-create it. The 1st rule is to reboot your PC. If that doesn't work, recreate it.

Add Your Questions or Comments