Multiple specific records based on user input on the form.

[Guest]

I’m trying to make a form with a text box for the user to be able to type in
a list of numbers (like “0001†or “0003†or “0005†) so that these numbers
can be used in a query later on to return those records (with details 0001,
0003, and 0005).

I’ve been trying to do this with this in the criteria

Like "*" & [Forms]![frm_B_LOAD]![detail_box] & "*" Or Is Null

[detail_box] = “0001†or “0003†or “0005â€

However for some reason it’s not taking. What am I doing wrong? I believe
I can successful do a ranged query based off of two given numbers from the
user, but I’m having trouble letting the user provide specific numbers.


Thanks in advance!

 

[Allen Browne]

As you found, this is not going to work, as the operators you type into the
text box will be treated as a value (not operators).

Where is this query going? You can probably build the SQL string (or WHERE
clause) from the values instead of placing them in the query.

For example, if this is to filter a report, you could leave the criteria out
of the query, and build the WhereCondition for OpenReport from the values in
the text box. If it is to filter a form, you could build the same string,
and assign it to the Filter property of the form (remembering to set
FilterOn as well.) Or, if you build the entire query statement, you could
assign it to the SQL property of a QueryDef, or the RecordSource property of
a form, or the RecordSource of a report (in the report's Open Event), or use
it to OpenRecordset() in code.

If the user is choosing from existing values, you could also use a
multi-select list box, as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Of if you just want the user to be able to type a bunch of keywords into a
text box, and then show any record that has any of these words in (say) the
Notes field, this example shows how to set the form's Filter in the
AfterUpdate event of the text box where the user enters the words:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to make a form with a text box for the user to be able to type
in
a list of numbers (like "0001" or "0003" or "0005" ) so that these numbers
can be used in a query later on to return those records (with details
0001,
0003, and 0005).

I've been trying to do this with this in the criteria

Like "*" & [Forms]![frm_B_LOAD]![detail_box] & "*" Or Is Null

[detail_box] = "0001" or "0003" or "0005"

However for some reason it's not taking. What am I doing wrong? I
believe
I can successful do a ranged query based off of two given numbers from the
user, but I'm having trouble letting the user provide specific numbers.


Thanks in advance!

 

[Guest]

Indeed, what I want to do is eventually do an append query based off of the
user's input. I tried and seem to be having limited success by creating
enough text fields for the user to type in one at a time, and then
referencing each of those fields in my query. Like so:

Like "*" & [Forms]![frm_B_LOAD]![DETAIL_001] & "*" Or Is Null Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_002] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_003] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_004] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_005] & "*" Or Is Null)Or (Like "*" &

This creates a very long lag time before returning my results. (I have
around 80, but considering dropping it to around 30 with the ability to do
ranged criteria as well).

Do you believe I would have success in trying the "multi-select list box"
option contained in your answer? But will this only work for reports, and
forms?

Thanks!

As you found, this is not going to work, as the operators you type into the
text box will be treated as a value (not operators).

Where is this query going? You can probably build the SQL string (or WHERE
clause) from the values instead of placing them in the query.

For example, if this is to filter a report, you could leave the criteria out
of the query, and build the WhereCondition for OpenReport from the values in
the text box. If it is to filter a form, you could build the same string,
and assign it to the Filter property of the form (remembering to set
FilterOn as well.) Or, if you build the entire query statement, you could
assign it to the SQL property of a QueryDef, or the RecordSource property of
a form, or the RecordSource of a report (in the report's Open Event), or use
it to OpenRecordset() in code.

If the user is choosing from existing values, you could also use a
multi-select list box, as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Of if you just want the user to be able to type a bunch of keywords into a
text box, and then show any record that has any of these words in (say) the
Notes field, this example shows how to set the form's Filter in the
AfterUpdate event of the text box where the user enters the words:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to make a form with a text box for the user to be able to type
in
a list of numbers (like "0001" or "0003" or "0005" ) so that these numbers
can be used in a query later on to return those records (with details
0001,
0003, and 0005).

I've been trying to do this with this in the criteria

Like "*" & [Forms]![frm_B_LOAD]![detail_box] & "*" Or Is Null

[detail_box] = "0001" or "0003" or "0005"

However for some reason it's not taking. What am I doing wrong? I
believe
I can successful do a ranged query based off of two given numbers from the
user, but I'm having trouble letting the user provide specific numbers.


Thanks in advance!

 

[Allen Browne]

The query itself cannot refer directly to the items selected in the list
box. (There is a workaround where the query calls a function that returns a
value to use in the WHERE clause, but I don't believe that's your best
option.)

The goal is an Append query. If your switch your query to SQL View (View
menu, from query design), you can see the SQL statement Access creates
behind the scenes. The best and most efficient solution is going to be to
build a string like that, and exeucte it.

Your query contains these clauses:
INSERT INTO MyTargetTable (Field1, Field2, ...)
SELECT Field1, Field2, ...
FROM MyOriginalTable ...
WHERE ...
ORDER BY ...;

The FROM clause might contain several tables, with JOINs between them.
The ORDER BY clause may be absent, or there might be a GROUP BY.
In any case, you can copy the first 3 clauses straight into your code.
The only part that needs to be built up in code is the WHERE clause.

Your code will therefore contain the stub of the SQL statement (all the text
before the WHERE clause), and the tail of the SQL statement (all the text
after the WHERE clause), and we will build the WHERE clause form the
non-blank boxes the user chooses.

The previous post explained how to build the WHERE clause. Add that to the
bit below, and the job is done:

Dim strWhere As String 'This is for the WHERE clause.
Dim lngLen As Long 'Length of the string.
Const strcStub = "INSERT INTO ... SELECT ... FROM ... "
Const strcTail = " ORDER BY ...;"

'Build the WHERE clause here.

db.Execute strcStub & strWhere & strcTail, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Indeed, what I want to do is eventually do an append query based off of
the
user's input. I tried and seem to be having limited success by creating
enough text fields for the user to type in one at a time, and then
referencing each of those fields in my query. Like so:

Like "*" & [Forms]![frm_B_LOAD]![DETAIL_001] & "*" Or Is Null Or (Like "*"
&
[Forms]![frm_B_LOAD]![DETAIL_002] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_003] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_004] & "*" Or Is Null)Or (Like "*" &
[Forms]![frm_B_LOAD]![DETAIL_005] & "*" Or Is Null)Or (Like "*" &

This creates a very long lag time before returning my results. (I have
around 80, but considering dropping it to around 30 with the ability to do
ranged criteria as well).

Do you believe I would have success in trying the "multi-select list box"
option contained in your answer? But will this only work for reports, and
forms?

Thanks!

As you found, this is not going to work, as the operators you type into
the
text box will be treated as a value (not operators).

Where is this query going? You can probably build the SQL string (or
WHERE
clause) from the values instead of placing them in the query.

For example, if this is to filter a report, you could leave the criteria
out
of the query, and build the WhereCondition for OpenReport from the values
in
the text box. If it is to filter a form, you could build the same string,
and assign it to the Filter property of the form (remembering to set
FilterOn as well.) Or, if you build the entire query statement, you could
assign it to the SQL property of a QueryDef, or the RecordSource property
of
a form, or the RecordSource of a report (in the report's Open Event), or
use
it to OpenRecordset() in code.

If the user is choosing from existing values, you could also use a
multi-select list box, as explained here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Of if you just want the user to be able to type a bunch of keywords into
a
text box, and then show any record that has any of these words in (say)
the
Notes field, this example shows how to set the form's Filter in the
AfterUpdate event of the text box where the user enters the words:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

I'm trying to make a form with a text box for the user to be able to
type
in
a list of numbers (like "0001" or "0003" or "0005" ) so that these
numbers
can be used in a query later on to return those records (with details
0001,
0003, and 0005).

I've been trying to do this with this in the criteria

Like "*" & [Forms]![frm_B_LOAD]![detail_box] & "*" Or Is Null

[detail_box] = "0001" or "0003" or "0005"

However for some reason it's not taking. What am I doing wrong? I
believe
I can successful do a ranged query based off of two given numbers from
the
user, but I'm having trouble letting the user provide specific numbers.