Dale James
asked on
Copy value from list box and transfer to text box within another userform
Hello Team
I have a form which contains a listbox, populated with values from a query
What I would like to achieve is, when the user double clicks an itemData, of that selected line, copy the value from Column(0) and then return to an opened form displaying in a text box the value copied from the Column(0)
So here's the forms and controls that are being used.
Search form name: frmGroupIDSearch
Listbox name: lstTracker
Listbox datasource: qryWorkTypeStatus
Value to be copied: Column(0) from lstTracker
Now, there is another factor to take into consideration. When the frmGroupIDSearch is opened, it's load event is being triggered from one of three forms so what I also require is to have some sort of reference given to the form that is being used to load the frmGroupIDSearch so that when the ItemData line is double cllicked, the code knows the correct form to return the value to a textbox called txtID.
For example:
There are three user forms, frmAudit1, frmAudit2, frmAudit3
If i trigger the load even of the frmGroupIDSearch form from frmAudit2, when the frmGroupIDSearch is loaded and one of the Itemdata lines is double clicked, I need the code to know to return back to frmAudit2 and place the value from Column(0) into a textbox called txtID.
My apologies if I have made my explanation a bit long winded.
As always...any assistance is much appreciated.
Sincerely
Dale
I have a form which contains a listbox, populated with values from a query
What I would like to achieve is, when the user double clicks an itemData, of that selected line, copy the value from Column(0) and then return to an opened form displaying in a text box the value copied from the Column(0)
So here's the forms and controls that are being used.
Search form name: frmGroupIDSearch
Listbox name: lstTracker
Listbox datasource: qryWorkTypeStatus
Value to be copied: Column(0) from lstTracker
Now, there is another factor to take into consideration. When the frmGroupIDSearch is opened, it's load event is being triggered from one of three forms so what I also require is to have some sort of reference given to the form that is being used to load the frmGroupIDSearch so that when the ItemData line is double cllicked, the code knows the correct form to return the value to a textbox called txtID.
For example:
There are three user forms, frmAudit1, frmAudit2, frmAudit3
If i trigger the load even of the frmGroupIDSearch form from frmAudit2, when the frmGroupIDSearch is loaded and one of the Itemdata lines is double clicked, I need the code to know to return back to frmAudit2 and place the value from Column(0) into a textbox called txtID.
My apologies if I have made my explanation a bit long winded.
As always...any assistance is much appreciated.
Sincerely
Dale
Never populate a bound field in the Open event of a form. That immediately puts the form into edit mode. If the user doesn't complete the entry, you end up with a mostly empty record unless your form's BeforeUpdate logic or the table's validation rules prevent it. When you pass some value this way, wait until the user types at least one character before populating a textbox with the passed in data. The BeforeInsert event will be more appropriate for this purpose. The other issue with using the Open event is that the Open event runs only ONCE and if you want to add more than one record none of the subsequent records will have that textbox automatically populated. Using the BeforeInsert event also solves this problem since it runs once for EACH record you add. It is poor practice for YOU to dirty a form before the user dirties it.
However, this assumes you intend to add a record. If what you really want to do is to open a record, then you need to use the where argument of the open form method. If you want to pull up an existing record if it exists or open a new, empty record if one doesn't already exist, then you need to use BOTH the WHERE argument and the OpenArgs.
PS - Access forms are simply called "forms". "userforms" is something else entirely and is what Excel, Word, and other Office products that support forms use. They are different and not interchangeable. If you want help for Access forms, search for that term. You will not necessarily find relevant results if you search for userforms.
However, this assumes you intend to add a record. If what you really want to do is to open a record, then you need to use the where argument of the open form method. If you want to pull up an existing record if it exists or open a new, empty record if one doesn't already exist, then you need to use BOTH the WHERE argument and the OpenArgs.
PS - Access forms are simply called "forms". "userforms" is something else entirely and is what Excel, Word, and other Office products that support forms use. They are different and not interchangeable. If you want help for Access forms, search for that term. You will not necessarily find relevant results if you search for userforms.
ASKER
Hello John/Pat
Thanks for your post.
The textbox that is to be populated from the search form is unbound and the value that it is being populated with is an audit number which refers to a record. The record will only be trigged when the user tabs out of the textbox and triggers the after update event.
In addition to what you have suggested, can you advise how to reference a form using a variable name ?
For example. If I trigger the search form from frmAudit2, how do I reference back to frmAudit2 without having to reference the actual form name of frmAudit2?
The reason I ask this is because there are three audit forms that can trigger the search form and I would need to able to refer to frmAudit2 form as frmActiveAudit etc. So when I trigger the load event of the search form, a variable for frmAudit2 would be generated and this variable name would then be used when the search form itemdata line is double clicked to refer back to, in this example frmAudit2.
I am aware that this may be not technically the best way to carryout this action but would like to know for my own information and experience.
Thanks for your post.
The textbox that is to be populated from the search form is unbound and the value that it is being populated with is an audit number which refers to a record. The record will only be trigged when the user tabs out of the textbox and triggers the after update event.
In addition to what you have suggested, can you advise how to reference a form using a variable name ?
For example. If I trigger the search form from frmAudit2, how do I reference back to frmAudit2 without having to reference the actual form name of frmAudit2?
The reason I ask this is because there are three audit forms that can trigger the search form and I would need to able to refer to frmAudit2 form as frmActiveAudit etc. So when I trigger the load event of the search form, a variable for frmAudit2 would be generated and this variable name would then be used when the search form itemdata line is double clicked to refer back to, in this example frmAudit2.
I am aware that this may be not technically the best way to carryout this action but would like to know for my own information and experience.
The textbox that is to be populated from the search form is unbound and the value that it is being populated with is an audit number which refers to a record. The record will only be trigged when the user tabs out of the textbox and triggers the after update event.The code will be triggered if the user closes the form without ever leaving the field (assuming the AfterUpdate event even runs for unbound controls populated this way which I don't think it does)
There are a couple of ways to do this. If the control you want to reference remains static, you can use the OpenArgs to send a form reference. But what has been suggested by earlier poster is to pass the value of the control in the OpenArgs. Then, in the BeforeInsert event, your code would be:
Me.SomeControl = Me.OpenArgs
Use a different event at your peril.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Then on the Open event of the form you open you use the OpenArgs to read the value and do whatever you need.