Re: move record...

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

"Douglas J. Steele" wrote:

> 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)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:5F9F562E-121A-4DD6-A34B-C90FC8A3FDDE@xxxxxxxxxxxxxxxx
> > 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" wrote:
> >
> >> 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.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:86B5BF74-B90C-4432-990E-068263A68C21@xxxxxxxxxxxxxxxx
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:697EE802-1C9D-4C3F-B8A2-CAB477EACD29@xxxxxxxxxxxxxxxx
> >> >> > 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" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no private e-mails, please)
> >> >> >>
> >> >> >>
> >> >> >> "Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> >> news:9D8FE5D2-B11A-4625-8E38-96B839482F37@xxxxxxxxxxxxxxxx
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: move record...
    ... > a particular record and its already assigned to a consignment invoice, ... > ask yes or no on deleting it so you can add it to the sold invoice. ... >> Doug Steele, Microsoft Access MVP ... >> (no private e-mails, please) ...
    (microsoft.public.access.formscoding)
  • Re: Pass an invoice form field to a inventory table?
    ... The point I was trying to make in my previous reply was that my feeling is that the Sold/Consignment/Warehouse status belongs to the Inventory items, not to the Invoice. ... "sold" & "consignment" are properties of my frminvoice. ... If an item from a consignment invoice is sold, I want to have it "removed" from the consignment invoice and I will add it as a Sold Invoice. ... from that point the user goes to the order entry subform, and selects the item serialnumber from a dropdown/lookup on my tblinventory. ...
    (microsoft.public.access.formscoding)
  • Re: move record...
    ... I'm not sure I'd have structured the database like that, ... And so if i have an inventory item on a consignment invoice ... > delete it from the invoice and then create a new sold invoice and add the ...
    (microsoft.public.access.formscoding)
  • Re: move record...
    ... 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 ...
    (microsoft.public.access.formscoding)
  • Re: Using two columns as a primary key
    ... To create a 2-field primary key, open your table in design view. ... choose the first field of the index. ... When Access opens the ... invoices are from four companies with their own invoice numbers. ...
    (comp.databases.ms-access)