ACCESS FEED: [MS_AccessPros] Re: Filling a list box based on a text box

Rabu, 28 Mei 2014

[MS_AccessPros] Re: Filling a list box based on a text box

Art - A space in the textbox Name?!

This is not really complicated. Using the AfterUpdate of the textbox you can change the listbox RowSource.

Private Sub txtmyDate_AfterUpdate()

Dim strRowSource As String

strRowSource = "SELECT B4602200.CUSTNAME AS Tenant " _

& ", B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration DATE] " _

& ", B4602200.LOCNCODE AS [Unit Code] " _

& "FROM B4602200 " _

& "WHERE B4602200.BSSI_Lease_Final_ExpDT > #1/1/1900# " _

& "AND B4602200.BSSI_Lease_Final_ExpDT <DATE " _

& " AND CUSTNAME = " & Chr(34) & [Applicant Name] & Chr(34)

Me.lstLeaseHistory.RowSource = strRowSource

Me.lstLeaseHistory.Visible = True

Me.lstLeaseHistory.Requery

End Sub

Notice I surround the textbox value with Chr(34). That's the ANSI number for double-quotes. I use that in case the cutomer name has an aporstrophe in it like "Joe's Market"

-Bill

---In MS_Access_Professionals@yahoogroups.com, <dbalorenzini@yahoo.com> wrote :

I have a form called ApplicationDetails. On it is a textbox called [Applicant Name]. Also I have a list box called lstLeaseHistory with the following record source:

SELECT B4602200.CUSTNAME AS Tenant, B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration Date], B4602200.LOCNCODE AS [Unit Code] FROM B4602200 WHERE (((B4602200.BSSI_Lease_Final_ExpDT)>#1/1/1900# And (B4602200.BSSI_Lease_Final_ExpDT)<Date()));

I am trying to match the value from the Applicant Name to the CUSTNAME field in the query. If there is a match I would like to list the Lease Expiration Date and Unit Number. If no match then return "Not a Previous Tenant".

Also the lstLeaseHistory listbox had its visible property set to false. There is a command button called cmdCheckLeaseHistory. If it is clicked then lstLeaseHistory listbox becomes visible and returns the specific lease information. Now that I am thinking about it maybe the lstbox should be unbound and let the command button handle all the logic. Any idea would be great.

Thank you,

Arthur Lorenzini

Sioux Falls, SD

__._,_.___

Posted by: wrmosca@comcast.net

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic