Listbox to Autopopulate Textbox on Form

[Rebel7]

Hi Everyone,

I've been trying to figure this out all day. Basically, I have a list box on my form that populates the database table with new SINs. The listbox also has a query that is looking at entries older than today (so ignoring new ones that were uploaded today) but I want to know how I can autopopulate a textbox with the specific record (ie. Social Insurance Number) that I click on in the listbox or if this is even possible. I want to only autopopulate the SIN textbox and leave the other textboxes alone (ie. Address, Postal Code, etc.) The textbox that I have also has a query to pull up the history of the person whose SIN I enter in the textbox. I can then re-enter new information pertaining to that SIN (new address, city, postal code). The reason is it is too time consuming to enter every SIN individually when I have it in a listbox.

Any help would be appreciated.

Thank you

 

[ansentry]

Put code like this behind the onClick event of the list box. Changes "Names" to suit.

Me.List6.Column(1) , assumes that your SSN's are in the second column of your list box (first column is 0)

Me.txtSIN is the text box on your form that you want to populate with the SIN number.

Private Sub List6_Click()
Me.txtSIN = Me.List6.Column(1)
End Sub

 

[Rebel7]

Thank you so much. It worked like a charm.

 

[Rebel7]

Hi guys,

I have another problem I encoutered while doing my form.

On my form, the list box that I have is suppose to run off a query that is to display all the SINs greater than today that need to be actioned. The purpose of my database is to record all the SINs with address change, phone number change, etc. Now, the list box is suppose to display all the SINs greater than today that have had information that we need to update (ie. address change). Therefore, in the database you can have the same SIN with three or four records (history of address changes) with the most recent date one being the correct information pertaining to the SIN. The problem is because the SINs are not unique in the database and are repeated as the database is recording the history of information change on the SIN, my query will pull up the same SIN numerous times if it is greater than today (with all the different changes to the same SIN). What I would like to do is to pull up unique SINs based on this criteria (older than today and with the value for column status "update required" or "need update" or blank) but if there are lets say one SIN with two records of change, I would like to pull up the most recent record instead of the older one.

Once this is done, I would want to have the query in the listbox re-run each time I click on an "Enter Data" button to enter the new information for a SIN that I have updated so the information in the listbox is current and not showing SINs that I have already actioned for today. Is this possible?

Any assistance would be greatly appreciated.

Thank you

 

[jal]

That was a lot of info you gave there and I didn't digest everything you asked. But I can probably get you started. Let's assume you have a table called Customers which has the SINs, and a table called Addresses which has the history of address changes. Let's try to get a list of the most recent addresses. I will only need to query the Addresses table to get this list, I won't need the Customers table.

SELECT A.* FROM Addresses as A
INNER JOIN
(
SELECT Max(AddressDate), SINS
FROM Addresses
GROUP BY Sins
) as NewestAddress
ON NewestAddress.SINS = A.SINS
AND NewstAddress.AddressDate = A.AddressDate

However, I might want to INNER JOIN the above to the Customers table if I want to pull that table's info as well.