link forms (1 Viewer)

[vba_php]

there's no difference

then you should be able to select more than 2 list items. it doesn't make sense if you can't.

 

[isladogs]

There is a difference between the two types of multiselect list boxes
The difference is how the different items can be selected.

I suggest you both use Access help to find out what that difference is.

 

[moke123]

prisescamilla,

You should really take a step back and get your foundation in order. Read up on Db design. Here's one link to get started...https://support.office.com/en-us/article/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5

Everything starts with your tables. When they are properly designed the rest becomes a lot easier.

 

[prisescamilla]

prisescamilla,

You should really take a step back and get your foundation in order. Read up on Db design. Here's one link to get started...https://support.office.com/en-us/article/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5

Everything starts with your tables. When they are properly designed the rest becomes a lot easier.

can you help me properly design my tables

 

[prisescamilla]

then you should be able to select more than 2 list items. it doesn't make sense if you can't.

i am able to select multiple items however, both items need to be on the same screen at the same time...if i search on an item, click on it, and then search for another item and click on it and then click on the "throw all items" button it only throws the second selection,not the first.

 

[vba_php]

i am able to select multiple items however, both items need to be on the same screen at the same time...if i search on an item, click on it, and then search for another item and click on it and then click on the "throw all items" button it only throws the second selection,not the first.

that's because the code I wrote for you was only meant to be run *once*. the code looks as listbox 1, grabs the items in there and throws them to your PO form (form1). what you're wanting now requires the code to modified because you're searching for 1 material at a time with your search box. I never put any "throw all items" button on a form in my examples. you must have done that yourself? unless you're talking about the button I gave you that throws all the selected items from list 1 to list 2 in form 1? to do what you want now, you would need code that preserves the first "throw" that shows up in the sql statement of list 2 and then "adds" the additional materials you select in your 2nd search to that original sql statement attached to list 2. this is *exactly* why other people here have suggested a redesign. If you keep needing functionality based on what you have, you'll end up with a "spaghetti" program, which is just like the massive Java applet-based program I worked with a decade ago at Pearson Education here in the states. And that didn't result in anything other than people not wanting to work for the company. So you could *possibly* end up in the same boat! So with that being said, anybody that has made the "redesign" suggestion here would serve as an extremely valuable asset to you at this point, if they're willing to take you on that is...

 

[prisescamilla]

that's because the code I wrote for you was only meant to be run *once*. the code looks as listbox 1, grabs the items in there and throws them to your PO form (form1). what you're wanting now requires the code to modified because you're searching for 1 material at a time with your search box. I never put any "throw all items" button on a form in my examples. you must have done that yourself? unless you're talking about the button I gave you that throws all the selected items from list 1 to list 2 in form 1? to do what you want now, you would need code that preserves the first "throw" that shows up in the sql statement of list 2 and then "adds" the additional materials you select in your 2nd search to that original sql statement attached to list 2. this is *exactly* why other people here have suggested a redesign. If you keep needing functionality based on what you have, you'll end up with a "spaghetti" program, which is just like the massive Java applet-based program I worked with a decade ago at Pearson Education here in the states. And that didn't result in anything other than people not wanting to work for the company. So you could *possibly* end up in the same boat! So with that being said, anybody that has made the "redesign" suggestion here would serve as an extremely valuable asset to you at this point, if they're willing to take you on that is...

do you by any chance know how I could redesign to fit all my needs?

 

[vba_php]

do you by any chance know how I could redesign to fit all my needs?

not in the slightest, unless you uploaded the full blown database that you're working with. that's impossible without a real-life working replica of what you've got. I could give it a go if you could upload something like that, but if it's too big I personally would request a consulting fee from you or your employer because I only go so far in terms of giving free advice. The knowledge I have is how I make my living. sorry. but if you wanna put something up here, I could tell you for sure if it's small enough to get something for free from me (or other people here).

 

[moke123]

can you help me properly design my tables

Did you read the link I posted?
The best way to do this is if you take a stab at it and post back what you come up with. We can then tweak it and guide you in the right direction.

From what you have said so far it sounds like you could start with these tables:
tblSuppliers
SupplierID (PrimaryKey)
SupplierName
More...

tblMaterials
MaterialID (PrimaryKey)
MaterialName
More...

tblAddresses
AddressID (PrimaryKey)
SupplierID (ForeignKey)
Street
City
State
Zip
Suite
AddressType (ie. Mailing,Shipping)

tblContacts
ContactID (PrimaryKey)
SupplierID (ForeignKey)
ContactName
ContactPhone
More...

tblMaterialSuppliers
MaterialSuppliersID (PrimaryKey)
MaterialID (ForeignKey)
SupplierID (ForeignKey)
Price?
More...

tblPurchases
PurchaseID (PrimaryKey)
SupplierID (ForeignKey)
MaterialID (ForeignKey)
PurchaseDate
Quantity
More...

There are a few more but this should give you some ideas. Look at them and see how they are related to one another. Add to them other pieces of info you may want. For instance you mentioned that there may be @ 200 items and you were concerned about a long list. You can get around that by perhaps adding a CategoryID field to the materials table. then a table of Categories like Paper goods, chemicals, cleaning supplies, Safety Items,etc. Then you can have a combox of categories and when you select one you filter your list to just that category.

Post back when you finish your tables.

 

[prisescamilla]

Did you read the link I posted?
The best way to do this is if you take a stab at it and post back what you come up with. We can then tweak it and guide you in the right direction.

From what you have said so far it sounds like you could start with these tables:
tblSuppliers
SupplierID (PrimaryKey)
SupplierName
More...

tblMaterials
MaterialID (PrimaryKey)
MaterialName
More...

tblAddresses
AddressID (PrimaryKey)
SupplierID (ForeignKey)
Street
City
State
Zip
Suite
AddressType (ie. Mailing,Shipping)

tblContacts
ContactID (PrimaryKey)
SupplierID (ForeignKey)
ContactName
ContactPhone
More...

tblMaterialSuppliers
MaterialSuppliersID (PrimaryKey)
MaterialID (ForeignKey)
SupplierID (ForeignKey)
Price?
More...

tblPurchases
PurchaseID (PrimaryKey)
SupplierID (ForeignKey)
MaterialID (ForeignKey)
PurchaseDate
Quantity
More...

There are a few more but this should give you some ideas. Look at them and see how they are related to one another. Add to them other pieces of info you may want. For instance you mentioned that there may be @ 200 items and you were concerned about a long list. You can get around that by perhaps adding a CategoryID field to the materials table. then a table of Categories like Paper goods, chemicals, cleaning supplies, Safety Items,etc. Then you can have a combox of categories and when you select one you filter your list to just that category.

Post back when you finish your tables.

Sorry, I hadn't seen your post.

okay I did the following for my tables:

tblSuppliers
-Supplier ID (PK)
-Supplier Name
-Supplier Address
-Supplier Phone #

tblPurchases
-Purchase ID (PK)
-Purchase Date
-PurchaseOrder #
-Supplier ID (FK)

tblMaterials
-material ID (PK)
-Material Name
-Material Price
-Supplier Name

 

[moke123]

Thats a start but I'm sure we need more.

Could you describe what your doing a little better in non-database lingo?

There are a lot of contingencies which make a huge difference in how complicated these can get.

We are a _____ Company.
I have ___users.
We order supplies for My company, other companies, customers.
this is to generate a purchase order for my boss to approve and then we send it to the various suppliers, monthly weekly, as needed.
we add items over time and then create the P.O.
The type of things we order are ___.
Do multiple suppliers offer the same Items?
Do you need to specify amounts or quantities?
How often do prices change?
do you just recieve items or do you also ship them?
we need to keep track of exceptions or items not recieved or backordered.
The materials can be categorized by____.
we need to track billing and payments.

 

[prisescamilla]

Thats a start but I'm sure we need more.

Could you describe what your doing a little better in non-database lingo?

There are a lot of contingencies which make a huge difference in how complicated these can get.

We are a _____ Company.
I have ___users.
We order supplies for My company, other companies, customers.
this is to generate a purchase order for my boss to approve and then we send it to the various suppliers, monthly weekly, as needed.
we add items over time and then create the P.O.
The type of things we order are ___.
Do multiple suppliers offer the same Items?
Do you need to specify amounts or quantities?
How often do prices change?
do you just recieve items or do you also ship them?
we need to keep track of exceptions or items not recieved or backordered.
The materials can be categorized by____.
we need to track billing and payments.

We are a manufacturing company. I and two more people will be using this database. We order supplies for my company this is to generate a purchase order for my boss to approve and then we send it to the supplier of that specific material as needed. We add items over time and change prices. Also, the quantity depends on how much material we need a given time so it is never constant. We need to keep track of items not received and backorders. Materials can't be categorized there's too many and suppliers sell miscellaneous items. I would like for the table of the purchase order to be teh qty first (manually inputed), Item Id second (be able to modify ID), material name third, UOM fourth (manually modify depending on what it is), Unit price of the material fifth, and lastly I want the line total of the material sixth (the qty times the unit price would equal the line total).