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].[AuthorFullName] "
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: [email protected]
Cc: [email protected]
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 <[email protected]> wrote:
From: L Tumbulu <[email protected]>
Subject: Re: [ms_access] Sum formula not working in the Control Source
To: [email protected]
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...@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: 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]
