Kamis, 29 Mei 2014

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

 

I guess I am not quite down with this. I have adapted the following code for another situation but am wondering can I format the 3 column of my lstPendingVerification listbox to format as a phone number? Right now when the list box populated the phone number is just a string of numbers. I tried formatting it coming out of the query but that did not work.

 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."






On Wednesday, May 28, 2014 12:06 PM, "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
This seems to work. Thanks for the help. THis is the final code.

Private Sub cmd_CheckDynamics_Click()
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
   
    If Me.lstLeaseHistory.ListCount < 1 Then
      Me.txtDynamicsCheck.Visible = True
      Me.lstLeaseHistory.Visible = False
    Else
      Me.txtDynamicsCheck.Visible = False
      Me.lstLeaseHistory.Visible = True
      Me.lstLeaseHistory.Requery
    End If
   
     
End Sub
 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."






On Wednesday, May 28, 2014 11:54 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Art,
 
Combo and List boxes have a ListCount property that can be used in your code.
 
Duane Hookom MVP
MS Access
 
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 28 May 2014 09:49:54 -0700
Subject: Re: [MS_AccessPros] Re: Filling a list box based on a text box



Bill,

I actually solved the date issue by adding ():

Private Sub cmd_CheckDynamics_Click()
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
 
In a majority of tries this will return a blank record set. What would be the best way to inform the user that there are no records?


With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."






On , Art Lorenzini <dbalorenzini@yahoo.com> wrote:


 actually put this into my cmdCheckDynamics click event. But now its asking for a "Date" parameter.
 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."






On Wednesday, May 28, 2014 9:04 AM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
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: Art Lorenzini <dbalorenzini@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)

.

__,_._,___

Tidak ada komentar:

Posting Komentar