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

I tried this:

strRowSource = "SELECT ReferenceList.[Reference Name] " _
        & ", ReferenceList.ReferenceType AS [Reference Type]" _
        & ", FORMAT(ReferenceList.Phone, "(@@@)@@@-@@@@)" AS Phone " _
        & ", ReferenceList.ApplicationID " _
        & ", ReferenceList.ApplicationReferenceID " _
        & "FROM ReferenceList " _
        & "WHERE ReferenceList.VerificationFlag = False " _
        & " AND ReferenceList.ApplicationID = " & [ApplicationID]

But it says its expecting and end of statement.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Thursday, May 29, 2014 12:14 PM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Art

You should be able to format right in the query.

Format(ReferenceList.Phone, "(@@@) @@@-@@@@" As myPhone

-Bill

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

Sorry I forgot to include the code.

Private Sub cmdPendingVerification_Click()
Dim strRowSource As String

    strRowSource = "SELECT ReferenceList.[Reference Name] " _
        & ", ReferenceList.ReferenceType AS [Reference Type]" _
        & ", ReferenceList.Phone " _
        & ", ReferenceList.ApplicationID " _
        & ", ReferenceList.ApplicationReferenceID " _
        & "FROM ReferenceList " _
        & "WHERE ReferenceList.VerificationFlag = False " _
        & " AND ReferenceList.ApplicationID = " & [ApplicationID]

Me.lstPendingVerification.RowSource = strRowSource

'Me.lstLeaseHistory.Visible = True

    'Me.lstLeaseHistory.Requery

    If Me.lstPendingVerification.ListCount < 1 Then
      Me.txtPendingVerification.Visible = True
      Me.lstPendingVerification.Visible = False
    Else
      Me.txtPendingVerification.Visible = False
      Me.lstPendingVerification.Visible = True
      Me.lstPendingVerification.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

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

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

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

alorenzini@crhanetwork.org

"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

alorenzini@crhanetwork.org

"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 ():

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

alorenzini@crhanetwork.org

"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

alorenzini@crhanetwork.org

"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