Populating list box in ADP using sql statement, and it doesnt work.

  • Thread starter Thread starter Lee Taylor-Vaughan
  • Start date Start date
L

Lee Taylor-Vaughan

Someone, please help me--even if you give me just a hint! :-)

Hello

Issue 1: I have a list box on a form, which is populated based upon the
entries made in various text boxes, and upon the Search button being
clicked. (cmdSearch).

below is the code, so far.

Public Sub cmdSearch_Click()
If Not IsNull(Me.scboState) Then

Me.List74.RowSource = "SELECT tblMain.FirstName, tblMain.MiddleName,
tblMain.LastName, tblMain.Address1, tblMain.City, tblMain.State,
tblMain.PhoneNumberHome, FROM tblMain WHERE (((tblMain.LastName) >=
[Forms]![frmUserSearch]![stxtLastName])) ORDER BY
tblMain.LastName;"

Me.Refresh
Me.Repaint

Else
MsgBox "Select a State from the list", vbCritical, "Missing State"

End If

End Sub

It doesn't work, meaning, nothing gets populuated to the list box, why, what
did i do wrong?

Issue 2: Also, what i would really like to do is enter say one or two
characters from a person first name and last name, select the state they
live in (from a combo box) and then have the list box populated from there,
but i cant even get it to work from just entering the last name (issue 1
above!). How do i get it to work by entering more than one piece of
information in muliptle boxes (FName, LName, MiddleName, SSN, etc...) --the
user has the option to leave a field blank too (this is the really tricky
part). if someone can give me an snipit of code to get me going, and a nudge
in the right direction, i think i will be fine. (i hope)


Thanks

Lee
 
First, you have a comma be PhoneNumberHome and FROM that must be removed:

... PhoneNumberHome, FROM tblMain ...

Replace with:
... PhoneNumberHome FROM tblMain ...

Second, SQL-Server is unable to resolve something like
[Forms]![frmUserSearch]![stxtLastName]. You must construct your sql
statement by using the concatenation operator, like:

... Where tblMain.LastName >= " &
[Forms]![frmUserSearch]![stxtLastName] & " Order by ...

Don't forget the required blank spaces! You can also drop the semi-comma at
the end of your sql statement.

S. L.
 
Thank you, with some muddling through i did it, with your help of course

here is how i finally did it.... (i am sure there is a quicker way!)

Public Sub cmdSearch_Click()
If Not IsNull(Me.scboState) Then

Dim SQLSelect As String
SQLSelect = "SELECT AccountID, FirstName, MiddleName, LastName,
AddressLine1, City, State "
Dim SQLFrom As String
SQLFrom = "FROM tblMain "
Dim SQLWhere As String
Dim SQLWhere1 As String
Dim SQLWhere2 As String
Dim SQLWhereControl As String

Dim SQLOrder As String
SQLOrder = " ORDER BY FirstName"

SQLWhere1 = "WHERE (LastName = N'"
SQLWhereControl = Me.stxtLastName
SQLWhere2 = "')"
SQLWhere = SQLWhere1 & SQLWhereControl & SQLWhere2

Me.List74.RowSource = SQLSelect & SQLFrom & SQLWhere & SQLOrder
Debug.Print Me.List74.RowSource

Me.Refresh
Me.Repaint


Else

MsgBox "Select a State from the list", vbCritical, "Missing State"

Me.List74.RowSource = ""
Me.Refresh
Me.Repaint

End If

End Sub




Sylvain Lafontaine said:
First, you have a comma be PhoneNumberHome and FROM that must be removed:

... PhoneNumberHome, FROM tblMain ...

Replace with:
... PhoneNumberHome FROM tblMain ...

Second, SQL-Server is unable to resolve something like
[Forms]![frmUserSearch]![stxtLastName]. You must construct your sql
statement by using the concatenation operator, like:

... Where tblMain.LastName >= " &
[Forms]![frmUserSearch]![stxtLastName] & " Order by ...

Don't forget the required blank spaces! You can also drop the semi-comma
at the end of your sql statement.

S. L.


Lee Taylor-Vaughan said:
Someone, please help me--even if you give me just a hint! :-)

Hello

Issue 1: I have a list box on a form, which is populated based upon the
entries made in various text boxes, and upon the Search button being
clicked. (cmdSearch).

below is the code, so far.

Public Sub cmdSearch_Click()
If Not IsNull(Me.scboState) Then

Me.List74.RowSource = "SELECT tblMain.FirstName, tblMain.MiddleName,
tblMain.LastName, tblMain.Address1, tblMain.City, tblMain.State,
tblMain.PhoneNumberHome, FROM tblMain WHERE (((tblMain.LastName) >=
[Forms]![frmUserSearch]![stxtLastName])) ORDER BY
tblMain.LastName;"

Me.Refresh
Me.Repaint

Else
MsgBox "Select a State from the list", vbCritical, "Missing State"

End If

End Sub

It doesn't work, meaning, nothing gets populuated to the list box, why,
what
did i do wrong?

Issue 2: Also, what i would really like to do is enter say one or two
characters from a person first name and last name, select the state they
live in (from a combo box) and then have the list box populated from
there,
but i cant even get it to work from just entering the last name (issue 1
above!). How do i get it to work by entering more than one piece of
information in muliptle boxes (FName, LName, MiddleName, SSN,
etc...) --the
user has the option to leave a field blank too (this is the really tricky
part). if someone can give me an snipit of code to get me going, and a
nudge
in the right direction, i think i will be fine. (i hope)


Thanks

Lee
Click to expand...
Click to expand...
 
2 more comments:

First, you must check for the possibility of having a quote in the first
name or last name. The single quote must be replaced by two single quotes
before their insertion the SQL string by using the replace function:

name = Replace (name, "'", "''")

Second, I don't remember exactly but I think that setting the RowSource of a
form or a control automatically call the requery method; so your call to the
Refresh and Repaint methods are supefluous.

S. L.

Lee Taylor-Vaughan said:
Thank you, with some muddling through i did it, with your help of course

here is how i finally did it.... (i am sure there is a quicker way!)

Public Sub cmdSearch_Click()
If Not IsNull(Me.scboState) Then

Dim SQLSelect As String
SQLSelect = "SELECT AccountID, FirstName, MiddleName, LastName,
AddressLine1, City, State "
Dim SQLFrom As String
SQLFrom = "FROM tblMain "
Dim SQLWhere As String
Dim SQLWhere1 As String
Dim SQLWhere2 As String
Dim SQLWhereControl As String

Dim SQLOrder As String
SQLOrder = " ORDER BY FirstName"

SQLWhere1 = "WHERE (LastName = N'"
SQLWhereControl = Me.stxtLastName
SQLWhere2 = "')"
SQLWhere = SQLWhere1 & SQLWhereControl & SQLWhere2

Me.List74.RowSource = SQLSelect & SQLFrom & SQLWhere & SQLOrder
Debug.Print Me.List74.RowSource

Me.Refresh
Me.Repaint


Else

MsgBox "Select a State from the list", vbCritical, "Missing State"

Me.List74.RowSource = ""
Me.Refresh
Me.Repaint

End If

End Sub




Sylvain Lafontaine said:
First, you have a comma be PhoneNumberHome and FROM that must be removed:

... PhoneNumberHome, FROM tblMain ...

Replace with:
... PhoneNumberHome FROM tblMain ...

Second, SQL-Server is unable to resolve something like
[Forms]![frmUserSearch]![stxtLastName]. You must construct your sql
statement by using the concatenation operator, like:

... Where tblMain.LastName >= " &
[Forms]![frmUserSearch]![stxtLastName] & " Order by ...

Don't forget the required blank spaces! You can also drop the semi-comma
at the end of your sql statement.

S. L.


Lee Taylor-Vaughan said:
Someone, please help me--even if you give me just a hint! :-)

Hello

Issue 1: I have a list box on a form, which is populated based upon the
entries made in various text boxes, and upon the Search button being
clicked. (cmdSearch).

below is the code, so far.

Public Sub cmdSearch_Click()
If Not IsNull(Me.scboState) Then

Me.List74.RowSource = "SELECT tblMain.FirstName, tblMain.MiddleName,
tblMain.LastName, tblMain.Address1, tblMain.City, tblMain.State,
tblMain.PhoneNumberHome, FROM tblMain WHERE (((tblMain.LastName) >=
[Forms]![frmUserSearch]![stxtLastName])) ORDER BY
tblMain.LastName;"

Me.Refresh
Me.Repaint

Else
MsgBox "Select a State from the list", vbCritical, "Missing State"

End If

End Sub

It doesn't work, meaning, nothing gets populuated to the list box, why,
what
did i do wrong?

Issue 2: Also, what i would really like to do is enter say one or two
characters from a person first name and last name, select the state they
live in (from a combo box) and then have the list box populated from
there,
but i cant even get it to work from just entering the last name (issue 1
above!). How do i get it to work by entering more than one piece of
information in muliptle boxes (FName, LName, MiddleName, SSN,
etc...) --the
user has the option to leave a field blank too (this is the really
tricky
part). if someone can give me an snipit of code to get me going, and a
nudge
in the right direction, i think i will be fine. (i hope)


Thanks

Lee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top