Append to Table Using Subforms based on external data

  • Thread starter Thread starter Greg K.
  • Start date Start date
G

Greg K.

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
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.)
 
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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Tom,

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


Tom Wickerath said:
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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Greg K. said:
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.)


could
even you
do, info
from quantities,
etc) they
can and
have be
added from
the
Click to expand...
Click to expand...
 
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 shit error
so please help me with that okay.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform causing duplicate records? 7
Help with simple sales data base 6
Subform to Parent Form 2
Updating tables when you input new data 0
looking for strategy for subForm filter 6
Send Subform data through email 0
Pushing data Orientation 3
Increment a number based on instances in another table 1

Back
Top