Help Please: Logging Transaction Activity while Updating Quantity

[J. Yuan]

Hi,
I am working on a checkout/inventory system. How can I make a button
that when pressed, would update the previous fields transaction number
to a table (for example, -3 printers, so that printer total is now 20
instead of 23) while also logging the transaction in another table
(for example, logging the date and -3 printers).

I have another question, while a value is selected by a combo box, how
can I retrieve it? I mean, I want one combo box to have general
categories (for example, all eletronic machines), and when an item is
selected from it (for example, printers), I want the second combo box
to display specific listings within the category (for example,
Lexmark, HP, Dell). My question is, how would the second combo box be
able to retrieve the selection from the first and update itself?

Thanks so much.

[Allen Browne]

Your first question is a big one. For an introduction, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

For your second question, this should show you how to modify the RowSource
of the 2nd combo based on the contents of the first:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Hi,
I am working on a checkout/inventory system. How can I make a button
that when pressed, would update the previous fields transaction number
to a table (for example, -3 printers, so that printer total is now 20
instead of 23) while also logging the transaction in another table
(for example, logging the date and -3 printers).

I have another question, while a value is selected by a combo box, how
can I retrieve it? I mean, I want one combo box to have general
categories (for example, all eletronic machines), and when an item is
selected from it (for example, printers), I want the second combo box
to display specific listings within the category (for example,
Lexmark, HP, Dell). My question is, how would the second combo box be
able to retrieve the selection from the first and update itself?

Thanks so much.

[J. Yuan]

Thanks for your reply. Regarding the second question, I viewed the
page you showed me, but still have some questions.

To retrieve the result of my first combo box, is this the correct
statement to use to locate its value
"Forms!frmCheck out!CmbInvoiceN umber;" where frmCheckout is the form
that it is on and CmbInvoiceNumbe r is the name of the combo box. I
tried this and it does not work.

The full SQL statement I used in the row source field of the second
combo box is this:
"SELECT TblStock.[Part Number] FROM TblStock WHERE TblStock.[Invoice
Number] = Forms!frmChecko ut!CmbInvoiceNu mber;"
I know it is correct except for the last part since I can change the
after = part to a specific value and it would work.

Can anyone shed light to where I am making my error?

Thannks.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >...

Your first question is a big one. For an introduction, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

For your second question, this should show you how to modify the RowSource
of the 2nd combo based on the contents of the first:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Hi,
I am working on a checkout/inventory system. How can I make a button
that when pressed, would update the previous fields transaction number
to a table (for example, -3 printers, so that printer total is now 20
instead of 23) while also logging the transaction in another table
(for example, logging the date and -3 printers).

I have another question, while a value is selected by a combo box, how
can I retrieve it? I mean, I want one combo box to have general
categories (for example, all eletronic machines), and when an item is
selected from it (for example, printers), I want the second combo box
to display specific listings within the category (for example,
Lexmark, HP, Dell). My question is, how would the second combo box be
able to retrieve the selection from the first and update itself?

Thanks so much.

[Allen Browne]

Concatenate the value from the combo into the string:

strSQL = "SELECT TblStock.[Part Number] FROM TblStock WHERE
TblStock.[Invoice Number] = " & Forms!frmChecko ut!CmbInvoiceNu mber & ";"

If the bound column of the combo a field of type Text, you need extra
quotes:

strSQL = "SELECT TblStock.[Part Number] FROM TblStock WHERE
TblStock.[Invoice Number] = """ & Forms!frmChecko ut!CmbInvoiceNu mber & """;"

If the combo is on the same form, you can use
Me
in place of
Forms!frmChecko ut

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Thanks for your reply. Regarding the second question, I viewed the
page you showed me, but still have some questions.

To retrieve the result of my first combo box, is this the correct
statement to use to locate its value
"Forms!frmCheck out!CmbInvoiceN umber;" where frmCheckout is the form
that it is on and CmbInvoiceNumbe r is the name of the combo box. I
tried this and it does not work.

The full SQL statement I used in the row source field of the second
combo box is this:
"SELECT TblStock.[Part Number] FROM TblStock WHERE TblStock.[Invoice
Number] = Forms!frmChecko ut!CmbInvoiceNu mber;"
I know it is correct except for the last part since I can change the
after = part to a specific value and it would work.

Can anyone shed light to where I am making my error?

Thannks.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message

news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >...

Your first question is a big one. For an introduction, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

For your second question, this should show you how to modify the RowSource of the 2nd combo based on the contents of the first:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Hi,
I am working on a checkout/inventory system. How can I make a button
that when pressed, would update the previous fields transaction number
to a table (for example, -3 printers, so that printer total is now 20
instead of 23) while also logging the transaction in another table
(for example, logging the date and -3 printers).

I have another question, while a value is selected by a combo box, how
can I retrieve it? I mean, I want one combo box to have general
categories (for example, all eletronic machines), and when an item is
selected from it (for example, printers), I want the second combo box
to display specific listings within the category (for example,
Lexmark, HP, Dell). My question is, how would the second combo box be
able to retrieve the selection from the first and update itself?

Thanks so much.

[J. Yuan]

Thanks for your reply. But somehow when I used the statement below,
Access returns the primary key of the field instead of the value I
wanted. For example, when I selected a value from the top combo box,
access returns the following SQL statement to the second combo box:
SELECT TblStock.[Part Number] FROM TblStock WHERE TblStock.[Invoice
Number] = "2";

"2" is apparently the primary key I have for the table, but I wanted
it to return not that number but the invoice number, which is the one
I selected. Is there a way to do that, or am I doing something wrong?

Thanks.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >...

Concatenate the value from the combo into the string:

strSQL = "SELECT TblStock.[Part Number] FROM TblStock WHERE
TblStock.[Invoice Number] = " & Forms!frmChecko ut!CmbInvoiceNu mber & ";"

If the bound column of the combo a field of type Text, you need extra
quotes:

strSQL = "SELECT TblStock.[Part Number] FROM TblStock WHERE
TblStock.[Invoice Number] = """ & Forms!frmChecko ut!CmbInvoiceNu mber & """;"

If the combo is on the same form, you can use
Me
in place of
Forms!frmChecko ut

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Thanks for your reply. Regarding the second question, I viewed the
page you showed me, but still have some questions.

To retrieve the result of my first combo box, is this the correct
statement to use to locate its value
"Forms!frmCheck out!CmbInvoiceN umber;" where frmCheckout is the form
that it is on and CmbInvoiceNumbe r is the name of the combo box. I
tried this and it does not work.

The full SQL statement I used in the row source field of the second
combo box is this:
"SELECT TblStock.[Part Number] FROM TblStock WHERE TblStock.[Invoice
Number] = Forms!frmChecko ut!CmbInvoiceNu mber;"
I know it is correct except for the last part since I can change the
after = part to a specific value and it would work.

Can anyone shed light to where I am making my error?

Thannks.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message

news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >...

Your first question is a big one. For an introduction, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

For your second question, this should show you how to modify the RowSource of the 2nd combo based on the contents of the first:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...
> Hi,
> I am working on a checkout/inventory system. How can I make a button
> that when pressed, would update the previous fields transaction number
> to a table (for example, -3 printers, so that printer total is now 20
> instead of 23) while also logging the transaction in another table
> (for example, logging the date and -3 printers).
>
> I have another question, while a value is selected by a combo box, how
> can I retrieve it? I mean, I want one combo box to have general
> categories (for example, all eletronic machines), and when an item is
> selected from it (for example, printers), I want the second combo box
> to display specific listings within the category (for example,
> Lexmark, HP, Dell). My question is, how would the second combo box be
> able to retrieve the selection from the first and update itself?
>
> Thanks so much.

[Allen Browne]

You can sort this out if you create yourself a query.
Have it return the field you want.
Switch the query to SQL View (View menu).
Now you can see the kind of statement that you must produce in your string.

--
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.

"J. Yuan" <jw****@mail.co m> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Thanks for your reply. But somehow when I used the statement below,
Access returns the primary key of the field instead of the value I
wanted. For example, when I selected a value from the top combo box,
access returns the following SQL statement to the second combo box:
SELECT TblStock.[Part Number] FROM TblStock WHERE TblStock.[Invoice
Number] = "2";

"2" is apparently the primary key I have for the table, but I wanted
it to return not that number but the invoice number, which is the one
I selected. Is there a way to do that, or am I doing something wrong?