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 |
#11
|
|||
|
|||
Append Invoice
Hi Lez,
Yeah, I can see that basing it off of the data in the line items table instead of a separate shipments table will cause bad results. As an aside, one additional thing you may want to do is to remove the shipment ID when the check box is unchecked. Back to the issue of increasing shipment IDs for each item; is there any way to know, based on the existing data, when someone is clicking the check box, that a particular item belongs to a particular shipment? I suspect not. Maybe what you should do is create a button on the main form that gets the next shipment ID, using the same process, and places it in a text box on the main form. Then when the user checks a particular item, it just uses the shipment ID value in the box on the main form. So when someone wants to initiate a new shipment, they first click that button before checking any of the line items. Hope that helps, Clifford Bass "Lez" wrote: Hi Clifford, To advise, I have tried the update as suggested and modified the code as below: Private Sub ChkShip_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.ChkShip = True Then varShipmentID = DMax("ShipmentID", "tblInvoicelineitems", _ "invoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub As expected, each row is now appended with a letter, so need to think how I can just add the first letter to regardless of the number of rows selected, then when we ship the balance of the order I can append the next number to the invoice. I am just wondering if I should just do this as part of the query for the items I am selecting, so we simply append the letter to the invoiceID, so just add a shipmentID to the tblInvoice? Regards Lez |
#12
|
|||
|
|||
Append Invoice
Hi Clifford,
Yes thanks for that, that is my thought now, I will let you know how I get on. Many thanks Lez "Clifford Bass" wrote in message ... Hi Lez, Yeah, I can see that basing it off of the data in the line items table instead of a separate shipments table will cause bad results. As an aside, one additional thing you may want to do is to remove the shipment ID when the check box is unchecked. Back to the issue of increasing shipment IDs for each item; is there any way to know, based on the existing data, when someone is clicking the check box, that a particular item belongs to a particular shipment? I suspect not. Maybe what you should do is create a button on the main form that gets the next shipment ID, using the same process, and places it in a text box on the main form. Then when the user checks a particular item, it just uses the shipment ID value in the box on the main form. So when someone wants to initiate a new shipment, they first click that button before checking any of the line items. Hope that helps, Clifford Bass "Lez" wrote: Hi Clifford, To advise, I have tried the update as suggested and modified the code as below: Private Sub ChkShip_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.ChkShip = True Then varShipmentID = DMax("ShipmentID", "tblInvoicelineitems", _ "invoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub As expected, each row is now appended with a letter, so need to think how I can just add the first letter to regardless of the number of rows selected, then when we ship the balance of the order I can append the next number to the invoice. I am just wondering if I should just do this as part of the query for the items I am selecting, so we simply append the letter to the invoiceID, so just add a shipmentID to the tblInvoice? Regards Lez |
#13
|
|||
|
|||
Append Invoice
Hi Clifford,
Ok what I have done is add field 'shipmentcode' to my tblInvoice 'nchar(10)' and using the code below: Private Sub chkDisp_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.chkDisp = True Then varShipmentID = DMax("shipmentcode", "tblInvoice", _ "invoiceNumber = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub However, each time I try it I get a Data Mismatch error, yet if I use the same on tblLineitems (the subform) it works without any problems. The datatype for the field is the same in both the tblInvoice and tblLineItems?? Can you suggest anything please Regards Lez "Clifford Bass" wrote in message ... Hi Lez, Yeah, I can see that basing it off of the data in the line items table instead of a separate shipments table will cause bad results. As an aside, one additional thing you may want to do is to remove the shipment ID when the check box is unchecked. Back to the issue of increasing shipment IDs for each item; is there any way to know, based on the existing data, when someone is clicking the check box, that a particular item belongs to a particular shipment? I suspect not. Maybe what you should do is create a button on the main form that gets the next shipment ID, using the same process, and places it in a text box on the main form. Then when the user checks a particular item, it just uses the shipment ID value in the box on the main form. So when someone wants to initiate a new shipment, they first click that button before checking any of the line items. Hope that helps, Clifford Bass "Lez" wrote: Hi Clifford, To advise, I have tried the update as suggested and modified the code as below: Private Sub ChkShip_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.ChkShip = True Then varShipmentID = DMax("ShipmentID", "tblInvoicelineitems", _ "invoiceID = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub As expected, each row is now appended with a letter, so need to think how I can just add the first letter to regardless of the number of rows selected, then when we ship the balance of the order I can append the next number to the invoice. I am just wondering if I should just do this as part of the query for the items I am selecting, so we simply append the letter to the invoiceID, so just add a shipmentID to the tblInvoice? Regards Lez |
#14
|
|||
|
|||
Append Invoice
Hi Lez,
I presume you are now setting the shipment code somewhere in the main invoice form. If that is the case, in the chkDisp_AfterUpdate you would only need to get the value you currently have in the main invoice form. Something like: Private Sub chkDisp_AfterUpdate() If [chkDisp].Value Then [txtShipmentID].Value = _ [Forms!]![Invoice Form Name]![txtShipmentCode].Value Else [txtShipmentID].Value = Null End If End Sub Clifford Bass "Lez" wrote: Hi Clifford, Ok what I have done is add field 'shipmentcode' to my tblInvoice 'nchar(10)' and using the code below: Private Sub chkDisp_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.chkDisp = True Then varShipmentID = DMax("shipmentcode", "tblInvoice", _ "invoiceNumber = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub However, each time I try it I get a Data Mismatch error, yet if I use the same on tblLineitems (the subform) it works without any problems. The datatype for the field is the same in both the tblInvoice and tblLineItems?? Can you suggest anything please Regards Lez |
#15
|
|||
|
|||
Append Invoice
Hi Clifford,
Sorry for the long delay in coming back to you on this, had some other stuff to get on with and need to return to this now. I have tried your example but am completely lost I kind of got the previous example, and as explained it worked in the sub form but not the main form, sorry if this is a dumb question, but just cannot get my head around your example. Regards Lez "Clifford Bass" wrote in message ... Hi Lez, I presume you are now setting the shipment code somewhere in the main invoice form. If that is the case, in the chkDisp_AfterUpdate you would only need to get the value you currently have in the main invoice form. Something like: Private Sub chkDisp_AfterUpdate() If [chkDisp].Value Then [txtShipmentID].Value = _ [Forms!]![Invoice Form Name]![txtShipmentCode].Value Else [txtShipmentID].Value = Null End If End Sub Clifford Bass "Lez" wrote: Hi Clifford, Ok what I have done is add field 'shipmentcode' to my tblInvoice 'nchar(10)' and using the code below: Private Sub chkDisp_AfterUpdate() ' Assumes the invoice ID is in a text box named "txtInvoiceID". ' Assumes the shipment ID will go in a text box named "txtShipmentID". ' First shipment gets "A". Allows for values up through the end of the ' UNICODE range of characters. (More than you will ever need!) Dim lngShipmentID As Long Dim varShipmentID As Variant If Me.chkDisp = True Then varShipmentID = DMax("shipmentcode", "tblInvoice", _ "invoiceNumber = " & [txtInvoiceID].Value) If IsNull(varShipmentID) Then [txtshipmentID].Value = "A" Else lngShipmentID = AscW(varShipmentID) If lngShipmentID 65535 Then [txtshipmentID].Value = ChrW$(lngShipmentID + 1) Else ' No more possible UNICODE values MsgBox "No more shipments possible" Cancel = True End If End If End If End Sub However, each time I try it I get a Data Mismatch error, yet if I use the same on tblLineitems (the subform) it works without any problems. The datatype for the field is the same in both the tblInvoice and tblLineItems?? Can you suggest anything please Regards Lez |
#16
|
|||
|
|||
Append Invoice
Hi Lez,
It may be best for you to explain anew the whole situation as you currently have it in development. Briefly, what forms / subforms, what they have on them that is pertinent to the issue in terms of text boxes, buttons and check boxes and the code related to those text boxes, buttons and check boxes. Then I am not looking back through all of the posts trying to piece it all together and getting an incorrect view of the current situation. Indicate what is currently happening, correctly and/or incorrectly, and your desired goal. Clifford Bass "Lez" wrote: Hi Clifford, Sorry for the long delay in coming back to you on this, had some other stuff to get on with and need to return to this now. I have tried your example but am completely lost I kind of got the previous example, and as explained it worked in the sub form but not the main form, sorry if this is a dumb question, but just cannot get my head around your example. Regards Lez |
#17
|
|||
|
|||
Append Invoice
I have an application to produce an invoices.
An order is requested and and an invoice number is issued, say 1001 The order is made up of 6 line items and the order is to be sent out in 3 shipments. So for the first shipment I want to append a letter to signify that this is the the same order but to be completed over a number of deliveries so the first shipment would be 1001-A , the second would be 1001-B and the final shipment would be 1001-C. If their was more than 3 shipments the letters would simply continue until the shipments have been completed. This is design as mainForm (frmInvoice) with subForm for line items(frmInvoiceLineItems) The main form has a text box txtShipmentID with a check box (chkDisp) to check if the order is to be split into a number of shipments. The code behind the check box is: Private Sub chkDisp_AfterUpdate() If [chkDisp].Value Then [txtInvoiceID].Value = _ [Forms!]![frmInvoice]![txtshipmentID].Value Else [txtInvoiceID].Value = Null End If End Sub The subform line items has a check box (chkShip) for each item on the order, by selecting this it would indicate that this item is to be shipped. There is not code behind this at this time. I am using a MSSQL BE with Access 2007 FE Many thanks Lez "Clifford Bass" wrote in message ... Hi Lez, It may be best for you to explain anew the whole situation as you currently have it in development. Briefly, what forms / subforms, what they have on them that is pertinent to the issue in terms of text boxes, buttons and check boxes and the code related to those text boxes, buttons and check boxes. Then I am not looking back through all of the posts trying to piece it all together and getting an incorrect view of the current situation. Indicate what is currently happening, correctly and/or incorrectly, and your desired goal. Clifford Bass "Lez" wrote: Hi Clifford, Sorry for the long delay in coming back to you on this, had some other stuff to get on with and need to return to this now. I have tried your example but am completely lost I kind of got the previous example, and as explained it worked in the sub form but not the main form, sorry if this is a dumb question, but just cannot get my head around your example. Regards Lez |
#18
|
|||
|
|||
Append Invoice
Hi Lez,
Some questions. When do you want to assign the shipment IDs? All at once when the invoice is being created? Or all later on? Or some at creation and others later? What do you want to happen when the chkDisp is cleared? Remove all existing shipment IDs? Any other information about how this is all to work? Clifford Bass |
#19
|
|||
|
|||
Append Invoice
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" wrote in message ... Hi Lez, Some questions. When do you want to assign the shipment IDs? All at once when the invoice is being created? Or all later on? Or some at creation and others later? What do you want to happen when the chkDisp is cleared? Remove all existing shipment IDs? Any other information about how this is all to work? Clifford Bass |
#20
|
|||
|
|||
Append Invoice
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 |
Thread Tools | |
Display Modes | |
|
|