link forms (1 Viewer)

prisescamilla · Dec 24, 2019

hello,

I have a listbox in a form named 'frmCustomers' that includes columns of Material, Company, ID and Price, and a listbox in a form called 'Form1' what I am aiming to do is that whenever I click on my selection from 'frmCustomers' I want that specific selection to appear on the listbox of 'Form1'.

vba_php · Dec 24, 2019

you'll have to have both forms open at the same time first of all. then on the "click" event of the first box or "double click" event, write code to change the rowsource of the second box to the value (and column index number, if applicable) of the first box. then I believe you'll have to ".requery" the 2nd box.

prisescamilla · Dec 24, 2019

Adam,

I know absolutely nothing about programming this is my first time. Can you please explain further how to write a code to change the source and how I would requery the second box, please.

vba_php · Dec 24, 2019

prisescamilla said:
Adam,

I know absolutely nothing about programming this is my first time. Can you please explain further how to write a code to change the source and how I would requery the second box, please.

I uploaded a sample for you. it's easy to follow.

prisescamilla · Dec 24, 2019

vba_php said:
I uploaded a sample for you. it's easy to follow.

Unfortunately, it did not work for me.

vba_php · Dec 24, 2019

prisescamilla said:
Unfortunately, it did not work for me.

what happened with it? what version do you have? give specifics and we'll fix it! no need to be sad.

prisescamilla · Dec 24, 2019

vba_php said:
what happened with it? what version do you have? give specifics and we'll fix it! no need to be sad.

It tells me my forms and lists can not be found. I am using version 2016.

vba_php · Dec 24, 2019

it's possible I was using object names too complex. or not using compatible sql in vba. I'm also getting a ridiculous error in my 2016 version when writing SQL in the query builder:

Unknown function dcount

Try this and see if it fixes the problem....

moke123 · Dec 24, 2019

It is not clear to me exactly what you want to happen.
A better description of what you have is needed.

On the second form, do you want the value to be the only value in the 2nd listbox?
I would think you would have a listbox(on 2nd form) that already has a rowsource and you want to select the same item in that list box (ie. highlighted)

If so, you can pass the value from the 1st listbox as your OpenArgs argument in the open form method and then in the 2nd forms on open event simply write

Code:

Me.YourlistboxName = me.openargs

prisescamilla · Dec 24, 2019

vba_php said:
it's possible I was using object names too complex. or not using compatible sql in vba. I'm also getting a ridiculous error in my 2016 version when writing SQL in the query builder:Try this and see if it fixes the problem....

that opens the second form however it does not show item selected on the second form.

prisescamilla · Dec 24, 2019

moke123 said:
It is not clear to me exactly what you want to happen.
A better description of what you have is needed.

On the second form, do you want the value to be the only value in the 2nd listbox?
I would think you would have a listbox(on 2nd form) that already has a rowsource and you want to select the same item in that list box (ie. highlighted)

If so, you can pass the value from the 1st listbox as your OpenArgs argument in the open form method and then in the 2nd forms on open event simply write

Code:

Me.YourlistboxName = me.openargs

I am aiming to make Purchase Orders easier. On the first listbox I have the materials with the price, company, and ID. On the second list box I have a template of the Purchase Order of the company. What i want is for me to select the material from my first listbox and that automatically sends the material with the price, company, and ID to the second listbox; therefore generating a purchase order that is just ready to print out instead of having to look for the material one by one.

vba_php · Dec 24, 2019

prisescamilla said:
that opens the second form however it does not show item selected on the second form.

the code can be easily modified, prise. I have no way really of knowing what the issue is on your end because I created it with the exact same version that you have and it works fine for me. But it sounds like you need something of a more basic nature. I can create what you just described to moke, as it is not difficult, but I'll let him answer you first. He can do the same thing I can do.

moke123 · Dec 24, 2019

I'm still not following your process. Are you not storing your selections in a table?

Since Adam seems to understand I'll defer to him for the moment.

vba_php · Dec 24, 2019

moke123 said:
Since Adam seems to understand I'll defer to him for the moment.

oh come on moke...you can do this.

prisescamilla said:
I am aiming to make Purchase Orders easier. What i want is for me to select the material from my first listbox and that automatically sends the material with the price, company, and ID to the second listbox;

the attached sample does exactly that.

prisescamilla said:
therefore generating a purchase order that is just ready to print out instead of having to look for the material one by one.

this really doesn't make any sense, unless you're talking about clicking a button that opens a report in access that has all the PO info in it, based on the limited information listed in listbox #2. is that what you're doing? I would guess that a PO generation operation would take place behind a button on a form when the record that has the PO info in it is the current and active record being looked at by the user, or the PO info is highlighted in a window/frame in the same form the report-generating button is on. Just like what the rest of the guys here say....I don't think you're set up correctly. But regardless, what you asked for is indeed in the attachment to this post.

if for some miracle this one doesn't work, here's what to do:

1) box 1 name on frmCustomers = "lst1". box 1 row source = "SELECT id, material FROM materials; "
2) code on the "on click" event of box 1:

Code:

    Dim rSource As String
        DoCmd.OpenForm "form1"
        rSource = "SELECT * FROM materials WHERE id = " & Me.lst1.Column(0)
        Forms!form1!lst2.RowSource = rSource
        Forms!form1!lst2.Requery
        DoCmd.Close acForm, Me.Name

prisescamilla · Dec 26, 2019

vba_php said:
oh come on moke...you can do this. the attached sample does exactly that.this really doesn't make any sense, unless you're talking about clicking a button that opens a report in access that has all the PO info in it, based on the limited information listed in listbox #2. is that what you're doing? I would guess that a PO generation operation would take place behind a button on a form when the record that has the PO info in it is the current and active record being looked at by the user, or the PO info is highlighted in a window/frame in the same form the report-generating button is on. Just like what the rest of the guys here say....I don't think you're set up correctly. But regardless, what you asked for is indeed in the attachment to this post.

if for some miracle this one doesn't work, here's what to do:

1) box 1 name on frmCustomers = "lst1". box 1 row source = "SELECT id, material FROM materials; "
2) code on the "on click" event of box 1:

Code:

Dim rSource As String DoCmd.OpenForm "form1" rSource = "SELECT * FROM materials WHERE id = " & Me.lst1.Column(0) Forms!form1!lst2.RowSource = rSource Forms!form1!lst2.Requery DoCmd.Close acForm, Me.Name

This worked perfectly fine until I inserted a Command button above list 1 to search for keywords. If i click on the list for a material it still sends it to the second list. However, when i search of the material and click the search button the company appears instead of the material and when i click on it, it gives me "enter a parameter value". Below I will leave the command im using on my command button. Can you please let me know what i am doing wrong.

Private Sub Command4_Click()
Dim SQL As String

SQL = "SELECT materials.material, materials.company, materials.id, materials.price " _
& "FROM materials " _
& "WHERE Material LIKE '*" & Me.Text2 & "*'" _
& "ORDER BY materials.material; "

Me.lst1.RowSource = SQL
Me.lst1.Requery

mike60smart · Dec 26, 2019

Hi
It would be far easier if you can upload a zipped copy of the db

vba_php · Dec 26, 2019

prisescamilla said:
Private Sub Command4_Click()
Dim SQL As String

SQL = "SELECT materials.material, materials.company, materials.id, materials.price " _
& "FROM materials " _
& "WHERE Material LIKE '*" & Me.Text2 & "*'" _
& "ORDER BY materials.material; "

Me.lst1.RowSource = SQL
Me.lst1.Requery

mike is right. given all that i've done for you, it would be better for you to upload a file with what you want to have happen at the *end* of all of this. but for now....you say you're not skilled in code. your code is not working cuz you're not taking into account the properties of LST1. there are only 2 columns set on it (look in the properties sheet of the box). so your code is mismatching with that default setting most likely. I had no idea you wanted to add more functionality to this. there are other issues too perhaps, but i'm making the obvious point (or something similar).

prisescamilla · Dec 26, 2019

vba_php said:
mike is right. given all that i've done for you, it would be better for you to upload a file with what you want to have happen at the *end* of all of this. but for now....you say you're not skilled in code. your code is not working cuz you're not taking into account the properties of LST1. there are only 2 columns set on it (look in the properties sheet of the box). so your code is mismatching with that default setting most likely. I had no idea you wanted to add more functionality to this. there are other issues too perhaps, but i'm making the obvious point (or something similar).

I am using the exact version you sent me. All I added was the command button on list 1 to search of materials because it is going to be a long list. However, when I try to search for materials only company appears and doesn't transfer to list 2. Im sorry if I'm frustrating you I just really need this job to work. Below I attached the db.

View attachment listbox to lisbox - material to PO listing.accdb

mike60smart · Dec 26, 2019

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?

vba_php · Dec 26, 2019

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.

link forms (1 Viewer)

Registered User.

Forum Troll

Attachments

Registered User.

Forum Troll

Registered User.

Forum Troll

Registered User.

Forum Troll

Attachments

AWF VIP

Registered User.

Registered User.

Forum Troll

AWF VIP

Forum Troll

Attachments

Registered User.

Registered User.

Forum Troll

Registered User.

Registered User.

Forum Troll

Attachments

Similar threads

Users who are viewing this thread