Combo box help

Turbo910

Registered User.
Local time
Today, 19:59
Joined
Nov 8, 2009
Messages
43
Hey guys,
Just undetaken a project for work using access 2007
Im not very literate with 2007, i knew a little bit of 2003 but not very advanced.

Basically i have a combo box for the products which is link to my Product_tbl and the field Product_Name. This then obviously shows all the products in the database.

I also have a brand new text box that hasnt been modified at all yet, BUT it will be used to show the quantity of the product selected by the combo box.

Ok so what i want to achieve and need help with:

Basically when i select a product from my combo box, i want the quantity to show in the new text box automatically. thats the first problem i need to overcome.

The next problem is once i have the first part up and running smoothly i want to be able to change the quantity up and down as people rent out the product and save it back into the table.

Thanks in advance!!
 
Add the quantity to the Row Source of the combo but set its column width to zero.

For the control source of the text box;
= comboboxname.Column(x) where x is the column number of the quantity (starting at zero)

However you should calculate the quantity rather than store it. You have a record of the number of items you have for rent. Calculate the quantity on hand by subtracting the active rentals. This is more accurate and reliable than updating the on hand quantity.
 
thankyou very much for your assisstance.
I have managed to get the first part working! YAY!

But i dont fully understand what you mean by the second part of your post OR maybe i didnt make my post clear enough.

For the second part what i want to do is lookup a piece of equipment (LIGHT1) and its quantity (5), which I can do already. But when some one rents 1 of the LIGHT1's, Somehow i can to be able to either change the quantity in the same box that 5 shows in and then save the new data in the same record (5 to 4), or in a seperate box that will minus the 1 off of the original 5 and then save 4 in the record instead of 5.

If that still isnt clear please just say.

And remeber I am very very basic with 2007 so i will need the help simplified as much as possible :)

Thanks!
 
thankyou very much for your assisstance.
I have managed to get the first part working! YAY!

But i dont fully understand what you mean by the second part of your post OR maybe i didnt make my post clear enough.

For the second part what i want to do is lookup a piece of equipment (LIGHT1) and its quantity (5), which I can do already. But when some one rents 1 of the LIGHT1's, Somehow i can to be able to either change the quantity in the same box that 5 shows in and then save the new data in the same record (5 to 4), or in a seperate box that will minus the 1 off of the original 5 and then save 4 in the record instead of 5.

If that still isnt clear please just say.

And remeber I am very very basic with 2007 so i will need the help simplified as much as possible :)

Thanks!
 
You have lights in a table with 5 as the quantity. Don't change this value when someone rents one. Use a query with a join between the Items table and the table which holds the rental information.

Count the number of currently active rentals. The on hand quantity will be the initial quantity less the total number of active rentals. Use an aggregate query to get the count.

This caluclation is fundamentally accurate where some error in the increment and decrement of the quantiy figue could lead to an error. This is particular the case when the dayabase has simultaneous users. A write conflict can lead to the quantity being corrupted.
 
But i dont have a rental table heres my table structure:

Basically i have a table (Product_tbl) with the following fields:
Product_ID
Product_Name
Product_Qua
Unit
Section
And a second table (Cost_tbl) with the following fields:

Cost_ID
Product_ID
Job_price
[COLOR=blue !important][COLOR=blue !important]Rent[/COLOR][/COLOR]_price
RRP
Actual_Selling_Price
My only relationship is a one-to-many from Product_ID in the Product_tbl to the Product_ID in the Cost_tbl.

and i dont know much about queries either my little knowledge is in simple forms and tables
 
I assumed you would be recording the transaction in your database too.

To simply reduce the number by one run the query:

Code:
Update Product_tbl 
Set Product_Qua = Product_Qua -1
Where Product_ID = Forms!formname!textboxname
 
Thanks thats alot simpler haha, so if thats to reduce by one, what if i want to reduce the amount by however much the user inputs into a textbox (Quantity out)??

How to i actually create the query on Access??

Also will this store with the record so when the next user opens up light ones record it will show the latest quantity??

Thankyou so much
 
Last edited:
Code:
Update Product_tbl 
Set Product_Qua = Product_Qua - Forms!formname!Quantitytextboxname
Where Product_ID = Forms!formname!Producttextboxname

Create a query by with the query design button on the Create ribbon in 2007. If I remember correctly there is a create menu in earlier versions.

Change its type to an update query.

You should be able to find this basic stuff in the Access Help.
 
So i created the query by entering the code you gave me and replacing with my values into the SQL part of the query but it doesnt affect the table or anything:S any guidence please? What does this code actually do?

Also what is the last part line of the code doing?

And also how do i incorporate a save or some kind of the button that the user can click once Finished inputting the data so that it changes the values in the Product_tbl automatically once pressed>

In other words say a user is new to the form and he opens it and find the product he wants, the data of it comes up blah blah, he puts in the amount being hired in the Quantity_out text field on the form(this isnt a field in any table) and the amount left on hand is shown in the New quantity text field (which also isnt in any form at all) so once the calculations have been done on the form i want the query to update the old quantity in stock to the new quantity in stock by the user clicking a save button.

That second one makes more sense i think lol

Thanks
 
Last edited:
Ok forget everything said in my last post!!!
I HAVE IT WORKING!!

Galaxiom you are The galaxy of galaxies, your such a life saver, is there any way i can recommend you or put a star against your name, anything?

And if you dont mind with any future problems would it be possible for me to message you privately?

So many Kind regards
 
Glad you got it going. It is always encouraging when something works out because I remember what it was like trying to get started in Access.

Best post the question in the forum. You will get the answer faster and also receive a wider perspective. There are many ways to achieve things in Access and the advantages of different techniques gets discussed in the forum.

Also posting here gives other the opportunity to learn from the questions and advice.
 

Users who are viewing this thread

Back
Top Bottom