Re: Writing a query using VBA - Variable where clause

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can reset everyone in the collection ItemsSelected to false, then, walk
that collection and set its member to true for member items IN the "temp"
table. (Temporary is then an bad name for such a table which should survive
the session, though :-) )



Vanderghast, Access MVP


"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:51C7FAFC-49FD-4F41-8CCA-B8E5D6B15EC7@xxxxxxxxxxxxxxxx
Thank you so much. I figured out that you had already dropped an answer
to
that part afterwards. SOrry for making you type that which you had already
answered.

I have one last question.

I want to make my form remember the last settings and default to them the
next time the form is opened. How can I make a list box open with entries
selected based on the contents of the temp table?

obviously I want the user to be able to change those settings, thus
overriding the temp table.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


"Michel Walsh" wrote:

Same as for building the IN string, or the parameter string (see the
example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string, instead
of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step
(before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP



"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:BAD73C7F-E1BF-448F-A351-FFFB0AAA2324@xxxxxxxxxxxxxxxx
How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


"Michel Walsh" wrote:

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that
temporary
table.

2- Make an ad hoc query from a string. The string will be a complete
SQL
statement that you will use appropriately (depends on the context).
See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected
items,
a little bit like solution 2) but send that string as a parameter to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:D82ED8D1-2136-4687-ADB6-E79DF137414D@xxxxxxxxxxxxxxxx
Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a
list
box.

If the list box has N entries, I want the user to be able to
highlight
n
entries, click a button, and generate a query that looks something
like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would
have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!








.

Relevant Pages

  • Re: Writing a query using VBA - Variable where clause
    ... but I'm still alittle lost on execution. ... that collection and set its member to true for member items IN the "temp" ... 2- Make an ad hoc query from a string. ...
    (microsoft.public.access.queries)
  • Re: Writing a query using VBA - Variable where clause
    ... the item is not in the temp table. ... Eng. ... If the value is a string, ... 2- Make an ad hoc query from a string. ...
    (microsoft.public.access.queries)
  • Re: Appending strings to integers
    ... I used the code you suggested and I can assign it to a string variable ... Dim temp As String ... What I can't do is assign that string to the cell in the data grid ... query result was numeric. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)