AUTOMATICALLY UPDATE A FORM FROM A RECORD ON A TABLE

[sklambert]

Hello,

I have a table of programmers (5 total), and a table of problem tickets (ASSIGNMENTS). I would like to automatically assign problem tickets to the next programmer in line when a new problem ticket is created. I know this is probably easy, but I can't put it together. Here is what I have:

PROGRAMMERS

JIM
JANE
BILL
BOB
MARY

ASSIGNMENTS

CREATE DATE
ASSIGN DATE
PROGRAMMER
BLAH, BLAH, BLAH

When I create a new record in ASSIGNMENTS, I want to retrieve the last programmer listed on an assignment, then update the PROGRAMMER field in ASSIGNMENTS with the next programmer in line. Can somebody help me with the logic?

 

[zevw]

This would be the simple way have a Date a Time Stamp field for each ticket when it was assigned.

Run a query on the latest ticket - which you will know from the date and time stamp.

Now if Programmer 2 got the last ticket assign to the next number. If the last number was 5 then assign back to 1.

I Think you can do it in a snap!!

Good Luck!!

 

[sklambert]

I understand the concept, but the problem is getting it into code. I understand running the query to get the last programmer, but then how do I go to the programmer table and get the next programmer's name, then update the PROGRAMMER field on the ASSIGNMENTS form?

 

[zevw]

Can I see the code till after the query

Thanks!!

 

[sklambert]

At this point I don't have any code. I am trying to figure out how to put it together. I just have the tables and the form.

 

[DeMann]

Okay, how about this.


Dim RS As New ADODB.Recordset
Set RS = New ADODB.Recordset
RS.ActiveConnection = CurrentProject.Connection

RS.Open "select * from Programmers ORDER BY Programmer.date DESC;", , adOpenKeyset, adLockOptimistic, adCmdTableDirect

RS.MoveFirst

Programer=RS(1)
Dater=Now()
RS(2).Value=Dater
RS.Update
RS.MoveNext
RS.Close

[Assign Date]=Dater
[Programmer]=Programer

this is assuming that your Programmers table has the following fields:
UID
ProgramerName
DateOfLastProject



_______________
conceptNEXT.com
[cN]

 

[sklambert]

Thanks DeMann. This doesn't really help me, as it is not what I am trying to accomplish. Maybe if I put my objective into steps, somebody can help me get those steps into code. I appreciate any assistance greatly.

TABLES:
PROGRAMMERS - PROGRAMMER NUMBER (1-10), PROGRAMMER NAME

ASSIGNMENTS - STATUS, PROGRAMMER NUMBER, PROGRAMMER, CREATE DATE, ASSIGN DATE, DUE DATE, COMPLETED DATE, DESCRIPTION, REMARKS

LAST ASSIGNED - PROGRAMMER NUMBER, PROGRAMMER NAME (only one record)

When I create a new record in ASSIGNMENTS, I want to

(1) Read the record in the LAST ASSIGNED table
(2) Add 1 to PROGRAMMER NUMBER in the LAST ASSIGNED table. If PROGRAMMER NUMBER = 10, move 1 to PROGRAMMER NUMBER
(3) Select the record with that PROGRAMMER NUMBER from the PROGRAMMERS table
(4) Move the PROGRAMMER NUMBER and PROGRAMMER NAME from PROGRAMMERS to PROGRAMMER NUMBER and PROGRAMMER name in ASSIGNMENTS
(5) Update the LAST ASSIGNED table with the PROGRAMMER NUMBER and PROGRAMMER NAME

I think this should be simple, I just don't know how to code the logic. Thanks so much for the assistance you have given, and thanks in advance for any assistance anyone can give.

 

[DeMann]

Try this. It is closer to what you described. The other way accomplishes the same thing, just a little simpler.

    Dim LastPGR As String
    Dim LastPGRnum As Integer
    Dim RS As New ADODB.Recordset
    
    Set RS = New ADODB.Recordset
    RS.ActiveConnection = CurrentProject.Connection
    
    RS.Open "select * from [last assigned];", , adOpenKeyset, adLockOptimistic, adCmdTableDirect
     
    LastPGRnum = RS(0)
    LastPGR = RS(1)
    
    RS.Close
    RS.Open "select * from programmers;", , adOpenKeyset, adLockOptimistic, adCmdTableDirect
     
    If LastPGRnum = RS.RecordCount Then
        LastPGRnum = 1
    Else
        LastPGRnum = LastPGRnum + 1
    End If
    
    RS.Close
    RS.Open "select * from Programmers where [programmer number]=" & LastPGRnum & ";", , adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
    LastPGR = RS(1)
    
    RS.Close
    RS.Open "select * from [last assigned];", , adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
    RS(0).Value = LastPGRnum
    RS(1).Value = LastPGR
    RS.Update
    RS.Close
    
    [programmer number] = LastPGRnum
    [programmer] = LastPGR

_______________
conceptNEXT.com
[cN]

 

[sklambert]

Thanks DeMann for your assistance. This was very helpful. I borrowed code from several places to put this together. Now, the only adjustment I would like to make is to the following code, within my routine:

With rst
.MoveFirst
Do While Not .EOF
If rst![programmer number] = NextProgrammerNum Then
NextProgrammer = rst!PROGRAMMER
NextProgrammerEmail = rst![programmer email]
End If
.MoveNext
Loop
End With

Instead of looping through all of the programmers to find the programmer number that matches NextProgrammerNum, I would like to select the record where [programmer number] = NextProgrammerNum. Can you help me code this? The code in your example does not work in my routine, and I suspect it's because I am declaring a DAO database, and you are using ADOB.


****************** ENTIRE ROUTINE ***********************

Private Sub ASSIGN_Click()

'open recordset
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim LastProgrammerNum As Integer
Dim LastProgrammer As String
Dim NextProgrammerNum As Integer
Dim NextProgrammer As String
Dim NextProgrammerEmail As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("LAST ASSIGNED")

'GET LAST ASSIGNED PROGRAMMER

With rst
.MoveFirst
LastProgrammerNum = rst![programmer number]
LastProgrammer = rst!PROGRAMMER
End With

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

'SET NEXT PROGRAMMER
'GET NEXT PROGRAMMER NAME & EMAIL

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("PROGRAMMERS")

If LastProgrammerNum = rst.RecordCount Then
NextProgrammerNum = 1
Else
NextProgrammerNum = LastProgrammerNum + 1
End If

With rst
.MoveFirst
Do While Not .EOF
If rst![programmer number] = NextProgrammerNum Then
NextProgrammer = rst!PROGRAMMER
NextProgrammerEmail = rst![programmer email]
End If
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

If Me.PROGRAMMER_NUMBER = 0 Then
Me.PROGRAMMER = NextProgrammer
Me.PROGRAMMER_NUMBER = NextProgrammerNum
Me.PROGRAMMER_EMAIL = NextProgrammerEmail
Me.STATUS = "PENDING"
Me.ASSIGN_DATE = Date
If Weekday(ASSIGN_DATE, 1) = 5 Or Weekday(ASSIGN_DATE, 1) = 6 Then
Me.DUE_DATE = DateAdd("d", 4, ASSIGN_DATE)
Else
Me.DUE_DATE = DateAdd("d", 2, ASSIGN_DATE)
End If

'UPDATE LAST ASSIGNED TABLE
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("LAST ASSIGNED")
With rst
.MoveFirst
.Edit
rst![programmer number] = NextProgrammerNum
rst!PROGRAMMER = NextProgrammer
.Update
End With

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Else
MsgBox ("THIS TASK HAS ALREADY BEEN ASSIGNED")
End If
End Sub

****************** end of code *****************************