Update field in table with characters (1 Viewer)

[fotiseliopo]

Guys,
I have a problem which I haven't got the knowledge yet to fix. The situation is as follow.
I have a main table names [final uses] where I have placed a check box and a command button to update the record based on the check box on the form . the outcome of the update process will result to an updated field in the table as "[user name] PD"
I have also placed in the query a date field so that all updates can be done same date based on the system date.
If I execute the command to update the record more than once during the day the out come of the update process will result to an updated field in the tabla as "[user name] PD PD" and if I execute the command a third time the field will be updated as "[user name] PD PD PD" and so forth.
How can I check if there is already within the field the CHAR "PD" after the user name so as not to update that record every time I execute the command and to update the new record I have checked on the form
Any ideas??

 

[Galaxiom]

The concept you have described is contrary to the fundamental principles of database design. One does not store multiple pieces of information in one field. The presence of a record in the login date field should be sufficient to record the fact they logged in.

If you need to display what you have described then you use a query to represent the stored data in that format.

 

[fotiseliopo]

Understood thanks,

 

[arnelgp]

so you need to update only if PD is not found?

update table1 set [username] = [username] & [forms]![formname]![textbox] where [username] Not like "*" & [forms]![formname]![textbox];

 

[fotiseliopo]

correct , thanks arnelgp,
give it a try and let you know

 

[arnelgp]

we need to add Space:

update table1 set [username] = [username] & " " & [forms]![formname]![textbox] where [username] Not like "* " & [forms]![formname]![textbox];

 

[Pat Hartman]

@fotiseliopo,
You have told us your solution and asked how to make it work. You haven't told us the problem you are trying to solve so it is unclear to us what the best solution might be.

I'm going to guess that you want to track who last changed a record. This does NOT require user action and should be controlled by the application so that it always happens when the record is changed even if the user forgets to press the button.

In the Form's BeforeUpdate event add the code to set the value.

Me.UserName = ???? -- not clear to me where this is coming from If your users log in, hide the login form rather than close it and reference the login form.

Me.UserName = Forms!frmLogin!txtUserName

If the users don't log in, you can use their UserName defined for their PC

Me.UserName = Environ("UserName")

I also log the date and time:

Me.LastupdateDT = Now()