Hi JA,
I certainly hope you didn't have to pay too much for that support engineer's
help. The newsgroups are free, and I'd like to believe that we provide high
quality support. (The samples I pointed you to all include the optional
WhereCondition statement in a DoCmd.OpenReport statement).
There are two ways of solving your last issue. The easiest way involves
adding one word to the Structured Query Language (SQL) statement that serves
as the rowsource for your combo box. This word would be: DISTINCT. If the row
source looks like this:
Select [TableName].[FieldName] from [TableName] order by [FieldName]
where TableName is the name of the applicable table, and FieldName is the
name of the field that contains your PO number. In that case, you can simply
add this keyword as follows:
Select DISTINCT [TableName].[FieldName] from [TableName] order by [FieldName]
If the row source is based on a saved query, then open the query in design
view. Then click on View | SQL View. Insert the DISTINCT keyword as indicated
above, just after the SELECT keyword. Save your changes, close the query, and
test.
However, while the above method is the easiest, it is *not* the most
efficient, because it will result in an automatic table scan. A table scan
means that all records must be read. If you have a network wire that
separates your PC from the data file, then you'll want to design efficiently
to avoid table scans. In that case, you very like would want to create a
lookup table, with the unique PO Number in the new table either set as a
primary key, or indexed uniquely. You would then join this field to your
existing PO Number field in a one-to-many relationship. So, what you need in
this case is a new query that serves as the source of data for a Make-Table
query. Something like this (as before):
Select DISTINCT [TableName].[FieldName] from [TableName] order by [FieldName]
You would then save this query as a temporary object (you can delete it when
you are done). Create a Make-Table query that uses this new query as a source
of data. See the following link for help on Action Queries, of which a
Make-Table query is one type.
http://office.microsoft.com/en-us/access/CH063653171033.aspx
You want the third item listed: "Create a table from another table with a
query". Alternatively, you could create a new table by from scratch, and then
use an Append query to add records to it. The Append query would use the same
temporary query that includes the DISTINCT (or a GROUP BY) clause as it's
source of data.
Note that a Make-Table query will not add any indexes; you'll need to open
the resulting table in design view and index the PO Number field
appropriately.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
JAJansenJr said:
Obviously in my last message I should have edited out ""Tom Wickerath"
wrote:". Sorry for missing that.
I have one last issue I need to find a solution for. The combo box shows
the PO number for every record. There are a large number of records in the
table the combo box draws from. Multiple records have the same PO number.
I need to find a way to search the table record by record and build up a
second table of unique PO numbers. The combo box then needs to draw from the
table of unique PO numbers.
Is there a simple way to build this second table of unique PO numbers? Or
is there another way to supply the combo box with the list of unique PO
numbers.
Thanks for all the help!
