Problem with setting start and end dates automatically

[mcgraw]

I am trying to track projects as they get moved from responsibility center to responsibility center, and eventually run totals on "total days assigned" so we can start to trend where bottlenecks are, and which group has issues for the longest period of time. (the DB is an Access 2007 DB)

So, I have a field called with_group in a table called Issues that is set with a list of the applicable groups for all projects.

On form [Issue Details], I set with_group_AfterUpdate() to try and track all changes and store those changes in a second table called tblWithGroup.

So, this is what I have so far:

 Private Sub with_group_AfterUpdate()
On Error GoTo errline
Stop
If tblWithGroup.Start_Date = Null Then
tblWithGroup.Start_Date = Me.Opened_Date
End If
.Update
!tblWithGroup.End_Date = Now()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblWithGroup")
With rs
    .AddNew
    !Issue_ID = Me.ID
    !Group = Me.with_group
    !Start_Date = Now()
    .Update
End With
If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
End If
Exit Sub

So, what I am TRYING to do, quite unsuccessfully D) is, if there is no start date, like when it is the first time a group is being assigned, have it set the start_date in tblWithGroup to the Open_date in Issues.

Then, on the next change have it 1: put the end_date as Now() on the current record, and then 2: open a new record, assign the group, and the start_date to Now()...and so on and so forth.

As I have said in other posts, I am brand spanking new to Access 2007, and to programming in general, and would like to learn how to do this kind of thing, so any help is greatly appreciated!

 

[Khalid_Afridi]

What you are saying is over the reader head. try to simplify your question avoiding unnecessary detail.

 

[mcgraw]

Ok, let me try this again. Sorry for the confusion!

I need to track the amount of time individual groups work on projects.

I have 4 groups, Group 1, 2, 3 and 4...when Group 1 is assigned the project, I need it to set the date Group 1 was given the assignment.

When Group 1 passes it off to Group 2, 3 or 4, I need it to put an End_date on that record, open a new record and put a start_date on it.

Each time the project is reassigned, I need an individual record created. Once I gather the information I can use DateDiff to show how long each group had the project.

Does that make any better sense?

 

[Brianwarnock]

What you are saying is over the reader head. try to simplify your question avoiding unnecessary detail.

This seems a good reason not to respond atleast for a day.
I too cannot comment on much of the code , however this

If tblWithGroup.Start_Date = Null Then
should be
If tblWithGroup.Start_Date Is Null Then

and if Time is not needed then use Date() rather than Now()

Brian

 

[Brianwarnock]

I've never done this perhaps its 2007 but shouldn't this have a with round it

.Update
!tblWithGroup.End_Date = Now()

Brian

 

[mcgraw]

Like I said, I'm a hack programmer, and most of what I have been able to create is from reverse enginering existing code in my current, and other database. So sorry if my explanations and code make no sense, I'm trying to learn it!

 

[Khalid_Afridi]

When Group 1 passes it off to Group 2, 3 or 4, I need it to put an End_date on that record, open a new record and put a start_date on it.

Do you have a single field for date entry? try to create Start_Date and End_Date columns (fields) in your database instead of one field. when the new group is assigned the task change the dates accordingly.

 

[Brianwarnock]

We all started there, I'm not saying your code makes no sense, I'm saying that I don't understand it, 2 entirely different things. I just pointed out 1 thing I knew to be wrong and 1 I suspected.

I'm retired 5 years and don't jave 2007 so the rest of your code could be good for all I know.
Hope somebody else comments if you still have problems,
Brian

 

[mcgraw]

the tblwithGroup structure is this:

ID = AutoNumber (PK)
Issue_ID = Number (foreign key to Issues table)
Group = Text (links to Group assignment from Issues table)
Start_date = date/time Short format
End_Date = date/time Short format.

the Issues table fields that come in to play are:

Group = text
ID = Autonumber (PK)
and possibly Open_date

One of the problems I'm having is how to get the form to know which record to put an end_date on in tblWithGroup.

 

[mcgraw]

I think I actually may ALMOST have it.

I had to rethink my logic quite a bit.

How do I state a WHERE clause in VB? I want to say
Where Issues.group_ID = tblWithGroup.ID set the end_date to date()

But apparently in VB, you can't just do "Where"

Here's what I have...

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblWithGroup")
With rs
If Me!group_ID = Null Then
Me!group_ID = Null
Else
Where Issues.group_ID = tblWithGroup.ID
!End_Date = Date
End If
.AddNew
!Issue_ID = Me.ID
!Group = Me.with_group
!Start_Date = Date
Me!group_ID = ID
.Update
End With

If I need to put this in a new post, let me know.

 

[Khalid_Afridi]

How do I state a WHERE clause in VB? I want to say
Where Issues.group_ID = tblWithGroup.ID set the end_date to date()

But apparently in VB, you can't just do "Where"

Here's what I have...

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblWithGroup")
With rs
If Me!group_ID = Null Then
Me!group_ID = Null
Else
Where Issues.group_ID = tblWithGroup.ID
!End_Date = Date
End If
.AddNew
!Issue_ID = Me.ID
!Group = Me.with_group
!Start_Date = Date
Me!group_ID = ID
.Update
End With

I am sorry to say but your coding concept is too poor to ms access.. remember in all version of access VBA is using for coding not vb.