Combine Tables

  • Thread starter Thread starter Brook
  • Start date Start date
B

Brook

I have two tables: tblCustomOrders (uses and autonumber to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is there
a way that when the data is is create in each of the above
tables, the tblMasterInventory is updated with the record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook
 
The 2 Orders tables need to be 1 table. You can separate out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can separate out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I have two tables: tblCustomOrders (uses and autonumber to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is there
a way that when the data is is create in each of the above
tables, the tblMasterInventory is updated with the record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook
Click to expand...


.
Click to expand...
 
You can have as many forms as you want all to update the
same table, but since the fields are the same, why bother
with two forms, just use conditional formatting to change
the title depending on what type of order they are
entering. I'm not sure of the exact code to do that but
I've seen it here before.

HTH
-----Original Message-----
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can separate out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I have two tables: tblCustomOrders (uses and
Click to expand...
Click to expand...
autonumber
to


.
Click to expand...
.
Click to expand...
 
Do you have any ideas on what I would search for the find
the information in the newsgroups on this issue?

Thanks for your reply,

Brook
-----Original Message-----
You can have as many forms as you want all to update the
same table, but since the fields are the same, why bother
with two forms, just use conditional formatting to change
the title depending on what type of order they are
entering. I'm not sure of the exact code to do that but
I've seen it here before.

HTH
-----Original Message-----
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can
Click to expand...
separate
out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I have two tables: tblCustomOrders (uses and
Click to expand...
autonumber
to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is there
a way that when the data is is create in each of the above
tables, the tblMasterInventory is updated with the record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
how about "conditional formatting" ??
-----Original Message-----
Do you have any ideas on what I would search for the find
the information in the newsgroups on this issue?

Thanks for your reply,

Brook
-----Original Message-----
You can have as many forms as you want all to update the
same table, but since the fields are the same, why bother
with two forms, just use conditional formatting to change
the title depending on what type of order they are
entering. I'm not sure of the exact code to do that but
I've seen it here before.

HTH
-----Original Message-----
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can separate
out the data by
using criteia. If it is really important that the
OrderNumbers have
different prefixes and number sets, then don't use an
Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the
code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

message
I have two tables: tblCustomOrders (uses and autonumber
to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is
there
a way that when the data is is create in each of the
above
tables, the tblMasterInventory is updated with the
record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook


.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
You can use 2 different forms, but that isn't really necessary. I have
something similar, where I need to do job numbers based on 5 different
engineering offices and what kind of a job it is (Environment, Water, etc.)
The user picks the Office, then the type, then the system uses code similar
to that below to get the next JobNumber. The code I'm using is far more
complex because it also needs to reset semi-automatically after the first of
the year.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can separate out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I have two tables: tblCustomOrders (uses and autonumber to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is there
a way that when the data is is create in each of the above
tables, the tblMasterInventory is updated with the record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook
Click to expand...


.
Click to expand...
Click to expand...
 
Do you have any suggestions on where I could look for some
samples of what I would like to do?

Brook
-----Original Message-----
You can use 2 different forms, but that isn't really necessary. I have
something similar, where I need to do job numbers based on 5 different
engineering offices and what kind of a job it is (Environment, Water, etc.)
The user picks the Office, then the type, then the system uses code similar
to that below to get the next JobNumber. The code I'm using is far more
complex because it also needs to reset semi-automatically after the first of
the year.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

So I can use one table? and use the code below?

I use two different forms: one to create the KI orders and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can
Click to expand...
separate
out the data by
using criteia. If it is really important that the OrderNumbers have
different prefixes and number sets, then don't use an Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax ("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

I have two tables: tblCustomOrders (uses and
Click to expand...
autonumber
to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders have a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is there
a way that when the data is is create in each of the above
tables, the tblMasterInventory is updated with the record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook


.
Click to expand...
Click to expand...


.
Click to expand...
 
Doe you have any tips or suggestions on how i would go
about doing this? I am wanting to use a Tab control with
two tabs: One for Inhouse Orders and One for Custom
Orders. So your saying I can set somthing up that: If
custom then "KI-0000" else if program then NW-0000?

Thanks for your help,

Brook
-----Original Message-----
how about "conditional formatting" ??
-----Original Message-----
Do you have any ideas on what I would search for the find
the information in the newsgroups on this issue?

Thanks for your reply,

Brook
-----Original Message-----
You can have as many forms as you want all to update the
same table, but since the fields are the same, why bother
with two forms, just use conditional formatting to change
the title depending on what type of order they are
entering. I'm not sure of the exact code to do that but
I've seen it here before.

HTH
-----Original Message-----
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders
and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can separate
out the data by
using criteia. If it is really important that the
OrderNumbers have
different prefixes and number sets, then don't use an
Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the
code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

message
I have two tables: tblCustomOrders (uses and
autonumber
to
create orderID, KI-0000) and tblProgramOrders (uses and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders
Click to expand...
Click to expand...
have
a
.
Click to expand...
.
Click to expand...
 
You can use a tab control. That is very similar to using 2 forms. Just
remember that the bound data that is in the same field will show on both
pages of the tab control, so it's almost useless. You could write a bunch of
code to disable or hide the unused tab page, but it would be easier to just
change the detail color depending upon the state of the record (i.e. grey if
new, green if KI, or blue is NW) That would be less code to write and
easier for the users to understand what's going on.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Brook said:
Doe you have any tips or suggestions on how i would go
about doing this? I am wanting to use a Tab control with
two tabs: One for Inhouse Orders and One for Custom
Orders. So your saying I can set somthing up that: If
custom then "KI-0000" else if program then NW-0000?

Thanks for your help,

Brook
-----Original Message-----
how about "conditional formatting" ??
-----Original Message-----
Do you have any ideas on what I would search for the find
the information in the newsgroups on this issue?

Thanks for your reply,

Brook

-----Original Message-----
You can have as many forms as you want all to update the
same table, but since the fields are the same, why bother
with two forms, just use conditional formatting to change
the title depending on what type of order they are
entering. I'm not sure of the exact code to do that but
I've seen it here before.

HTH
-----Original Message-----
So I can use one table? and use the code below?

I use two different forms: one to create the KI orders
and
one to create the NW orders. Is this still possible with
your suggestions?

Brook
-----Original Message-----
The 2 Orders tables need to be 1 table. You can
separate
out the data by
using criteia. If it is really important that the
OrderNumbers have
different prefixes and number sets, then don't use an
Autonumber. Instead
use an expression, something like (aircode):

IIf(Left([txtOrderID],2) = "KI", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"KI"'")", DMax
("OrderID", "tblOrders",
"Left([OrderID],2 ="'"NW"'")"

All on one line of code. You may have to play with the
code a bit to get it
right, as I haven't check the syntax.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

message
I have two tables: tblCustomOrders (uses and
autonumber
to
create orderID, KI-0000) and tblProgramOrders (uses
and
autonumber to create orderID, NW-0000).
The reason for two tables is that custom orders
Click to expand...
have
a
different OrderID than the Program Orders. Both tables
have the same number and type of fields its just the
orderid is different.

I would like to maintain a MasterInventoryTable. Is
there
a way that when the data is is create in each of the
above
tables, the tblMasterInventory is updated with the
record.
And same if the masterinventorytable is edited, the
information be sent to the corresponding table.

Any ideas/suggestions are appreciated.

Brook


.

.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top