link forms (1 Viewer)

mike60smart

Registered User.
Local time
Today, 12:57
Joined
Aug 6, 2017
Messages
1,899
Hi Adam

Sorry for Jumping in but I am always of the opinion that when I see a fundamental problem then I will state what it is.

This appears to be a process for Purchase Orders and whichever way you look at it the 1 table "Materials" is the wrong way to approach the problem.

Best to get the OP creating all necessary tables before trying the VBA approach.
 

moke123

AWF VIP
Local time
Today, 08:57
Joined
Jan 11, 2013
Messages
3,849

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
Mike,

this is another case where there are 2 people handling this. lets stick with just one, ok? if you want to step in, then say so and I'll back off. let us not confuse people with too many cooks. it's good to have options, but I believe they need to see an example. pris, see attached. the sql problem line should read:
Code:
& "WHERE materials.material LIKE '*' & '" & Me.Text2 & "' & '*' " _
also, I changed the column count to 4 and changed the column lengths as well. those are part of the equation here. google it to learn what to do. :)

this works great, thank you.. Now, what if I want to add multiple items to the same PO?
 

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
Hi

Your main problem is that you only have 1 table.

You would usually have a table for Customers and a table for Orders and yet another table for Materials.

Can you explain exactly what your business process is?

Im confused on what exactly you mean. It's just POs for necessary inventory materials, nothing big.
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
this works great, thank you.. Now, what if I want to add multiple items to the same PO?
that's almost impossible to answer without seeing your complete database file pris. I don't anybody here that has attempted to help you would able to do that. what you're working with it totally unknown since you've only shown the skinny version of a file. but one option, based on my spitball guesses would be:

=> make the box "mult-select" in the properties.
=> change the SQL to loop through the box's items and check for ".selected".
=> make new SQL statement based on concatenated statement you retrieve from the loop.

but I'm guessing you dont' know how to do all that...
 

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
that's almost impossible to answer without seeing your complete database file pris. I don't anybody here that has attempted to help you would able to do that. what you're working with it totally unknown since you've only shown the skinny version of a file. but one option, based on my spitball guesses would be:

=> make the box "mult-select" in the properties.
=> change the SQL to loop through the box's items and check for ".selected".
=> make new SQL statement based on concatenated statement you retrieve from the loop.

but I'm guessing you dont' know how to do all that...

so there's no solution to that?
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
so there's no solution to that?
of course there's a solution! but you didn't give enough info to be able to provide a viable solution without a lot of variables in the "unknown" category. do you have anything else to share that would help us help *you* as it relates to what you now have from me and what you *want* as it relates to the example you have?
 

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
of course there's a solution! but you didn't give enough info to be able to provide a viable solution without a lot of variables in the "unknown" category. do you have anything else to share that would help us help *you* as it relates to what you now have from me and what you *want* as it relates to the example you have?

Okay, I'll try to explain as best as I can from the beginning. In my business we make purchase orders of, for example gloves, and I wanted to, instead of searching one by one for that exact material, create a database where I can just search for the material and when I select that material it automatically goes to the template of the PO with the company, price, and name of material ready to go. You already gave me that and it works great thank you. However, sometimes we order multiple items from the same company. Lets say from company "pei" we have to order gloves, bags, and pencils. I want to be able to select all those items and send them to the same PO. Also, the next thing I would like to add to my PO is that when I enter the PO template a pop up appears asking "what company is the material from" to where i would select a company and in a text box automatically the Name, address, and phone number of the company appears. If you have any doubts or i didnt explain myself thoroughly please let me know.
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
it automatically goes to the template of the PO with the company, price, and name of material ready to go.
THAT is the problem. you said that long ago in this thread. no one knows what you mean by "template". that's why I asked many posts ago if you are doing this in an attempt to throw the info you are selecting into a report that will serve as the PO document. that's usually the standard when people build apps with access for the purpose of printing PO documents I believe. I've done some, but not many. same thing goes for invoices. invoices are printed out as report objects. I've done many jobs where the person wants that to happen. care to clarify for everyone?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 08:57
Joined
Apr 27, 2015
Messages
6,280
P.ease pardon my interruption Prisescamilla, but have you considered using the North Wind Traders application? From what you have described, the sample application seems to be very close to what you are trying to do.

As DBGuy would say, "just my 2 cents"
 

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
THAT is the problem. you said that long ago in this thread. no one knows what you mean by "template". that's why I asked many posts ago if you are doing this in an attempt to throw the info you are selecting into a report that will serve as the PO document. that's usually the standard when people build apps with access for the purpose of printing PO documents I believe. I've done some, but not many. same thing goes for invoices. invoices are printed out as report objects. I've done many jobs where the person wants that to happen. care to clarify for everyone?

I have attached what my template for PO is
View attachment Database41.accdb
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
I have attached what my template for PO is
View attachment 78092
that doesn't show anything. that's just a form with no sample data. it would be better for everyone if you put some fake data in, re-uploaded it and explained what textboxes play what part in this little piece of entertainment we are reading. ;)
but have you considered using the North Wind Traders application? From what you have described, the sample application seems to be very close to what you are trying to do.
take note of that, pris. that comes from a wise man. that's a great idea.
 
Last edited:

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
that doesn't show anything. that's just a form with no sample data. it would be better for everyone if you put some fake data in, re-uploaded it and explained what textboxes play what part in this little piece of entertainment we are reading. ;)take note of that, pris. that comes from a wise man. that's a great idea.

i added the sample data

View attachment Database41.accdb
 

moke123

AWF VIP
Local time
Today, 08:57
Joined
Jan 11, 2013
Messages
3,849
What we really need to see are your table structures.
Forms and reports should be the last thing you build. Without a proper foundation the whole house crumbles.

Can you post a copy with your tables included? or a snapshot of your relationships?

Did you look at any of the data models in the links I posted?
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
pris,

see attached. that is how you do it. but IMO, you should really listen to the rest of the guys here who are talking about restructuring and stuff. What you're doing can be manipulated to get the job done, like I'm showing you here, but it would be best to fix it if you have time. :)
 

Attachments

  • PO generation - materials list.zip
    85.5 KB · Views: 82

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
What we really need to see are your table structures.
Forms and reports should be the last thing you build. Without a proper foundation the whole house crumbles.

Can you post a copy with your tables included? or a snapshot of your relationships?

Did you look at any of the data models in the links I posted?


I looked at them but keep in mind I know absolutely nothing about programming so to me I was just reading Chinese.
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
have you gotten what you needed at least?
 

prisescamilla

Registered User.
Local time
Today, 05:57
Joined
Dec 24, 2019
Messages
24
have you gotten what you needed at least?

everything works great. however, the list only lets me select two if i do not search for the materials. in this sample table there are only 3 materials so they all considerably fit in the selection box all at the same time. however, keep in mind that there is over 200 materials that are going to be placed in that table and im going to have to search for them because they wont all . therefore, i would like to be able to add multiple materials even if i have to search for more. i dont know if that made sense?
 

vba_php

Forum Troll
Local time
Today, 07:57
Joined
Oct 6, 2019
Messages
2,884
everything works great. however, the list only lets me select two if i do not search for the materials. in this sample table there are only 3 materials so they all considerably fit in the selection box all at the same time. however, keep in mind that there is over 200 materials that are going to be placed in that table and im going to have to search for them because they wont all . therefore, i would like to be able to add multiple materials even if i have to search for more. i dont know if that made sense?
it's been a while since I used an access form, but try changing the props from "simple" to "extended". I can't remember what the difference is. see image.
 

Attachments

  • multi select props.jpg
    multi select props.jpg
    103.2 KB · Views: 85

Users who are viewing this thread

Top Bottom