Problems with select statement that is a combination of select and avariable

[mieke]

Hi,

I would like to create a search form that is pretty flexible so I
created a from with oll the fields possible on it so you can fill them
in or not.
then I put a button on it that will get the query in a listbox.

this is the code behind the search button:

Private Sub cmdSearch_Click()
Dim ctl As Control
Dim qry As String
Dim teller As Integer
Dim lengte As Integer
Dim i As Integer
Dim field As String
Dim fieldtxtlen As String
Dim fieldtxt As String
Dim Condition As String
Dim sSelect As String

'Clear the listBox
lstQuery.RowSource = ""

' Check how much fields are filled in and put those fieldnames in
variable qry
teller = 0
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
If Not IsNull(ctl.Value) Then
qry = qry + ctl.Name + ","
teller = teller + 1
End If
End If
Next

' Determine where condition
Condition = ""
lengte = Len(qry)
For i = 1 To lengte
If Mid(qry, i, 1) <> "," Then
fieldtxt = fieldtxt + Mid(qry, i, 1)
Else
fieldtxtlen = Len(fieldtxt)
field = Mid(fieldtxt, 4, fieldtxtlen - 3)
Condition = Condition + "tblmaterial." + field + " = me."
+ fieldtxt + _
".value and "
fieldtxt = ""
End If
Next

'get rid of the last " and " in the condition
lengte = Len(Condition)
If Mid(Condition, lengte - 4, 5) = " and " Then
Condition = Mid(Condition, 1, lengte - 4)
End If

'open the recordset to fill in the listbox
rstado.Open "select tblMaterial.AssetTag, tblmaterial.ClassID," &
_
"tblMaterial.SerialNumber, tblMaterial.Model,
tblMaterial.Status, " & _
"tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation," & _
"tblUsers.CostCenter from tblMaterial " & _
"inner join tblUsers on tblMaterial.accountname =
tblUsers.accountname" & _
" where " & Condition & "", CurrentProject.Connection,
adOpenStatic, adLockReadOnly
lstQuery.RowSourceType = "Value List"
lstQuery.ColumnCount = 9

Do While Not rstado.EOF
lstQuery.AddItem (rstado!AssetTag & ";" & rstado!ClassID & ";"
& rstado!SerialNumber & _
";" & rstado!Model & ";" & rstado!Status &
";" & rstado!LastName & _
";" & rstado!FirstName & ";" & rstado!
OfficeLocation & ";" & rstado!CostCenter)
rstado.MoveNext
Loop

rstado.Close
End Sub


The problem is that my open Recordset is not working, and I don't see
why.

Can anyone help me on this?

 

[mscertified]

print out the resulting SQL statement and post it. You have probably
generated invalid SQL. Try copying and pasting it into a new query and
running it from there.
What error message are you getting?

-Dorian

 

[Steve Sanford]

Two things I see:

1) you are missing a declaration for the recordset

DIM rstado as ADODB.Recordset


2) If do not make an entry in at least one of the controls, the code will
bomb at this point:

'get rid of the last " and " in the condition
lengte = Len(Condition)
If Mid(Condition, lengte - 4, 5) = " and " Then
Condition = Mid(Condition, 1, lengte - 4)
End If

If "Condition" is empty (or Length < 5), the Mid() function will fail.
A better way might be:

'get rid of the last " and " in the condition
lengte = Len(Condition)
If lengte > 4 then
If Right(Condition, 5) = " and " Then
Condition = Mid(Condition, 1, lengte - 4)
End If
End If


HTH

 

[mieke]

Two things I see:

1) you are missing a declaration for the recordset

DIM rstado as ADODB.Recordset

2) If do not make an entry in at least one of the controls, the code will
bomb at this point:

'get rid of the last " and " in the condition
lengte = Len(Condition)
If Mid(Condition, lengte - 4, 5) = " and " Then
Condition = Mid(Condition, 1, lengte - 4)
End If

If "Condition" is empty (or Length < 5), the Mid() function will fail.
A better way might be:

'get rid of the last " and " in the condition
lengte = Len(Condition)
If lengte > 4 then
If Right(Condition, 5) = " and " Then
Condition = Mid(Condition, 1, lengte - 4)
End If
End If

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

















- Tekst uit oorspronkelijk bericht weergeven -

Hi Dorian,

This would be my sql statement when I only fill in the class:

select tblMaterial.AssetTag,
tblmaterial.ClassID,tblMaterial.SerialNumber, tblMaterial.Model,
tblMaterial.Status, tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation,tblUsers.CostCenter from tblMaterial inner
join tblUsers on tblMaterial.accountname = tblUsers.accountname where
tblmaterial.ClassID = me.cmbClassID.value

I think the problem is that he doesn't take the value that is in the
field but I don't know how to do this, maybe if I put quotes around
the me.cmbClassID.value but that too gives me errors

for the errors, if I try with just filling up the listbox then it
comes asking me for the value of me.cmbClassID.value
and if I try it with the open recordset then it says no value given
for required parameters. (but I suppose that this is the same problem)


Steve,

The recordset is declarated in a public module where I put all my
public variables so this is not the problem since I also use this
recordset to fill in some comboboxes in the onLoad.

You second remark, is correct, I already saw this, but I was just
triyng to get it working first. But thanks.

I hope this clarifies some things for you, since I'm already looking
on it for 2 days and can't seem to find the problem.

Regards,
Mieke