how to update so that foreign matches primary key

[jaeezzy]

Hi, I've two tables:

Dispatch
-----------------------------------------------------------------------
DispatchID | CustomerID | DispatchDate | Service | ConsignmentID
-----------------------------------------------------------------------
1 1 2008-07-04 Zippy AZ432-566

DispatchItem
-----------------------------------------------------------------------
DispatchItemID | DispatchID | OrderItemID | NbrDispatched
-----------------------------------------------------------------------
1 1 1 1

In the above tables: In Dispatch, DispatchID is autonumber and is a primary key, CustomerID is a foreign key and can be repeated, DispatchDate is the date of the dispatch, Service is the name of service used to dispatch items and ConsignmentID is just some ID and can be null.

Similarly, in DispatchItem, DispatchItemID is autonumber and a primary key, DispatchID is a foreign key, OrderItemID is a foreign key and NbrDispatched is the number of item(s) dispatched.

Now, when new items are dispatched I inserted CustomerID, DispatchDate and Service and left the ConsignmentID as it can be null and DispatchID as it increments automatically. Now, the problem I'm facing is, I want to insert details in DispatchItem as well and, no where in the world, I could figure out how I can insert the same DispatchID that got inserted while inserting other details in Dispatch table in DispatchID column in DispatchItem table. Also, I'm wondering how I can include query in insert statement for OrderItemID coz it has to be the one where OrderNbr = ? AND ItemID = ? From other table. I'm using VBA for the insert and update from a form. Thanks.

 

[neileg]

The usual way is to use a form/subform set up. Access manages the parent/child links and populates the FK for you.

 

[jaeezzy]

The usual way is to use a form/subform set up. Access manages the parent/child links and populates the FK for you.

Thanks for the reply neileg. As I'm new to access can you please let me know how is that done? I haven't made any form/subform for it coz it will be direct going to the tables. Thanks...

 

[neileg]

I don't understand. You've said you're using a form. To be honest, you could hack this in VBA but one of Access's strengths is the form/sub form setup.

I've knocked up a quick sample.

 

[boblarson]

Tcoz it will be direct going to the tables.

What does that statement mean? Is this manual entry directly in tables (which if it is then it shouldn't be - no user should be working in tables or queries manually) or is it an import process?

 

[jaeezzy]

Hi neileg, thanks for the effort. Ya I'm using form and it has a subform which will display users which and how many items are due for a particular order number. A form also has the following to record dispatch details:
>Two text boxes for getting dispatch date and consignment ID from users.
>One combo box for selecting service for dispatching items.
>Number of dispatch will be 1 automatically. ( I know its not the way but just to get it work)

Now, when users click "Save" button, details from the above controls in the form will be used to update or insert new row in required tables. Here's how I've tried it and works fine for me:

================================================================
Private Sub Command44_Click()
Dim intinput As Integer
Dim ordernbr As String
Dim custID As Integer
Dim dispatchdate As Date
Dim service As String
Dim consignment As String
Dim dispatch As Integer
strMsg = "Preparing to save dispatch details.."
strMsg = strMsg & "Please confirm that all dispatch details are correct."
intinput = MsgBox(strMsg, vbOKCancel + vbQuestion)
If intinput = vbCancel Then
Cancel = True
Else
ordernbr = Me.txtordernbr (This is selected order number)
If ordernbr = "O1000002" Then
custID = 2
End If
If ordernbr = "O1000003" Then
custID = 3
End If
If ordernbr = "O1000004" Then
custID = 4
End If
dispatchdate = Me.txtdispatchdate
service = Me.comboservice
If Not IsNull(txtconsignment) Then
consignment = Me.txtconsignment
End If
dispatch = 1
CurrentDb.Execute "INSERT INTO [tblDispatch] ([CustomerID],[DispatchDate],[Service],[ConsignmentID]) VALUES (" & custID & ", '" & dispatchdate & "', '" & service & "', '" & consignment & "')", dbFailOnError
CurrentDb.Execute "UPDATE [tblOrderItem] SET [NbrDispatched] = [NbrDispatched]+" & dispatch & " WHERE [OrderNbr] = '" & ordernbr & "'", dbFailOnError
CurrentDb.Execute "UPDATE [tblItem] SET [StockHolding] = [StockHolding]-" & dispatch & " WHERE [ItemID] IN (SELECT [ItemID] FROM [tblOrderItem] WHERE [OrderNbr] = '" & ordernbr & "')", dbFailOnError
Me.Combo2.Requery
Me.subformorderdetails.Requery
Me.subformdispatch.Requery
Me.subformhistory.Requery
End If
End Sub
================================================================

This code inserts record in tblDispatch but doesn't include DispatchID as it is autonumber and gets incremented automatically. Similarly, it also updates tables: 1) tblOrderItem and 2) tblItem. All good so far. I thing more is, I have to insert details in tblDispatchItem as well and this is where the problem lies (for me).

Problems:
- How can I insert the same DispatchID from tblDispatch in DispatchID column of tblDispatchItem table with insert sql statement in the code coz it was auto-incremented.
- Also, OrderItemID will have to be of the selected order number and ItemID from tblOrderItem. tblOrderItem is mentioned later.
- One new one, how can I compare the date inserted by user in the text box with the one in the table when the order was made.

OrderItem:
---------------------------------------------------------------------------------
OrderItemID | OrderNbr | ItemID | Price | NbrRequested | NbrDispatched
---------------------------------------------------------------------------------
1 | O1000001 | 1 | $120 | 2 | 1
2 | O1000001 | 2 | $130 | 1 | 1

I need the OrderItemID from the above table to include in the tblDispatchItem where NbrDispatched has been updated with the click of the save button. Hope I could explain what I'm trying to do with this. Thanks a lot...

 

[neileg]

What you are proposing is wrong headed. If you use an unbound form you can't capture the autonumber that is created when you append (well you probably can but it's a lot of work). Why not use a bound form, so much easier?

 

[jaeezzy]

ya,maybe it would have been easier but i have no choice its part of my assignment. anyways thanks for the help..

 

[neileg]

You could use DMax() to find the largest PK value after the append, but that would only work in a single user application. I guess you could use DLookup() to get the PK that matched all your variables providing you can guarantee these are unique.

But if it's an assignment, you'll have to do the work!