Append Invoice

[Lez]

Hi Guys,

I have an got an invoice application and want to be able to part dispatch
items and append a letter to each invoice , so for each dispatch I can
append a letter to the end of the invoice.

The situation is this:

An order invoice is raised for 6 items.

The order is to be completed in 3 shipments, so the first invoice would go
out with 1001A
Next items ship invoice would be 1001B
Final shipment is done invoice is 1001C

Naturally would want to reuse the append characters

I have tried this solution suggested by Karl Dewey, which appends a number
on the the string, but does the same for the next dispatched invoice:

Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1)

I needs a way to know what the last appended character/number and add the
next in sequence. (The invoice number is auto number)

Setup is SQL 2005 BE with Access 2007 FE

Many thanks
Lez

 

[Clifford Bass]

Hi Lez,

Let me see if I understand this correctly. You have invoices with a
particular invoice number (1001) and there are multiple shippings, which are
given a separate "ship invoice" number? What exactly is your invoice / ship
invoice table structure? One table? Two tables? I am thinking it should be
two.

tblInvoices
InvoiceID
etc.

tblShipInvoices
InvoiceID (linked to tblInvoices.InvoiceID)
ShipID (text field)

Then you can use something like this to determine the next ShipID if
the InvoiceID is numeric:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = " & [txtInvoiceID]), "A")

Or if the InvoiceID is text:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & [txtInvoiceID] &
""""), "A")

Or if the InvoiceID is text and might contain the quote symbol:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ &
Replace([txtInvoiceID], """", """""") & """"), "A")

If that does not help, post back with the pertinant information such as
where this is being done (on a form, in a query) and the table(s) and
field(s) involved.

Clifford Bass

 

[Lez]

Hi Clifford,

Many thanks and sorry about the cross post, not sure where it should have
been placed.

Anyway, to advise on the structure

I have a tblInvoices (MainForm)
invoiceID is autonumber for invoice number (PK)

I have tblinvoiceLineItems: (subform)
with FK invoiceID, this table field fldDespatchStatus (true/false)

The invoice number is simply being added within a query applied to produce a
printed invoice/dispatch note. Ideally, I would like to add the appended
letter to be saved as part of the line items of the invoice to identify
which items where shipped with what appended letter should any issues arise
at a later date.

i.e
Invoice 1001-A
Product 1
Product 2
Product 3

Invoice 1001-B
Product 4
Product 5
Product 6

I looked at your suggestion, and sort of get it, but not sure:

A: How can I reuse the letters for different invoices?
B: what value would I save it table ship?

Hopefully this give you a bit more info on what I am looking for.

Regards
Lez

Hi Lez,

Let me see if I understand this correctly. You have invoices with a
particular invoice number (1001) and there are multiple shippings, which
are
given a separate "ship invoice" number? What exactly is your invoice /
ship
invoice table structure? One table? Two tables? I am thinking it should
be
two.

tblInvoices
InvoiceID
etc.

tblShipInvoices
InvoiceID (linked to tblInvoices.InvoiceID)
ShipID (text field)

Then you can use something like this to determine the next ShipID if
the InvoiceID is numeric:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = " & [txtInvoiceID]),
"A")

Or if the InvoiceID is text:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ & [txtInvoiceID] &
""""), "A")

Or if the InvoiceID is text and might contain the quote symbol:

Nz(DMax("ShipID", "tblShipInvoices", "InvoiceID = """ &
Replace([txtInvoiceID], """", """""") & """"), "A")

If that does not help, post back with the pertinant information such
as
where this is being done (on a form, in a query) and the table(s) and
field(s) involved.

Clifford Bass

Hi Guys,

I have an got an invoice application and want to be able to part dispatch
items and append a letter to each invoice , so for each dispatch I can
append a letter to the end of the invoice.

The situation is this:

An order invoice is raised for 6 items.

The order is to be completed in 3 shipments, so the first invoice would
go
out with 1001A
Next items ship invoice would be 1001B
Final shipment is done invoice is 1001C

Naturally would want to reuse the append characters

I have tried this solution suggested by Karl Dewey, which appends a
number
on the the string, but does the same for the next dispatched invoice:

Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1)

I needs a way to know what the last appended character/number and add the
next in sequence. (The invoice number is auto number)

Setup is SQL 2005 BE with Access 2007 FE

Many thanks
Lez

 

[Clifford Bass]

Hi Lez,

Okay, that can work. So, going from what you currently have, you need
to add a shipment ID column to the tblInvoiceLineItems table, in which you
will store for each item an A or B or whatnot. One way to deal with that,
including the assignment of the next shipment ID may be to add a third table
that holds the main shipment data for each invoice. Things such as invoice
ID, shipment ID, shipment date, notes, tracking number, and so on. Then it
would be related to the tblInvoiceLineItems through the invoice ID and the
shipment ID.

tblinvoiceLineItems
InvoiceID
ItemID
ShipmentID
...

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

This assumes that you ship all of a particular item and do not divide
quantities. If you divide quantities (i.e. ordered 10, shipped 3, 7 back
ordered) you will need to do something a bit different. You will need a new
form (or subform) for creating shipments. When you go to save a new
shipment, you can use your shipments form's Before Update event to determine
the shipment ID to use:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' 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.NewRecord Then
varShipmentID = DMax("ShipmentID", "tblShipments", _
"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

Hope that helps,

Clifford Bass

 

[KARL DEWEY]

What I posted should not have appended a number on the the string but
increment the sufix letter.

Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1)

I just tested it and it works.

 

[Lez]

Hi Clifford,

Sorry for not responding to this sooner, been sorting some other stuff out.
To confirm the process I need to apply here is the ability to part ship an
order and Append the invoice number with the shipping character i.e.

Original order number = 1001

6 items ordered

first shipment dispatches 3 items invoice number = 1001-A

next shipment sends the next 3 items, invoice number = 1001-B

So just wondering if this suggestion would work?

Regards
Lez

Hi Lez,

Okay, that can work. So, going from what you currently have, you need
to add a shipment ID column to the tblInvoiceLineItems table, in which you
will store for each item an A or B or whatnot. One way to deal with that,
including the assignment of the next shipment ID may be to add a third
table
that holds the main shipment data for each invoice. Things such as
invoice
ID, shipment ID, shipment date, notes, tracking number, and so on. Then
it
would be related to the tblInvoiceLineItems through the invoice ID and the
shipment ID.

tblinvoiceLineItems
InvoiceID
ItemID
ShipmentID
...

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

This assumes that you ship all of a particular item and do not divide
quantities. If you divide quantities (i.e. ordered 10, shipped 3, 7 back
ordered) you will need to do something a bit different. You will need a
new
form (or subform) for creating shipments. When you go to save a new
shipment, you can use your shipments form's Before Update event to
determine
the shipment ID to use:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' 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.NewRecord Then
varShipmentID = DMax("ShipmentID", "tblShipments", _
"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

Hope that helps,

Clifford Bass

Hi Clifford,

Many thanks and sorry about the cross post, not sure where it should have
been placed.

Anyway, to advise on the structure

I have a tblInvoices (MainForm)
invoiceID is autonumber for invoice number (PK)

I have tblinvoiceLineItems: (subform)
with FK invoiceID, this table field fldDespatchStatus (true/false)

The invoice number is simply being added within a query applied to
produce a
printed invoice/dispatch note. Ideally, I would like to add the appended
letter to be saved as part of the line items of the invoice to identify
which items where shipped with what appended letter should any issues
arise
at a later date.

i.e
Invoice 1001-A
Product 1
Product 2
Product 3

Invoice 1001-B
Product 4
Product 5
Product 6

I looked at your suggestion, and sort of get it, but not sure:

A: How can I reuse the letters for different invoices?
B: what value would I save it table ship?

Hopefully this give you a bit more info on what I am looking for.

Regards
Lez

 

[Clifford Bass]

Hi Lez,

It should. You may need to tweak it depending on your needs. Note
that your A, B and whatnot are stored in a separate field. If you want to
report the expanded "invoice numbers" as a single item, just concatenate the
InvoiceID and ShipmentID fields (for example: InvoiceID & "-" & ShipmentID).
How about you give it a try in a development copy of the database?

Clifford Bass

 

[Lez]

Ok Thanks Clifford,

I have used your code and it works, 'kind of'

As all the items have already been added to an invoice. this does not work
for me, what I am thinking is however, as I have a select box to choose the
items I am to ship, this would be the ideal option to append the shippingID
to the invoice.

So when I select the item I would want to the A, B etc at that point.

My thoughts are that if I change that after update of the check box it will
add the letter, but I would assume at this point, without trying, each row
would be appended a letter.

I will try and advise.

Any thoughts appreciated.

Regards
Lez

 

[Lez]

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

 

[Lez]

Hi Karl,

To advise, my invoiceID is an autonumber field (int) and so adds a number
rather than a letter.

I did try it using a text field and it works as you suggested. I might come
back to this again in the future.

Many thanks
Lez

What I posted should not have appended a number on the the string but
increment the sufix letter.

Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1)

I just tested it and it works.

--
Build a little, test a little.

Hi Guys,

I have an got an invoice application and want to be able to part dispatch
items and append a letter to each invoice , so for each dispatch I can
append a letter to the end of the invoice.

The situation is this:

An order invoice is raised for 6 items.

The order is to be completed in 3 shipments, so the first invoice would
go
out with 1001A
Next items ship invoice would be 1001B
Final shipment is done invoice is 1001C

Naturally would want to reuse the append characters

I have tried this solution suggested by Karl Dewey, which appends a
number
on the the string, but does the same for the next dispatched invoice:

Left([ship invoice], 4) & Chr(Asc(Right([ship invoice], 1))+1)

I needs a way to know what the last appended character/number and add the
next in sequence. (The invoice number is auto number)

Setup is SQL 2005 BE with Access 2007 FE

Many thanks
Lez

 

[Clifford Bass]

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]

Hi Clifford,

Yes thanks for that, that is my thought now, I will let you know how I get
on.

Many thanks
Lez

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

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

 

[Lez]

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

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

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

 

[Clifford Bass]

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]

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

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

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]

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]

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]

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

 

[Lez]

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,

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