Query not working with Lookup Wizard?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Lookup Wizard to drop down a number list for inventory. And when I
go to sum the part no inventory in the query it adds the autonumber instead
of the real number you see on the table.

Any advice would be great.

Thanks
 
I am using Lookup Wizard to drop down a number list for inventory. And when I
go to sum the part no inventory in the query it adds the autonumber instead
of the real number you see on the table.

Any advice would be great.
Click to expand...

My advice would be... don't use the Lookup Wizard, ever.

It conceals the actual contents of your table (the autonumber) behind
the "looked up" value, and causes exactly this kind of confusion.

If you want to total the "real number" you will need to create a Query
joining your main table to the lookup table, and sum the number from
the lookup table. Note that you can do this perfectly well even
*without* using Microsoft's very limited and confusing Lookup feature.
It's quite easy to put a Combo Box onto a form - I admit it's one or
two mouseclicks easier if you've defined it as a lookup field, but
AFAIK that's the only benefit of using a Lookup Field.

John W. Vinson[MVP]
 
Thanks for responding John,

I am little confused, do you mean that I can write an expression in a query
to get a drop down list to appear on my main table. And if so do I use the
DLookup function, I see others talking about it. How do I use it?

Thanks
 
Thanks for responding John,

I am little confused, do you mean that I can write an expression in a query
to get a drop down list to appear on my main table. And if so do I use the
DLookup function, I see others talking about it. How do I use it?
Click to expand...

You should not be opening the Table datasheet for ANY purpose other
than debugging and design. Table datasheets are of *very* limited
functionality, and most developers keep them concealed from user view
entirely. Use a Form based on your table instead!

The Lookup Wizard makes it easier to use table datasheets... but at a
cost most serious Access folks consider too high. See

http://www.mvps.org/access/lookupfields.htm

for a critique.

The DLookUp function is often useful, but it is NOT what I was
suggesting. Instead, create a Query in the query design window. Add
your main table; add the Lookup Table which the lookup wizard created
for you. They'll both be in the tables area at the top of the query
design window, joined by a join line on the ID field. You can now
select the text field from the lookup table, and the other fields from
your main table, and sort by, search by, sum, display or otherwise use
all of the fields - INCLUDING the lookup field. The Lookup Wizard
isn't clever enough to do this, but you can easily do it yourself
without help from that inept entity! <g>

John W. Vinson[MVP]
 
I made a combo box on the form and it is working like I want. Thanks for your
input.

But say I wanted to add a field to the table later on. Can I do that from
the form view, or will I have to add it at the table and then redo my entire
form?

Thanks for your response

Dustin
 
I made a combo box on the form and it is working like I want. Thanks for your
input.

But say I wanted to add a field to the table later on. Can I do that from
the form view, or will I have to add it at the table and then redo my entire
form?
Click to expand...

The latter. That's why it's usually prudent to carefully think out
your tables' requirements prior to building the application, and
include all of the fields that you're likely to need.

That said, it's not all that big a deal to add a new field to a Form's
Recordsource and create a new control on the form.

John W. Vinson[MVP]
 
Back
Top