Re: subform issue

okay, well, a couple things. first of all

In one of the tables (orders), there is a city code that is NOT part
of the form but should automatically update when the new record is
created.
It's not updating that field. I made it a lookup field

let's stop here. if you are saying that you have a Lookup field in
tblOrders - in the table itself, not a combobox control in a form - then i'd
recommend you stop immediately and get rid of that Lookup in the table. back
up your database (or at least back up tblOrders); then open tblOrders in
Design view, go to the city code field, click the Lookup tab in Field
Properties, and change the Display Control setting to TextBox. save and
close the table. Lookup fields in tables are bad news; for more information,
see http://www.mvps.org/access/lookupfields.htm.

second, i assume that each order in tblOrders is linked to a specific
customer. and if i understood you correctly, each customer is linked to a
specific city code. if that's correct, what is your business reason for
storing a city code in each order record, when orders and city codes are
already indirectly linked via customers?

hth


"Patttt" <Patttt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3E254809-389A-4A32-8543-567CF34173E6@xxxxxxxxxxxxxxxx
Me too! However, now I have a new issue (of course). When one enters a new
order using the form we've been working on, the data populates a couple of
tables. In one of the tables (orders), there is a city code that is NOT
part
of the form but should automatically update when the new record is
created.
It's not updating that field. I made it a lookup field that has it
identify
the city code based on the customer name in the customer table (they're
related tables) but it's still not updating. Can the table be designed to
enter the info on its own? Thanks again for any help.

"tina" wrote:

you're welcome, glad it worked for you! :)


"Patttt" <Patttt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D6AD3B1C-7C42-49CA-A1EC-B6CB7224794A@xxxxxxxxxxxxxxxx
Hi Tina,
I just wanted to let you know that your expertise was a terrific help!
I
fixed the problem - everything is running smoothly. You're the best!
Pat


"tina" wrote:

well, in the subform, i'd get rid of the two-table query. bind the
subform
to tblOrderDetails, or to a query that includes only
tblOrderDetails.
make
sure the subform is linked to the mainform, by setting the
LinkChildFields
property to OrderID (which refers to the OrderID foreign key field
in
tblOrderDetails) and setting the LinkMasterFields property to
OrderID
(which
refers to the OrderID primary key field in tblOrders).

if you want to see/choose products by name, in the OrderDetails
subform,
then add a combobox control in the subform, with the ControlSource
set
to
the ProductID field in tblOrderDetails, and the RowSource set to a
SQL
statement based on tblProducts, something like

SELECT ProductID, ProductName FROM tblProducts ORDER BY ProductName;

set the combobox control's BoundColumn to 1 (the default), the
ColumnCount
to 2, and the ColumnWidths to 0";2" so the ProductID will be
correctly
stored in the ProductID field in tblOrderDetails, but the combobox
control
in the subform with *display* the product name, not the id.

hth


"Patttt" <Patttt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0EB51FEA-911F-4F0B-A035-845ED84D4A0B@xxxxxxxxxxxxxxxx
The subform is based on a query that does contain my data when I
open
the
query. It's based on 2 tables that have primary key/ foreign key
relationships. The query contains a field called "product id" that
is
the
primary key one of the tables. However, an early designer added
some
code
that replaces the product id number with the name of the product
(another
table that is also related). I had it working once in the subform,
but
it
only displayed the product id code - not the product name. Now, I
don't
have
any entries in the subform at all. To answer your questions:
1. the mainform is bound to the Orders table
2. Order Id is the primary key of Orders
3. The subform is bound to a query based on Orders and Order
Details
tables.
the tables are related on the Order ID field.
4. Order ID from Order is part of the query (not displayed in the
subform).
Order ID is part of the main form and displayed on the main form.

Thanks so much for your help! For some reason, the query DOES
display
the
records, but the subform doesn't AND we can't add anything new via
the
query
or the subform. Pat


"tina" wrote:

you need to go back and look at the tables/relationships. what
table
is
bound to the mainform? what is that table's primary key field?
what
table is
bound to the subform? does that table contain a foreign key
field
that
links
back to the primary key field of the first table? if yes, is
that
foreign
key field included in the RecordSource of the subform? is the
primary
key
field of the first table included in the RecordSource of the
mainform?

hth


"Patttt" <Patttt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:53FCC2A7-3347-46FE-B756-621215A92E34@xxxxxxxxxxxxxxxx
nope....that didn't fix it. I did notice, though, that I have
an
error
message on the status bar that says "join key of table Order
Details
not
in
record set" when I try to add a new record. I don't have any
idea
where to
go
to fix it!

"Patttt" wrote:

"Allow Additions" says yes, although "Data Entry" says no.
I'll
try
changing
that one. thanks!

"Brian" wrote:

Check your subform's AllowAdditions property. Did it get
set
to
False
somehow?

"Patttt" wrote:

Is there a reason why a subform won't let you enter new
data? I
can
enter new
data in the main form section just fine. The subform
does
have
the
dropdown
list that displays the correct choices, but when I click
on
one
of
them,
nothing is entered in the field. Essentially, the
subform
only
displays the
existing data but won't let you enter new records. The
tables
ARE
related, so
it's not a relationship issue. any thoughts? Thanks for
your
help!
Pat











.

Relevant Pages

  • Re: subform issue
    ... I'd already learned about the lookup field the hard way. ... a query for him that tells him how much he sold by city codes using the dates ... Design view, go to the city code field, click the Lookup tab in Field ... ProductName FROM tblProducts ORDER BY ProductName; ...
    (microsoft.public.access.tablesdbdesign)
  • Re: displaying additional data that correlates to my query
    ... One approach would be to take the result of your GROUP BY query and do an ... the same ProductName have the same minimum TargetDifference. ... SELECT [Flux Density Query].ProductName,MIN([Flux Density ...
    (microsoft.public.access.queries)
  • Re: Limit field choices based on another field
    ... I'm not making sense of the SQL statement. ... ProductName ... Allen Browne - Microsoft MVP. ... > created the stored query: ...
    (microsoft.public.access.formscoding)
  • displaying additional data that correlates to my query
    ... ProductName, Voltage, Current, Peak, TargetDifference ... I have created an additional query to retrieve the Peak that is closest to ...
    (microsoft.public.access.queries)
  • RE: QUERY DESIGN HELP
    ... which custId, productName, categoryName the customer wants to be listed under. ... At the moment I have a query that pulls all this info together. ...
    (microsoft.public.access.queries)