change each record of a query by vba

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I want to change the value of a special field of each
record in a endless form provided by a query.
There are 3 fields for each record, for example:
UserID, Password und ProjectID.
Now, I would like to change new each password of each row.

For example:

Peter - passwort1 - 2
Peter - passwort2 - 1
Peter - passwort2 - 4

after vba:

Peter - newpassword - 2
Peter - newpassword - 1
Peter - newpassword - 4

Thank you for your help
 
Alex,

I am not 100% clear what you mean, but I would expect that this is a job
for an Update Query. If you want to run the update in a macro, then the
OpenQuery action would be applicable. If you want to do it in VBA,
well... there are a number of approaches to this. But first, maybe you
could just clarify whose password is getting changed, and where the new
values come from, and whether there is anyone else in the database apart
from Peter.
 
Okay, Peter is the omly in in the query, because it is
specified by using "LIKE [Forms]![Formular1]![Text0]" in
Criteria. ([Text0]is for example "Peter")
The unique user in this query has now fo instance 3
differten projectIDs and for instance the same password.
And now I want to change with vba the password of each
row/record. For example with an Inputbox:
var = InputBox("What should be your next password?")

And now ich passwordfield in the records should be changed
Is it now a better discription? Thanks a lot!
 
Alex,

Yes, thank you for the further explanation.

On the same form where you have the Text0 textbox for the person's name
criteria, can you put another unbound textbox for the entry of the new
password? Then, make your existing query into an Update Query (select
Update from the Query menu in design view of the query), so you can put
the new password in? The SQL view of such a query will look something
like this...
UPDATE YourTable SET Password = [Forms]![Formular1]![NewPassword]
WHERE UserID = [Forms]![Formular1]![Text0]
 
Tanks a lot! Good idea without vba.
Can I also send you an email, if I have any further
questions?
-----Original Message-----
Alex,

Yes, thank you for the further explanation.

On the same form where you have the Text0 textbox for the person's name
criteria, can you put another unbound textbox for the entry of the new
password? Then, make your existing query into an Update Query (select
Update from the Query menu in design view of the query), so you can put
the new password in? The SQL view of such a query will look something
like this...
UPDATE YourTable SET Password = [Forms]![Formular1]! [NewPassword]
WHERE UserID = [Forms]![Formular1]![Text0]

--
Steve Schapel, Microsoft Access MVP
Okay, Peter is the omly in in the query, because it is
specified by using "LIKE [Forms]![Formular1]![Text0]" in
Criteria. ([Text0]is for example "Peter")
The unique user in this query has now fo instance 3
differten projectIDs and for instance the same password.
And now I want to change with vba the password of each
row/record. For example with an Inputbox:
var = InputBox("What should be your next password?")

And now ich passwordfield in the records should be changed
Is it now a better discription? Thanks a lot!
Click to expand...
.
Click to expand...
 
The system asks me everytime if it is okay that I update
data. I can put it off in the menu extras -> option -> etc.

But I think with this status I changed only my Access
programm. Is it possible that I blocked tzhis information
message for all users with vba ?
Database is in an network folder and nobody should get a
information if he willl really update data.
Thanks
-----Original Message-----
Alex,

Yes, thank you for the further explanation.

On the same form where you have the Text0 textbox for the person's name
criteria, can you put another unbound textbox for the entry of the new
password? Then, make your existing query into an Update Query (select
Update from the Query menu in design view of the query), so you can put
the new password in? The SQL view of such a query will look something
like this...
UPDATE YourTable SET Password = [Forms]![Formular1]! [NewPassword]
WHERE UserID = [Forms]![Formular1]![Text0]

--
Steve Schapel, Microsoft Access MVP
Okay, Peter is the omly in in the query, because it is
specified by using "LIKE [Forms]![Formular1]![Text0]" in
Criteria. ([Text0]is for example "Peter")
The unique user in this query has now fo instance 3
differten projectIDs and for instance the same password.
And now I want to change with vba the password of each
row/record. For example with an Inputbox:
var = InputBox("What should be your next password?")

And now ich passwordfield in the records should be changed
Is it now a better discription? Thanks a lot!
Click to expand...
.
Click to expand...
 
Alex

If you are using a macro with the OpenQuery action to run the Update,
put a SetWarnings/No action in the macro before the OpenQuery action.
 
Thanks a lot
-----Original Message-----
Alex

If you are using a macro with the OpenQuery action to run the Update,
put a SetWarnings/No action in the macro before the OpenQuery action.

--
Steve Schapel, Microsoft Access MVP


.
Click to expand...
 
Back
Top