Append Invoice

[Lez]

Hi Clifford,

Sorry for the delay in getting back to you on this, got caught up in another
issue which had higher priority for the client.

OK, to confirm, I am happy to assign items not split a shipmentID of A.

Can I just ask the following questions:

1. How do I create a compound primary key? this is my table scheme:

CREATE TABLE [dbo].[DS_Shipment](
[shipmentID] [int] IDENTITY(1,1) NOT NULL,
[invoiceID] [int] NULL,
[shipmentdate] [smalldatetime] NULL,
[shipcode] [nchar](10) NULL,
[notes] [nvarchar](255) NULL,
[trackingnumber] [nvarchar](20) NULL,
CONSTRAINT [PK_DS_Shipment] PRIMARY KEY CLUSTERED
(

2. I have changed the control source of the txtshipmentID as requested and
added a button to create a shipment ID, but do not understand the shipment
form? I already have a the form open at the this point (frmInvoice), are you
saying create a NEW form (frmShipment) to create the shipmentID? if so what
field do I add is it the compound PK?

I think I get what you are saying as I type this it is becoming clearer,
will do a search on creating a compound PK whilst I await your reply and
will post my update if I resolve before I hear back from you.

Many thanks
Lez

Hi Lez,

Here is what I think. I agree, you will need a separate table to
record the shipment information. But it would only need to hold things
like
the invoice ID, the shipment ID, the date shipped and anything else
specific
to the shipment such as shipper and tracking number. It would not need to
hold invoice items as that would be duplicating information. You will
need
to decide how you want to deal with invoices that are not split. Do they
all
get assigned a shipment ID of A? Let us assume so as it will simplify
things. So your shipment table (repeated from before):

tblShipments (compound primary key of InvoiceID, ShipmentID)
InvoiceID
ShipmentID
ShipmentDate
Notes
ShippingMethodCode
TrackingNumber
.....

Set your main table forms shipment ID text box to something like:

=DMax("ShipmentID", "tblShipments", "InvoiceID = " & [txtInvoiceID])

Get rid of the chkDisp and replace it with a button for creating a new
shipment. That button will open up the shipment form in add mode using
the
current invoice number and a new shipment ID which could be set when the
form
is opened using code similar to what I posted before. When you close the
form and the record is saved, refresh the main form's shipment ID text box
so
it is current. In the subform, when new items are added, default their
shipment IDs to the value on the main form by setting its Default Value
to:

=[Forms]![frmInvoices]![txtShipmentID]

So if it is known to be a split shipment to start with, the user could
add those items for the first shipment, create a new shipment, add those
items, and so on. You could also use your chkShip button to change the
shipment for a particular item. To do so, the user would create a new
shipment and then click on the check boxes for the items in the new
shipment.
So the code for the subform's check box would be something simple like:

Private Sub chkShip_Click()

If Not Me.NewRecord Then
[txtShipmentID].Value = _
[Forms]![frmInvoices]![txtShipmentID].Value
End If

End Sub

There are probably other ways to do it of course. This is only one
and
is subject to refinement. But it should more or less fit your described
situation.

If you decide to go this route and run into troubles with the code,
post back.

Clifford Bass

Hi Clifford,

The shipment ID's need to be created when the items are selected. I would
want to save the shippmentID's, so we can see which items where shipped
with
which shipment ID.

I assume therefore thinking as I type this, I would need to add another
table tblShippment so we can save the invoiceID the shippemntID,
productID's
ad dated shipped for reference.

When we select the chkDisp I want to associate the shipment ID I have a
print Invoice button on the main form that will produce an invoice with
the
line items listed and the now modified invoice on i.e. 1001-A etc.

Regards
Lez

 

[Clifford Bass]

Hi Lez,

Apologies for the lack or response. Besides being preoccupied with
various high priority projects and therefore not logging into the discussion
group to check for posts, Microsoft's system seems to have failed me again
(so what else is new?), and not notified me of your response.

Did you figure it out?

Clifford Bass

 

[Lez]

Hi Clifford,

No worries, thanks for the post. To confirm, yes have sorted it thanks, got
my head around it an working fine thanks

Many thanks for your time and patience.

Regards
Lez

Hi Lez,

Apologies for the lack or response. Besides being preoccupied with
various high priority projects and therefore not logging into the
discussion
group to check for posts, Microsoft's system seems to have failed me again
(so what else is new?), and not notified me of your response.

Did you figure it out?

Clifford Bass

Hi Clifford,

Sorry for the delay in getting back to you on this, got caught up in
another
issue which had higher priority for the client.

OK, to confirm, I am happy to assign items not split a shipmentID of A.

Can I just ask the following questions:

1. How do I create a compound primary key? this is my table scheme:

CREATE TABLE [dbo].[DS_Shipment](
[shipmentID] [int] IDENTITY(1,1) NOT NULL,
[invoiceID] [int] NULL,
[shipmentdate] [smalldatetime] NULL,
[shipcode] [nchar](10) NULL,
[notes] [nvarchar](255) NULL,
[trackingnumber] [nvarchar](20) NULL,
CONSTRAINT [PK_DS_Shipment] PRIMARY KEY CLUSTERED
(

2. I have changed the control source of the txtshipmentID as requested
and
added a button to create a shipment ID, but do not understand the
shipment
form? I already have a the form open at the this point (frmInvoice), are
you
saying create a NEW form (frmShipment) to create the shipmentID? if so
what
field do I add is it the compound PK?

I think I get what you are saying as I type this it is becoming clearer,
will do a search on creating a compound PK whilst I await your reply and
will post my update if I resolve before I hear back from you.

Many thanks
Lez