If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Append Invoice
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 "Clifford Bass" wrote in message ... 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 "Lez" wrote: 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 |
#22
|
|||
|
|||
Append Invoice
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" wrote: 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 |
#23
|
|||
|
|||
Append Invoice
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 "Clifford Bass" wrote in message ... 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|