How to display a modified recordset in a query ?

[KARI854]

Hello everybody,

I'm trying to create a query which displays records from a table but with some modifications.

I tried the following simple code which updates directly the source table. Could you tell me what's missing to display the modifications only in a query without modifying the source table ?

Thanks a lot because this is making me crazy...

Dim curDatabase As Database
Dim MySet As Recordset
Dim MyQu As QueryDef
Dim strStatement As String
strStatement = "SELECT * FROM [PP 02 RESS] "

Set curDatabase = CurrentDb
Set MySet = curDatabase.OpenRecordset(strStatement, dbOpenDynaset)

MySet.MoveFirst
If MySet![Plnt] = "0010" Then
MySet.AddNew
MySet![Plnt] = "0099"
MySet.Update
MySet.Bookmark = MySet.LastModified
End If

 

[pbaldy]

Try

SELECT IIf([Plnt] = "0010", "0099", [Plnt]) AS NewField FROM [PP 02 RESS]

The spaces in your table name are not a good idea.

 

[Kondwani]

I dont know if you preffer SQL, but just to add for your help, here is my code.
Although this one will change your source table, so
maybe you'd be better off using the SELECT IIF statement like pbaldy said
This will work only where ="0010" and change it to "0099"

private sub()
Dim dbs as database
dim Rst as recordset
set Rst =dbs.openrecordset("PP 02 RESS")
with Rst
.Movefirst
Do until .EOF
if ![Plnt] = "0010" then
.Edit
![Plnt] = "0099"
.update
ELSE
.Movenext
End If
End With
Loop
end sub

 

[pbaldy]

this here -----> strStatement = "SELECT * FROM [PP 02 RESS] "

i would ...

Dim dbs as database
dim a as recordset
set a =dbs.openrecordset("PP 02 RESS")
dbs.Execute ( "SELECT * FROM a ")

Even if it would work (which it won't), I'm not sure why you would do that (in fact, what is it supposed to do?).

 

[KARI854]

Thanks for your answers.
I have a table with different rows like :

Field 1 Field 2
A 1
A 6
A 3
B 1
B 4
B 5

And I want to make a query which is going to give me the following result (without changing the source table) :
Line 1 A X Y Z (New line if Field 1 is different from the previous Field 1)
Line 2 A 1 6 3 (If Field 1 is the same as the previous, then put all the values in one row)
Line 3 B X Y Z
Line 4 B 1 4 5

So it' quite complicated, that's why I wanted to create a query with VBA and not with SQL. But I don't see how to do this.

Thanks for your help

 

[DCrake]

A bit confusing but are you attempting to create a cross tab whereby the contents of row 1 is column headings and row 2 is the value for each coressponding column heading? If so you need a crosstab query.

David

 

[Kondwani]

I think i know what you are trying to do.
listing unique values in a query, right?

the criteria is values which have a count >0
so it will look somehting like

criteria: = [row].[value].(count) > 0

i think..
???