help with a query in vba (1 Viewer)

[Gr3g0ry]

i have this form and need to write a query that will get Product name, category, price, quantity in stock from the products table ...

Select productname, category, price, quantityinstock from Products where Productid = pid.value

im new to written queries ... and this is as far as ive gotten.
how do i implement it and use the values returned to populate the form ??

 

[plog]

First, quantityinstock should not be a value in a table. This value should be determined via math---all the amounts put into inventory minus all the amounts taken from inventory.

Second, what's the purpose of this form? What datasource is it bound to?

Third, why's it not bound to the product table itself? That would make whatever you are attempting to achieve in VBA moot.

 

[CJ_London]

how do i implement it and use the values returned to populate the form ??

Really depends on what the form is intended to do. As below to simply display the contents of the table or use a combo to select the product so you can add a quantity

1. remove the second row of controls as not needed
2. change the form type to continuous
3. put the sql in your form recordsource
4. bind each control to the relevant field (the controlsource property)

Or save your query, then select it and on the create ribbon, select more forms the multiple items

I note you are not selecting a field for quantity - all depends on what that field is for as to what happens next

 

[tvanstiphout]

First, quantityinstock should not be a value in a table. This value should be determined via math---all the amounts put into inventory minus all the amounts taken from inventory.

Second, what's the purpose of this form? What datasource is it bound to?

Third, why's it not bound to the product table itself? That would make whatever you are attempting to achieve in VBA moot.

Re Quantity in Stock: you may want to look at the new Northwind 2 Developer Edition template (see File > New or https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb}
Inventory is covered in this help page: https://support.microsoft.com/en-us...nventory-198fb5f2-836f-4c35-aef9-380ab2757504
A video of the developers discussing the inventory module is here:

 

[Gr3g0ry]

This is part of a project to rent party supplies. you start by selecting a customer. I have a list of products thats used to populate the Product ID combo box. The same query is used for each combo box, except that the second combobox will show all except the value from the first box, and the third combobox will show all except the values from the first and second combo box.

you can then select the quantity you want to rent for each item, after selecting quantity the process button is pressed to confirm, enabling the rent button. this works perfectly. calculations, subtractions and all.

the issue arises when i want to view a rental. i wanna populate the Product name, Category, price and Quantityin Stock for each product id.

ive applied the little ive learnt from normalization and this is my database relationship

as i said my query skills arent great. i need to retrieve everythign for a rental. using one query. i tried using the one query

this is where i got, well i have Number of days and quantity now, but want to include Producr name, category and QIS from the products table as well.

hope that gives as much detail as needed

 

[June7]

I will echo other comments - QuantityInStock should be calculated and not saved to table (there is exception but I doubt you need to apply) - review http://allenbrowne.com/AppInventory.html

Your Rent table is not normalized. This will likely cause you much aggravation in future. Have related table RentDetails where each product rented is a record. This means you can have as few or as many products as needed for each rental. Use form/subform arrangement for data entry/edit.

Use combobox to select product and the related data you want to show can be included in the combobox RowSource. You show table name Inventory in image, not Products.
Select productID, productname, category, price, quantityinstock from Inventory;
Then textboxes can reference columns of combobox to display that info for selected item. Reference columns by their index which begins with 0. Expression in textbox to show productname:
=[comboboxname].Column(1)

 

[plog]

You have bigger table issues than what I initially pointed out with QuantityinStock. You need to step away from forms for a bit and focus on fixing your tables. No point building a house on a poorly laid foundation.

Big Issue 1 -> Numerated fields. When you feel the need to suffix field names with numbers, its time for a new table. Instead of 5 fields for Products and Quantities in Rent table, you need a whole new table structured like so:

RentProducts
rp_ID, autonumber, primary key
ID_Rent, number, foreign key to RentID in Rent
ID_Product, foreign key to ProductID in Inventory
rp_Quantity, number, will hold quantity amount

With that table you can accomodate an unlimited number of rented products instead of just the 5 you have set your table up for right now. Better still, if you only have 1 product, you only use 1 record in RentProducts and don't leave 8 fields empty like you are now.

Big Issue 2 -> Storing Calculated values. You do not store data that is dynamic and based on other data you are already storing. I already pointed out that QuantityinStock should not be a field in a table, but a field in a query where you use math to determine the amount in stock. I also now think you have a few other fields like this. My guess is that Days is just math on StartDate and EndDate, if that's the case, you don't store it, you calculate it in a query like QuantityinStock. I bet Status is also calculable which means it shouldn't be stored. Perhaps DaysOverdue falls into this category as well. TotalCost is most likely calculable as well.

Question 1 -> Do you not care about the specific items rented out? Right now you are not tracking items individually you are tracking them generally. This seems really odd because you are trying to track Condition and STatus of them--what happens if one bouncy house is in poor condition and another is in good condition? There's no way in your data to designate that difference. Seems like you are missing a table.

Again, step away from forms because your tables need to change which means any forms you build with your current table structure will be wrong.

 

[Gr3g0ry]

I will echo other comments - QuantityInStock should be calculated and not saved to table (there is exception but I doubt you need to apply) - review http://allenbrowne.com/AppInventory.html

Your Rent table is not normalized. This will likely cause you much aggravation in future. Have related table RentDetails where each product rented is a record. This means you can have as few or as many products as needed for each rental. Use form/subform arrangement for data entry/edit.

Use combobox to select product and the related data you want to show can be included in the combobox RowSource.
Select productID, productname, category, price, quantityinstock from Products;
Then textboxes can reference columns of combobox to display that info for selected item. Reference columns by their index which begins with 0. Expression in textbox to show productname:
=[comboboxname].Column(1)

can i say ... you are a genius ... a damn genius ... works like a charm ...

 

[Gr3g0ry]

ok with that said, i need to pay more attention to normalizationin the fututre, but can i ask though

You have bigger table issues than what I initially pointed out with QuantityinStock. You need to step away from forms for a bit and focus on fixing your tables. No point building a house on a poorly laid foundation.

Big Issue 1 -> Numerated fields. When you feel the need to suffix field names with numbers, its time for a new table. Instead of 5 fields for Products and Quantities in Rent table, you need a whole new table structured like so:

RentProducts
rp_ID, autonumber, primary key
ID_Rent, number, foreign key to RentID in Rent
ID_Product, foreign key to ProductID in Inventory
rp_Quantity, number, will hold quantity amount

With that table you can accomodate an unlimited number of rented products instead of just the 5 you have set your table up for right now. Better still, if you only have 1 product, you only use 1 record in RentProducts and don't leave 8 fields empty like you are now.

Big Issue 2 -> Storing Calculated values. You do not store data that is dynamic and based on other data you are already storing. I already pointed out that QuantityinStock should not be a field in a table, but a field in a query where you use math to determine the amount in stock. I also now think you have a few other fields like this. My guess is that Days is just math on StartDate and EndDate, if that's the case, you don't store it, you calculate it in a query like QuantityinStock. I bet Status is also calculable which means it shouldn't be stored. Perhaps DaysOverdue falls into this category as well. TotalCost is most likely calculable as well.

Question 1 -> Do you not care about the specific items rented out? Right now you are not tracking items individually you are tracking them generally. This seems really odd because you are trying to track Condition and STatus of them--what happens if one bouncy house is in poor condition and another is in good condition? There's no way in your data to designate that difference. Seems like you are missing a table.

Again, step away from forms because your tables need to change which means any forms you build with your current table structure will be wrong.

noted ... and you are right ... for my next project, ill not be making any of these mistakes