Need help with ADP code

G

Guest

Greetings all. Can anyone help me get this code to work in ADP? I copied it
from an Access tips website and changed the table and field names. It works
in MDB perfectly. In ADP when I hit the cmdUpdateList button nothing happens
at all. I want the list box, lstJobInfo, to populate based on criteria
entered in the various text and combo boxes. If nothing is entered in any of
the boxes I want the update list button to return all records from
tblJobInfo. I realize I posted this same code on Saturday, but it was for a
different problem. Thanks to Brian B. for that help. Thank you in advance.

Private Sub cmdUpdateList_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion & "*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRTE) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRTE & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 & "*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
 
G

Guest

Sylvain, I replaced all * with %, and still nothing happens. This code is
behind the on click event of a command button. Is there something missing
somewhere else? It seems it has to be something simple. Thanks a lot for
helping me.

Sylvain Lafontaine said:
Replace * with % for your LIKE on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Greetings all. Can anyone help me get this code to work in ADP? I copied
it
from an Access tips website and changed the table and field names. It
works
in MDB perfectly. In ADP when I hit the cmdUpdateList button nothing
happens
at all. I want the list box, lstJobInfo, to populate based on criteria
entered in the various text and combo boxes. If nothing is entered in any
of
the boxes I want the update list button to return all records from
tblJobInfo. I realize I posted this same code on Saturday, but it was for
a
different problem. Thanks to Brian B. for that help. Thank you in
advance.

Private Sub cmdUpdateList_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion & "*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*' AND"
End If

If Not IsNull(Me.txtRTE) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRTE & "*' AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 & "*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

You are missing a blank space between "" at the end of your row source
assignation:

... & "" & strOrder

You should the sql string into a variable and display it in a message box to
make sure that everything is OK. Even better, try it inside the Query
Analyser.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Sylvain, I replaced all * with %, and still nothing happens. This code is
behind the on click event of a command button. Is there something missing
somewhere else? It seems it has to be something simple. Thanks a lot for
helping me.

Sylvain Lafontaine said:
Replace * with % for your LIKE on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Greetings all. Can anyone help me get this code to work in ADP? I
copied
it
from an Access tips website and changed the table and field names. It
works
in MDB perfectly. In ADP when I hit the cmdUpdateList button nothing
happens
at all. I want the list box, lstJobInfo, to populate based on criteria
entered in the various text and combo boxes. If nothing is entered in
any
of
the boxes I want the update list button to return all records from
tblJobInfo. I realize I posted this same code on Saturday, but it was
for
a
different problem. Thanks to Brian B. for that help. Thank you in
advance.

Private Sub cmdUpdateList_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion &
"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*'
AND"
End If

If Not IsNull(Me.txtRTE) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRTE & "*'
AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 &
"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*'
AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks again for the help, but it still does nothing. I do not know anything
about code, nor do I know much about Access. When you say test the code in
the query analyzer, do you mean trying it on the server itself, or run it in
the adp as a view or stored procedure? And what parts of the code do I need
to test? I tried putting various parts of it into the SQL pane of a stored
procedure, but I kept getting error messages.

Sylvain Lafontaine said:
You are missing a blank space between "" at the end of your row source
assignation:

... & "" & strOrder

You should the sql string into a variable and display it in a message box to
make sure that everything is OK. Even better, try it inside the Query
Analyser.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Sylvain, I replaced all * with %, and still nothing happens. This code is
behind the on click event of a command button. Is there something missing
somewhere else? It seems it has to be something simple. Thanks a lot for
helping me.

Sylvain Lafontaine said:
Replace * with % for your LIKE on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Can anyone help me get this code to work in ADP? I
copied
it
from an Access tips website and changed the table and field names. It
works
in MDB perfectly. In ADP when I hit the cmdUpdateList button nothing
happens
at all. I want the list box, lstJobInfo, to populate based on criteria
entered in the various text and combo boxes. If nothing is entered in
any
of
the boxes I want the update list button to return all records from
tblJobInfo. I realize I posted this same code on Saturday, but it was
for
a
different problem. Thanks to Brian B. for that help. Thank you in
advance.

Private Sub cmdUpdateList_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO, tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion &
"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*'
AND"
End If

If Not IsNull(Me.txtRTE) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRTE & "*'
AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2 &
"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*'
AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

By Query Analyser, I meant the SQL-Server Query Analyser that comes with the
standard or developer edition of SQL-Server 2000. The Developer Edition
2000 is relatively cheap (50$US), however I won't suggest you to buy it
because of the SQL-Server 2005 version which is now on the market. I
suggest that you make a look at the SQL-Server 2005 Express Edition.

The Query Analyser has changed its name and is a separate download from the
SQL-Server 2005 Express Edition, so I suggest that you make a post on the
SQL-Server newsgroup if you don't find it.

For the SQL part, you should test the whole string. Until you find where
the error is, the code won't work in ADP, obviously.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Thanks again for the help, but it still does nothing. I do not know
anything
about code, nor do I know much about Access. When you say test the code
in
the query analyzer, do you mean trying it on the server itself, or run it
in
the adp as a view or stored procedure? And what parts of the code do I
need
to test? I tried putting various parts of it into the SQL pane of a
stored
procedure, but I kept getting error messages.

Sylvain Lafontaine said:
You are missing a blank space between "" at the end of your row source
assignation:

... & "" & strOrder

You should the sql string into a variable and display it in a message box
to
make sure that everything is OK. Even better, try it inside the Query
Analyser.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Sylvain, I replaced all * with %, and still nothing happens. This code
is
behind the on click event of a command button. Is there something
missing
somewhere else? It seems it has to be something simple. Thanks a lot
for
helping me.

:

Replace * with % for your LIKE on SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Can anyone help me get this code to work in ADP? I
copied
it
from an Access tips website and changed the table and field names.
It
works
in MDB perfectly. In ADP when I hit the cmdUpdateList button
nothing
happens
at all. I want the list box, lstJobInfo, to populate based on
criteria
entered in the various text and combo boxes. If nothing is entered
in
any
of
the boxes I want the update list button to return all records from
tblJobInfo. I realize I posted this same code on Saturday, but it
was
for
a
different problem. Thanks to Brian B. for that help. Thank you in
advance.

Private Sub cmdUpdateList_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tbljob_info.Region, tbljob_info.CO,
tbljob_info.RTE,
tbljob_info.[F1/F2], tbljob_info.EWO " & _
"FROM tbljob_info"

strWhere = "WHERE"

strOrder = "ORDER BY tbljob_info.EWO;"


'Set the WHERE clause for the Listbox RowSource if information has
been
entered into a field on the form
If Not IsNull(Me.cboRegion) Then '<--If the textbox txtRegion
contains
no
data THEN do nothing
strWhere = strWhere & " (tbljob_info.Region) Like '*" & Me.cboRegion
&
"*'
AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCO) Then
strWhere = strWhere & " (tbljob_info.CO) Like '*" & Me.txtCO & "*'
AND"
End If

If Not IsNull(Me.txtRTE) Then
strWhere = strWhere & " (tbljob_info.RTE) Like '*" & Me.txtRTE & "*'
AND"
End If

If Not IsNull(Me.cboF1F2) Then
strWhere = strWhere & " (tbljob_info.[F1/F2]) Like '*" & Me.cboF1F2
&
"*'
AND"
End If

If Not IsNull(Me.txtEWO) Then
strWhere = strWhere & " (tbljob_info.EWO) Like '*" & Me.txtEWO & "*'
AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstJobInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Listbox double click event 8
Search Form won't return records 9
Display Yes/No instead of -1, 0 on Search Forms 1
Search form problem 1
Help with VBA with an unbound form 4
My code seems to hate me.... 2
HELP with ERROR Message 9
SQL code to filter selection in combo boxes 6

Top