inventory

[Frank Ruffolo]

i've got a basic order entry d-base w/ a form for adding products, etc.

can anyone help me w/ inventory control?

i want to be able to not only enter a new product but also enter the number
of units. then once a sale is made on a given item it subtracts the number
of units sold from the inventory. is this a big task.

help

thank you,

//frank

[Allen Browne]

Yes, this is a big question.
There is no one-size-fits all solution, but to set you off on a useful path,
see:
Inventory Control - Quantity On Hand
at:
http://members.iinet.net.au/~allenbr...Inventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...

i've got a basic order entry d-base w/ a form for adding products, etc.

can anyone help me w/ inventory control?

i want to be able to not only enter a new product but also enter the number of units. then once a sale is made on a given item it subtracts the number
of units sold from the inventory. is this a big task.

[Bogdan Zamfir]

Hi,

I have a demo application for parts stock management and inventory control
(although not finished) written in Visual FoxPro 8.
You can download it from http://amssoft.dyndns.org:8090/Chris/

However, it can be developed in Access too.

If you are interested to get more info about this, you can e-mail me.

Regards,
Bogdan Zamfir
_______________ _____________
Independent Consultant

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...

i've got a basic order entry d-base w/ a form for adding products, etc.

can anyone help me w/ inventory control?

i want to be able to not only enter a new product but also enter the number of units. then once a sale is made on a given item it subtracts the number
of units sold from the inventory. is this a big task.

help

thank you,

//frank

[Frank Ruffolo]

the DSum option looks like what i might get away with.

in my orders form i have a subform for product selection and quantity to be
purchased. how do i format DSum to subtract the quantity sold from the
UnitsInStock?

i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described in
your helpful link.

thank you,
//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Yes, this is a big question.
There is no one-size-fits all solution, but to set you off on a useful path, see:
Inventory Control - Quantity On Hand
at:
http://members.iinet.net.au/~allenbr...Inventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...

i've got a basic order entry d-base w/ a form for adding products, etc.

can anyone help me w/ inventory control?

i want to be able to not only enter a new product but also enter the

number

of units. then once a sale is made on a given item it subtracts the number of units sold from the inventory. is this a big task.

[Allen Browne]

The actual DSum() expression will depend on your table structure.
The Control Source of the text box will look something like this:
=DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com...

the DSum option looks like what i might get away with.

in my orders form i have a subform for product selection and quantity to be purchased. how do i format DSum to subtract the quantity sold from the
UnitsInStock?

i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described in
your helpful link.

thank you,
//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Yes, this is a big question.
There is no one-size-fits all solution, but to set you off on a useful

path,

see:
Inventory Control - Quantity On Hand
at:
http://members.iinet.net.au/~allenbr...Inventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...

i've got a basic order entry d-base w/ a form for adding products, etc.
can anyone help me w/ inventory control?

i want to be able to not only enter a new product but also enter the

number

of units. then once a sale is made on a given item it subtracts the number of units sold from the inventory. is this a big task.

[Frank Ruffolo]

let's say the table structure is identicle to the one in your link.

thank you for your generous help.

//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

The actual DSum() expression will depend on your table structure.
The Control Source of the text box will look something like this:
=DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com...

the DSum option looks like what i might get away with.

in my orders form i have a subform for product selection and quantity to

be

purchased. how do i format DSum to subtract the quantity sold from the
UnitsInStock?

i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described in
your helpful link.

thank you,
//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Yes, this is a big question.
There is no one-size-fits all solution, but to set you off on a useful

path,

see:
Inventory Control - Quantity On Hand
at:
http://members.iinet.net.au/~allenbr...Inventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...
> i've got a basic order entry d-base w/ a form for adding products, etc. >
> can anyone help me w/ inventory control?
>
> i want to be able to not only enter a new product but also enter the
number
> of units. then once a sale is made on a given item it subtracts the

number

> of units sold from the inventory. is this a big task.

[Allen Browne]

Then use the function supplied on the web page.
DSum() would be too slow, complicated and cumbersome for that stucture.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:rt******** **************@ news04.bloor.is .net.cable.roge rs.com...

let's say the table structure is identicle to the one in your link.

thank you for your generous help.

//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

The actual DSum() expression will depend on your table structure.
The Control Source of the text box will look something like this:
=DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0))

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com...

the DSum option looks like what i might get away with.

in my orders form i have a subform for product selection and quantity to

be

purchased. how do i format DSum to subtract the quantity sold from the
UnitsInStock?

i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described

in your helpful link.

thank you,
//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...
> Yes, this is a big question.
> There is no one-size-fits all solution, but to set you off on a useful path,
> see:
> Inventory Control - Quantity On Hand
> at:
> http://members.iinet.net.au/~allenbr...Inventory.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Frank Ruffolo" <fr******@roger s.com> wrote in message
> news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...
> > i've got a basic order entry d-base w/ a form for adding products,

etc.

> >
> > can anyone help me w/ inventory control?
> >
> > i want to be able to not only enter a new product but also enter the > number
> > of units. then once a sale is made on a given item it subtracts the number
> > of units sold from the inventory. is this a big task.
>
>

[Frank Ruffolo]

where is the code inserted?

i don't know quite how to set up the form to use this code.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Then use the function supplied on the web page.
DSum() would be too slow, complicated and cumbersome for that stucture.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:rt******** **************@ news04.bloor.is .net.cable.roge rs.com...

let's say the table structure is identicle to the one in your link.

thank you for your generous help.

//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

The actual DSum() expression will depend on your table structure.
The Control Source of the text box will look something like this:
=DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0))

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com...
> the DSum option looks like what i might get away with.
>
> in my orders form i have a subform for product selection and quantity

to

be
> purchased. how do i format DSum to subtract the quantity sold from

the > UnitsInStock?
>
> i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described

in > your helpful link.
>
> thank you,
> //frank
>
>
>
> "Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
> news:3f******** **************@ freenews.iinet. net.au...
> > Yes, this is a big question.
> > There is no one-size-fits all solution, but to set you off on a useful > path,
> > see:
> > Inventory Control - Quantity On Hand
> > at:
> > http://members.iinet.net.au/~allenbr...Inventory.html
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Frank Ruffolo" <fr******@roger s.com> wrote in message
> >

news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com... > > > i've got a basic order entry d-base w/ a form for adding products, etc.
> > >
> > > can anyone help me w/ inventory control?
> > >
> > > i want to be able to not only enter a new product but also enter

the > > number
> > > of units. then once a sale is made on a given item it subtracts the > number
> > > of units sold from the inventory. is this a big task.
> >
> >
>
>

[Allen Browne]

Okay.

1. Choose the Module tab of the Database Window.

2. Click New. Access opens a code window.

3. Beneath the "Option" statement(s), paste in the function (everything from
"Function OnHand( ..." to "End Function").

4. If you are using Access 2000 or 2002, choose References from the Tools
menu and check the box beside:
Microsoft DAO 3.6 Library

5. Choose Compile from the Debug menu, to ensure Access has understood
everything.

6. Save the module with a name such as Module1.

You can now use this function like any of the built-in ones.
For example, if you have a form that contains a ProductID text box, you
could add another text box and set its Control Source to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:u9******** *************@n ews04.bloor.is. net.cable.roger s.com...

where is the code inserted?

i don't know quite how to set up the form to use this code.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Then use the function supplied on the web page.
DSum() would be too slow, complicated and cumbersome for that stucture.

"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:rt******** **************@ news04.bloor.is .net.cable.roge rs.com...

let's say the table structure is identicle to the one in your link.

thank you for your generous help.

//frank

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...
> The actual DSum() expression will depend on your table structure.
> The Control Source of the text box will look something like this:
> =DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0))
>
> "Frank Ruffolo" <fr******@roger s.com> wrote in message
> news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com...
> > the DSum option looks like what i might get away with.
> >
> > in my orders form i have a subform for product selection and quantity

to

> be
> > purchased. how do i format DSum to subtract the quantity sold from the > > UnitsInStock?
> >
> > i'm going to setup my tblProducts, tblAcq, tblAcqDetails as described

in

> > your helpful link.
> >
> > thank you,
> > //frank
> >
> >
> >
> > "Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
> > news:3f******** **************@ freenews.iinet. net.au...
> > > Yes, this is a big question.
> > > There is no one-size-fits all solution, but to set you off on a

useful

> > path,
> > > see:
> > > Inventory Control - Quantity On Hand
> > > at:
> > > http://members.iinet.net.au/~allenbr...Inventory.html
> > >
> > > "Frank Ruffolo" <fr******@roger s.com> wrote in message
> > >

news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com... > > > > i've got a basic order entry d-base w/ a form for adding products, > etc.
> > > >
> > > > can anyone help me w/ inventory control?
> > > >
> > > > i want to be able to not only enter a new product but also

enter the

> > > number
> > > > of units. then once a sale is made on a given item it

subtracts the

> > number
> > > > of units sold from the inventory. is this a big task.

[Frank Ruffolo]

how generous of you to take the time to spell everything out.

you have laid out a great foundation on which i will learn and build - i
appreciate it.

//frank


"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Okay.

1. Choose the Module tab of the Database Window.

2. Click New. Access opens a code window.

3. Beneath the "Option" statement(s), paste in the function (everything from "Function OnHand( ..." to "End Function").

4. If you are using Access 2000 or 2002, choose References from the Tools
menu and check the box beside:
Microsoft DAO 3.6 Library

5. Choose Compile from the Debug menu, to ensure Access has understood
everything.

6. Save the module with a name such as Module1.

You can now use this function like any of the built-in ones.
For example, if you have a form that contains a ProductID text box, you
could add another text box and set its Control Source to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:u9******** *************@n ews04.bloor.is. net.cable.roger s.com...

where is the code inserted?

i don't know quite how to set up the form to use this code.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:3f******** **************@ freenews.iinet. net.au...

Then use the function supplied on the web page.
DSum() would be too slow, complicated and cumbersome for that stucture.
"Frank Ruffolo" <fr******@roger s.com> wrote in message
news:rt******** **************@ news04.bloor.is .net.cable.roge rs.com...
> let's say the table structure is identicle to the one in your link.
>
> thank you for your generous help.
>
> //frank
>
> "Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
> news:3f******** **************@ freenews.iinet. net.au...
> > The actual DSum() expression will depend on your table structure.
> > The Control Source of the text box will look something like this:
> > =DSum("Quantity ", "SomeTable" , "ProductID = " & Nz([ProductID], 0)) > >
> > "Frank Ruffolo" <fr******@roger s.com> wrote in message
> > news:Vc******** *************@n ews01.bloor.is. net.cable.roger s.com... > > > the DSum option looks like what i might get away with.
> > >
> > > in my orders form i have a subform for product selection and

quantity

to
> > be
> > > purchased. how do i format DSum to subtract the quantity sold from

the

> > > UnitsInStock?
> > >
> > > i'm going to setup my tblProducts, tblAcq, tblAcqDetails as

described

in
> > > your helpful link.
> > >
> > > thank you,
> > > //frank
> > >
> > >
> > >
> > > "Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
> > > news:3f******** **************@ freenews.iinet. net.au...
> > > > Yes, this is a big question.
> > > > There is no one-size-fits all solution, but to set you off on

a useful
> > > path,
> > > > see:
> > > > Inventory Control - Quantity On Hand
> > > > at:
> > > > http://members.iinet.net.au/~allenbr...Inventory.html
> > > >
> > > > "Frank Ruffolo" <fr******@roger s.com> wrote in message
> > > >

news:FL******** *************@n ews04.bloor.is. net.cable.roger s.com...

> > > > > i've got a basic order entry d-base w/ a form for adding

products,

> > etc.
> > > > >
> > > > > can anyone help me w/ inventory control?
> > > > >
> > > > > i want to be able to not only enter a new product but also

enter the
> > > > number
> > > > > of units. then once a sale is made on a given item it subtracts the
> > > number
> > > > > of units sold from the inventory. is this a big task.