I need some help with code and I don't know how to search for my problem

[Jon123]

I have a checklist type form where user enter their initials to signoff tasks that they have performed. Some times a checklist goes into a rework where certian steps or tasks need to be repeated. When the step is repeated someone else will complete that step and they will enter their initials. So as an example say step1 has the initials 'aa' entered and the form goes into rework. step 1 will be changed by code to '1- aa' the 1- means its a rework and allows someone else to double click that step and enter their initials

to it as they did the rework so now step1 = 'aa, bb' and and the 1- is gone and I have the initials of both people that complted this task. It is possible for the rework to happen again and then a 3rd set of initials would be entered.
Now here is my problem. If the step is in rework and the user does not complete the step and they close the form I want to remove their initals and keep the step in rework. so '1- aa, bb' would become 'aa, bb' then the incomplete would put it back to '1- aa. I can't figure out how to drop the last set of initials entered.

This code puts the step into rework.

  If Not IsNull(Me.step3) And Left(Me.step3, 2) <> "1-" Then
                Me.[step3] = "1" & "- " & Me.[step3]

This is the code on the double click line for the step

  If (Me.[ReworkFLAG] = 1) And (Me.[step3] Like "1-*") And IsNull(Me.[CompleteCE]) Then
  DoCmd.OpenForm "Frm-initals"

The Frm-initialopens and I have this code in the forms property ON OPEN

    Me.txt1 = Forms![frm-mainform].ActiveControl
    If Me.txt1 Like "1-*" Then
    txt1 = Trim(Mid([txt1], 3))
    Else
    End If

The user then enters their initials in the newinitials field on the form and click an update button. This is the code on the update button

    Me.[txt1] = Me.[txt1] & ", " & Me.[newinitals]
    Forms![frm-Mainform].ActiveControl = Me.txt1
    DoCmd.close acForm, "Frm-initals"

So now where my problem comes in. I can catch the incomplete entry, I can put the 1- back in the initials line and keep the step in rework. But I cant figure out how to drop those last initials entered.

I hope i have explained my problem well enough its hard to explain these things.

and thank you for those who read this long post.

jon

 

[spikepl]

There are two issues here. Firstly, how to store data and secondly, the workflow.

Data storage
In a db you do not combine separate data unless you have to. Here you don't have to. Do not confuse display with storage. You can display it one way but store it another way. Since each item can have a variable number of signatures, then the signatures belong in a different table, e.g.:

tblSignatures
-------------
SignatureID PK
TaskID FK from tblTasks - the ones to be signed off
UserID FK from tblUSres with your various users


Workflow
Normally you avoid typing when you know the options in advance, because typing, invariably, leads to mistyping. Comboboxes, listboxes can hold the id's of signatories. Or the user may be logged in, in which case all his changes are automatically logged with his signature.

 

[Jon123]

ok so if I were to pull the user's initialsfrom a seperate table based on user that is logged in I still have the issue how do I drop the last set entered?

jim

 

[jdraw]

Further to spikepl's comments, I think it's a database structure issue.
You seem, at least my understanding of your description, to be concatenating initials in a field. I think you have work and that work is assigned to a Person. That Person either completes the work successfully or does not. If they do not, then some description of why not can be logged and the work set up for rework and assigned to another Person. It would seem that proper tables, some dates and initials and comments can manage the set up.

It would be helpful for readers to see your Tables and relationships.

 

[Jon123]

So this will work.

Me.step3 = Left([step3], Len([step3]) - 4)

but this only works if I force the initials to 2 letters. It would be better if I could remove back to the , in the field. If the field was aa, bb, cc and I removed the last 4 it will equal aa, bb which is good unless someone uses 3 letter initials. I can fix it to only allow 2 letter initials and will if there is not way to remove all charaters up to the ,

jon

 

[jdraw]

I'm saying show us your tables and relationships. Tell us more about WHAT you are trying to accomplish. Readers may offer options/suggestions.

 

[Jon123]

I'm sorry and I i understand what you are saying. However, right now I do not have the time to make a change in the table setup. What I have will work if there is no other way to find to last comma and delete that and all charters after that comma.

 

[Jon123]

 Me.step3 = Left(Me.step3, InStrRev(Me.step3, ",") - 1)

Works like a champ

thank you all for the help