Hi, all!
In my database, I'm using continuous forms with filters as search forms. On one in particular, I have a left-joined query that pulls from two major tables, and feeds them into the search. One of the tables is tblPolicy, which holds information on all the insurance policies, and the other is tblRnwlTrack, which tracks the renewal process that we go through on each of them. The query is left-join because there will always be a record in the tblPolicy side, but until the renewal process is initiated by a user, there's not going to be one on the tblRnwlTrack side.
What I want this form to accomplish is to take all the records from tblPolicy, and organize them by expiration date. It also has fields from tblRnwlTrack that are empty until the record is created. If there is a renewal record on the line the user clicks, then it should open that renewal record in the appropriate form, but if there is no renewal record yet, I need to give the user the option to create one, and then open the renewal form.
Where I'm struggling is creating that record. I've programmed a msgbox to prompt the user when there is no renewal record, but getting it to take a "Yes" and create that new record is proving difficult. If I can carry the PK over from tblPolicy to the new record I create in tblRnwlTrack, it should be easy to get the rest of the data from tblPolicy to fill in on the form where needed. Bot how do I make that record and carry that value over?
Also, in testing, I am finding hat the message box that's SUPPOSED to be just for when there is no record on the tblRnwlTrack side is popping up on every entry, regardless. Can anyone see what I'm doing wrong?
Code:
Private Sub btnView_Click()
'If renewal analysis record already exists, open it. If not, create one.
'Declare variable.
Dim question As String
Dim buttons As Integer
Dim title As String
Dim choice As Integer
Dim current As Variant
current = Me.ctlCurrent.Value
question = "There is currently no renewal analysis in process" _
& " for this policy." & Chr(10) & "Would you like to" _
& " initiate one?"
buttons = vbYesNo + vbQuestion + vbDefaultButton1
title = "Begin Renewal Process?"
choice = MsgBox(question, buttons, title)
If IsNull(current) = True Then
MsgBox choice
ElseIf IsNull(current) = False Then
DoCmd.OpenForm "frmRnwAnalysis", acNormal, , "RnwID =" & Me.ctlRnwID
End If
' If choice = 6 Then
' dbs.Execute "INSERT INTO tblRnwlTrack( [PolID] )" & _
' "VALUES ( " & Me.ctlPolID & " )", dbFailOnError
' End If
End Sub