Replace / Substitute command

[policechiefwiggum]

Hi All,

I've got a bit of code that works well for the 1st part of a process but i'm not sure how to "undo" the change it makes for the 2nd part.

I have this code: (note - i can't spell and "ProjectTitile" is the correct name for the form field!!)

If Me.proj_status.Value = "On Hold" Then
Me.ProjectTitile.Value = Me.ProjectTitile.Value & " (On Hold)"
Me.Form.Caption = "Updating Project ID '" & Me.uatproject.Value & "' Project Name '" & Me.ProjectTitile.Value & "'"

Dim UATProjectSTR As String
UATProjectSTR = Me.uatproject.Value
Dim UpdateProjectSQL As String
Set ConnectDatabase = CurrentProject.Connection
Set DatabaseSQL = New ADODB.Command

UpdateProjectSQL = "UPDATE [projects] SET [ProjectTitle] = '" & Me.ProjectTitile.Value & "' WHERE [UATID] = " & UATProjectSTR

DatabaseSQL.ActiveConnection = ConnectDatabase
DatabaseSQL.CommandText = UpdateProjectSQL
DatabaseSQL.Execute
MsgBox ("Project " & ProjName & " has been placed on Hold.")

The above code works perfectly by adding "(On Hold)" when a project is placed On hold, however what i want to do is remove just the "on Hold" part of the project title when the project is either opened or closed.

I've been told i can do this using the Replace or Substitue command but i have no idea how to use this command, i've had a bash with this

Replace Me.proj_status.Value, " (On Hold)", ""

I'm not sure if this is doing anything, and even if it is i cant see how it wouild work because i've not got any code to update the database with the new project name!!

Thanks
for your help as always

 

[randy700]

Something like this?

Me.ProjectTitile.Value = left(Me.ProjectTitile.Value, len(Me.ProjectTitile.Value) - 10)

Randy

 

[policechiefwiggum]

Thanks Randy 700,

I thought about doing something like this but my concern was i'd have to do an if statement to run this code only if the drop down was set to "on Hold" before being changed to open or closed.

and i'm not sure how to do that. i can do an if statement on the current value, but not the previous!

 

[MajP]

Public Function removeOnHold(str As Variant) As String
If Not IsNull(str) Then
removeOnHold = Replace(Expression:=str, Find:="(On Hold)", Replace:="")
End If
End Function

Test

Public Sub aremoveOnHold()
Debug.Print removeOnHold("ProjectA (On Hold)")
End Sub

So you could use this function with either some similar code or sql update query

 

[TheAceMan1]

[blue]I've been told i can do this using the Replace or Substitue command but i have no idea how to use this command, ...[/blue]

In any code module type the word, put the cursor on the word, then hit F1 to read about it!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

All these messages and I think everyone has missed the mark. IMO, the project status should be stored in a separate field, not in the title. You can always display the status in forms and reports. I feel changing the title is the wrong solution. Next, someone will want to change the requirements to include (Delayed), (Terminated), (Active), or (Whatever). It is much easier to simply maintain a status field and not soil the project title.

Duane
Hook'D on Access
MS Access MVP

 

[policechiefwiggum]

Dhookom - with the facts i've given you i would agree with you so let me give you a bit more background.

The database i've designed is for my manager to assign projects to various memebers of my team.

on the "main form" my manager selects the person he wants to view from a drop down, this then populates a 2nd dropdown with validation of "by testers name & project status in "open", "On Hold" (i.e. everything but closed) however he needs to see at this stage that the project is on hold - hence the reason for changing the project title.

When he selects the project he wants to view he then clicks on "View Project" which opens a new form which has a project status field. this is where he can place projects on hold.

Hope this clarifies things and you no longer think i'm mental (although your not wrong if you do!!) ;o)

 

[policechiefwiggum]

Here you go, i've asked for so much help (and never been let down!) so its only fair i let you see my handy work!

if you have any feedback its greatfully taken, but do please bear in mind, this is my 1st access / database project.

Oh and you may want to run for the hills!! my bosses boss like the database so much version 2.0 is going in to development next year with more features required!!!

 

[dhookom]

I still don't see why you would want to change the title. The 2nd dropdown can use a Row Source like:

SELECT ProjectID, ProjectTitile & IIf(Proj_Status = "On Hold"," (On Hold)",Null)
FROM Projects
WHERE Proj_Status IN ( "open", "On Hold")
ORDER BY ProjectTitile;

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Not to be overly critical ;-) but I would make some changes in version 2.0.

Is there a reason why you worked so hard to create unbound forms? If you really want to keep coordinatorupdate form unbound, it seems you could create a single recordset to pull all the fields to stick into the text boxes.

I would also normalize to get rid of most of the yes/no fields and dates. I would have a table related to the main project table that would store tasks and when they were completed. This might look like:
[tt][blue]
tblProjTasks
UATID Task TaskDate PctComplete
================================================
203 TestPlanComplete 12/3/2009 100
203 TestPlanApproved 12/5/2009 100
203 ScriptsComplete 50
203 UATComplete 0
[/blue][/tt]
You would have a lookup table of tasks and possibly their sequence. Then you could add new tasks when your pointed-haired-boss changes the task list.

I would do something similar with the PM, TPM, and TM manager fields. These should be in a separate related table. You may need to track more project roles in the future or have more than one person per role.

I would also never store a percent as text. Consider using a numeric field.

All forms should have their Allow Design Changes property set to "Design View Only"

I would also pull out the OldUpdate field to put in a related table so each update would create its own record with the project ID, User, Date, and Comments.

Duane
Hook'D on Access
MS Access MVP

 

[policechiefwiggum]

Thanks for your feedback dhookom,

TBH the reason i've done what i've done is probably due to lack of experience.

I'm sure a lot of what i've done can be done in a much better and simpler way. as for the unbound forms that was done because the file is hosted on a network server and will, under v2.0 be accessed by up to 10 people as users and potentially by 100's of people via a webform over MS SharePoint, i was told that binding the fields would mean they would be populated on the fly, which over the network would lead to slow performance, where as the way i've coded the data the way i have been populated on the form load to aid performance.

v2.0 is going to have so many changes from this version i think i'll build it from the ground up again which will give me the option to re visit how all the data is stored.

thanks again
Pete

 

[MajP]

This is not a shortcoming of access. To check for null
if isnull(me.username) then
But better
if trim(me.userName & " ") = "" then

this checks for null, empty string, or empty spaces

this statement always returns null hence false. It is never true.
if something = null