Choose from list

[Always_Learning]

Hi There,

Could anyone help please?
I currently have a form where after filling it in and clicking ok, there is a select statement executed that adds related records to a datatable.
I need to interrupt that select statement to allow the user to pick which related records they want to associate rather than add all the records.

Could someone advise on the best way to go about this.

I appreciate the help.

Best Regards,

 

[Minty]

Depending on how many records there are, I would probably add the associated records to a multiselect list box, and either select them from that or move them into another list box before then pressing a "Commit these records" command button.

 

[Always_Learning]

Hi Minty,
Thanks for the reply.

Would I be able to call a form from the form I am in.
I was thinking of once the user presses ok in the original form I call another form with the list of records to choose from and then on closing that form add the chosen records to datatable.

Thanks again for the help.

Best Regards,

 

[Minty]

Yes you can open another form from your existing form. I would use something like this ;

Where items are moved from the list on the right to the one on the left, then another control is used to "action" the selected records. Obviously if you need more columns you could put the lists above each other rather than side by side.

 

[Always_Learning]

Hi Minty,

Yes that is exactly what I need. The ability to add or remove.
Did you create that form by hand or is there a wizard that can create it?

Thanks again.

Best Regards,

 

[Minty]

I'm afraid hand crafted, I may be able to upload a stripped down demo version, but not sure if I'll have time to create it today.

I would break it down into stages. First create your form with the main list in it. That is simply a case of creating a list box and applying the correct record source for it , presumably based on something from your calling form.

Once you have that working I can post up the code for the moving around bits. (Or google will find it if you careful with your search terms)

 

[Always_Learning]

Hi Minty,

You have been very helpful, thank you.
I have created a form with a list in it but I am not sure how I pass a value from my main form and use it in the query for the list in the second form.
Would you know how I can do that.

I appreciate your help and time.

Best Regards,

 

[Minty]

I would probably use the OpenArgs method detailed here ; http://www.access-programmers.co.uk/forums/showthread.php?t=210530
That thread details passing multiple values, but you can obviously pass a single value if that is all you need.

 

[Always_Learning]

Hi Minty,
I am now managing to pass a value but how do I use it in the sql statement created for the list.

This is the sql I am trying to use but it does not work. There is a value in pValue that should return records.

SELECT * FROM Tbl_Table1 INNER JOIN
Tbl_Table2 ON Tbl_Table1.Type = Tbl_Table2.Description
WHERE Tbl_Table2.TypeID=" & pValue & " AND Tbl_Table1.Status='In Use'"

Thanks.

All the best,

 

[Minty]

That all looks about right, what code are you using to set the value and the recordsource? Can you post it up, I'm assuming it's on_Open or on_Load of the form?

 

[Always_Learning]

Hi Minty,
It's done from the list properties on the form design.

Should I do it in code somehow?

Thanks.

(I'll continue from home later, hope you are still around then.)

 

[Minty]

Yes you will need to do it in code, as the pValue can't be accessed from the form unless you set its value on the form somewhere. I'm going off line shortly but hopefully someone else will continue to assist if I'm not about.

On the form load event you would need something like (untested but should be about right);

Dim pValue as Long
Dim sSql as String

pValue = Me.OpenArgs

sSql = "SELECT * FROM Tbl_Table1 INNER JOIN "
sSql = sSql & "Tbl_Table2 ON Tbl_Table1.Type = Tbl_Table2.Description "
sSql = sSql & "WHERE Tbl_Table2.TypeID=" & pValue & " AND  Tbl_Table1.Status='In Use' ;"

Debug.Print sSql        ' Comment this out when it working

Me.[COLOR="Red"]YourListBox[/COLOR].RecordSource = sSql

The red bit needs to be changed to your listbox control name. Ensure you keep the spaces at the end of each text string to make it work correctly.

 

[Always_Learning]

Hi Minty,

Thanks a lot for that, I'll give it a try later while watching the football.

You have been more than helpful and I appreciate you taking the time for me.

All the best.

 

[Always_Learning]

Hi there,

Everything seems to be working but
Me.LstActions.RecordSource = sSql
This line of code is giving me an error of "Compile error, Method or Data Member not found"
Could that be because I am in the second form and the me.LstActions is not on the first form.
Or could it be that I have not created the list correctly?

Any ideas?

Thanks.

Best Regards.