move record...

[Brook]

good day,

I currently have a frminvoices that I use to record both consignment and
sold invoices.

when I create an invoice, all of my records are unique so therefore no one
record / or serial number can appear on another invoice.

Right now, I have it set up so that on my frminvoicedetails subform I
don't allow for duplicates so that only one item can be on any one invoice...

The problem that I am having is that when I have a consignment invoice and
once of the items sells, and I have to create a sold invoice, I have to go to
the specified consignment invoice delete the item then be able to create my
sold invoice.

Is there a way that I can add it to a sold invoice via a message box
confirmation? or if anyone else has any suggestions i'm open for anything...

Thanks in advance...

Brook

 

[Douglas J. Steele]

Are you saying that you have multiple tables: one for each type of invoice?

Why not just have a single table with a flag in it indicating which type of
invoice it is?

Is your question how to use the response to a message box to trigger further
actions?

If MsgBox("Mark this invoice as sold?", vbYesNo) = vbYes Then
' do whatever you have to do to mark it as sold
Else
' do nothing: the user responded No
End If

 

[Arvin Meyer [MVP]]

How about adding a sold to field to the table? A simple CustomerID can both
mark it as no longer available and add the CustomerID for an invoice (which
is a report). You need to take the CustomerID and the unique ID of the
consignment item, so that you can generate an invoice. Use a query as the
recordsource of the form so that you don't display already sold items.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Brook]

Douglas,

I only have one table that holds two types of invoices Sold and
Consignment. And so if i have an inventory item on a consignment invoice or
marked in my table as consignment I have to go into my consignment invoice
delete it from the invoice and then create a new sold invoice and add the
inventory item to the sold invoice in which it will be marked in the invoices
table as sold.

So right now, I have it set up so that there are no duplicates allow for
my orders on my invoices since each of my inventory items are unique. I don't
have any item with the same serial number.

So if I have an item on my consignment invoice and it sells, and I create
a sold invoice. If I try to add that item to the sold invoice, it tells me
that no duplicates are allow, so again, I have to delete it from the
consignment invoice.

Yes I would like to have some type of message box to be able to "move" or
add the record to the sold invoice and remove it from the consignment invoice.

does this clear anything up?

Thanks,

Brook

 

[Douglas J. Steele]

I'm not sure I'd have structured the database like that, but it's your
database, and if that's how you want to do it...

I showed you below how to react to the user's response to the Message Box.

Within that structure, you can delete a row from a table using:

CurrentDb.Execute "DELETE FROM MyTable WHERE MyId = " & Me.txtId,
dbFailOnError

You can add a row to a table using:

CurrentDb.Execute "INSERT INTO MyTable(Field1, Field2) " & _
"VALUES(" & Me.txtId, & ", " & Me.txtFIeld2 & ")", dbFailOnError

 

[Brook]

Douglas,

How would you set up the DB table for the invoices? Would you have one
invoice table for Sold and One for Consignment?

For the message box, can it be set up so that if the user selects the
record that is already in a consignment invoice then the message box would
popup and ask the user to delete the record from the consignment invoice?

Thanks for your help...

When I set this up, I thought it would hav been the easiest way, but am
open for changes before I get too far along in the db design..

Brook

 

[Douglas J. Steele]

History is usually important, so I would assume a many-to-many relationship
between invoice and inventory item. In that way, you can indicate that a
particular inventory item was associated with a specific Consignment invoice
and with a specific Sale invoice. Heck, you could even have the same
inventory item come back several times! Implementation of many-to-many
relationships require the introduction of a third "intersection" entity
between the two other entities. That intersection entity would have, as its
primary key, a composite key consisting of whatever fields make up the
primary keys of the other two entities. However, you may not be interested
in history, and may decide you don't want this complexity.

Your message box can be set up to pop up under whatever circumstances you
want. If you've selected a particular record, check whether it's assigned to
a consignment invoice, and pop up you box if it is.

 

[Brook]

Douglas,

In regards to your first commments, what you have recommeding is exactly
what I have, so I must be doing something right.

As far as the message box, I like your recommendation in that If I select
a particular record and its already assigned to a consignment invoice, then
ask yes or no on deleting it so you can add it to the sold invoice.

And I guess to go a step further, can the message box contain the invoice
number of the consignment invoice the item is associated with?

Thanks,

Brook

 

[Douglas J. Steele]

You can put whatever text you like in the text of a message box.

MsgBox("Do you want me to change details for " & Me.txtInvoiceNumber & "?",
vbYesNo)

 

[Brook]

Thanks douglas,


I guess my last question would be where to place the code for the message
box?

On the selection of the record on my frminvoices? How would this message
box "override" the message box stating hte item is duplicate and not allowed?

Thanks for all your help!

Brook