Course List - Access 30x

Student Interaction: Microsoft Access 307

Richard on 1/1/2007: Custom search button, DoCmd.FindRecord, GoToControl, InputBox, BeforeUpdate, Requery, DB Password, Splitter

�Moheb on 6/18/2008: Want to email record which will be in report form but a single record. I tried it via macro but it sends all the records.

Richard Rost on 6/26/2008: Moheb, you need to limit the results with a Query, or use the CRITERIA in the DoCmd.OpenReport method.

Nathan�Wittmann on 9/8/2008: I am struggling with VBA a little. I can follow examples, but I am unable to do somethings that seem simple to me. In the past with Excel VBA I would use the macro recorder to give me the code that I need to do specific tasks. I am unable to do that here. For an example I am trying to have the Data settings changed on a form with a button click. I want to have the record change from allow edits "no" to allow edit "yes". I have tried to use the object browser with no luck. It doesnt seem logical to me. Anyways I would appreciate an example to change different settings on a form or text box ect. But I would appreciate even more is a method that is as effective as recording a task and then using the code like I used to do in Excel VBA. Thanks Nathan

Richard Rost on 9/9/2008: Nathan, Access really doesn't have a macro recorder like you're used to with Excel and Word. I show throughout the Access series different ways to change various properties. For example, in Access 202 I showed you how to change the Visible property of a text box. Well, to change the Allow Edits property of a form, all you have to do is say: Me.AllowEdits=FALSE. You can do this at run time in pretty much any event.

Nathan�Wittmann on 10/20/2008: First of all thanks for all your help so far. I am struggling with some basic things that I used to do in Excel VBA. I want to be able to validate information by searching for a duplicate entry. So to give you an example of what I have setup is: I have a projectdetailID, ProjectID, ItemID. I adding components to a project using ProjectDetailsID. What I don't want is the user to add the same ItemID twice. So I need an error when I have the user attempt to duplicate the same ItemID twice. Example first entry is ProjectDetailsID = 1, PrjoectID = 1, ItemID = 35 second entry is ProjectDetailsID = 1, ProjectID = 1, ItemID = 36 Third entry is ProjectDetailsID = 1, ProjectID = 1, and then user duplicates by selecting ItemID = 35 again. The way I used to do this was search and if the search came back true then I would do an If statement that would error if it was duplicated. I just am lost on how I would do it now. I am using a query so I assume I would need to search qry for that field an if it duplicates it msgbox an error. Any help would be appreciated. I have decided that I need to take a VBA class or something. I was able to get by in Excel VBA, but everything in access has been more advanced them I am used to.

Denise�Nichols on 10/24/2008: I especially like how you show us how to troubleshoot a problem. That is worth a ton.

Richard Rost on 10/25/2008: Nathan, just use a BeforeUpdate event and DLOOKUP to see if that ItemID exists in the table already. If so, just issue a "1" for the Cancel property and optionally pop a warning message up. I cover BeforeUpdate and cancelling an event in Access 305. DLOOKUP was covered back in Access 302.

Richard Rost on 10/25/2008: Thank you, Denise.

Paula on 4/8/2009: Richard, I've gone thru Access 307, Lessons 2 & 3 re. nested combo boxes. Do you have a lesson on how to used 2 or 3 combo boxes in multiple rows . . . (cboBox1) (cboBox2) (cboBox3) BudgetCategory Descript ExpenseType 1. 2. 3. 4. 5. Thanks!

Richard Rost on 4/8/2009: Paula, I'm sorry but I don't understand your question. Can you please elaborate and give me an example?

Paula on 4/9/2009: Sorry, I'll try again. I was unable to paste a small screenshot here as an example, so here's a simple different one. Say I have a subform to record salespeople's trips to various vendors around the country each month. Some of the fields are: Date cboState cboCity cboVendor cboMerchandise 03/05/09 NY NYC A Taylor Co dresses 03/10/09 FL Miami Turen Inc jewelry 03/25/09 CA Fresno ABC Inc blouses The subform visually looks like an Excel spreadsheet with multiple rows/records for each month. The nested combo boxes work fine for the first row. Beginning with the second row, the cboCity (whose selections are based on the cboState in ROW 2) gives this error message: Forms!sfrmSalesTrips!cboState Can you provide some help here? Thank you so much!

Richard Rost on 4/9/2009: Paula, I understand your problem now. I cover this in Access 310. If you need to refer to a field on a FORM it's: Forms!FormName!FieldName If you need to refer to a field on a SUBFORM, it's: Forms!FormName!SubformName.Form!FieldName It's confusing. I know. If, for example, you need to get the OrderTotal field off of your OrderSubform which is a subform of the OrderF form, you'd say: Forms!OrderF!OrderSubform.Form!OrderTotal Hope this helps. Again, Access 310 explains this in more detail.

Paula on 4/13/2009: Thanks Richard for trying to help. I have not yet been successful in getting 3 nested combo boxes to work on a continuous form. Here's what I've tried: 1) I tried the code you suggested, substituting my form/subform/field names: Forms!FormName!SubformName.Form!FieldName If ComboBox A is based on straightforward data with no criteria (ie States table), and ComboBox B (cities) is based on ComboBox A(states), when I enter data in Row 2 it doesn�t seem to know that I�m now in Row 2 on the subform. I get the same error as before "Enter Parameter Value" box with Forms!frmName!sfrmName.Form!cboField In the Parameter Box that appears for ComboBox B, if I enter the ID value that is the IDcriteria for the ComboBox A in the underlying query ... such as 1, 2, 3 etc. ... then the proper choices appear in ComboBox B. This carries through with ComboBox C which is built on ComboBox B. 2) I also went thru your Access Lesson 310. In TrackingPayments1, I found a reference to getting values on a main form that are based on a field in a subform. I've done this before. However, my current dilemma is different. My combo boxes are all on the subform, and they reference SQL code or an actual query. The combo boxes or underlying data don't have anything to do with the main form. I�d be really grateful if you can figure this out. Thanks so much for your time!

Richard Rost on 4/14/2009: Paula, I think I have a solution for you. I posted it as an update at the bottom of this page: http://599cd.com/tips/access/two-combo-boxes

ian�dudley on 5/6/2009: Hi Richard, Re: Dynamic Combo Boxes I have a question relating to dynamic combo boxes (just included in my own CRM database using your lesson guide 307). Although the filtering works correctly and displays in the dependent combo box the data will not save to its table/record and disappears altogether if I close the form and revisit the same record. Any ideas why the data is not being saved? Thanks, ian

Richard Rost on 5/7/2009: Ian, the only way the data wouldn't be saved is if your combo boxes aren't BOUND to the underlying table. Check the Control Source properties. Also, you might need to issue a ComboBoxName.Requery in the OnCurrent event of the form in order for them (especially the dependent one) to have the right set of data in them.

Paula on 5/12/2009: Hello Richard, The nested combo boxes work GREAT! I followed the info at http://599cd.com/tips/access/two-combo-boxes/ It worked fine. But then I went back and edited some of the data in the first combo box, and the selections in the remaining combo boxes would not change. I needed to add the AfterUpdate refresh command. I have 4 nested combo boxes on my form. IT WORKS WONDERFULLY! MANY THANKS!

Lorraine McCord on 5/14/2009: Hi Richard, I have taken many of your courses and currently viewing Access 307, but it is not quite answering my question. I have a form like this: Phone Numbers------------------------ Business 800-555-1212 Business 2 Business Fax 800-555-1213 Home Mobile 832-888-1212 Phone type (Business, Business 2�) needs to be glued to the phone number next to it. PhoneType is a combo box. I have a subform called frmPhoneNumbers, which has five fields�CompanyID, PhoneNumberID, PhoneType, PhoneNumber, and PhoneExtension. If I choose a different PhoneType, I want the form to display the correct phone number or a blank if there is no phone number�such as, Business Fax will show a different phone number than the Business phone number or Home phone number. I tried doing the DLookup, didn�t work and now I am lost. I tried to use the combo box feature in Access 2007 forms. That is the: Find a record on my form based on the value I selected in my combo box but it is not working. Has this specific question been covered in any of your classes? Any ideas on how I should set this up properly? Thanks!

Richard Rost on 5/18/2009: Paula, I'm glad it works for you. Yes, you'll need to manually requery the combo boxes if you add records.

Richard Rost on 5/20/2009: Lorraine, I don't quite understand what you're trying to do. Are you saying that the customer record already has the different phone numbers and you want the user to pick a phone number type (business, fax, etc.) from a combo box and have that number displayed? Yes, a DLOOKUP in an AfterUpdate event for the combo box should do that. I'm curious as to why you're setting the database up this way (instead of just showing all of the numbers in a subform).

�Jay on 6/13/2009: Hi, you refer to a class which will go into greater detail on database security and workgroups. Have you completed this course yet?

Richard Rost on 6/13/2009: Jay, I have not completed it, and I might not even bother. Reason: Microsoft removed user-level security from Access in version 2007. User-level security in Access isn't very secure at all (see my blog post about it). If there are enough people interested, I might cover security in Access 2003, but if you need REAL security, you might want to consider upsizing your backend database to SQL Server.

Cheri�Parrag on 1/5/2010: Richard, in which lesson did you discuss creating that main menu with all of the task buttons? Thanks.

Richard Rost on 1/5/2010: Tasks are covered in 206 and 207. You can find the complete list of topics on the Access Course Index.

Add Your Questions or Comments