Pass an invoice form field to a inventory table?

[Brook]

Good Day all,

I have an invoice form, with an orders entry subform, within my frminvoice
i have a cboinvoicetype with either "sold" or "consignment", and when this is
selected, I would like to have the value "Sold" "Consignment" saved to my
tblinventory field "invoicetype". Is this possible? Would I have to set up my
tblinventory as a hidden subform?

Thanks in advance for any tips/suggestions..

Brook

 

[Steve Schapel]

Brook,

It is difficult to give a precise response without knowing more. The
fact that you are wanting to do this in the first place could possibly
indicate that the table design could be improved. What is the nature of
the relationship between the tblinventory table and the table that the
frminvoice form is based on? It may be possible to include the
tblinventory table into the query that the frminvoice form is based on.
Otherwise it is possibly a job for an Update Query which you would run
on the After Update event of the cboinvoicetype control.

 

[Brook]

Good Day Steve,

Well, I guess my ultimate goal / what I would like to accomplish is this:

I have the following that I am working with:

tblinventory -- with serial#, colours, size, and other misc details.
each item/record within my inventory is unique.

tblinvoices -- invoice#, invoicedate, PO#, Billto information
(name,address, companyname,etc), shipto information
also within tblinvoices I have two types of invoiceschosen
from a cbobox (cboinvoicetype) types: "Sold" and "Consignment"


tblinvoicedetails -- with performs a lookup on my tblinventory to assigns
order Serial#, Design name, colours, etc.

What I would like to do is pass the following information to my
tblinventory for each order/invoice: invoicetype to know if the item is
"Sold" or "Consignment", and I would also like to pass the information about
the company (Company Name, Contact).

I am unsure how I would do this? Would I have to create another subform
for my inventory?

Thanks for your help!

Brook

 

[Brook]

Steve,

I did forget to mention I have the following forms:

frminvoices
frminvoicedetails subform

Thanks again..

Brook

 

[Steve Schapel]

Brook,

What you are suggesting seems a bit contradictory. As you mentioned,
each inventory item in the tblinventory table is uniue. That means you
could only ever have one piece of invoicetype information about any
given inventory item. So if you were to "pass" the invoicetype to the
tblinventory table, I guess you would end up with a situation of each
inventory item showing at any given time the invoicetype for the most
recent time that item was included in an invoice. I doubt that this is
what you intend?

On the other hand, if you want to know the invoicetype of every instance
of an item being included in an order, well it seems to me you already
have that information without needing to do anything else. If you made
a query based on the tblinvoices table, which already includes the
invoicetype for each invoice, and the tblinvoicedetails table, which
already includes all the times each inventory item (represented by the
serial#) is included in each invoice, well then you automaticllay have
the invoicetype of each instance of any given inventory item being invoiced.

So, hope that sort of makes sense.

 

[Brook]

Steve,

Well, actually each piece could either be sold, in which that case, I
would like to pass invoicetype of "Sold" to the inventorytable from the
tblinvoice details and therefor would not be availabe as a choice to add to a
new invoice, or it could be out at a client on consignment, in that case I
would like to pass invoicetype of "Consignment" to the inventorytable from
the tblinvoice details and therefore, once the item is sold, a new "sales"
invoice is prepared and then the invoicetype would be updated to "Sold".

Also with this, my tblinventory, I would be able to perform reporting on
what items are sold and who to, what items are on Consignment and where they
are located.

Please let me know if this is still not clear and I will try to post more
information.

Brook

 

[Steve Schapel]

Brook,

Ah, I see. Thanks for the clarification, and sorry I misunderstood you
before. I now understand that each record in the tblinventory table is
a distinct object. And that it is appropriate for the status of each
piece (sold, consigned) to be recorded in this table. In this light, it
is now a question for me whether this sold/consigned data is really a
property of the invoice. Anyway, you obviously know your business
better that I could hope to imagine... it's just that it sort of goes
against the grain to have the same information in more than one place in
the database. Is it possible, theoretically at least, for an invoice to
include pieces being sold and other pieces being consigned?

Well, if you want an invoice to be recorded as an invoicetype Sold or
Consignment, and then have this value assigned to the invoiced items,
this can be done via an Update Query. I guess you would have to decide
at which point in the process you do this... when the invoice is
printed? The code would be something like this...
CurrentDb.Execute "UPDATE tblinventory INNER JOIN tblinvoicedetails ON
tblinventory.[serial#] = tblinvoicedetails.[serial#] SET invoicetype ='"
& me.invoicetype & "' WHERE tblinvoicedetails.[invoice#]=" &
Me.[invoice#], dbFailOnError

The other approach, as I intimated before, is to assign the status to
each inventory item at the point where it is added to an invoice, and
dispense with the invoicetype in the invoice table altogether.

By the way, on a complete tangent, it is not a good idea to use a # as
part of a field name.

 

[Brook]

Hello Steve,

thanks... and i'm glad that I was able to make sense of things (or at least
a little bit). Yes, "sold" & "consignment" are properties of my frminvoice.
In your mentioning that it "goes against the grain to have the same
information in more than one place in the database". --> would you have a
better suggestion on how I should set it up. B/c right now this is what I
have in regards to orders/inventory/& invoices.

tblinventory -- contains all the inventory that I have, including Sold,
Consignment, & in my warehouse

tblinvoices - contains my sold & consignment invoices --> on sold items go
on a sold invoice and only consignment invoices go on a consignment invoice.
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.

tlbinvoicedetails--> contains information about each inventory item for
each invoice, including ordernumber, designname, size, etc.

frmvoices / frminvoicedetails subform --> from my frminvoices, the user
selects what type of invoice it is "sold" or "consignment" and assigns an
invoicenumber, then the user selects the client that is to be billed/shipped
to.

from that point the user goes to the order entry subform
(frminvoicedetails subform), and selects the item serialnumber from a
dropdown/lookup on my tblinventory. Once the serialnumber is selected the
following are filled in from the information in my tblinventory (size,
designname, designnumber, sqfeet, price).

I hope this is making some sense on how I am doing things and why I am
wanting my tblinventory to contain the invoicetype(rugstatus of sold or
consignment)? b/c otherwise, I have my tblinventory that contains all my
inventory, both warehouse, items I have added to my sold invoices, and items
that have been added to my consignment invoices. The problem that I am facing
and trying to solve is that once my invoice is created, I don't know (from my
tblinventory) who has what and what is on consignment & is sold since that
information is only held in my tblinvoicedetails.

I hope i'm not rambling on....

Please let me know if you have any questions...

I like the idea of the update query, but the problem with that or that I
have found with that, is that pesky nasty looking update query box comes up.
Can I get rid of that? Can I change it, to make it more useful?

thanks so much...

if need be you can email me and I can email you print screens of my
database form layouts / documenter info...

Brook

Brook,

Ah, I see. Thanks for the clarification, and sorry I misunderstood you
before. I now understand that each record in the tblinventory table is
a distinct object. And that it is appropriate for the status of each
piece (sold, consigned) to be recorded in this table. In this light, it
is now a question for me whether this sold/consigned data is really a
property of the invoice. Anyway, you obviously know your business
better that I could hope to imagine... it's just that it sort of goes
against the grain to have the same information in more than one place in
the database. Is it possible, theoretically at least, for an invoice to
include pieces being sold and other pieces being consigned?

Well, if you want an invoice to be recorded as an invoicetype Sold or
Consignment, and then have this value assigned to the invoiced items,
this can be done via an Update Query. I guess you would have to decide
at which point in the process you do this... when the invoice is
printed? The code would be something like this...
CurrentDb.Execute "UPDATE tblinventory INNER JOIN tblinvoicedetails ON
tblinventory.[serial#] = tblinvoicedetails.[serial#] SET invoicetype ='"
& me.invoicetype & "' WHERE tblinvoicedetails.[invoice#]=" &
Me.[invoice#], dbFailOnError

The other approach, as I intimated before, is to assign the status to
each inventory item at the point where it is added to an invoice, and
dispense with the invoicetype in the invoice table altogether.

By the way, on a complete tangent, it is not a good idea to use a # as
part of a field name.

--
Steve Schapel, Microsoft Access MVP

Steve,

Well, actually each piece could either be sold, in which that case, I
would like to pass invoicetype of "Sold" to the inventorytable from the
tblinvoice details and therefor would not be availabe as a choice to add to a
new invoice, or it could be out at a client on consignment, in that case I
would like to pass invoicetype of "Consignment" to the inventorytable from
the tblinvoice details and therefore, once the item is sold, a new "sales"
invoice is prepared and then the invoicetype would be updated to "Sold".

Also with this, my tblinventory, I would be able to perform reporting on
what items are sold and who to, what items are on Consignment and where they
are located.

Please let me know if this is still not clear and I will try to post more
information.

Brook

 

[Steve Schapel]

Brook,

Thanks for the further explanation. I am pretty sure I understand the
basics of what you're doing.

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. Your approach is to allocate the InvoiceType
to the invoice, and then try to replicate that information through to
the items included in the invoice. I would look at it the other way
around. Allocate the status to the items on the invoice. The Invoice
inherits its type by virtue of the status of the items it contains. I
would not have an InvoiceType field in the tblinvoices table. At any
given time, if you need to know the invoice type, it can easily be
derived via a query based on the status of the items on that invoice.
Maybe this might seem a little esoteric, but I would expect in the long
run that you will have less headaches if you force your database to
follow this principle.

 

[Brook]

Steve,

I understand your point of replication, and inventory status. when I place
an order from my frmorders, it is entered into my tblinventory and has a
default status of "Warehouse".

When I create my invoice, invoicedetails is when it changes to either Sold
or Consignment which is allocated to my invoice, and that is why I was
needing to update my tblinventory so that it is current with my invoiced data.

I think at this point I am going to run an update query on the close of
printing my invoice.

Brook

 

[Brook]

Steve,

I wanted to say thank you for all your help and patience with me.

and please... if you have any suggestions on a different / better layout
please let me know, I am very open to suggestions and ideas.

Thanks again..

Brook

 

[Brook]

Hello Steve,

I am working on setting up and "update query" to update my tblinventory
from my frminvoicedetails subform, I do have a couple of questions for you if
you don't mind?

I was going to set up the code to run on closing of my invoice report, but
I can't get that far right now.

below is my code for my update query, but when I try to run it just from a
command button the system asks me for each value: "Enter Parameter Value"?

UPDATE tblinventory SET tblinventory.invoicenumber =
Forms!frminvoicedetails_Subform!invoicenumber, tblinventory.rugstatus =
Forms!frminvoicedetails_Subform!invoicetype, tblinventory.clientlocation =
Forms!frminvoicedetails_Subform!clientname
WHERE
(((tblinventory.OrderNumber)=Forms!frminvoicedetails_Subform!ordernumber));

Do you have any suggestions?

Thanks,

Brook

 

[Steve Schapel]

Brook,

frminvoicedetails_Subform form cannot be referenced in this way. It is
not open, it is merely displayed through the medium of a subform control
on the main form. Therefore you will need to refer to is as such, i.e.
like this...
Forms!YourMainForm!frminvoicedetails_Subform.Form!invoicenumber

 

[Brook]

Steve,

I updated my code to what you have suggested but am still getting the
"enter parameter value" message box? is there something else I can do to
debug?

is there something else that I can send you for reference?

Thanks,

Brook

 

[Steve Schapel]

Brook,

is there something else that I can send you for reference?

Yes, you could say what the parameter is that you are being prompted for

 

[Brook]

Steve,

Here is my updated code showing the changes that you recommended.

UPDATE tblinventory SET tblinventory.rugstatus =
forms!frminvoices!frminvoicedetails_subform!form!invoicetype,
tblinventory.invoicenumber =
forms!frminvoices!frminvoicedetails_subform!form!iinvoicenumber,
tblinventory.clientlocation =
forms!frminvoices!frminvoicedetails_subform!form!clientname
WHERE
(((tblinventory.ordnum)=[forms]![frminvoices]![frminvoicedetails_subform]![form]![serialnumber]));

and when I run the code I get prompts for all my values I'm trying to update:

firstprompt:
Forms!frminvoices!frminvoicedetails_subform!form!invoicetype

secondprompt:
Forms!frminvoices!frminvoicedetails_subform!form!invoicenumber

ThirdPrompt:
Forms!frminvoices!frminvoicedetails_subform!form!clientname

forthprompt:
Forms!frminvoices!frminvoicedetails_subform!form!serialnumber

I'm not sure if this is relevant, but on my
frminvoicedetails_subform, the "serialnumber" field is pulled from my
qryinventory --> see the code below:

SELECT qryinventory.ordnum, qryinventory.orderid,
qryinventory.DesignNumber, qryinventory.DesignName, qryinventory.Quality,
qryinventory.Size, qryinventorypricecalculations.Sqfeet,
qryinventorypricecalculations.PricePerSqFoot,
qryinventorypricecalculations.totalprice, qryinventory.ShippingCost FROM
qryinventory INNER JOIN qryinventorypricecalculations ON
qryinventory.orderid=qryinventorypricecalculations.orderid ORDER

endcode:

Hope this helps!

Brook

 

[Steve Schapel]

Brook,

Where are you running the Update Query from, and what method? Er, the
frminvoices form is open at the time, right?

There should be a . instead of a ! in the statements, for example...
forms!frminvoices!frminvoicedetails_subform.form!invoicetype

 

[Brook]

Hello Steve,

I was able to get the updatequery working, however there is one issue
that I am seeing now that the query is running.

For my frminvoicedetailssubform, which contains the invoiceorders, can
contain more that order. When I run my update query it only updates one
record. I thought that by adding the "WHERE
(((tblinventory.ordernumber)=[forms]![frminvoices]![frminvoicedetailssubform].[form]![serialnumber]))"
it would match each record in the frminvocedetailssubform to the
tblinventory using the ordernumber field and update the information for each
record?

Am I wrong in my assumption?

Thanks for all your help and patience with me!

Brook

Brook,

Where are you running the Update Query from, and what method? Er, the
frminvoices form is open at the time, right?

There should be a . instead of a ! in the statements, for example...
forms!frminvoices!frminvoicedetails_subform.form!invoicetype

--
Steve Schapel, Microsoft Access MVP

Steve,

Here is my updated code showing the changes that you recommended.

UPDATE tblinventory SET tblinventory.rugstatus =
forms!frminvoices!frminvoicedetails_subform!form!invoicetype,
tblinventory.invoicenumber =
forms!frminvoices!frminvoicedetails_subform!form!iinvoicenumber,
tblinventory.clientlocation =
forms!frminvoices!frminvoicedetails_subform!form!clientname
WHERE
(((tblinventory.ordnum)=[forms]![frminvoices]![frminvoicedetails_subform]![form]![serialnumber]));

and when I run the code I get prompts for all my values I'm trying to update:

firstprompt:
Forms!frminvoices!frminvoicedetails_subform!form!invoicetype

secondprompt:
Forms!frminvoices!frminvoicedetails_subform!form!invoicenumber

ThirdPrompt:
Forms!frminvoices!frminvoicedetails_subform!form!clientname

forthprompt:
Forms!frminvoices!frminvoicedetails_subform!form!serialnumber

I'm not sure if this is relevant, but on my
frminvoicedetails_subform, the "serialnumber" field is pulled from my
qryinventory --> see the code below:

SELECT qryinventory.ordnum, qryinventory.orderid,
qryinventory.DesignNumber, qryinventory.DesignName, qryinventory.Quality,
qryinventory.Size, qryinventorypricecalculations.Sqfeet,
qryinventorypricecalculations.PricePerSqFoot,
qryinventorypricecalculations.totalprice, qryinventory.ShippingCost FROM
qryinventory INNER JOIN qryinventorypricecalculations ON
qryinventory.orderid=qryinventorypricecalculations.orderid ORDER

endcode:

Hope this helps!

Brook

 

[Steve Schapel]

Brook,

Doesn't [serialnumber] relate to each tblinventory item? I am not sure
of the meaning of the [ordnum] field, but if you want the update query
to apply to all items in the invoice, you will need to use a field in
your Where clause that identifies the invoice, rather than identifies
the inventory item. Maybe invoicenumber? Hope that makes sense. The
way you have it at the moment, it will only apply to the currently
active record in the frminvoicedetailssubform subform.

As regards my "help and patience", you're welcome, and I think we can
get it working to produce the outcome you want. But I am also bound to
re-iterate my earlier comments that I don't consider this a valid
approach. That is especially so, now that I realise you are also
replicating other information as well as the 'invoicestatus'.

 

[Brook]

Hello Steve,

You are correct in your response... about the serial number. I know that
this has definately become more trouble than I thought it would be.

Let me ask you if this solution is possible:?

Can I simple just "pass" the field invoicetype from my
subform1(frminvoicedetails) to subform2 (frminventorysubform)? and

Such as the following type of code:?
Me.invoicetype = Me.Parent.Form!invoicetype

Let me know what you think?

Thanks again,

Brook

Brook,

Doesn't [serialnumber] relate to each tblinventory item? I am not sure
of the meaning of the [ordnum] field, but if you want the update query
to apply to all items in the invoice, you will need to use a field in
your Where clause that identifies the invoice, rather than identifies
the inventory item. Maybe invoicenumber? Hope that makes sense. The
way you have it at the moment, it will only apply to the currently
active record in the frminvoicedetailssubform subform.

As regards my "help and patience", you're welcome, and I think we can
get it working to produce the outcome you want. But I am also bound to
re-iterate my earlier comments that I don't consider this a valid
approach. That is especially so, now that I realise you are also
replicating other information as well as the 'invoicestatus'.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,

I was able to get the updatequery working, however there is one issue
that I am seeing now that the query is running.

For my frminvoicedetailssubform, which contains the invoiceorders, can
contain more that order. When I run my update query it only updates one
record. I thought that by adding the "WHERE
(((tblinventory.ordernumber)=[forms]![frminvoices]![frminvoicedetailssubform].[form]![serialnumber]))"
it would match each record in the frminvocedetailssubform to the
tblinventory using the ordernumber field and update the information for each
record?

Am I wrong in my assumption?

Thanks for all your help and patience with me!

Brook