For the books database I will try the coding you sent me What I would like to achieve is have the one combo list box for the author, the other one for the books the second combo box do if the second one would only display the books associated with that author.
I know it requires coding, and I find it more professional then a subform because on a subform their is no way to remove the gray grid lines to turn off the borders like you do in a spreadsheet. I will try the coding after the combo box is updated For the products and vendors, the coding you send me does that go in the vba coding or the afterupdate for the vendors list box is that correct? Thanks, stSQL = "SELECT [Authors].[AuthorID ], [Authors].[AuthorFu llName] " If Not IsNull(Me.cmbAuthor ) Then stSQL = stSQL & "WHERE [Authors].[AuthorID ] = " & Me.cboAuthorID ' assuming AuthorID is numeric, otherwise the statement should have been "WHERE [Authors].[AuthorID ] = '" & Me.cboAuthorID & "'" End If Me.lstBooks. RowSource = stSQL 'lstBooks is the name of the listbox that displays all the books by the selected author. --- On Wed, 4/8/09, L Tumbulu <[email protected]> wrote: From: L Tumbulu <[email protected]> Subject: Re: [ms_access] Sum formula not working in the Control Source To: [email protected] Date: Wednesday, April 8, 2009, 6:35 AM I think the SQL statement should be "SELECT ProductName FROM Products WHERE VendorID = " & Me.Vendor & " ORDER BY ProductName; " (Please note the use and positions of the quotes) In the above SQL statement I assume that VendorID is numeric. I still don't understand the significance of Me.Products= Me.Products. ItemData( 0). What do you want to achieve by this statement? As for your Book database have you considered using a main form and a subform? In this scenario you should have a table of authors and another table of books. Create a form that shows authors and their books (this should come from a query that pulls data from the two tables). Then create another form on which you create a combo box whose row source is the Authors table. Drag the first form onto this form. Set Link Master Fields and Link Child Fields to the AuthorID. When you select the author on the main form the subform will display all the books by that author. No coding is required. Is this what you want to achieve? If you want to use a list box instead of a subform you may consider using VBA as follows (after the combo box on the main form is updated) stSQL = "SELECT [Authors].[AuthorID ], [Authors].[AuthorFu llName] " If Not IsNull(Me.cmbAuthor ) Then stSQL = stSQL & "WHERE [Authors].[AuthorID ] = " & Me.cboAuthorID ' assuming AuthorID is numeric, otherwise the statement should have been "WHERE [Authors].[AuthorID ] = '" & Me.cboAuthorID & "'" End If Me.lstBooks. RowSource = stSQL 'lstBooks is the name of the listbox that displays all the books by the selected author. HTH Liveson ----- Original Message ----- From: Michael Van Der Stad To: ms_acc...@yahoogrou ps.com Cc: tumb...@sdvmalawi. com Sent: Tuesday, 07 April, 2009 12:14 PM Subject: Re: [ms_access] Sum formula not working in the Control Source can you please give m that example in the coding. Me.Products. RowSource = "SELECT ProductName FROM" & _ " Products WHERE VendorID = " & Me.Vendor & _ " ORDER BY ProductName" Me.Products = Me.Products. ItemData( 0)Can you please let me know if this is correct Also I have another database, for books that when I select the author, I want the book to populate the list box only associated with the authors. I hear that Infopath is alot easier on these things Also in the form it's only dispaying 6 records not all 100 Even when I populate one list box to the subform I still have trouble What is a cascading list box? A cascading list box is a list box with choices that change based on the value that a user selects in another list box. For example, if a user clicks Condiments in the Categories box shown in the following illustration, the Products box will display a list of condiments. The following sections show you how to design a form template with two list boxes, where the second list box is filtered based on the value a user selects from the first list box. When the value in the first list box changes, the filter is automatically applied to the second list box, which changes its values. Compatibility considerations Filters can be used only in form templates that are designed to be filled out in InfoPath. Filters are not available in browser-compatible form templates (browser-compatible form template: A form template that is designed in InfoPath by using a specific compatibility mode. A browser-compatible form template can be browser-enabled when it is published to a server running InfoPath Forms Services.). Top of Page Step 1: Insert a cascading list box To create a cascading list box, you must insert two list boxes in your form template. Note When you insert a cascading list box in your form template, ensure that the two list boxes are not in a repeating section (repeating section: A control on a form that contains other controls and that repeats as needed. Users can insert multiple sections when filling out the form.) or repeating table (repeating table: A control on a form that contains other controls in a table format and that repeats as needed. Users can insert multiple rows when filling out the form.). If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.Under Insert controls in the Controls task pane, click Drop-Down List Box. When a user fills out a form that is based on your form template, the selection in this drop-down list box will determine the choices that are available in the second list box.Click List Box. Your form template should now contain a drop-down list box and a standard list box. Double-click the drop-down list box that you inserted in your form template in step 2.In the Drop-Down List Box Properties dialog box, click the Data tab.In the Field name box, type listBox1, and then click OK.Double-click the list box that you inserted in your form template in step 3. In the List Box Properties dialog box, click the Data tab.In the Field name box, type listBox2Cascade, and then click OK. http://office. microsoft. com/en-us/ infopath/ HA102352251033. aspx --- On Mon, 4/6/09, L Tumbulu <l.tumb...@sdvmalawi .com> wrote: From: L Tumbulu <l.tumb...@sdvmalawi .com> Subject: Re: [ms_access] Sum formula not working in the Control Source To: ms_acc...@yahoogrou ps.com Date: Monday, April 6, 2009, 9:43 AM I don't seem to understand why you have Me.Products= Me.Products. ItemData( 0) at the end of the SQL statement. Is this part of the criteria? In addition, if Categories is a text then you need to enclose it in single quotes like "WHERE VendorID = '" & Me.Categories & "'". Please note that after the equals sign there is a single quote (') and then double quotes (") and after Categories there is the ampersand (&), double quotes ("), single quote and then double quotes again. HTH Liveson ----- Original Message ----- From: Michael Van Der Stad To: ms_acc...@yahoogrou ps.com Sent: Sunday, 05 April, 2009 2:48 AM Subject: Re: [ms_access] Sum formula not working in the Control Source I am having some trouble to just to populate two list boxes vendors and products I generate the code, the computer don't like it. Me.Products. RowSource = "SELECT ProductName FROM" "Products WHERE VendorID= " & Me.Categories _ "ORDER BY ProductName" Me.Products = Me.Products. ItemData (0) can u please help me thanks Mikevds --- On Fri, 4/3/09, L Tumbulu <l.tumb...@sdvmalaw i .com> wrote: From: L Tumbulu <l.tumb...@sdvmalaw i .com> Subject: Re: [ms_access] Sum formula not working in the Control Source To: ms_acc...@yahoogrou ps.com Date: Friday, April 3, 2009, 3:29 PM Dawn You seem to be getting quite weird problems with your database. If the formula is in the control footer or the report footer and the control source is valid the formula should work i.e. If Num_Unit_Facility_ 01 is a valid control source (check the underlying table) and the formula is placed in the Num_Unit_Facility_ 01 footer you should get the correct answer unless there is something wrong with your Access. Looking at your question again you say that you have created the formula in the Control Source of your Sum_Num_Unit_ Facility_ 01 field. This is not clear at least to me. You place the formula in the control's footer on the report and not in the Control Source. Perhaps I don't understand the question. Regards Liveson ----- Original Message ----- From: dawnmahc To: ms_acc...@yahoogrou ps.com Sent: Thursday, 02 April, 2009 19:55 PM Subject: [ms_access] Sum formula not working in the Control Source Hello everyone, I'm pretty new to MS Access. I've created the following formula in the Control Source of my Sum_Num_Unit_ Facility_ 01 field: =Sum([Num_Unit_ Facility_ 01]) I'm getting the #Error message. I cannot figure out why this would be happening. It seems like this would be the easiest formula in the world. Can someone please help me? Thank you in advance. Dawn [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
