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.

 

[fsupport1600]

tblRooms
ID (primarykey)
RoomNumber
RoomName
RoomComments

tblProducts
prodTagID (primarykey)
ProductType
ProductManufature
ProductModel
ProductPrice
ProductDescription

tblFunitureSelection
ID (primarykey)
RoomNumber (relationship from tblRooms)
ProdTagID (relationship from tblproducts)
Quantity

tblTypicalRooms
ID (primarykey)
RoomTitle

tblTypicalProducts
ID (primarykey)
RoomTitle (relationship from tblTyipicalRooms)
prodTagID (relationship from tblproducts)
typQuantites

 

[dhookom]

Naming multiple fields "ID" in the same database is confusing. Also the related fields (foreign keys) should always be to the Primary Key fields from the other tables.
[tt]
tblFunitureSelection
ID (primarykey)
RoomNumber (relationship from tblRooms)
[red]Should be to ID from tblRooms[/red]
ProdTagID (relationship from tblproducts)
Quantity
[/tt]

[tt]

tblTypicalProducts
ID (primarykey)
RoomTitle (relationship from tblTypicalRooms)
[red]Should be to ID from tblTypicalRooms[/red]
prodTagID (relationship from tblproducts)
typQuantites
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Sorry for the confusion. The primary key in tblRooms is set to RoomNumber. The table shows the key next to that field name. I now have the following:

tblRooms
RoomID
RoomNumber (primarykey)
RoomName
RoomComments

tblProducts
prodTagID (primarykey)
ProductType
ProductManufature
ProductModel
ProductPrice
ProductDescription

tblFunitureSelection
FunitureSelectionID primarykey)
RoomNumber (relationship from tblRooms)
ProdTagID (relationship from tblproducts)
Quantity

tblTypicalRooms
TypicalRoomNameID (primarykey)
RoomTitle

tblTypicalProducts
TypicalProductID (primarykey)
RoomTitle (relationship from tblTyipicalRooms)
prodTagID (relationship from tblproducts)
typQuantites

 

[dhookom]

Why do you even have a RoomID if it is not the primary key? Is it an autonumber? Is RoomNumber text or numeric?

Also, tblTypicalProducts should be related back to tblTypicalRooms through the primary key (TypicalRoomnameID) which the primary key rather than through RoomTitle.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

I thought I needed a "RoomID" in the event I needed to calculate the total number of rooms based of this field. It is Autonumbering. I guess I could delete it.

RoomNumber is text based. The rrom number will begin with a letter folled by a number: ex: A101.

 

[fsupport1600]

Ok, this is where I am at now with the tables based on your suggestions:

tblTypicalRooms
TypicalRoomNameID (primarykey)
RoomTitle

tblTypicalProducts
TypicalProductID (primarykey)
TypicalRoomNameID (relationship from tblTyipicalRooms)
prodTagID (relationship from tblproducts)
typQuantites

 

[dhookom]

I am still assuming your main form has a record source containing Me.RoomNumber.

  Dim strSQL as String
  strSQL ="Insert into tblFurnitureSelection ( RoomNumber, ProductTag ,  Quantity) " & _
  "SELECT """ & Me.RoomNumber & """, ProdTagID, typQuantites FROM tblTypicalProducts " & _
  "WHERE TypicalRoomNameID = " & Me.TypicalRoomSelectionListBoxCurrent
  Db.Execute strSQL, dbFailOnError
  Me.[Name of your subform goes here].Requery

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

After making the changes to the tables it appears my subform is a bit out a whack now.

Prior to the table changes, my subform had the following columns:

ProdTagID|ProductType|Quantity|ProductManufacture|ProdcutPrice

Now my subform shows:

FurnitureSelectionID|ProductTag|Quantity

With the old subform, I was able to select the ProdTagID from the table and the rest of the table fields would populate. All I would do is enter in the quantity for that selected ProdTagID. The click on the next row and do the same.

 

[dhookom]

Here is the code that works. The copy and paste from previous code messed up some carriage returns. You had also changed the name of the prodTagID field in tblFurnitureSelection.

Private Sub Command21_Click()
  Dim strSQL As String
  strSQL = "INSERT INTO tblFurnitureSelection ( RoomNumber, prodTagID, Quantity) " & _
  "SELECT '" & Me.RoomNumber & "', ProdTagID, typQuantites FROM tblTypicalProducts " & _
  "WHERE TypicalRoomNameID = " & Me.TypicalRoomSelectionListBox
  
  CurrentDb.Execute strSQL, dbFailOnError
  Me.[Subform: Product Selection for Room].Requery
End Sub

You should probably set a unique indext on the RoomNumber and ProdTagID fields to prevent duplicates.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Ahh yes working now.

Is their a way I can display the ProductType, ProductManufacture, and Price columns in the subform and hide the FunitureSelectionID column in the "Product Selection for Room subform"?

Can I just add those fields to the subform keeping the prodTagID and the Quantity fields for the copy function?

 

[dhookom]

You can join the products table to the current table in the subreform record source. This assumes the join is on primary and foreign key fields.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

I went into the tblFunitureSelection which from what I can tell is where the selection information is getting saved to and I "Add Existing Fields" and draged over the columns I wanted from the tblProducts. However when I go back into my subform to select a product, the new columns show up, but they do not populate once the prodTagID is selected.

 

[fsupport1600]

I had to rebuild my form/subform and it seems to be populating correctly.

I'll need rework my other forms I had setup to create the Typical Rooms so hopefully with the information you have provided me in this post, I hope I do not struggle with it too bad.

 

[fsupport1600]

I missed this in your previous post:

"You should probably set a unique indext on the RoomNumber and ProdTagID fields to prevent duplicates."

I went into the tblFunitureSelection and set the RoomNumber field and prodTagID fields to "Indexed Yes(No Duplicates)" but after doing so I realize that would not work. because it would not allow me have mutiple rooms to assign products too.

So, I am a bit lost again as to where i need to create this unique key. I am assuming it need to be something along the lines of, "If Product A101 is assigned to Room B123" do not allow a duplicate entry of A101/B123 selection.

 

[dhookom]

The unique index on room and product would prevent you from have two records in the same room with the same product. You could still have more than one room with the same product. For instance it would not allow:
[tt]
Room Product Qty
B123 A101 3
B123 A101 4 [red]Not Allowed[/red]
C456 A101 2 [green]Allowed[/green]
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Duane,

Still having a bit of a problem when I set prodTagID and RoomNumber in the tblFurnitureSelection as indexed.

When I go into the table design view and click Indexes from the tool bar it shows the following:

Index name = PrimaryKey
Field name = FunitureSelectionID
Sort= Ascending

Index name = RoomNumber
Field name = RoomNumber
Sort= Ascending

Index name = ProdTagID
Field name = ProdTagID
Sort= Ascending

Primary is set to "YES"
Unique is set to "YES"
Ignore Nulls set to "NO"

When I got to save it show the following message:

changes not successful they would create duplicate values in the index, primary key, or relationship.

 

[dhookom]

I think you are attempting to assign the primary key to the wrong field. You had it correct on Feb 4.

To find out how to create a multiple field index, click Help from the menu and search for "Index". You may even find a link to "Prevent entry of duplicate values".

This process for finding assistance is generally quicker than posting a message here and waiting for a response.

Duane
Hook'D on Access
MS Access MVP

 

[fsupport1600]

Sounds good, thanks for the response.