close a record based on condition

[SylvieB]

Hello group,
On a Lead form, I have a combo box (txtClosedReason) with 4 options to
select from when a lead closes. Only when “convert to opportunity†is chosen,
another form display with the information from the Lead form. This works
fine. However, what I want to accomplish is when “convert to opportunity†is
selected and a check mark is put on the “Close Now†check box (chkClose),
only then the data displays on the Opportunity form and the “today date†is
automatically displays. The "today date" part is working fine as well. Thank
you in advance for any help.
Here is the codes I have :

Private Sub txtClosedReason_AfterUpdate()
Dim strWhere As String
If txtClosedReason = "convert to opportunity" Then
strWhere = "ID= " & Me.ID
DoCmd.OpenForm "opportunities details", whereCondition:=strWhere
End If
End sub

Private Sub chkClose_AfterUpdate()
If Me!chkClose = True Then
Me!txtClose = Date
Else
Me!txtClose = Null
End If
Me.Dirty = True
End Sub

 

[Klatuu]

You did not ask a question. Based on what you want and the code you posted,
it should be working. The only thing I see is Me.Dirty = True is not
necessary. The from will be dirtied as soon as anything in the current record
changes.

 

[SylvieB]

Thank you for your answer. Actually it does not work the way i want to
because the opportunity should convert only when the "Close Now" button is
checked and currently it does without checking the "Close Now". that's the
part i don't know how to do.
Any suggestions?

 

[Klatuu]

If what you are saying is that the form opportunities details should only
open if txtClosedReason is "convert to opportunity" and txtClose is checked,
then the following will work. But, this is a form navigation problem. Which
control is first in the tab order, txtClosedReason or txtClose? How you
structure the code depends on how the user is using the form and how you want
it to work. As written, your code expects txtClose to already be set before
you enter the choice in txtClosedReason, so I will use that.

Private Sub txtClosedReason_AfterUpdate()
Dim strWhere As String

If txtClosedReason = "convert to opportunity" And Me.txtClose Then
strWhere = "ID= " & Me.ID
DoCmd.OpenForm "opportunities details", whereCondition:=strWhere
End If

End sub

Private Sub chkClose_AfterUpdate()

If Me!chkClose = True Then
Me!txtClose = Date
Else
Me!txtClose = Null
End If

End Sub

Note the indentation. Easier to read, isn't it.

 

[SylvieB]

Thank you for pointing this out. I had to write the code in teh txtclose
control because only when it closes, the data displays in the opportunity
form. That works great.
One more thing, the data does not show on the opportunity form once "close"
is selected. An empty record opens under Opportunity form. It seems like the
data needs to be saved before it can be display on the opportunity form. I
have to exit the form and open it again to see the data. My question is: How
can i write a line of code for the data to show on the opportunity form once
it converts, and close the Lead form. I wrote this code but that does not
work, it only closes the Lead form:

DoCmd.close acform, me.name, acSaveYes

Thank you for your help.

 

[Klatuu]

That is because the data has not yet been saved to the table. You can force
the record to save by using Me.Dirty = False before you open the other form:

If txtClosedReason = "convert to opportunity" And Me.txtClose Then
Me.Dirty = False
strWhere = "ID= " & Me.ID
DoCmd.OpenForm "opportunities details", whereCondition:=strWhere
End If

 

[SylvieB]

Thank you very much for your help. That works great.