I'm not quite sure how to start going about this. I will try to explain
what I am trying to accomplish.

Basically, the database pulls in the Order, Customer, and Product info from
our AS/400. I want the sales people who are requesting a change to the
order (either ship date, cancelling product/order, changing quantities, etc)
to be able to pull up their desired customer on a form. The subform will
show all orders for that customer. The salesperson can then select which
order(s) they want to change. When selecting the order(s), a second subform
will appear showing all the lines on the order(s) at which point they can
select individual lines ... if needed (some changes affect the entire
order.) When they have selected all the items they want to change and have
entered what the new ship date, qty, etc. is, then the data should be added
to a table with a unique Change Request ID. The data that comes from the
AS/400 remains unchanged and would be available for the next change request.

I have a database with the following tables:

tbl_Change_Request_Header
tbl_Change_Request_Order_Details
tbl_Cust_Mast (from AS/400)
tbl_Item_Master (from AS/400)
tbl_Order_Header (from AS/400)
tbl_Order_Details (from AS/400)

I hope this explanation is as clear as mud.
Any help you can supply would be greatly appreciated.

If you need more info, please let me know

Greg K

Hi Greg,

I would create an unbound Query By Form (QBF) form, that allowed me to
easily find one or more customers, based on various attributes such as
customer name, customer number, city, state, postal code, etc. You could even
use the customer's telephone number as a valid search criteria. The records
that match your search would be displayed in a read-only subform (just enough
fields from the customer table to allow someone to positively identify the
customer). You can then activate a form that opens up to this customer and
displays all of their orders, by double-clicking anywhere within the subform.
The new form that opens would be modelled after the Customer Orders form
found in the Northwind sample database (Northwind.mdb), which is likely on
your hard drive already.

The part of your request that I find a little confusing is the following
statement:

"The data that comes from the AS/400 remains unchanged and would be
available for the next change request."

Why would you not want to update the AS/400 with the change request
information?

I have a sample database with a QBF form that I can send to you if you send
me a private request by e-mail. My e-mail address is available at the bottom
of the Contributor's page indicated in my signature below. Whatever you do,
please do not post your real e-mail address (or mine) to a newsgroup message.

Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Greg K." wrote:

> I'm not quite sure how to start going about this. I will try to explain
> what I am trying to accomplish.
>
> Basically, the database pulls in the Order, Customer, and Product info from
> our AS/400. I want the sales people who are requesting a change to the
> order (either ship date, cancelling product/order, changing quantities, etc)
> to be able to pull up their desired customer on a form. The subform will
> show all orders for that customer. The salesperson can then select which
> order(s) they want to change. When selecting the order(s), a second subform
> will appear showing all the lines on the order(s) at which point they can
> select individual lines ... if needed (some changes affect the entire
> order.) When they have selected all the items they want to change and have
> entered what the new ship date, qty, etc. is, then the data should be added
> to a table with a unique Change Request ID. The data that comes from the
> AS/400 remains unchanged and would be available for the next change request.
>
> I have a database with the following tables:
>
> tbl_Change_Request_Header
> tbl_Change_Request_Order_Details
> tbl_Cust_Mast (from AS/400)
> tbl_Item_Master (from AS/400)
> tbl_Order_Header (from AS/400)
> tbl_Order_Details (from AS/400)
>
> I hope this explanation is as clear as mud.
> Any help you can supply would be greatly appreciated.
>
> If you need more info, please let me know
>
> Greg K
>
>
>

The AS/400 data is read only. The change request (once I can get it in a
table) would be generated as a report that will get emailed to the
appropriate shipping location to notify them of the change. The documents
they use to ship from have already been printed, and simply changing the
data in the AS/400 would not notify them of the change.

I know how to call up all the information I need with forms and subforms
(the issue is not the ability to find or display info, it is the writing it
to a separate table that I am having trouble with.)


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:123D0457-FA73-4B7F-B145-(E-Mail Removed)...
> Hi Greg,
>
> I would create an unbound Query By Form (QBF) form, that allowed me to
> easily find one or more customers, based on various attributes such as
> customer name, customer number, city, state, postal code, etc. You could
even
> use the customer's telephone number as a valid search criteria. The
records
> that match your search would be displayed in a read-only subform (just
enough
> fields from the customer table to allow someone to positively identify the
> customer). You can then activate a form that opens up to this customer and
> displays all of their orders, by double-clicking anywhere within the
subform.
> The new form that opens would be modelled after the Customer Orders form
> found in the Northwind sample database (Northwind.mdb), which is likely on
> your hard drive already.
>
> The part of your request that I find a little confusing is the following
> statement:
>
> "The data that comes from the AS/400 remains unchanged and would be
> available for the next change request."
>
> Why would you not want to update the AS/400 with the change request
> information?
>
> I have a sample database with a QBF form that I can send to you if you
send
> me a private request by e-mail. My e-mail address is available at the
bottom
> of the Contributor's page indicated in my signature below. Whatever you
do,
> please do not post your real e-mail address (or mine) to a newsgroup
message.
>
> Tom
>> __________________________________________
>
> "Greg K." wrote:
>
> > I'm not quite sure how to start going about this. I will try to explain
> > what I am trying to accomplish.
> >
> > Basically, the database pulls in the Order, Customer, and Product info
from
> > our AS/400. I want the sales people who are requesting a change to the
> > order (either ship date, cancelling product/order, changing quantities,
etc)
> > to be able to pull up their desired customer on a form. The subform
will
> > show all orders for that customer. The salesperson can then select
which
> > order(s) they want to change. When selecting the order(s), a second
subform
> > will appear showing all the lines on the order(s) at which point they
can
> > select individual lines ... if needed (some changes affect the entire
> > order.) When they have selected all the items they want to change and
have
> > entered what the new ship date, qty, etc. is, then the data should be
added
> > to a table with a unique Change Request ID. The data that comes from
the
> > AS/400 remains unchanged and would be available for the next change
request.
> >
> > I have a database with the following tables:
> >
> > tbl_Change_Request_Header
> > tbl_Change_Request_Order_Details
> > tbl_Cust_Mast (from AS/400)
> > tbl_Item_Master (from AS/400)
> > tbl_Order_Header (from AS/400)
> > tbl_Order_Details (from AS/400)
> >
> > I hope this explanation is as clear as mud.
> > Any help you can supply would be greatly appreciated.
> >
> > If you need more info, please let me know
> >
> > Greg K
> >
> >
> >

Hi Greg,

You should be able to add a command button to the form that runs an append
query. The append query would append the current record to your
tbl_Change_Request_Header and tbl_Change_Request_Order_Details tables. Quick
question: Would you want to append all order detail records to this table, or
only those that were changed?

In any event, you will want to append the data to the
tbl_Change_Request_Header table first, before appending the related child
records, if you have referential integrity (RI) enforced, which is always a
good idea.

Something like this (only marginally tested Air Code for the Northwind
Customer Orders form):

Copy the structure only of the Orders table in Northwind to a new table
named "tbl_Change_Request_Header". Add a command button to the main form
named "cmdAddChangeRequest". Add the following code for the click event of
this command button:

Option Compare Database
Option Explicit

Private Sub cmdAddChangeRequest_Click()
On Error GoTo ProcError

Dim strSQL As String
Dim lngOrderID As Long

lngOrderID = Me.[Customer Orders Subform1].Form.[OrderID]

strSQL = "INSERT INTO tbl_Change_Request_Header " _
& "( OrderID, CustomerID, EmployeeID, OrderDate, " _
& "RequiredDate, ShippedDate, ShipVia, Freight, " _
& "ShipName, ShipAddress, ShipCity, ShipRegion, " _
& "ShipPostalCode, ShipCountry ) " _
& "SELECT OrderID, CustomerID, EmployeeID, " _
& "OrderDate, RequiredDate, ShippedDate, ShipVia, " _
& "Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " _
& "ShipPostalCode, ShipCountry " _
& "From Orders " _
& "WHERE Orders.OrderID= " & lngOrderID & ";"

'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdAddChangeRequest_Click..."
Resume ExitProc
End Sub


This will append the selected order to your tbl_Change_Request_Header table.
Of course, you will only be able to append a given record one time, without
causing a primary key violation. Perhaps the best thing (?) would be to run
code after this append operation to open a form to allow the record to be
modified. You could then easily include a hidden timestamp field. Does this
help get you started?


Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


"Greg K." wrote:

> The AS/400 data is read only. The change request (once I can get it in a
> table) would be generated as a report that will get emailed to the
> appropriate shipping location to notify them of the change. The documents
> they use to ship from have already been printed, and simply changing the
> data in the AS/400 would not notify them of the change.
>
> I know how to call up all the information I need with forms and subforms
> (the issue is not the ability to find or display info, it is the writing it
> to a separate table that I am having trouble with.)
>
>
> "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
> news:123D0457-FA73-4B7F-B145-(E-Mail Removed)...
> > Hi Greg,
> >
> > I would create an unbound Query By Form (QBF) form, that allowed me to
> > easily find one or more customers, based on various attributes such as
> > customer name, customer number, city, state, postal code, etc. You could
> even
> > use the customer's telephone number as a valid search criteria. The
> records
> > that match your search would be displayed in a read-only subform (just
> enough
> > fields from the customer table to allow someone to positively identify the
> > customer). You can then activate a form that opens up to this customer and
> > displays all of their orders, by double-clicking anywhere within the
> subform.
> > The new form that opens would be modelled after the Customer Orders form
> > found in the Northwind sample database (Northwind.mdb), which is likely on
> > your hard drive already.
> >
> > The part of your request that I find a little confusing is the following
> > statement:
> >
> > "The data that comes from the AS/400 remains unchanged and would be
> > available for the next change request."
> >
> > Why would you not want to update the AS/400 with the change request
> > information?
> >
> > I have a sample database with a QBF form that I can send to you if you
> send
> > me a private request by e-mail. My e-mail address is available at the
> bottom
> > of the Contributor's page indicated in my signature below. Whatever you
> do,
> > please do not post your real e-mail address (or mine) to a newsgroup
> message.
> >
> > Tom
> >> __________________________________________
> >
> > "Greg K." wrote:
> >
> > > I'm not quite sure how to start going about this. I will try to explain
> > > what I am trying to accomplish.
> > >
> > > Basically, the database pulls in the Order, Customer, and Product info
> from
> > > our AS/400. I want the sales people who are requesting a change to the
> > > order (either ship date, cancelling product/order, changing quantities,
> etc)
> > > to be able to pull up their desired customer on a form. The subform
> will
> > > show all orders for that customer. The salesperson can then select
> which
> > > order(s) they want to change. When selecting the order(s), a second
> subform
> > > will appear showing all the lines on the order(s) at which point they
> can
> > > select individual lines ... if needed (some changes affect the entire
> > > order.) When they have selected all the items they want to change and
> have
> > > entered what the new ship date, qty, etc. is, then the data should be
> added
> > > to a table with a unique Change Request ID. The data that comes from
> the
> > > AS/400 remains unchanged and would be available for the next change
> request.
> > >
> > > I have a database with the following tables:
> > >
> > > tbl_Change_Request_Header
> > > tbl_Change_Request_Order_Details
> > > tbl_Cust_Mast (from AS/400)
> > > tbl_Item_Master (from AS/400)
> > > tbl_Order_Header (from AS/400)
> > > tbl_Order_Details (from AS/400)
> > >
> > > I hope this explanation is as clear as mud.
> > > Any help you can supply would be greatly appreciated.
> > >
> > > If you need more info, please let me know
> > >
> > > Greg K
> > >
> > >
> > >
>
>
>

Tom,

Yes, I think this will give me a place to start ... thanks for the info.


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:43B76E2E-99CA-4D17-BA9A-(E-Mail Removed)...
> Hi Greg,
>
> You should be able to add a command button to the form that runs an append
> query. The append query would append the current record to your
> tbl_Change_Request_Header and tbl_Change_Request_Order_Details tables.
Quick
> question: Would you want to append all order detail records to this table,
or
> only those that were changed?
>
> In any event, you will want to append the data to the
> tbl_Change_Request_Header table first, before appending the related child
> records, if you have referential integrity (RI) enforced, which is always
a
> good idea.
>
> Something like this (only marginally tested Air Code for the Northwind
> Customer Orders form):
>
> Copy the structure only of the Orders table in Northwind to a new table
> named "tbl_Change_Request_Header". Add a command button to the main form
> named "cmdAddChangeRequest". Add the following code for the click event of
> this command button:
>
> Option Compare Database
> Option Explicit
>
> Private Sub cmdAddChangeRequest_Click()
> On Error GoTo ProcError
>
> Dim strSQL As String
> Dim lngOrderID As Long
>
> lngOrderID = Me.[Customer Orders Subform1].Form.[OrderID]
>
> strSQL = "INSERT INTO tbl_Change_Request_Header " _
> & "( OrderID, CustomerID, EmployeeID, OrderDate, " _
> & "RequiredDate, ShippedDate, ShipVia, Freight, " _
> & "ShipName, ShipAddress, ShipCity, ShipRegion, " _
> & "ShipPostalCode, ShipCountry ) " _
> & "SELECT OrderID, CustomerID, EmployeeID, " _
> & "OrderDate, RequiredDate, ShippedDate, ShipVia, " _
> & "Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " _
> & "ShipPostalCode, ShipCountry " _
> & "From Orders " _
> & "WHERE Orders.OrderID= " & lngOrderID & ";"
>
> 'Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
>
> ExitProc:
> Exit Sub
> ProcError:
> MsgBox "Error " & Err.Number & ": " & Err.Description, _
> vbCritical, "Error in procedure cmdAddChangeRequest_Click..."
> Resume ExitProc
> End Sub
>
>
> This will append the selected order to your tbl_Change_Request_Header
table.
> Of course, you will only be able to append a given record one time,
without
> causing a primary key violation. Perhaps the best thing (?) would be to
run
> code after this append operation to open a form to allow the record to be
> modified. You could then easily include a hidden timestamp field. Does
this
> help get you started?
>
>
> Tom
>
> http://www.access.qbuilt.com/html/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
>
> "Greg K." wrote:
>
> > The AS/400 data is read only. The change request (once I can get it in
a
> > table) would be generated as a report that will get emailed to the
> > appropriate shipping location to notify them of the change. The
documents
> > they use to ship from have already been printed, and simply changing the
> > data in the AS/400 would not notify them of the change.
> >
> > I know how to call up all the information I need with forms and subforms
> > (the issue is not the ability to find or display info, it is the writing
it
> > to a separate table that I am having trouble with.)
> >
> >
> > "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
> > news:123D0457-FA73-4B7F-B145-(E-Mail Removed)...
> > > Hi Greg,
> > >
> > > I would create an unbound Query By Form (QBF) form, that allowed me to
> > > easily find one or more customers, based on various attributes such as
> > > customer name, customer number, city, state, postal code, etc. You
could
> > even
> > > use the customer's telephone number as a valid search criteria. The
> > records
> > > that match your search would be displayed in a read-only subform (just
> > enough
> > > fields from the customer table to allow someone to positively identify
the
> > > customer). You can then activate a form that opens up to this customer
and
> > > displays all of their orders, by double-clicking anywhere within the
> > subform.
> > > The new form that opens would be modelled after the Customer Orders
form
> > > found in the Northwind sample database (Northwind.mdb), which is
likely on
> > > your hard drive already.
> > >
> > > The part of your request that I find a little confusing is the
following
> > > statement:
> > >
> > > "The data that comes from the AS/400 remains unchanged and would be
> > > available for the next change request."
> > >
> > > Why would you not want to update the AS/400 with the change request
> > > information?
> > >
> > > I have a sample database with a QBF form that I can send to you if you
> > send
> > > me a private request by e-mail. My e-mail address is available at the
> > bottom
> > > of the Contributor's page indicated in my signature below. Whatever
you
> > do,
> > > please do not post your real e-mail address (or mine) to a newsgroup
> > message.
> > >
> > > Tom
> > >> __________________________________________
> > >
> > > "Greg K." wrote:
> > >
> > > > I'm not quite sure how to start going about this. I will try to
explain
> > > > what I am trying to accomplish.
> > > >
> > > > Basically, the database pulls in the Order, Customer, and Product
info
> > from
> > > > our AS/400. I want the sales people who are requesting a change to
the
> > > > order (either ship date, cancelling product/order, changing
quantities,
> > etc)
> > > > to be able to pull up their desired customer on a form. The subform
> > will
> > > > show all orders for that customer. The salesperson can then select
> > which
> > > > order(s) they want to change. When selecting the order(s), a second
> > subform
> > > > will appear showing all the lines on the order(s) at which point
they
> > can
> > > > select individual lines ... if needed (some changes affect the
entire
> > > > order.) When they have selected all the items they want to change
and
> > have
> > > > entered what the new ship date, qty, etc. is, then the data should
be
> > added
> > > > to a table with a unique Change Request ID. The data that comes
from
> > the
> > > > AS/400 remains unchanged and would be available for the next change
> > request.
> > > >
> > > > I have a database with the following tables:
> > > >
> > > > tbl_Change_Request_Header
> > > > tbl_Change_Request_Order_Details
> > > > tbl_Cust_Mast (from AS/400)
> > > > tbl_Item_Master (from AS/400)
> > > > tbl_Order_Header (from AS/400)
> > > > tbl_Order_Details (from AS/400)
> > > >
> > > > I hope this explanation is as clear as mud.
> > > > Any help you can supply would be greatly appreciated.
> > > >
> > > > If you need more info, please let me know
> > > >
> > > > Greg K
> > > >
> > > >
> > > >
> >
> >
> >

Thanks for the info you pass to me but i want to ask how can i start
useing the alert i have try to use it but is giveing me some **** error
so please help me with that okay.


Greg K. wrote:
> I'm not quite sure how to start going about this. I will try to explain
> what I am trying to accomplish.
>
> Basically, the database pulls in the Order, Customer, and Product info from
> our AS/400. I want the sales people who are requesting a change to the
> order (either ship date, cancelling product/order, changing quantities, etc)
> to be able to pull up their desired customer on a form. The subform will
> show all orders for that customer. The salesperson can then select which
> order(s) they want to change. When selecting the order(s), a second subform
> will appear showing all the lines on the order(s) at which point they can
> select individual lines ... if needed (some changes affect the entire
> order.) When they have selected all the items they want to change and have
> entered what the new ship date, qty, etc. is, then the data should be added
> to a table with a unique Change Request ID. The data that comes from the
> AS/400 remains unchanged and would be available for the next change request.
>
> I have a database with the following tables:
>
> tbl_Change_Request_Header
> tbl_Change_Request_Order_Details
> tbl_Cust_Mast (from AS/400)
> tbl_Item_Master (from AS/400)
> tbl_Order_Header (from AS/400)
> tbl_Order_Details (from AS/400)
>
> I hope this explanation is as clear as mud.
> Any help you can supply would be greatly appreciated.
>
> If you need more info, please let me know
>
> Greg K

Donsegxzy,

What are you talking about? Sorry, I don't understand your statement. It
doesn't appear related to this thread in any way.


Tom

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"donsegxzy" wrote:

> Thanks for the info you pass to me but i want to ask how can i start
> useing the alert i have try to use it but is giveing me some **** error
> so please help me with that okay.