checkings works in form but not in subform

[adaniele]

i have a DB to manage tasks. The main table, which contains information about the task on itself, is populated by a form. This form has a subform to add subtasks which are stored in another table.
The problem is that i can check if the information entered in the form is correct, but i can not do the same in the subform. The subform get information from the table where those records are stored. I tryed using before update and after update but it did not work. Also, it seems to be checking allways the first record.

example:
task 5 has the following subtasks: A,B,C,D
if i try to check the values it allways check against the first subtask (A).

Private Sub subtask_AfterUpdate()
MsgBox "Please, fill the DESCRIPTION field."
Me.taskdescription.SetFocus
End Sub
Private Sub subtask_beforeUpdate(Cancel As Integer)
If Me.subtask = DLookup("[subtask]", "change_desc", "[change_id2]=" & Me.change_id2 & "") Then
MsgBox "This task letter already exists."
Me.subtask.SetFocus
End If
End Sub


thx in advance, Max.

 

[adaniele]

i forgot to tell you that subtask field is a combo which get information from a value list.

thx

 

[adaniele]

status changed but still broken

Guys, i change the code 'cos the table where the subtasks are has 2 fields. Now in the before update can detect if a subtask code already exist. However, if by mistake a user try to change it and then leave the original letter, the before update strts a loop and i dont know what to do. any help??

Private Sub subtask_AfterUpdate()
MsgBox "Please, fill the DESCRIPTION field for subtask " & Me.subtask & ""
Me.taskdescription.SetFocus
End Sub
Private Sub subtask_beforeUpdate(Cancel As Integer)
If Me.subtask = DLookup("[subtask]", "change_desc", "[change_id2]=" &
Me.change_id2 & " and [subtask]='" & Me.subtask & "'") Then
MsgBox "This task letter already exists."
End If
End Sub

thx in advance, Max.

 

[WayneRyan]

Max,

As you've seen already, if the Criteria for the DLookUp function returns
multiple records (you only used one key before you added the task to it),
then it will return the FIRST record. You've overcome that.

For your current problem:

Notice the --> Private Sub subtask_beforeUpdate(Cancel As Integer)
After displaying your message box, set --> Cancel = True

That will cancel the update.

You could also consider not letting the user enter the field at all.
You could use the form's BeforeInsert event and use the DMax function
to get the highest task and increment it.

Me.subtask = Chr(Asc(Nz(DMax("Asc([subtask])", _
                             "change_desc", _
                             "[change_id2] = " & Me.change_id2 & " and " & _
                             "[subtask]= '" & Me.subtask & "'"), 65) + 1)

Wayne

 

[adaniele]

subtask before insert

wayne, thx for your suggestion..now i am checking almost everything excepting for your last suggestion.

i added the code for the before insert and also made the field a text box instead a combo box. but now the subtask field appears empty and the following error is displayed:

CAN NOT FIND LIBRARY OR PROYECT

then i debugg and the CHR in the statement is highlighted

Me.subtask = Chr(Asc(Nz(DMax("Asc([subtask])", "change_desc", "[change_id2] = " & Me.change_id2 & " and " & "[subtask]= '" & Me.subtask & "'"), 65) + 1))

thx

 

[WayneRyan]

Max,

Chr is really a valid function. You have a references problem. Get your
code in Design View, then Tools --> References. You can use the Search
Facility here and look for "References". It's a common topic.

Also, thanks for finding my missing parenthesis, been a long day.

hth,
Wayne

 

[adaniele]

references

wayne, i have been there before but i dont know what to do whith that list...any help?

thx in advance, Max.

 

[adaniele]

addition form

Wayne,
i solved the problem changing the subtask type from string to number.
But now something else is wrong.
If i edit an existing ticket i can add subtasks perfectly, but if i want to add a new task, this new task does not exist yet, so when it try to add the first subtask in the subform an error appears.

Me.subtask = DMax("[subtask]", "change_desc", "[change_id2] = " & Me.change_id2 & "") + 1

It looks in the table when the change_id2 = something when the change_Id2 is not created yet.

any suggestion. Thx in advance, Max.

 

[adaniele]

solved

Guys, i solved this problem, creating a flag in the before update. when the form is loaded i assigned 1 to the flag. In the before update i added a conditional...

If i = 0 Then (it is the first subtask to be added, only enters here once)
Me.subtask = 1
i = 1
Else
If i = 1 Then
Me.subtask = DMax("[subtask]", "change_desc", "[change_id2] = " & Forms![AddChange]![Change_id] & " ") + 1
End If
End If

thx.Max.

 

[WayneRyan]

Max,

That's what the Nz function is supposed to do:

Me.subtask = Nz(DMax("[subtask]", "change_desc", "[change_id2] = " & Forms![AddChange]![Change_id]), 0) + 1
             ===                                                                                   ====

If there are no entries for the Change_Id, it will force a return of 0.

Wayne

 

[adaniele]

wayne, i didn't know what NZ was..... it is much better than the another way.

thx for your help.max.