I have two list boxes (lstBox1 and 1

[ActMod]

I have two list boxes (lstBox1 and lstBox2) on a form (frmForm1). lstBox1 has three columns of constants that with "RowSource" set equal to "Select * FROM tbl1". lstBox2 has four columns of constants with "RowSource" set equal to "Select * FROM tbl2". I am trying to apply the User-Selected constants from lstBox1 to ALL the records in lstBox2. I suppose that I need to add some code to lstBox1 to the "After Update" property, but I don't know what the code should look like.

Any help would be greatly appreciated.

Jim

 

[Blorf]

your row source for Lstbox2 can be changed to include what is selected in LstBox1, Example:

SELECT tbl2.*, [forms]![frmForm1].[lstBox1] AS List1 FROM tbl2;

Then change the proberties of Lstbox1, so the after update event calls a macro. The Macro runs requery on object lstBox2.

Hope that helps.

ChaZ

 

[ActMod]

ChaZ,

Thanks much for your suggestion. Your code worked fine with respect to copying the values in the first column from "lstBox1" to "lstBox2". Can you help me with respect to copying the values for other columns. I tried the following in an attempt to copy the first two columns, but I get an "undefined function" error message.

SELECT tbl2.*, forms!frmForm1.lstBox1.Column(0) AS List1, forms!frmGPCI2004.lstBox1.Column(1) AS List2 FROM tbl2;

I have not yet tried the "requery" macro to update lstBox2 with new selections. Could you help me with this macro as well?

Again, thank you very much for your help.

Jim

 

[Blorf]

I beleive I understand what you want. You don't just want the first column, you want all the data as part of your second result.

Ok, first, even though the list box shows all the other fields, it doesn't store them, so we have to get tricky.

There is probably a better way, but here goes.

First, you need to make a query. The query needs to include all fields from Tbl1, but not using * (Star).

In the criteria field, for your key value, put
=[forms]![frmForm1].[lstBox1]

Save the query. Call it something like MyQuery.

Then, change your row source in lstbox2 to ready the following:

Select Tbl1.*, MyQuery.* from Tbl1, MyQuery;

Now, for the Macro, while in design view of your firm, right click LstBox1, and select properties from the list.

Click the Event tab
selec the line titled "After Update"
There should be the ... button to the right of that line. Click it.

Select Macro Builder.

It will ask for a Macro Name. Call it ReQ (for Requery)

On the first line of your Macro, type Requery. Hit Enter.

A new dialoug box appears down at the bottm, titled Control Name. In that box, type LstBox2

Save the Macro, and Hopefully, all should work fine.

ChaZ

 

[ActMod]

ChaZ,

Looks like I have my work cut out for me. Since this will likely take awhile, I just want to thank you for all your help.

I will get back to you with how well I was able to follow your suggestions and get the program to work.

Thanks again.

Jim

 

[Blorf]

No problem.

Good luck.


ChaZ

 

[ActMod]

Chaz,

Amazing!! It worked beautifully. Now if I only knew what I (actually you) did.

Thanks very much.

Jim

 

[Blorf]

I am glad I could help.

ChaZ