Typical "selection" option 1

[fsupport1600]

I am working on a funiture selection database for my interior designer for my office.

The designer will input the rooms names and room numbers in one table for the building, in another table they will input all the products for that project. Chairs, desks, tables....etc. In another table they will then select the room number, and select each product and the quanity for that room.

In some instances, for example a classroom, is going to have typical products. Their could be 50 classrooms that require the exact same furniture.

Is their a way to setup so called a typical classroom selection, and then have those items and quantities applied to mutiple rooms? This ways the designer does not have to select the each product again that would apply for the typically room.

This is my first stab at access, so right now its a very basic datbase consisting of 3 tables. It would be nice to have an option to select "Typical Classroom" or Typical "Office" and when selected in the table, it would automatically populate that room with the "typical funiture" and quanities needed vs having to input that data mutiple times for each room that has the same funitire selection.

Any help on this appreciated.

Just an FYI I have not setup any forms so data entry is directly in the table for the time being.

 

[dhookom]

You could create a table of Typicals with fields like:
[tt][blue]
tblTypicals
==================
typTypID autonumber primary kye
typTitle values like "Standard Classroom"
[/blue][/tt]
Then create a junction table to assign products to typicals
[tt][blue]
tblTypicalProducts
==================
tprTPrID autonumber primary kye
tprTypID links to tblTypicals.typTypID
tprProdID links to tblProducts.ProdID
tprQuantity num of each product
[/blue][/tt]

You can then create an append query that inserts all of the tprProdID values into your current room/product table where the tprTypID matches a selected typical.

I would do all of this from a form where the user selects a typical from a combo box (cboTypID) and one or more rooms from a multiselect list box (lboRoomID).

If you need more help, you should come back with all of your significant table, field, form, and control names.


Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

This what I have so far.

tblFurniture
==================
ProductTag Primary Key
ProductName
ProductMake
ProductModel
ProductSerial
UnitPrice

tblRooms
==================
RoomNumber Primary Key
RoomName

tblFurnitureSelection
==================
ID Primary Key
RoomNumber link to tblRooms
ProductTag link to tblFurniture
Quantity

So this is what I have so far. If its easier to assist, I can post my file to the forum.

How I plan to have the user interact the datbase is, when it is opened, they will import or manaually enter in the room names. They will then enter in all the funiture product data. After that they can then begin to select what rooms have what furniture and to have the ability to configure the typical room options would be great. I have yet to work with forms so any help and guidance is appreciated.

 

[dhookom]

The easiest solution might be to add an [IsTypical] field to tblRooms to create records that are used solely to identify which rooms will be used as typical. You would add all the products and quantities as needed for a typical room.

Assuming this code is run on a click event of a command button. The form has a combo box "cboRoomNumber" to select the room to append the furniture to and a combo box "cboTypicalRoom" to select the typical room.

You might want to add a unique index on the Roomnumber and ProductTag in the tblFurnitureSelection to prohibit duplicates.

Dim strSQL as String
strSQL ="INSERT INTO tblFurnitureSelection " & _
  "(RoomNumber], [ProductTag], [Quantity]) " & _
  "SELECT '" & Me.cboRoomNumber & "', ProductTag, Quantity " & _
  " FROM tblFurnitureSelection " & _
  " WHERE RoomNumber = '" & Me.cboTypicalRoom & "' "
Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Duane,

I was able to to get my subform to work and display the funiture selected based on the room name/number.

Is there a way using using a mutiselect list box on the same form that lists the Product Names, allowing me to select mutiple products and then have them appear in the subform and all I would have to do is to enter in the quantites for each of the products as they are listed?

I created the listbox using the wizard and originally have it outside of the subform and I had no where to append the selection too. When I placed the mutiselect list into the subform, it appears the selection was not appending to the ProductName field and creating a new recoard or row for each product selected so I could enter in the quantity for each of the items.

I do have the quantity as a required field...could this be the problem to why its not creating new records based on the seletion I have made from my list?

Thank you for the help.

 

[fsupport1600]

I was able to create a muti-select field in the FunitureSelection Table. I have built 2 querys:

(Flat style)
Field: RoomNumber
Table: Funiture Selection

Field: Mutiselect.Value
Table: tblFunitureSelection

Field: Quantity
Table: tblFunitureSelection
====================================================

(Not Flat)
Field: RoomNumber
Table: tblFunitureSelection

Field: Mutiselect
Table: tblFunitureSelection

When I run the first query, flat style, the quantity value is tied to all the products for that room. If I change one products quantity it changes them all for that room.

Is their a way to allow the quantity field to be independent for each product?

as mentioned earlier I would like to select the products from a mutiselect box on a form, and then on the same form have a subform display all the products selected and input the quantity value individually.

 

[dhookom]

I don't and won't use multivalue fields. Use the junction table you already had to store the room and furniture. This allows you to store the quantities.

I rarely if ever use these functions/features in Access:
- Multivalue fields
- Lookup fields
- Field captions in table designs
- Input Masks
- Formatting of fields in queries

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

That is what I am using now. However with that, to add a funiture selection record and quantity I have to select the product row by row. I was hoping I could select multiple product from a list and have it update the subform and then I would only have to input the quantity values for those products from the subform list.

 

[dhookom]

Assume you have a room that has a field that identifies it as a "typical" room that you want to clone. You can run an append query based on the tblFurnitureSelection filtered to that one room. The append query would include a column with the new room value. The appended records would basically clone an existing room's furniture.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

I am going to have to revist the "typical" room selection at a later time.

However, I was hoping I could create a muti-select list box based on the ProdcutName on the main form and have it update the subform and then I would only have to input the quantity values for those products from the subform list. Right now to add a funiture selection record and quantity, I have to select the product row by row in the subform.

To have a multi-select list box, I could chdeck off the products I want and have them appear in the subform as individual records so I could input the quantities.

You able to provide some step-by-step instruction on making this happen.

Much appreciated.

 

[dhookom]

You could use an unbound multi-select list box with some code that would loop through the selected items to build a SQL string to append to your furniture select table that is bound to the subform.

I would just a simple continous subform bound to the furniture selection table contained on a main form for the room. You could possibly append all products and enter quantities as needed. Then have a button to delete the subform records with no quanitity.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Duane,

I am hoping you can provide some help here. I went ahead and created the following tables.

You could create a table of Typicals with fields like:

tblTypicals
==================
typTypID autonumber primary kye
typTitle values like "Standard Classroom"


tblTypicalProducts
==================
tprTPrID autonumber primary kye
tprTypID links to tblTypicals.typTypID
tprProdID links to tblProducts.ProdID
tprQuantity num of each product

I have a form that lists the room number and name, and a subform that allows you to select a product and enter the quanitity, row by row.

Can you provide me with some step-by-step instructions on how I can add a drop down list on the form, that will list the typTitle, and once that is selected, it would populate the subform with the products associated from the tblTypicalProducts.

I am revisiting this access database and hope youi could provide some help here.

Thank You

 

[dhookom]

Without knowing the control names or the data types I expect you could have code something like:

Dim strSQL as String
strSQL ="Insert into tblFurnitureSelection ( RoomNumber, ProductTag ,  Quantity) " & _
  "SELECT " & Me.RoomNumber & ", tprProdID, tprQuantity FROM tblTypicalProducts " & _
  "WHERE tprTypID = " & Me.cboTypTypID
CurrentDb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

On the form, I created a list box called: TypicalRoomSelectionListBox

This list is populated with the "RoomTitle" field from the tblTypicalRooms. The tblTypicalProducts contains the fields, RoomTitle, prodTagID and typQuantities

I would like to then, select a Room Title from the drop down list, and then it will automatically populate my subform with the product information from the tblTypicalProducts.

My subform is based off the tblFunitureSelection. So the two fields I would need the information from would be the prodTagID and the typQuantities to populate my subform.
Currently on the subform the user can selection row by row the ProdTagID and enters is the quantity manually.

The code you posted, where would I enter that into in?

 

[dhookom]

I missed any mention of a table tblTypicalRooms. Is this the same as tblTypicals?

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Yes, sorry. I renamed tblTypicals to tblTypicalRooms.

 

[dhookom]

You create a nice moving target ;-)
Assuming you have
- the Roomnumber on the current form
- the list box bound to the primary key (...typID) of tblTypicalRooms (not the RoomTitle)
you should be able to have a button with the On Click event set to something like:

Dim strSQL as String
strSQL ="Insert into tblFurnitureSelection ( RoomNumber, ProductTag ,  Quantity) " & _
  "SELECT " & Me.RoomNumber & ", tprProdID, tprQuantity FROM tblTypicalProducts " & _
  "WHERE tprTypID = " & Me.TypicalRoomSelectionListBox
CurrentDb.Execute strSQL, dbFailOnError
  Me.[Name of your subform goes here].Requery

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

I was waiting for that comment, but you are dead right. I have been a bit scattered brained working through this database. Sorry about that.

I will give this a try and report back.

Greatly appreciate the help.

 

[fsupport1600]

Duane,

I have been studying the code you provided and have a couple of questions:

the insert into tblFunitureSelection line, you have the following fields:
RoomNumber, ProductTag & Quantity

Then for the SELECT command, you have the following fields selected from the tblTypicalProducts:
tprProdID, tprQuantity

In tblTypicalProducts I have a relationship setup for the ProductTag from the tblFunitureSelection. So in the select command, should I change tprProdID to ProductTag?

for the list box, you say to bound it to the primary key (...typID) of tblTypicalRooms (not the RoomTitle)? If this is the case, how do I get the Room Titles to appear in the list vs. the ID?

The name of my subform is "Subform: Product Selection for Room". Should I include "" in the name as well as the square brackets or just enter the name between the square brackets?

Thank You

 

[dhookom]

Maybe you should provide your significant tables, fields, and relationships so we are on the same page.

Typically list and combo boxes display a title value but are bound to a primary key. The wizards for these controls by default build then that way. Your primary key column should have a 0" width.



Duane
Hook'D on Access
MS Access MVP