Re: Filling form based on combo box and query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

this is actually a new question, same topic. However, for some reason, only
the Reply, not the New Question button is responding!

This should be so easy. I tried to use the Combo box wizard to find a
record on Form2 based on the value in the box. Done that a zillion times.
However, I get an error msg I've never seen before: Data cannot be retrieved
from the source you've selected. You must select a different table or query
to continue in the wizard.

Is it possible I'm getting this because of the way I'm setting the record
source? Form1 has a list box with 4 options. The afterUpdate opens Form2;
Form2 is based on a query that looks back to Form1 for the criteria:
[Forms]![Form1]![lstFourItems].

So anyway, I decided to buid it from scratch. I used the Query Builder to
set the Row Source to SELECT AllItems.ItemID, AllItems.ItemDescription,
AllItems.[System No], AllItems.Discipline
FROM AllItems
WHERE (((AllItems.Discipline)=Forms!Form1!lstFourItems))
ORDER BY AllItems.ItemID, AllItems.[System No];

I also copied the code in the AfterUpdate event from another box on another
form that works; made all the name changes, etc. Problem is, although the
information in the fields on Form2 update, the record number at the bottom
indicates I'm still on record one, and when I leave the field I get a primary
key violation.

I've never had trouble before; I do suspect it is the way I am setting the
data source for the form.

thanks for sharing your knowledge!



ItemID is a primary key. I tried setting the Bound Column in the property
*** to 1 and then to 0 (I didn't know it was zero based until perusing this
discussion group!)

--
Monica K. Murphy


"John Vinson" wrote:

On Wed, 10 Jan 2007 11:26:00 -0800, dawnecia
<dawnecia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have a combo box that looks up the customer number, with the customer
number the customer name, address, phone, and other contact information will
prefill. I am using
me.customer = cboCustomerNo.column(1)
me.customerAdd = cboCustomerNo.column(2)
me.customerCity = cboCustomerNo.column(3)
etc

It is pulling the customer names but not the other fields. Any suggestions?

If you're attempting to store the customer name, address and city
redundantly in a second table, be aware that you probably DON'T want
to do so! If you just want to *see* them, you can instead set the
control source of textboxes on the form; e.g. for Customer you could
use

=cboCustomerNo.Column(1)

As for why the data isn't showing up, a couple of things to check:

- Is the combo's ColumnCount property equal to 4 (or more)?
- Does its Rowsource property consist of a query which returns all the
desired fields?
- Are you using (1) to refer to the second column of the combo, (2)
for the third etc. (since it's zero based)?

John W. Vinson[MVP]

.

Quantcast