2 tables, 1 query, 1 form, 1 subform

[Michael Horne]

I am new to Access. Using Office Access 2003.

table1 = contractors information (name, address, etc.)
table2 = order info with autonumber field (part 1, part 2, order date, etc.)
query1= join by contractor number [enter contractor number]
form1= contractors information displayed, but cannot edit or select field
(zero data entry allowed), plus autonumber field
subform1=order information, plus autonumber field

So far this works, but has a few issues.

The table2 (orders) has 3 rows. When I am using the form it correctly
displays one of the selected contractor and contractors order information.
However, when I click the next button (on bottom toolbar) the order
information does not change to show the different information for each order.
It is a different record number, the order fields keep showing the last rows
saved information for that field.

Also the autonumber in the subform changes, but the autonumber in the form
does not, it shows the last row saved autonumber.

When I click the new record button it does clear the order fields
information, and when I type any information in a field the autonumber does
increment to the correct number.

Is there a way to make the bottom toolbar have the new record button added
to it, so the users can advance through the orders and then add?

Any clues?

 

[Duane Hookom]

Is [enter contractor number] the name of a field?
I don't understand your issue but do you have anything in the Link
Master/Child properties of the subform control? This should be the field
names that are used to join the tables.

BTW: Do you really have fields for Part 1 and Part 2? How many part fields
do you have? Do you understand this might not be normalized?

 

[Michael Horne]

Hi, thanks for helping me. I tried to be clear, but I have only been using
Access for a few months now.

Both table1 and table2 have a field which stores the number assigned to each
contractor. Table1 field is called Nbr_Contract. Table2 field is called
ID_CNTRCT.

Query1 joins the two tables on these contractor number fields. In the query
for Nbr_Contract I have selected Total= Group By, and Show is checked, and
Criteria= [enter contract #]. [enter contract #] pops up a window when the
form is run, forcing the user to input a contract to return results.

I am so new I have no idea where to find the Link Master/Child properties of
the subform control, or what that is exactly. Can you tell me where I need
to go?

I have one big issue and two small issues.

Big issue= When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the values shown on the screen do not change (They should change to
show the different values based upon each order placed).

Small issue1a= On form. Order# (this is an autonumber in the orders table).
When user starts the form. If contract number has multiple rows listed in
the orders table, then when the user clicks the next row button in the form,
the value shown on the screen does NOT change (This should change to show the
different order #'s based upon each order placed).

Small issue 1b= On Subform. Order# (this is an autonumber in the orders
table). When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the value shown on the screen DOES change (This shows the correct
autonumber value of each order placed).

I think the flow of the form is better if the order# shows up in the form
not the subform. Why is the behavior so different?

Small issue2= When user starts the form, the bottom contains the next record
button, end button, and begin button. Why does it not contain a button to
create a new record? The is one I turned on but it sits on the toolbar row
on top.

Haha. No I really do not have fields for Part1 and Part2. I clearly did
not word that well. The orders table allows each order to have 15 items in
it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes
and it contains a list of parts, which only 1 item can be selected.

I hope I have been more clear, and less murky. Any help is greatly
appreciated!!

--
Michael Horne

Is [enter contractor number] the name of a field?
I don't understand your issue but do you have anything in the Link
Master/Child properties of the subform control? This should be the field
names that are used to join the tables.

BTW: Do you really have fields for Part 1 and Part 2? How many part fields
do you have? Do you understand this might not be normalized?

--
Duane Hookom
Microsoft Access MVP

I am new to Access. Using Office Access 2003.

table1 = contractors information (name, address, etc.)
table2 = order info with autonumber field (part 1, part 2, order date, etc.)
query1= join by contractor number [enter contractor number]
form1= contractors information displayed, but cannot edit or select field
(zero data entry allowed), plus autonumber field
subform1=order information, plus autonumber field

So far this works, but has a few issues.

The table2 (orders) has 3 rows. When I am using the form it correctly
displays one of the selected contractor and contractors order information.
However, when I click the next button (on bottom toolbar) the order
information does not change to show the different information for each order.
It is a different record number, the order fields keep showing the last rows
saved information for that field.

Also the autonumber in the subform changes, but the autonumber in the form
does not, it shows the last row saved autonumber.

When I click the new record button it does clear the order fields
information, and when I type any information in a field the autonumber does
increment to the correct number.

Is there a way to make the bottom toolbar have the new record button added
to it, so the users can advance through the orders and then add?

Any clues?

 

[Duane Hookom]

"The orders table allows each order to have 15 items in it"... I would not
put any more work into this until after you normalized the table structure.
Each Item should create its own record in a related table.

Take a look at the Northwind sample database. It has a fairly good structure
for managing customers, orders, order details, etc.

Also, do a web search on "Data normalization" to find out what's wrong with
your current table structure.
--
Duane Hookom
Microsoft Access MVP

Hi, thanks for helping me. I tried to be clear, but I have only been using
Access for a few months now.

Both table1 and table2 have a field which stores the number assigned to each
contractor. Table1 field is called Nbr_Contract. Table2 field is called
ID_CNTRCT.

Query1 joins the two tables on these contractor number fields. In the query
for Nbr_Contract I have selected Total= Group By, and Show is checked, and
Criteria= [enter contract #]. [enter contract #] pops up a window when the
form is run, forcing the user to input a contract to return results.

I am so new I have no idea where to find the Link Master/Child properties of
the subform control, or what that is exactly. Can you tell me where I need
to go?

I have one big issue and two small issues.

Big issue= When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the values shown on the screen do not change (They should change to
show the different values based upon each order placed).

Small issue1a= On form. Order# (this is an autonumber in the orders table).
When user starts the form. If contract number has multiple rows listed in
the orders table, then when the user clicks the next row button in the form,
the value shown on the screen does NOT change (This should change to show the
different order #'s based upon each order placed).

Small issue 1b= On Subform. Order# (this is an autonumber in the orders
table). When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the value shown on the screen DOES change (This shows the correct
autonumber value of each order placed).

I think the flow of the form is better if the order# shows up in the form
not the subform. Why is the behavior so different?

Small issue2= When user starts the form, the bottom contains the next record
button, end button, and begin button. Why does it not contain a button to
create a new record? The is one I turned on but it sits on the toolbar row
on top.

Haha. No I really do not have fields for Part1 and Part2. I clearly did
not word that well. The orders table allows each order to have 15 items in
it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes
and it contains a list of parts, which only 1 item can be selected.

I hope I have been more clear, and less murky. Any help is greatly
appreciated!!

--
Michael Horne

Is [enter contractor number] the name of a field?
I don't understand your issue but do you have anything in the Link
Master/Child properties of the subform control? This should be the field
names that are used to join the tables.

BTW: Do you really have fields for Part 1 and Part 2? How many part fields
do you have? Do you understand this might not be normalized?

--
Duane Hookom
Microsoft Access MVP

I am new to Access. Using Office Access 2003.

table1 = contractors information (name, address, etc.)
table2 = order info with autonumber field (part 1, part 2, order date, etc.)
query1= join by contractor number [enter contractor number]
form1= contractors information displayed, but cannot edit or select field
(zero data entry allowed), plus autonumber field
subform1=order information, plus autonumber field

So far this works, but has a few issues.

The table2 (orders) has 3 rows. When I am using the form it correctly
displays one of the selected contractor and contractors order information.
However, when I click the next button (on bottom toolbar) the order
information does not change to show the different information for each order.
It is a different record number, the order fields keep showing the last rows
saved information for that field.

Also the autonumber in the subform changes, but the autonumber in the form
does not, it shows the last row saved autonumber.

When I click the new record button it does clear the order fields
information, and when I type any information in a field the autonumber does
increment to the correct number.

Is there a way to make the bottom toolbar have the new record button added
to it, so the users can advance through the orders and then add?

Any clues?

 

[Michael Horne]

Duane,
I still am a bit fuzzy on normalization. I would not know how to rate what
normal form that I am currently at. Do you have a simplified definition that
you could share with me?

I have looked at Northwind sample database and I think that mine is pretty
close to it as is.
My orders table is about equal to Northwinds.
My customer table is about equal to Northwinds.
My products table is about equal to Northwinds.
I do not have an equivalent table to Northwinds Order Details. So if I make
a Order Details table and no longer store the results the purchase in the
orders table will that fix my Big Issue?
--
Michael Horne

"The orders table allows each order to have 15 items in it"... I would not
put any more work into this until after you normalized the table structure.
Each Item should create its own record in a related table.

Take a look at the Northwind sample database. It has a fairly good structure
for managing customers, orders, order details, etc.

Also, do a web search on "Data normalization" to find out what's wrong with
your current table structure.
--
Duane Hookom
Microsoft Access MVP

Hi, thanks for helping me. I tried to be clear, but I have only been using
Access for a few months now.

Both table1 and table2 have a field which stores the number assigned to each
contractor. Table1 field is called Nbr_Contract. Table2 field is called
ID_CNTRCT.

Query1 joins the two tables on these contractor number fields. In the query
for Nbr_Contract I have selected Total= Group By, and Show is checked, and
Criteria= [enter contract #]. [enter contract #] pops up a window when the
form is run, forcing the user to input a contract to return results.

I am so new I have no idea where to find the Link Master/Child properties of
the subform control, or what that is exactly. Can you tell me where I need
to go?

I have one big issue and two small issues.

Big issue= When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the values shown on the screen do not change (They should change to
show the different values based upon each order placed).

Small issue1a= On form. Order# (this is an autonumber in the orders table).
When user starts the form. If contract number has multiple rows listed in
the orders table, then when the user clicks the next row button in the form,
the value shown on the screen does NOT change (This should change to show the
different order #'s based upon each order placed).

Small issue 1b= On Subform. Order# (this is an autonumber in the orders
table). When user starts the form. If contract number has multiple rows
listed in the orders table, then when the user clicks the next row button in
the form, the value shown on the screen DOES change (This shows the correct
autonumber value of each order placed).

I think the flow of the form is better if the order# shows up in the form
not the subform. Why is the behavior so different?

Small issue2= When user starts the form, the bottom contains the next record
button, end button, and begin button. Why does it not contain a button to
create a new record? The is one I turned on but it sits on the toolbar row
on top.

Haha. No I really do not have fields for Part1 and Part2. I clearly did
not word that well. The orders table allows each order to have 15 items in
it, so there are 15 dropdown boxes in the form. Click on any of the 15 boxes
and it contains a list of parts, which only 1 item can be selected.

I hope I have been more clear, and less murky. Any help is greatly
appreciated!!

--
Michael Horne

Is [enter contractor number] the name of a field?
I don't understand your issue but do you have anything in the Link
Master/Child properties of the subform control? This should be the field
names that are used to join the tables.

BTW: Do you really have fields for Part 1 and Part 2? How many part fields
do you have? Do you understand this might not be normalized?

--
Duane Hookom
Microsoft Access MVP


:

I am new to Access. Using Office Access 2003.

table1 = contractors information (name, address, etc.)
table2 = order info with autonumber field (part 1, part 2, order date, etc.)
query1= join by contractor number [enter contractor number]
form1= contractors information displayed, but cannot edit or select field
(zero data entry allowed), plus autonumber field
subform1=order information, plus autonumber field

So far this works, but has a few issues.

The table2 (orders) has 3 rows. When I am using the form it correctly
displays one of the selected contractor and contractors order information.
However, when I click the next button (on bottom toolbar) the order
information does not change to show the different information for each order.
It is a different record number, the order fields keep showing the last rows
saved information for that field.

Also the autonumber in the subform changes, but the autonumber in the form
does not, it shows the last row saved autonumber.

When I click the new record button it does clear the order fields
information, and when I type any information in a field the autonumber does
increment to the correct number.

Is there a way to make the bottom toolbar have the new record button added
to it, so the users can advance through the orders and then add?

Any clues?

 

[Philip Herlihy]

Duane,
I still am a bit fuzzy on normalization. I would not know how to rate what
normal form that I am currently at. Do you have a simplified definition that
you could share with me?

I have looked at Northwind sample database and I think that mine is pretty
close to it as is.
My orders table is about equal to Northwinds.
My customer table is about equal to Northwinds.
My products table is about equal to Northwinds.
I do not have an equivalent table to Northwinds Order Details. So if I make
a Order Details table and no longer store the results the purchase in the
orders table will that fix my Big Issue?

Try this sample video lesson:
http://www.lynda.com/home/Player.aspx?lpk4=13150

or this:
http://www.lynda.com/home/Player.aspx?lpk4=31001

or this:
http://office.microsoft.com/trainin...ID=RC061183261033&CTT=6&Origin=RP061494301033
(you'll need to correct the line-break).

There are some very formal descriptions of Normalisation ("normal
forms") but it's really something most easily grasped from examples - I
hope the above will help. There are probably countless more videos on
UTube!

Phil, London

 

[Michael Horne]

Philip,

Thanks for the information. My work is blocking the first two links
(lynda.com) so I will try those at home.