Results 1 to 6 of 6
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Adding new record to a table from a different form.

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I suspect ctlCurrent is never Null - that it has something, maybe even an empty string. Have you step debugged? Check the value as code executes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Sorry, ctlCurrent is set to take the value from the RnwID field of the row the user selects. RnwID is the PK for tblRnwlTrack, so it should be Null if there's no record yet (unless I'm mistaken on how that works, and I could be). You may laugh, or you may face-palm, but I do not know how to step debug. I try Debug.Print, and sometimes I can get it to work, other times I get nothing in the Immediate Window. I've been learning via the internet and what I have had a chance to read of the books, so I do have some gaps.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Review link at bottom of my post for debugging guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Got the debug.print to work! It's printing the right values ("Null" where there's no ID, and the ID when there is one, and a 6 or 7 for the msgbox answer), I just can't get it to take the right course of action WITH that data.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding New record to Table
    By Robert2150 in forum Forms
    Replies: 4
    Last Post: 03-24-2015, 10:42 AM
  2. Replies: 2
    Last Post: 03-19-2014, 04:51 PM
  3. Replies: 4
    Last Post: 08-14-2012, 07:14 AM
  4. Replies: 3
    Last Post: 10-28-2010, 07:02 AM
  5. Replies: 1
    Last Post: 03-29-2010, 04:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums