Hello,
Here is the code for my OK command.
It does not contain anything about the comments field. I thought you could
put in what is needed.
Thank you,
Swordsman8
Private Sub cmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("SortedSeals")
If Me.listMfg.ItemsSelected.Count > 0 Then
For Each varItem In Me.listMfg.ItemsSelected
strCriteria = strCriteria & ",'" & Me.listMfg.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
Else
strCriteria = "NewTable.MFG Like ' * ' "
End If
Dim varItem2 As Variant
Dim strCriteria2 As String
If Me.listProcess.ItemsSelected.Count > 0 Then
For Each varItem2 In Me.listProcess.ItemsSelected
strCriteria2 = strCriteria2 & ",'" & Me.listProcess.ItemData(varItem2) &
"'"
Next varItem2
strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
Else
strCriteria2 = "NewTable.Process Like ' * ' "
End If
Dim varItem3 As Variant
Dim strCriteria3 As String
If Me.listCast.ItemsSelected.Count > 0 Then
For Each varItem3 In Me.listCast.ItemsSelected
strCriteria3 = strCriteria3 & ",'" & Me.listCast.ItemData(varItem3) & "'"
Next varItem3
strCriteria3 = Right(strCriteria3, Len(strCriteria3) - 1)
Else
strCriteria3 = "NewTable.Cast Like ' * ' "
End If
Dim varItem4 As Variant
Dim strCriteria4 As String
If Me.listChamfer.ItemsSelected.Count > 0 Then
For Each varItem4 In Me.listChamfer.ItemsSelected
strCriteria4 = strCriteria4 & ",'" & Me.listChamfer.ItemData(varItem4) &
"'"
Next varItem4
strCriteria4 = Right(strCriteria4, Len(strCriteria4) - 1)
Else
strCriteria4 = "NewTable.Chamfer Like ' * ' "
End If
Dim varItem5 As Variant
Dim strCriteria5 As String
If Me.listFlange.ItemsSelected.Count > 0 Then
For Each varItem5 In Me.listFlange.ItemsSelected
strCriteria5 = strCriteria5 & "," & Me.listFlange.ItemData(varItem5)
Next varItem5
strCriteria5 = Right(strCriteria5, Len(strCriteria5) - 1)
Else
strCriteria5 = "NewTable.Flange_Type Like ' * '"
End If
strSQL = "SELECT * FROM NewTable " & _
"WHERE NewTable.MFG IN(" & strCriteria & ") and NewTable.Process
IN(" & strCriteria2 & ") and NewTable.Cast IN(" & strCriteria3 & ") and
NewTable.Chamfer IN(" & strCriteria4 & ") and NewTable.Flange_Type IN(" &
strCriteria5 & ") And NewTable.Group>=(" & cboGroupLow & ") And
NewTable.Group<=(" & cboGroupHigh & ") AND NewTable.Seal>=(" & cboSealLow &
") And NewTable.Seal<=(" & cboSealHigh & ") AND NewTable.Sa>=(" & cboSaLow &
") And NewTable.Sa<=(" & cboSaHigh & ") AND NewTable.Sq>=(" & cboSqLow & ")
And NewTable.Sq<=(" & cboSqHigh & ") AND NewTable.Sp>=(" & cboSpLow & ") And
NewTable.Sp<=(" & cboSpHigh & ") AND NewTable.Sv>=(" & cboSvLow & ") And
NewTable.Sv<=(" & cboSvHigh & ") AND NewTable.St>=(" & cboStLow & ") And
NewTable.St<=(" & cboStHigh & ") AND NewTable.Ssk>=(" & cboSskLow & ") And
NewTable.Ssk<=(" & cboSskHigh & ") AND NewTable.Sku>=(" & cboSkulow & ") And
NewTable.Sku<=(" & cboSkuHigh & ") AND NewTable.Sz>=(" & cboSzLow & ") And
NewTable.Sz<=(" & cboSzHigh & ") " & _
"AND NewTable.Smvr>=(" & cboSmvrLow & ") And NewTable.Smvr<=(" &
cboSmvrHigh & ") AND NewTable.Sds>=(" & cboSdsLow & ") And NewTable.Sds<=(" &
cboSdsHigh & ") AND NewTable.Sal>=(" & cboSalLow & ") And NewTable.Sal<=(" &
cboSalHigh & ") AND NewTable.Std>=(" & cboStdLow & ") And NewTable.Std<=(" &
cboStdHigh & ") AND NewTable.Sdq>=(" & cboSdqLow & ") And NewTable.Sdq<=(" &
cboSdqHigh & ") AND NewTable.Sdr>=(" & cboSdrLow & ") And NewTable.Sdr<=(" &
cboSdrHigh & ") AND NewTable.Sk>=(" & cboSkLow & ") And NewTable.Sk<=(" &
cboSkHigh & ") AND NewTable.Spk>=(" & cboSpkLow & ") And NewTable.Spk<=(" &
cboSpkHigh & ") AND NewTable.Svk>=(" & cboSvkLow & ") And NewTable.Svk<=(" &
cboSvkHigh & ") AND NewTable.Ra>=(" & cboRaLow & ") And NewTable.Ra<=(" &
cboRaHigh & ") AND NewTable.Rp>=(" & cboRpLow & ") And NewTable.Rp<=(" &
cboRpHigh & ") AND NewTable.Rv>=(" & cboRvLow & ") And NewTable.Rv<=(" &
cboRvHigh & ") AND NewTable.Rt>=(" & cboRtLow & ") And NewTable.Rt<=(" &
cboRtHigh & ")" & _
" AND NewTable.Rsk>=(" & cboRskLow & ") And NewTable.Rsk<=(" &
cboRskHigh & ") AND NewTable.Rku>=(" & cboRkuLow & ") And NewTable.Rku<=(" &
cboRkuHigh & ") AND NewTable.Rz>=(" & cboRzLow & ") And NewTable.Rz<=(" &
cboRzHigh & ") AND NewTable.RTp>=(" & cboRTpLow & ") And NewTable.RTp<=(" &
cboRTpHigh & ") AND NewTable.Rk>=(" & cboRkLow & ") And NewTable.Rk<=(" &
cboRkHigh & ") AND NewTable.Rpk>=(" & cboRpkLow & ") And NewTable.Rpk<=(" &
cboRpkHigh & ") AND NewTable.Rvk>=(" & cboRvkLow & ") And NewTable.Rvk<=(" &
cboRvkHigh & ") AND NewTable.PV>=(" & cboPV2Dlow & ") And NewTable.PV<=(" &
cboPV2DHigh & ") AND NewTable.PV_3D>=(" & cboPV3DLow & ") And
NewTable.PV_3D<=(" & cboPV3DHigh & ")" & _
"ORDER BY NewTable.Group, NewTable.Seal;"
qdf.SQL = strSQL
DoCmd.OpenQuery "SortedSeals", acViewNormal, acEdit
Set db = Nothing
Set qdf = Nothing
DoCmd.Close acForm, "frmSelectSeals"
End Sub
Sorry for the mess with the SQLstring but it is correct when shown in visual
basic
Dale Fye said:
Post the code you think is causing the problem, and I'll take a look at it.
--
Email address is not valid.
Please reply to newsgroup only.
swordsman8 said:
Hello Dale,
I am needing a little more help. I see what you are saying but I do not
think I can do correctly from what I have so here we go.
Field Name: Comments
Form Name: frmSelectSeals
I think the problem is either I am not setting up the filter properly or it
is because of the way I have the rest of my code writen. Part of what the
code does is make the SQL string placed in the querry. This deletes the last
search criteria and makes the new one the SQL string.
Could you please explain to me how to have it work properly.
I am getting better at writing the code but I a still a novice.
Thank you,
Swordsman8
On my OK button (used when you have made all of your selection) I have all
of my code writen in the button on click command. What it does it delete the
:
Swordsman,
How I would handle this would depend on whether you want to be able to
search for a single word, or for multiple key words at the same time.
1. If you want to filter for a single key word, I would recommend putting a
textbox in the forms header or footer, along with command buttons to filter
and clear the filter. Using this technique, you would type the keyword in
the text box, and add some code in the click event of the Filter command
button, something like:
Private sub cmd_Filter_Click
me.filter = "[CommentField] Like '*'" & me.txt_Filter & "'*'"
me.FilterOn = True
end sub
Private sub cmd_FilterClear_Click
me.filter = ""
me.filterOn = false
end sub
2. If you want to filter on multiple words, what I have done in the past is
something similar to above, but you have to parse the textbox contents first.
It might look something like:
Private Sub cmd_Filter_Click()
Dim A() As String
Dim intLoop As Integer
Dim varFilter As Variant
A = Split(Me.txt_Filter, " ")
varFilter = Null
For intLoop = LBound(A) To UBound(A)
varFilter = (varFilter + " AND ") _
& "[CommentField] Like '*'" & A(intLoop) & "'*'"
Next
If Len(varFilter & "") = 0 Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = varFilter
Me.FilterOn = True
End If
End Sub
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
:
OK so here is where I am and what I am trying to do next.
Where I am.
I have my Visual Basic code tied into the OK command button. I have a Form
to search through my data and only put the requested information in a query.
So far I have all of this working fine. I have the code clear the query each
time it is used and repopulate the querry with the new choices.(The Where
From etc...)
The Problem with what I am trying to do next.
It has been decided that there needs to be a comments column. They then
what the option to search for a key word in the column to define the results.
The other problem is that the comments field has no specific order of info
in it. This means while multiple rows have the same info in the comments
field each row could have a different was of saying it.
Example
Laser Cut, Production
Production, Laser Cut
So here is it.
In visual basic I need to be able to do the following:
Leave Field blank (so not to limit results)
Input a key word to limit results
Thank you,
Swordsman8
If you have question just let me know.
