Results 1 to 10 of 10
  1. #1
    billybeer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    27

    importing data from one table into form and entering into different table


    I have a database that has a form that I would like to populate some of the fields by running a query on a certain table (Table 1). Once the form is populated I want the user to add some additional data and then enter all the data now in the form to a different table (Table 2). When I set the Record Source to the query so that I can run that query on opening the form and populate the fields it works. But when I try to enter the data into the different table I get an error "This Recordset is not updateable". Is what I want to do possible basically switch the recordset from the query on Table 1 to populate form to Table 2 to enter the data.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Are both tables of the same structure?

    The recordset may not be updatable because you are currently editting it.

    Can I buy a carriage return in your next post?

  3. #3
    billybeer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    27
    Sorry about the carriage return. The tables are separate. One is a ODBC connection
    (Table 1) and the other is the main table in the Access database (Table 2).

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand 'switch the recordset' - why would you change the form RecordSource?

    The question was "Are tables of the same structure?" We know they are separate but are they the same structure?
    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
    billybeer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    27
    So your question got me looking at my table and lo and behold
    Table 2 is being updated with the new data from the form. However
    normally when you hit enter the data is entered into the table and a
    msgbox tells you if the record is successfully entered and all entry boxes
    are cleared and you can then enter a new record. So this is when the
    error box pops up saying "This Recordset is not updateable" The form
    does not clear even though the record has made it to Table 2.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'boxes are cleared'? Are these controls bound to fields? Exactly how do you 'clear'? Provide code for analysis.
    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.

  7. #7
    billybeer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    27
    Code:
      On Error GoTo Err_Enter_Request_Click     
        Dim db As Database
        Dim rs As Recordset
            
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblMain")
        
        rs.AddNew
        
        rs!Prosecutor = IIf(Me.Prosecutor.Value = "", Null, Me.Prosecutor.Value)
        rs!dateIn = IIf(Me.dateIn.Value = "", Null, Me.dateIn.Value)
        rs!dateout = IIf(Me.dateout.Value = "", Null, Me.dateout.Value)
        rs!CTN = IIf(Me.CTN.Value = "", Null, Me.CTN.Value)
        rs!ICNIC = IIf(Me.ICNIC.Value = "", Null, Me.ICNIC.Value)
        rs!DFirst = IIf(Me.DFirst.Value = "", Null, Me.DFirst.Value)
        rs!DLast = IIf(Me.DLast.Value = "", Null, Me.DLast.Value)
        rs!PoliceAgency = IIf(Me.PoliceAgency.Value = "", Null, Me.PoliceAgency.Value)
        rs!Status = IIf(Me.Status.Value = "", Null, Me.Status.Value)
        'rs!ChargeType = IIf(Me.ChargeType.Value = "", Null, Me.ChargeType.Value)
        rs!FramePrincipalCharge = IIf(Me.FramePrincipalCharge.Value = "", Null, Me.FramePrincipalCharge.Value)
        rs!PrincipalCharge = IIf(Me.PrincipalCharge.Value = "", Null, Me.PrincipalCharge.Value)
        rs!FrameCharge2 = IIf(Me.FrameCharge2.Value = "", Null, Me.FrameCharge2.Value)
        rs!Charge2 = IIf(Me.Charge2.Value = "", Null, Me.Charge2.Value)
        rs!FrameCharge3 = IIf(Me.FrameCharge3.Value = "", Null, Me.FrameCharge3.Value)
        rs!Charge3 = IIf(Me.Charge3.Value = "", Null, Me.Charge3.Value)
        rs!InPersonInterviews = IIf(Me.InPersonInterviews.Value = "", Null, Me.InPersonInterviews.Value)
        rs!PhoneInterviews = IIf(Me.PhoneInterviews.Value = "", Null, Me.PhoneInterviews.Value)
        rs!SearchWarrants = IIf(Me.SearchWarrants.Value = "", Null, Me.SearchWarrants.Value)
        rs!VideosReviewed = IIf(Me.VideosReviewed.Value = "", Null, Me.VideosReviewed.Value)
        rs!TimeSpent = IIf(Me.TimeSpent.Value = "", Null, Me.TimeSpent.Value)
        rs!Notes = IIf(Me.Notes.Value = "", Null, Me.Notes.Value)
        
        
            If Me.Dirty Then
                  Me.Undo
            End If
        
        rs.Update
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        
        Prosecutor.Value = ""
        dateIn.Value = Date
        dateout.Value = ""
        CTN.Value = ""
        ICNIC.Value = ""
        DFirst.Value = ""
        DLast.Value = ""
        PoliceAgency.Value = ""
        Status.Value = Null
        'ChargeType.Value = Null
        PrincipalCharge.Value = ""
        InPersonInterviews.Value = ""
        PhoneInterviews.Value = ""
        SearchWarrants.Value = ""
        VideosReviewed.Value = ""
        TimeSpent.Value = ""
        Notes.Value = ""
        Me.Notes.BackColor = 16777215
        'ChargeType.Enabled = False
        Me.PrincipalCharge.Enabled = False
        Me.Charge2.Enabled = False
        Me.Charge3.Enabled = False
        Me.Charge1Felony.Enabled = False
        Me.Charge1Misd.Enabled = False
        Me.Charge2Felony.Enabled = False
        Me.Charge2Misd.Enabled = False
        Me.Charge3Felony.Enabled = False
        Me.Charge3Misd.Enabled = False
        Me.FramePrincipalCharge.Value = Null
        Me.FrameCharge2.Value = Null
        Me.FrameCharge3.Value = Null
        Me.PrincipalCharge = ""
        Me.Charge2 = ""
        Me.Charge3 = ""
        ClearStatusBarMsg
        
        MsgBox "Your Record was Saved"
        
        Prosecutor = ""
        dateIn = Date
        dateout = ""
        CTN = ""
        ICNIC = ""
        DFirst = ""
        DLast = ""
        PoliceAgency = ""
        Status = Null
        'ChargeType = Null
        PrincipalCharge = ""
        InPersonInterviews = ""
        PhoneInterviews = ""
        SearchWarrants = ""
        VideosReviewed = ""
        TimeSpent = ""
        Notes = ""
        Me.Notes.BackColor = 16777215
        ClearStatusBarMsg
        Prosecutor.SetFocus
        'ChargeType.Enabled = False
        Me.PrincipalCharge.Enabled = False
        Me.Charge2.Enabled = False
        Me.Charge3.Enabled = False
        Me.Charge1Felony.Enabled = False
        Me.Charge1Misd.Enabled = False
        Me.Charge2Felony.Enabled = False
        Me.Charge2Misd.Enabled = False
        Me.Charge3Felony.Enabled = False
        Me.Charge3Misd.Enabled = False
        Me.FramePrincipalCharge.Value = Null
        Me.FrameCharge2.Value = Null
        Me.FrameCharge3.Value = Null
        Me.PrincipalCharge = ""
        Me.Charge2 = ""
        Me.Charge3 = ""
        
    Exit_Enter_Request_Click:
        Exit Sub
    
    
    Err_Enter_Request_Click:
        MsgBox "Record NOT ENTERED.  Make sure all fields in red have been entered and try again."
        Resume Exit_Enter_Request_Click
        
        
     
    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Form is opened to record from table 1 and every field is saved to new record in tblMain? How is form opened to specific record?
    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.

  9. #9
    billybeer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    27
    This form is only for new records being created and entered into tblMain. It does not open or edit existing records in tblMain.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.

    Import the ODBC source into Access.
    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. Replies: 2
    Last Post: 01-13-2017, 03:25 PM
  2. Replies: 12
    Last Post: 10-15-2012, 08:07 AM
  3. Replies: 13
    Last Post: 10-04-2012, 04:15 PM
  4. Replies: 1
    Last Post: 05-17-2011, 05:19 AM
  5. Replies: 0
    Last Post: 03-15-2010, 02:38 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