creating a select statement to select records from the last update

[Maggie24]

Hi all,

I want to run a select statement which will bring me back all the records i have updated in my last update statement (to be displayed in a list box)

how would i create a select statement to do this?? Or what would be the best approach to doing this??

Many thanks,

Maggie

"Work is the curse of the drinking classes

 

[Crowley16]

the simple answer is: you can't

what you can do is to firstly select all the records that WOULD be updated by your update statement, and THEN update them...

 

[KenReay]

Hi

Well you could, if your Update query included the update of a column a known (unique) value, you could then select on that value

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Maggie24]

hmmm

well my problem is i use an initial search query(select stmt) based on an a word entered in a text box to allow the user to select a number of record to update (based on the text box value)

From the values returned to the list box the user selects the records he wishes to update. So using a for loop based on the itemselected property i update the records the user has selected.

i want to populate the listbox with the set of updated records but if i set the listbox.rowsource = to the inital search query it brings me an updated list of all the records initally returned by the search query (i only want the ones which the user selected to be updated from the list of records initially returned)

am i explaining my problem okay and does anyone have any suggestions as to how i can display only the records which theb user selected to be updated??

MANY THANKS,
MAGGIE

"Work is the curse of the drinking classes

 

[Crowley16]

I see...

why don't you just store the records that are selected then?

declare an array, and in your for loop, add the item being updated to that array...
or you could just build up a sql statement directly in the for loop

sql = "SELECT blah FROM blah WHERE "

for blah
update blah
sql = sql & "value = '" & blah & "' OR "
next blah

 

[KenReay]

Hi

Or if the List box is populated via a table or query, and the mechanism to select is a yes/no in that table, then


SELECT * FROM UpdatedTable WHERE PrimeKey IN (SELECT Primekey FROM ListBoxTable WHERE Selected = True);

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Maggie24]

Crowley 16 or anyone else who knows arrays!

I have created an array which holds all the product nos of records i have updated.

i now want to select from my db based on the data in the array.

I have:
List42.RowSource = "Select * from tblProducts where Product No in '" & myarray & "'"

but it is giving me a data error.

what is the correct way to select from a table based on values in an array?

PLEASE HELP!

"Work is the curse of the drinking classes

 

[RoyVidar]

Something like this, just detailing Crowley16's suggestion a little?

[tt]dim blah as long
dim sCrit as string
for blah = 0 to ubound(myarray)
sCrit = sCrit & "," & myarray(blah)
next blah
Me!List42.RowSource = "Select * from tblProducts where " & _
"[Product No] in (" & mid$(scrit,2) & ")"
Me!List42.requery[/tt]

If the product number is text, perhaps alter a little:

[tt] sCrit = sCrit & "','" & myarray(l)
...
...in (" & mid$(scrit,3) & "')"[/tt]

But wouldn't KenReay's suggestion be better/easier?

Roy-Vidar

 

[jksmi]

Hi,

just to add to KenReay's suggestion - if you timestamp your updates, rather than use an id, you have a simple way to call up your last update info and data about when stuff was actually last updated (useful for audits data integrity, etc...)

HTH, Jamie
FAQ219-2884

 

[Maggie24]

Hello all,

thank you all for your help and suggestions.

Crowley 16's suggestion of declaring an array, and having a for loop, which adds the item being updated to that array did the trick for me,

thanks a million

Maggie

"Work is the curse of the drinking classes

 

[PHV]

And what about this ?
List42.RowSource = "Select * from tblProducts where [Product No] in (" & Join(myarray, ",") & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244