subform issue

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" wrote in message
news
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
news
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" wrote in message
...
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" wrote in message
...
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

yes, I'd already learned about the lookup field the hard way. It's now a
text box. The man who owns the database is a friend of my husband who owns a
brewery. He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table. I've already created
a query for him that tells him how much he sold by city codes using the dates
he choses based on the relationship between the Orders and Customers tables.
Since I did the original work for him, he's now coming up with a lot of
"issues" that used to work and now don't. Every time I think I'm finished, he
comes up with something new.......... It's been so long since I've worked
much with Access (last certification was XP) that I can't even remember if
it's possible to have a field automatically add data in a table like this.
I'm more than happy if you tell me it can't be done....... Thanks again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in message
...
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" wrote in message
...
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

good job re the demise of the Lookup field. and if that's the worst thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note: the fact
that the client "wants a field to show up in x table", is not a sound
business reason. you can't expect your client - in this case, a friend - to
know beans about relational design principles, so s/he doesn't get to call
the shots in structural design. no more than you would tell a builder how to
build the foundation of your new house - unless you're an experienced
builder yourself!) in this case, i can see a business reason for putting the
code in the Orders table: a customer may move, but that should not affect
reporting of *past* orders data, only open, unshipped orders and future
orders. especially from the tax angle - nothing worse than being audited
going back three years, and the taxes don't add up because some customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order record,
shouldn't be hard. there are a number of ways to set it up; exactly how you
do it depends on how the orders data entry form is set up. when entering a
new order in the form, do you use a combobox control to choose the customer?
if so, you should be able to include the city code field in the combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city code.
remember that combobox columns are zero-based, so the first column (left to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way. It's now
a
text box. The man who owns the database is a friend of my husband who owns
a
brewery. He has to pay taxes based on the locations of his customers, so
he
wants the location code to show up in the Orders table. I've already
created
a query for him that tells him how much he sold by city codes using the
dates
he choses based on the relationship between the Orders and Customers
tables.
Since I did the original work for him, he's now coming up with a lot of
"issues" that used to work and now don't. Every time I think I'm finished,
he
comes up with something new.......... It's been so long since I've worked
much with Access (last certification was XP) that I can't even remember if
it's possible to have a field automatically add data in a table like this.
I'm more than happy if you tell me it can't be done....... Thanks again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in message
...
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" wrote in message
...
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

Ha! No - it's certainly not the worst -- just the latest

I entered your line of code in the AfterUpdate Event box and did a test
entry, but it didn't update the city code in the orders table. The customer
combo box already has a row source that says "SELECT DISTINCTROW Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the city code
in the row source also? I didn't do that.

The owner said he started out with a template that he downloaded from the
microsoft site, if that helps any (it didn't for me - but you might be more
familiar with what's available). Once he started playing with the template,
his troubles started. He's not opposed to deleting the city code from the
orders table, but you make an excellent point about saving data in case of
tax audits, so I guess I'll keep trying to get this working! Thanks for
hanging in with me on this. Pat

"tina" wrote:

good job re the demise of the Lookup field. and if that's the worst thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note: the fact
that the client "wants a field to show up in x table", is not a sound
business reason. you can't expect your client - in this case, a friend - to
know beans about relational design principles, so s/he doesn't get to call
the shots in structural design. no more than you would tell a builder how to
build the foundation of your new house - unless you're an experienced
builder yourself!) in this case, i can see a business reason for putting the
code in the Orders table: a customer may move, but that should not affect
reporting of *past* orders data, only open, unshipped orders and future
orders. especially from the tax angle - nothing worse than being audited
going back three years, and the taxes don't add up because some customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order record,
shouldn't be hard. there are a number of ways to set it up; exactly how you
do it depends on how the orders data entry form is set up. when entering a
new order in the form, do you use a combobox control to choose the customer?
if so, you should be able to include the city code field in the combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city code.
remember that combobox columns are zero-based, so the first column (left to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way. It's now
a
text box. The man who owns the database is a friend of my husband who owns
a
brewery. He has to pay taxes based on the locations of his customers, so
he
wants the location code to show up in the Orders table. I've already
created
a query for him that tells him how much he sold by city codes using the
dates
he choses based on the relationship between the Orders and Customers
tables.
Since I did the original work for him, he's now coming up with a lot of
"issues" that used to work and now don't. Every time I think I'm finished,
he
comes up with something new.......... It's been so long since I've worked
much with Access (last certification was XP) that I can't even remember if
it's possible to have a field automatically add data in a table like this.
I'm more than happy if you tell me it can't be done....... Thanks again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in message
...
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" wrote in message
...
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

comments inline.

"Patttt" wrote in message
...
Ha! No - it's certainly not the worst -- just the latest

well, you're in good company, if that's any comfort!


I entered your line of code in the AfterUpdate Event box and did a test
entry, but it didn't update the city code in the orders table. The
customer
combo box already has a row source that says "SELECT DISTINCTROW
Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the city
code
in the row source also? I didn't do that.

well, yes. you can't set the value of the field in the Orders table, using
code that refers to the combobox control, unless the value is available in
the combobox's RowSource. add the city code field to the RowSource, set the
ColumnWidth for that column to zero, and refer to that column's Index value
in the code i posted.


The owner said he started out with a template that he downloaded from the
microsoft site, if that helps any (it didn't for me - but you might be
more
familiar with what's available). Once he started playing with the
template,
his troubles started. He's not opposed to deleting the city code from the
orders table, but you make an excellent point about saving data in case of
tax audits, so I guess I'll keep trying to get this working! Thanks for
hanging in with me on this. Pat

you're welcome. we'll get it figured out, don't worry.


"tina" wrote:

good job re the demise of the Lookup field. and if that's the worst
thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note: the
fact
that the client "wants a field to show up in x table", is not a sound
business reason. you can't expect your client - in this case, a friend -
to
know beans about relational design principles, so s/he doesn't get to
call
the shots in structural design. no more than you would tell a builder
how to
build the foundation of your new house - unless you're an experienced
builder yourself!) in this case, i can see a business reason for putting
the
code in the Orders table: a customer may move, but that should not
affect
reporting of *past* orders data, only open, unshipped orders and future
orders. especially from the tax angle - nothing worse than being audited
going back three years, and the taxes don't add up because some
customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order record,
shouldn't be hard. there are a number of ways to set it up; exactly how
you
do it depends on how the orders data entry form is set up. when entering
a
new order in the form, do you use a combobox control to choose the
customer?
if so, you should be able to include the city code field in the
combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city code.
remember that combobox columns are zero-based, so the first column (left
to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way. It's
now
a
text box. The man who owns the database is a friend of my husband who
owns
a
brewery. He has to pay taxes based on the locations of his customers,
so
he
wants the location code to show up in the Orders table. I've already
created
a query for him that tells him how much he sold by city codes using
the
dates
he choses based on the relationship between the Orders and Customers
tables.
Since I did the original work for him, he's now coming up with a lot
of
"issues" that used to work and now don't. Every time I think I'm
finished,
he
comes up with something new.......... It's been so long since I've
worked
much with Access (last certification was XP) that I can't even
remember if
it's possible to have a field automatically add data in a table like
this.
I'm more than happy if you tell me it can't be done....... Thanks
again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in message
...
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" wrote in
message

...
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

What is Me! ? Is it a function?

Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the
end of the row source on the Customer name field. When I switched to the form
view, the customer name had disappeared. Obviously, THAT wasn't the right
answer. I remved the text I added to the row source property and it restored
the customer name. So, I decided to add a combo box for the CSitus code and
set its width to zero. I used the combo box wizard to create the object and
link it to the customer table. I entered your
"Me!CSitusfield=Me!comboboxControl.column5" in the after update event
property but got an error message ("Microsoft can't find the object
Me!CSitus.....") when I attempted to enter a new record. I think I'm on the
right path but am having problems with the syntax. The form is based on the
Orders Table, and I'm using the Customer Table to locate the CSitus code (the
city code). Am I getting close? thanks, Pat

"tina" wrote:

comments inline.

"Patttt" wrote in message
...
Ha! No - it's certainly not the worst -- just the latest

well, you're in good company, if that's any comfort!


I entered your line of code in the AfterUpdate Event box and did a test
entry, but it didn't update the city code in the orders table. The
customer
combo box already has a row source that says "SELECT DISTINCTROW
Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the city
code
in the row source also? I didn't do that.

well, yes. you can't set the value of the field in the Orders table, using
code that refers to the combobox control, unless the value is available in
the combobox's RowSource. add the city code field to the RowSource, set the
ColumnWidth for that column to zero, and refer to that column's Index value
in the code i posted.


The owner said he started out with a template that he downloaded from the
microsoft site, if that helps any (it didn't for me - but you might be
more
familiar with what's available). Once he started playing with the
template,
his troubles started. He's not opposed to deleting the city code from the
orders table, but you make an excellent point about saving data in case of
tax audits, so I guess I'll keep trying to get this working! Thanks for
hanging in with me on this. Pat

you're welcome. we'll get it figured out, don't worry.


"tina" wrote:

good job re the demise of the Lookup field. and if that's the worst
thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note: the
fact
that the client "wants a field to show up in x table", is not a sound
business reason. you can't expect your client - in this case, a friend -
to
know beans about relational design principles, so s/he doesn't get to
call
the shots in structural design. no more than you would tell a builder
how to
build the foundation of your new house - unless you're an experienced
builder yourself!) in this case, i can see a business reason for putting
the
code in the Orders table: a customer may move, but that should not
affect
reporting of *past* orders data, only open, unshipped orders and future
orders. especially from the tax angle - nothing worse than being audited
going back three years, and the taxes don't add up because some
customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order record,
shouldn't be hard. there are a number of ways to set it up; exactly how
you
do it depends on how the orders data entry form is set up. when entering
a
new order in the form, do you use a combobox control to choose the
customer?
if so, you should be able to include the city code field in the
combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city code.
remember that combobox columns are zero-based, so the first column (left
to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way. It's
now
a
text box. The man who owns the database is a friend of my husband who
owns
a
brewery. He has to pay taxes based on the locations of his customers,
so
he
wants the location code to show up in the Orders table. I've already
created
a query for him that tells him how much he sold by city codes using
the
dates
he choses based on the relationship between the Orders and Customers
tables.
Since I did the original work for him, he's now coming up with a lot
of
"issues" that used to work and now don't. Every time I think I'm
finished,
he
comes up with something new.......... It's been so long since I've
worked
much with Access (last certification was XP) that I can't even
remember if
it's possible to have a field automatically add data in a table like
this.
I'm more than happy if you tell me it can't be done....... Thanks
again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in message
...
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

What is Me! ? Is it a function?

Me!SomeControlName

is the VBA syntax to refer to a control in the form *that the VBA code is
running in*. "Me" just replaces the full form reference, as

Forms("NameOfForm")!SomeControlName

think of it as a first-person kind of thing: when referring to yourself, or
something related to you, you would say "me" or "my". if i'm referring to
you, i'd say "Pat" or "Pat's".

as for the city code issue - hang tough; we will get there, as i promised
earlier. it really isn't hard, i just need a clearer picture of your setup,
so i can give you clear instructions. so let's get back to basics a moment.
post your customers table structure, like this:

tblCustomers
CustomerID (primary key)
FirstName
LastName
CityCode (foreign key from tblCities)

next, post your cities table, in the same format, like this:

tblCities
CityCode (primary key)
CityName

give me *real* information, though - the real table and field names, and the
correct designations of the primary and foreign key fields. from there, i
can tell you how to set RowSource of the customer combobox control on the
data entry form, so you can use the code i posted before to get the value
you need into the orders record.

hth


"Patttt" wrote in message
...
What is Me! ? Is it a function?

Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the
end of the row source on the Customer name field. When I switched to the
form
view, the customer name had disappeared. Obviously, THAT wasn't the right
answer. I remved the text I added to the row source property and it
restored
the customer name. So, I decided to add a combo box for the CSitus code
and
set its width to zero. I used the combo box wizard to create the object
and
link it to the customer table. I entered your
"Me!CSitusfield=Me!comboboxControl.column5" in the after update event
property but got an error message ("Microsoft can't find the object
Me!CSitus.....") when I attempted to enter a new record. I think I'm on
the
right path but am having problems with the syntax. The form is based on
the
Orders Table, and I'm using the Customer Table to locate the CSitus code
(the
city code). Am I getting close? thanks, Pat

"tina" wrote:

comments inline.

"Patttt" wrote in message
...
Ha! No - it's certainly not the worst -- just the latest

well, you're in good company, if that's any comfort!


I entered your line of code in the AfterUpdate Event box and did a
test
entry, but it didn't update the city code in the orders table. The
customer
combo box already has a row source that says "SELECT DISTINCTROW
Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the
city
code
in the row source also? I didn't do that.

well, yes. you can't set the value of the field in the Orders table,
using
code that refers to the combobox control, unless the value is available
in
the combobox's RowSource. add the city code field to the RowSource, set
the
ColumnWidth for that column to zero, and refer to that column's Index
value
in the code i posted.


The owner said he started out with a template that he downloaded from
the
microsoft site, if that helps any (it didn't for me - but you might be
more
familiar with what's available). Once he started playing with the
template,
his troubles started. He's not opposed to deleting the city code from
the
orders table, but you make an excellent point about saving data in
case of
tax audits, so I guess I'll keep trying to get this working! Thanks
for
hanging in with me on this. Pat

you're welcome. we'll get it figured out, don't worry.


"tina" wrote:

good job re the demise of the Lookup field. and if that's the worst
thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note:
the
fact
that the client "wants a field to show up in x table", is not a
sound
business reason. you can't expect your client - in this case, a
friend -
to
know beans about relational design principles, so s/he doesn't get
to
call
the shots in structural design. no more than you would tell a
builder
how to
build the foundation of your new house - unless you're an
experienced
builder yourself!) in this case, i can see a business reason for
putting
the
code in the Orders table: a customer may move, but that should not
affect
reporting of *past* orders data, only open, unshipped orders and
future
orders. especially from the tax angle - nothing worse than being
audited
going back three years, and the taxes don't add up because some
customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order
record,
shouldn't be hard. there are a number of ways to set it up; exactly
how
you
do it depends on how the orders data entry form is set up. when
entering
a
new order in the form, do you use a combobox control to choose the
customer?
if so, you should be able to include the city code field in the
combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so
it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox
control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city
code.
remember that combobox columns are zero-based, so the first column
(left
to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need
to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way.
It's
now
a
text box. The man who owns the database is a friend of my husband
who
owns
a
brewery. He has to pay taxes based on the locations of his
customers,
so
he
wants the location code to show up in the Orders table. I've
already
created
a query for him that tells him how much he sold by city codes
using
the
dates
he choses based on the relationship between the Orders and
Customers
tables.
Since I did the original work for him, he's now coming up with a
lot
of
"issues" that used to work and now don't. Every time I think I'm
finished,
he
comes up with something new.......... It's been so long since I've
worked
much with Access (last certification was XP) that I can't even
remember if
it's possible to have a field automatically add data in a table
like
this.
I'm more than happy if you tell me it can't be done.......
Thanks
again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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" wrote in message
news 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" wrote in
message

...
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

The cities code is part of the Customers table. Here's the structu
Customer ID - autonumber, primary key
Company Name, text
CSitus (this is the city code field), text
First Name, text
Last name, text
Billing Address, text
City, text
State, text
Zip, text
Phone Number, text
Notes, memo

Here is the structure of the Orders table:
OrderID, autonumber, primary key
Customer ID, number
Order Date, date/time
Purchase Order Number, text
CSitus, number
Payment received, yes/no

The form also uses the Order Details Table. Structu
OrderDetail ID, autonumber, primary key
Order ID, number
Product ID, number (the table actually displays the name of the product
rather than the numeric id number. This comes from the Product table.
Quantity, number
Unit Price, number
Discout, number

Here is the structure of the Products table:
Product ID, autonumber, primary key
Product Name, text
CostofEach, currency
Unit Size, number
In Stock, yes/no

These 4 tables are related on the redundant ID fields in each. The FORM
called "Add an Order and Details" is based on the Orders table and includes
all flelds in Orders table other than CSitus, which is the field he wants to
populate when a new order is entered. The form also contains a subform that
is now based on the Order Details table plus a calculated field that I added
to provide an extended price for the order (quantity X unit price).

Yes, I figured Me! is a function, but I couldn't find it in the functions
lists.



"tina" wrote:

What is Me! ? Is it a function?

Me!SomeControlName

is the VBA syntax to refer to a control in the form *that the VBA code is
running in*. "Me" just replaces the full form reference, as

Forms("NameOfForm")!SomeControlName

think of it as a first-person kind of thing: when referring to yourself, or
something related to you, you would say "me" or "my". if i'm referring to
you, i'd say "Pat" or "Pat's".

as for the city code issue - hang tough; we will get there, as i promised
earlier. it really isn't hard, i just need a clearer picture of your setup,
so i can give you clear instructions. so let's get back to basics a moment.
post your customers table structure, like this:

tblCustomers
CustomerID (primary key)
FirstName
LastName
CityCode (foreign key from tblCities)

next, post your cities table, in the same format, like this:

tblCities
CityCode (primary key)
CityName

give me *real* information, though - the real table and field names, and the
correct designations of the primary and foreign key fields. from there, i
can tell you how to set RowSource of the customer combobox control on the
data entry form, so you can use the code i posted before to get the value
you need into the orders record.

hth


"Patttt" wrote in message
...
What is Me! ? Is it a function?

Anyway, here's what I've done -- First, I added ";Customers.CSitus" to the
end of the row source on the Customer name field. When I switched to the
form
view, the customer name had disappeared. Obviously, THAT wasn't the right
answer. I remved the text I added to the row source property and it
restored
the customer name. So, I decided to add a combo box for the CSitus code
and
set its width to zero. I used the combo box wizard to create the object
and
link it to the customer table. I entered your
"Me!CSitusfield=Me!comboboxControl.column5" in the after update event
property but got an error message ("Microsoft can't find the object
Me!CSitus.....") when I attempted to enter a new record. I think I'm on
the
right path but am having problems with the syntax. The form is based on
the
Orders Table, and I'm using the Customer Table to locate the CSitus code
(the
city code). Am I getting close? thanks, Pat

"tina" wrote:

comments inline.

"Patttt" wrote in message
...
Ha! No - it's certainly not the worst -- just the latest

well, you're in good company, if that's any comfort!


I entered your line of code in the AfterUpdate Event box and did a
test
entry, but it didn't update the city code in the orders table. The
customer
combo box already has a row source that says "SELECT DISTINCTROW
Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the
city
code
in the row source also? I didn't do that.

well, yes. you can't set the value of the field in the Orders table,
using
code that refers to the combobox control, unless the value is available
in
the combobox's RowSource. add the city code field to the RowSource, set
the
ColumnWidth for that column to zero, and refer to that column's Index
value
in the code i posted.


The owner said he started out with a template that he downloaded from
the
microsoft site, if that helps any (it didn't for me - but you might be
more
familiar with what's available). Once he started playing with the
template,
his troubles started. He's not opposed to deleting the city code from
the
orders table, but you make an excellent point about saving data in
case of
tax audits, so I guess I'll keep trying to get this working! Thanks
for
hanging in with me on this. Pat

you're welcome. we'll get it figured out, don't worry.


"tina" wrote:

good job re the demise of the Lookup field. and if that's the worst
thing
you ever have to learn "the hard way" in Access, then you'll have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers, so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there are
sometimes sound business reasons for breaking those rules. (note:
the
fact
that the client "wants a field to show up in x table", is not a
sound
business reason. you can't expect your client - in this case, a
friend -
to
know beans about relational design principles, so s/he doesn't get
to
call
the shots in structural design. no more than you would tell a
builder
how to
build the foundation of your new house - unless you're an
experienced
builder yourself!) in this case, i can see a business reason for
putting
the
code in the Orders table: a customer may move, but that should not
affect
reporting of *past* orders data, only open, unshipped orders and
future
orders. especially from the tax angle - nothing worse than being
audited
going back three years, and the taxes don't add up because some
customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order
record,
shouldn't be hard. there are a number of ways to set it up; exactly
how
you
do it depends on how the orders data entry form is set up. when
entering
a
new order in the form, do you use a combobox control to choose the
customer?
if so, you should be able to include the city code field in the
combobox's
RowSource. you can hide the column (set its' ColumnWidth to zero) so
it
doesn't show in the droplist, but the city code values will still be
available to the form. you could then add code to the combobox
control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the city
code.
remember that combobox columns are zero-based, so the first column
(left
to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll need
to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way.
It's
now
a
text box. The man who owns the database is a friend of my husband
who
owns
a
brewery. He has to pay taxes based on the locations of his
customers,
so
he
wants the location code to show up in the Orders table. I've
already
created
a query for him that tells him how much he sold by city codes
using
the
dates
he choses based on the relationship between the Orders and
Customers
tables.
Since I did the original work for him, he's now coming up with a
lot
of
"issues" that used to work and now don't. Every time I think I'm
finished,
he
comes up with something new.......... It's been so long since I've
worked
much with Access (last certification was XP) that I can't even
remember if
it's possible to have a field automatically add data in a table
like
this.
I'm more than happy if you tell me it can't be done.......
Thanks
again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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

Yes, I figured Me! is a function, but I couldn't find it in the functions
lists.

sorry i wasn't clear. NO, Me is *not* a function. it is an object reference
in VBA syntax that refers to the "current" form (or report) that the code is
running in. go back and read my previous explanation again and see if it
makes more sense the 2nd time around.

The form also uses the Order Details Table. Structu
OrderDetail ID, autonumber, primary key
Order ID, number
Product ID, number (the table actually displays the name of the product
rather than the numeric id number. This comes from the Product table.

so ProductID is a Lookup field in tblOrderDetails. just like i said about
the other one - *get rid of the Lookup field in the table*!

These 4 tables are related on the redundant ID fields in each. The FORM
called "Add an Order and Details" is based on the Orders table and
includes
all flelds in Orders table other than CSitus, which is the field he wants
to
populate when a new order is entered.

okay, then. first, add field CSitus to the *RecordSource* of form "Add an
Order and Details". if you're using a version of Access prior to A2000, it
may be necessary to add a control to the form, with ControlSource of CSitus,
so the system will recognize it. if you need to do that, just make sure you
add the control to the Detail section of the form, and set its' Visible
property to False.

next, let's talk about the RowSource of the combobox control, in the form,
that you use to choose a customer name for a new order record. (and forget
about the subform, it's not relevant to the discussion.) i'm going to call
the combobox "cboCustomer". you posted a RowSource for cboCustomer a few
posts ago, as

SELECT DISTINCTROW Customers.*
FROM Customers ORDER BY Customers.CompanyName;

first, you don't need all those fields - unless you're using the customer's
address, etc, to help choose the right customer. next, why did you include
DISTINCTROW in the query? please tell me you don't have the same customer
listed more than once in tblCustomers! if you do, we need to stop here and
have a separate discussion about table structure. if you don't, let's change
the RowSource to only the fields you *need* to see in the combobox droplist
when choosing a customer for an order record. for example:

SELECT CustomerID, CompanyName, CSitus FROM Customers ORDER BY CompanyName;

based on the above RowSource, set the following properties for cboCustomer
as

ColumnCount: 3
BoundColumn: 1
ColumnWidths: 0";2";0"
ListWidth: 2.25"
LimitToList: Yes

in the combobox control's AfterUpdate event, add the following code, as

Me!CSitus = Me!cboCustomer.Column(2)

taking another look at the RowSource posted above, you'll see that CSitus is
the third field listed.
looking at the BoundColumn setting, you'll see that the value that will be
stored in the control's ControlSource field is the first column -
CustomerID. (the number in the BoundColumn property setting is *not* an
index value, so the "zero-based" rule does not apply here. and yes, i know
it's confusing.)
looking at ColumnWidths property setting posted above, you'll see that the
first and third column's widths are zero, so the customer id and city code
will not be seen when the droplist is open - only the customer name.
and finally, looking at the VBA code above, you'll see that the *index*
value referred to is Column(2); remember that combobox column indexes are
zero-based, first column is Column(0), second is Column(1), third is
Column(2), etc.

and btw, in case you're not already familiar with object-naming conventions,
suggest you see http://home.att.net/~california.db/tips.html#aTip5.

hth


"Patttt" wrote in message
news
The cities code is part of the Customers table. Here's the structu
Customer ID - autonumber, primary key
Company Name, text
CSitus (this is the city code field), text
First Name, text
Last name, text
Billing Address, text
City, text
State, text
Zip, text
Phone Number, text
Notes, memo

Here is the structure of the Orders table:
OrderID, autonumber, primary key
Customer ID, number
Order Date, date/time
Purchase Order Number, text
CSitus, number
Payment received, yes/no

The form also uses the Order Details Table. Structu
OrderDetail ID, autonumber, primary key
Order ID, number
Product ID, number (the table actually displays the name of the product
rather than the numeric id number. This comes from the Product table.
Quantity, number
Unit Price, number
Discout, number

Here is the structure of the Products table:
Product ID, autonumber, primary key
Product Name, text
CostofEach, currency
Unit Size, number
In Stock, yes/no

These 4 tables are related on the redundant ID fields in each. The FORM
called "Add an Order and Details" is based on the Orders table and
includes
all flelds in Orders table other than CSitus, which is the field he wants
to
populate when a new order is entered. The form also contains a subform
that
is now based on the Order Details table plus a calculated field that I
added
to provide an extended price for the order (quantity X unit price).

Yes, I figured Me! is a function, but I couldn't find it in the functions
lists.



"tina" wrote:

What is Me! ? Is it a function?

Me!SomeControlName

is the VBA syntax to refer to a control in the form *that the VBA code
is
running in*. "Me" just replaces the full form reference, as

Forms("NameOfForm")!SomeControlName

think of it as a first-person kind of thing: when referring to
yourself, or
something related to you, you would say "me" or "my". if i'm referring
to
you, i'd say "Pat" or "Pat's".

as for the city code issue - hang tough; we will get there, as i
promised
earlier. it really isn't hard, i just need a clearer picture of your
setup,
so i can give you clear instructions. so let's get back to basics a
moment.
post your customers table structure, like this:

tblCustomers
CustomerID (primary key)
FirstName
LastName
CityCode (foreign key from tblCities)

next, post your cities table, in the same format, like this:

tblCities
CityCode (primary key)
CityName

give me *real* information, though - the real table and field names, and
the
correct designations of the primary and foreign key fields. from there,
i
can tell you how to set RowSource of the customer combobox control on
the
data entry form, so you can use the code i posted before to get the
value
you need into the orders record.

hth


"Patttt" wrote in message
...
What is Me! ? Is it a function?

Anyway, here's what I've done -- First, I added ";Customers.CSitus" to
the
end of the row source on the Customer name field. When I switched to
the
form
view, the customer name had disappeared. Obviously, THAT wasn't the
right
answer. I remved the text I added to the row source property and it
restored
the customer name. So, I decided to add a combo box for the CSitus
code
and
set its width to zero. I used the combo box wizard to create the
object
and
link it to the customer table. I entered your
"Me!CSitusfield=Me!comboboxControl.column5" in the after update event
property but got an error message ("Microsoft can't find the object
Me!CSitus.....") when I attempted to enter a new record. I think I'm
on
the
right path but am having problems with the syntax. The form is based
on
the
Orders Table, and I'm using the Customer Table to locate the CSitus
code
(the
city code). Am I getting close? thanks, Pat

"tina" wrote:

comments inline.

"Patttt" wrote in message
...
Ha! No - it's certainly not the worst -- just the latest

well, you're in good company, if that's any comfort!


I entered your line of code in the AfterUpdate Event box and did a
test
entry, but it didn't update the city code in the orders table. The
customer
combo box already has a row source that says "SELECT DISTINCTROW
Customers.*
FROM Customers ORDER BY Customers.CompanyName; ". should I put the
city
code
in the row source also? I didn't do that.

well, yes. you can't set the value of the field in the Orders table,
using
code that refers to the combobox control, unless the value is
available
in
the combobox's RowSource. add the city code field to the RowSource,
set
the
ColumnWidth for that column to zero, and refer to that column's
Index
value
in the code i posted.


The owner said he started out with a template that he downloaded
from
the
microsoft site, if that helps any (it didn't for me - but you
might be
more
familiar with what's available). Once he started playing with the
template,
his troubles started. He's not opposed to deleting the city code
from
the
orders table, but you make an excellent point about saving data in
case of
tax audits, so I guess I'll keep trying to get this working!
Thanks
for
hanging in with me on this. Pat

you're welcome. we'll get it figured out, don't worry.


"tina" wrote:

good job re the demise of the Lookup field. and if that's the
worst
thing
you ever have to learn "the hard way" in Access, then you'll
have a
fantastically better track record than i have!

He has to pay taxes based on the locations of his customers,
so he
wants the location code to show up in the Orders table.

hmm, okay. normalization is king, *but* in the real world there
are
sometimes sound business reasons for breaking those rules.
(note:
the
fact
that the client "wants a field to show up in x table", is not a
sound
business reason. you can't expect your client - in this case, a
friend -
to
know beans about relational design principles, so s/he doesn't
get
to
call
the shots in structural design. no more than you would tell a
builder
how to
build the foundation of your new house - unless you're an
experienced
builder yourself!) in this case, i can see a business reason for
putting
the
code in the Orders table: a customer may move, but that should
not
affect
reporting of *past* orders data, only open, unshipped orders and
future
orders. especially from the tax angle - nothing worse than being
audited
going back three years, and the taxes don't add up because some
customers
have moved in the interim - ouch!

as for automatically entering the city code in each new order
record,
shouldn't be hard. there are a number of ways to set it up;
exactly
how
you
do it depends on how the orders data entry form is set up. when
entering
a
new order in the form, do you use a combobox control to choose
the
customer?
if so, you should be able to include the city code field in the
combobox's
RowSource. you can hide the column (set its' ColumnWidth to
zero) so
it
doesn't show in the droplist, but the city code values will
still be
available to the form. you could then add code to the combobox
control's
AfterUpdate event, something along the lines of

Me!CityCodeField = Me!ComboboxControl.Column(x)

replace "x" with the index number of the column that has the
city
code.
remember that combobox columns are zero-based, so the first
column
(left
to
right) is (0), the second column is (1), etc.

if that's not how your orders data entry form is set up, you'll
need
to
describe it in some detail before i can make other suggestions.

hth


"Patttt" wrote in message
...
yes, I'd already learned about the lookup field the hard way.

It's
now
a
text box. The man who owns the database is a friend of my
husband
who
owns
a
brewery. He has to pay taxes based on the locations of his
customers,
so
he
wants the location code to show up in the Orders table. I've
already
created
a query for him that tells him how much he sold by city codes
using
the
dates
he choses based on the relationship between the Orders and
Customers
tables.
Since I did the original work for him, he's now coming up with
a
lot
of
"issues" that used to work and now don't. Every time I think
I'm
finished,
he
comes up with something new.......... It's been so long since
I've
worked
much with Access (last certification was XP) that I can't even
remember if
it's possible to have a field automatically add data in a
table
like
this.
I'm more than happy if you tell me it can't be done.......
Thanks
again
for your help! Pat

"tina" wrote:

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" wrote in message
...
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