Find end of string

[NigelBrown]

Hello,

I hope someone can help me, I have the below code, this runs on every click of the button - what I would like it to do is instead of replace the current text in [AssignedBy1], if possible I would like it to add on after the comma ? (I would like it to work as an audit trail) - Any help would be greatly appreciated - I am gathering it needs to be a ".Find"," Then ?????"

Private Sub ChangeOwner_Click()
On Error GoTo Err_ChangeOwner_Click
 
 FullName = DLookup("Name", "useraccessnames", UserName = getUserID)
 Me.AssignedBy1 = FullName & Date & ","
 
    Dim stDocName As String
  stDocName = "qry_ChangeOwner_AssetServices_Update"
  DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks
Nigel

[ajalwaysus]

@NigelBrown
I may not understand the question fully, but would this work?

 Me.AssignedBy1 = Me.AssignedBy1 & "," & FullName & Date & ","

That way you take what you already have and just add on to the end of it and then reassign it to the field.

Also as NeoPa will most likely fix later, please wrap all your code in [code] brackets.

-AJ

[NeoPa]

@ajalwaysus
You probably want to leave off the trailing ",", but otherwise this should be what you're looking for Nigel.
@ajalwaysus
Spot on AJ. Please remember for next time Nigel.

[NeoPa]

It's just occurred to me that it may be possible to add the first one using this method. If so, some slightly different code would be required to avoid starting with a ",".

Me.AssignedBy1 = (Me.AssignedBy1 + ",") & FullName & Date()

See Using "&" and "+" in WHERE Clause for the explanation.

[NigelBrown]

Thanks guys - it does work but I need it to update the string i.e if the field AssigedBy1 already has for example:

User25/09/2009,User29/09/2005,

Anytime the button is clicked I would like it to add the new info, i.e if it was pressed today it would add onto the string User02/10/2009 so it would look like User25/09/2009,User29/09/2005,User02/10/2009 and so on.

It is to track records that get allocated to different departments until they are resolved, the reason I thought about the comma was to give the code something to find - in this case it would want to find the last comma and then update with User & Date. The code above does add on the end of the string but it also updates to todays date only - it over writes any previous info.

Thanks - really appreciate your time

[NeoPa]

I'm confused. Which code above?

Have you tried the code in post #4? Does it not work for you exactly? If not please explain clearly what is different from what you require.

[NigelBrown]

Yes I have tried the code in #4 - it applies the User and a date, but overwrites any data that is already in [AssignedBy1]. I need it to add on the end of the current string - i.e. if [AssignedBy1] already has "User01/10/2009" (assuming that it was done yesterday) then today if the on click was activated i would like it to show "User01/10/2009,User02/10/2009" and so on - if this is possible ?
Thanks

[ajalwaysus]

Nigel,

Are you assigning anything to Me.AssignedBy1 before this line of code, because there is no reason why this code does not take what is already in Me.AssignedBy1 (i.e. "User25/09/2009") and then assign ", " and "User29/09/2005" to the end of that. If this is not working then please do a

Debug.Print (Me.AssignedBy1)
 

before and after the line of code provided in post #4 then post the results please.

-AJ

[NigelBrown]

Hi AJ,

No - AssignedBy1 will be blank to start with, I have added the debug.print and the same thing happens - it overwrites anything in AssignedBy1 with "User and the latest date (i.e today) - it does not even place a comma now i just run it on one item and below is the result :

Nigel Brown02/10/2009

This did say NigelBrown01/10/2009.

Thanks

[FishVal]

Did you ever follow the advices given to you in post#2 and post#4 ?

[ajalwaysus]

Nigel,

This may sound condescending but I only wish to get to the bottom of this, not to offend anyone, but this doesn't make a lick of sense, so I have some questions.

1. Before you run the code provided by us, what is in Me.AssignedBy1?
2. Is Me.AssignedBy1 the field that contains the info you wish to concatenate additional info onto?

And this is a breakdown of what this code should do:
This will take the value already in the Me.AssignedBy1 field and add ", " to the end of it:

Me.AssignedBy1 & ","

Then this code will add the new data you wish to add on to the end of Me.AssignedBy1:

& FullName & Date & ","

And then this code takes that value and puts it back into Me.AssignedBy1 overwriting what was in there originally:

Me.AssignedBy1 = Me.AssignedBy1 & "," & FullName & Date() & ","

If there was no data in Me.AssignedBy1 then you will only have the new data, if there was data in Me.AssignedBy1 then this will add the new data on to the end, there is no reason I can see why this isn't happening.

-AJ

[NigelBrown]

Its OK - I am prob missing something here (and yes FishVal I have followed 2 & 4) - below is what I have so far - as I run this agreed if the field is blank then the info is fine (this works) if the filed currently holds a value then is gets overwritten.

FullName = DLookup("Name", "useraccessnames", UserName = getUserID)
Debug.Print
 Me.AssignedBy1 = Me.AssignedBy1 & "," & FullName & Date & ","
Debug.Print (Me.AssignedBy1)

Answer to your questions 1. Either the above value or blank (this depends on wether a user has allocated it yet to a new dept) and 2. and yes {AssignedBy1] is the field that I wish to concatenate info onto.

[FishVal]

Well.

The only thing I could suggest you is to debug calculation in

Me.AssignedBy1 = Me.AssignedBy1 & "," & FullName & Date & ","
Debug.Print (Me.AssignedBy1)
 

code line.
Put a breakpoint on it and trigger the code. When execution stops on the breakpoint (if ever) check what Me.AssignedBy1 and Date returns and what value has FullName variable.

[NigelBrown]

This is based on Me.AssignedBy1 already having "NigelBrown01/10/2009"

when the code is run again the debug shows Me.AssignedBy = NigelBrown02/10/2009, FullName = NigelBrown and Date=02/10/2009. It will then overwrite the original text of NigelBrown01/10/2009 not add to the string. Dont worry about it guys I will find a solution somehow - thanks for all the help anyway. Just to bear in mind this is not a text box that I am updating but a field from a query that I am viewing through a Form if this changes anything?

[FishVal]

...
when the code is run again the debug shows Me.AssignedBy = NigelBrown02/10/2009
...

I guess you've meant Me.AssignedBy1. If so, then the value is overwritten before the code runs.

[NeoPa]

Nigel,

I'm off out to celebrate my Anniversary tonight so I have little time. I would just point out that your code does not reflect the code in post #4. That code should give you exactly what you're asking for. If it doesn't then it's a fair bet that you've not copied it correctly.

I will revisit this over the weekend, so please let me know how you get on (please try it again). I will respond depending on your reported findings.

[NigelBrown]

Hi,
Hope you had a good weekend neopa - I have tried the code below and it still does the same thing - it overwrites the data that is already in Me.AssignedBy1 ? I appologise if I am doing this wrong? i.e I just run it where Me.Assignedby1 = "MikeWhite02/10/2009" I expected after I run it for Me.AssignedBy1 to = "MikeWhite02/10/2009,NigelBrown05/10/2009"

Me.AssignedBy1 = (Me.AssignedBy1 + ",") & FullName & Date()

but it overwrite the original text to just show "NigelBrown05/10/2009.

[NeoPa]

This is very strange Nigel. Can you post your whole procedure for me please.

Also, can you add these lines before and after that line. It should actually end up as this :

MsgBox Me.AssignedBy1
Me.AssignedBy1 = (Me.AssignedBy1 + ",") & FullName & Date()
MsgBox Me.AssignedBy1

Lastly, post the values printed as they appear (both before and after the line that updates it). I'm intrigued by this. It is behaving most strangely if all you say is true.

PS. Don't forget the CODE tags around your code. This is mandatory because it's pretty illegible without.

[NigelBrown]

its ok Neopa - the code does work - I was setting me.assignedby1 to null after the code had run (full procedure below) - however if I wanted to run the code against the form instead of a txtbox - can this be done? when I try and run the code directly to a field on the form i get a Write Confilct error with options Save Record, Copy to Clipboard, Drop Changes ? anyway around this ? after the user is applied the procedure runs an update query which takes Me.Assignedby1 and applies it to a field ina query (was trying to do it this way to avoid the confict error)?

Private Sub ChangeOwner_Click()
On Error GoTo Err_ChangeOwner_Click
 
DoCmd.SetWarnings False
 
FullName = DLookup("Name", "useraccessnames", UserName = getUserID)
 
Me.AssignedBy = (Me.AssignedBy + ",") & FullName & Date
 
    Dim stDocName As String
 
     stDocName = "qry_ChangeOwner_AssetServices_Update"
 
    If msgbox("Are you sure you want to Change the owner", vbYesNo, "Change Owner") = vbYes Then
 
    'DoCmd.Save
 
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.Requery
 
    Else
 
    'Me.AssignedBy1 = Null
    GoTo Exit_ChangeOwner_Click:
 
 
Exit_ChangeOwner_Click:
    Exit Sub
 
Err_ChangeOwner_Click:
    msgbox Err.Description
    Resume Exit_ChangeOwner_Click
    End If
 
 
End Sub

[NigelBrown]

Hi NeoPa - I solved it with the below - this way I can null out me.assignedBy1 and aviod the write confilct - the below is working fine (the below brings in all the values i.e User/Date from me.assigned by, applies the new user info - runs the update query then Nulls out me.assignedBy1 ready for the next update) - thanks for all your help

Me.AssignedBy1 = Me.AssignedBy
FullName = DLookup("Name", "useraccessnames", UserName = getUserID)
Me.AssignedBy1 = (Me.AssignedBy1 + ",") & FullName & Date

[NeoPa]

Nigel,

You need to use the CODE tags when posting code. Please use the tags in future.

Administrator.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).

[NeoPa]

Good for you Nigel. I'm pleased you resolved your problem.

I was just bracing myself to read through it all properly to understand what was going on when you posted. Nice timing :)

[NigelBrown]

Thanks NeoPa - will do - have another question regarding the code above (1 problem after another and this I have no idea) it will now not find the user ????? it was working fine - getting very frustrating> If I add in

getUserID = Environ("UserName")

it then errors on Object Required.
I have no idea why this is now happening as have not changed anything other than what we have discussed.

[NigelBrown]

Hi neoPa - It seems the DLookup is not working now

FullName = DLookup("Name", "useraccessnames", UserName = getUserID)

The getUserID is picking up the userID but "UserName" is saying null - I have copied in my table that it is refering to below - any help would be good

The table name is : useraccessnames, and the field names are UserName and Name: as follows:

UserName Name


At the moment now it is only picking up the first name in the table.
thanks

[NigelBrown]

Again - my appologies to this forum - I have resolved this now so please ignore my previous note - Neo I have adjusted my Dlookup as below.

FullName = Nz(DLookup("Name", "useraccessnames", "UserName = '" & getUserID & "'"), True)

It seems to work now - but please feel free to advise if I have any potential errors in here. Thanks again to all that posted.

[NeoPa]

@NigelBrown
Absolutely no problem with that Nigel. Most of us have put posts in we've later realised weren't required after some further thought. Perfectly normal.
@NigelBrown
I can't imagine that the value you want to put in [FullName], assuming the DLookup() resolves to Null, would be True. Are you sure this is correct.

As for the DLookup() call itself -Spot on.

[NigelBrown]

You are right NeoPa, I have now set this to False - my appologies - have not done VBA code for a couple of years so refreshing my memory - thanks for all your help - got me out of a hole.
thanks again

[NeoPa]

I'm still a little confused Nigel. Surely [FullName] would require a textual value. Both True & False are boolean values. I can't see how that would make sense (whichever is used).