Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231

    Record Is Created, but form won't load due to Run Time Error 3141

    After stepping through my code I know that the record gets created, which indicates that my sql statements are working. But right after the record get created and I attempt to load an edit form based on that newly created record, I get a run-time error 3141.

    So, I'm confused. If the sql statements run, who can I be getting this error? Code is posted below:

    I've also tested out my sql statement after copying my debug.print statement, which works fine.

    Private Sub AddPacker(rstPacker As Recordset, _
    Packer_BUSINESS_NAME As String)

    Dim Grower_lnk As Integer
    Dim PackerLicence As String
    Dim grow_lic As String
    Dim PackName As String
    Dim PkCheckBox As Long
    Dim rstPackerExists As Recordset
    Dim db As Database
    Dim strSQL As String

    PkCheckBox = Forms!BUSINESS_MAIN!Check_Packer

    If PkCheckBox <> 0 Then



    ElseIf PkCheckBox = 0 Then
    MsgBox "Please Select Packer Check Box before Proceeding"
    Exit Sub
    End If

    PackerLicence = DMax("[PLICENCE]", "FULLPACKER")
    PackerLicence = PackerLicence + 1

    'Test whether marketer record already exists
    Set db = CurrentDb
    strSQL = "SELECT FULLPACKER.[Business Name] from FULLPACKER WHERE [Business Name]= " & "'" & Packer_BUSINESS_NAME & "'"
    Debug.Print strSQL

    Set rstPackerExists = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

    ' Insert packer record
    If rstPackerExists.EOF Then
    With rstPacker
    .AddNew
    ![Business Name] = Packer_BUSINESS_NAME
    Grower_lnk = Forms!BUSINESS_MAIN!MEMBER_ID
    ![PLicence] = PackerLicence
    !MEMBER_ID = Grower_lnk
    .Update
    '.Bookmark = .LastModified
    End With
    rstPacker.Close
    'DoCmd.Requery
    Me.Refresh
    End If

    If Not rstPackerExists.EOF Then
    MsgBox "Packer Record for: " & Packer_BUSINESS_NAME & " already exists, Please proceed with edit session.", vbCritical
    Exit Sub
    End If
    'PackName = Packer_BUSINESS_NAME

    'Pass arguments to form load event

    DoCmd.OpenForm "frm_CREATE_PACKER", , , , , , Grower_lnk......... This is the line where the error occurs..

    'Open the form based on where clause

    'sWHERE = strBUSINESS_NAME

    DoCmd.OpenForm "frm_CREATE_PACKER", , , , , , Grower_lnk

    If Len(Grower_lnk) > 0 Then
    Forms!frm_CREATE_PACKER![MEMBER_ID].SetFocus
    DoCmd.GoToControl "MEMBER_ID_FULLPACKER"
    DoCmd.FindRecord Grower_lnk, , True, , True, , True
    End If

    MsgBox "New Packer Record Has Been Created for: " & PackName

    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,815
    Why do you have the first OpenForm line at all?
    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
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    That's how I got it to work for me.

    CC

  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,815
    It triggers error and shouldn't be needed anyway. Your code attempts to open the form twice. Why the value in OpenArgs? Are you using the OpenArgs in code behind frm_CREATE_PACKER?

    Are you trying to edit an existing record or add new record? Why is the recordset opened filtered if all you want to do is add new record?

    Don't think I would bother open and manipulating recordset. I use CurrentDb.Execute to append or update records. Then form can be opened filtered to the specific record, unless you want to allow navigation of all existing records.

    CurrentDb.Execute "INSERT INTO fullpacker([Business Name], PLicence, Member_ID) VALUES('" & Packer_Business_Name & "', '" & PackerLicence & "', " & Grower_lnk)"
    DoCmd.OpenForm "frm_CREATE_PACKER", , , "Member_ID=" & Grower_lnk
    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
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Thanks June7,

    The initial work flow was to first create the member record(not shown). From that form, the client then wanted to be able to create an associated packer record. Once the Create Packer click event was selected, the same member name displayed from the main form was used to open the Create Packer record form, where the client then had the chance to amend the record with additional details.

    Yes, I was passing the member_id number from the main form, and then passing that to the frm_Create_Packer form, so that only that record would be displayed when the create packer form opened.

    I'll give your db.execute line a try. Thanks again.

    CementCarver

  6. #6
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    June7,

    Now I get a run-time error 3163, trying to pass too much data and it stalls at the DoCmd.OpenForm "frm_CREATE_PACKER", , , "Member_ID=" & Grower_lnk
    line again.


    CementCarver

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The CurrentDb.Execute works? The record is created with the values from the variables?

    I will probably have to review the db directly if you want to provide because all that code syntax works for me.
    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.

  8. #8
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Yes, the CurrentDb.execute works and the variables hold the correct data.

    I'll have to remove all my confidential data and prepare a subset of what I have. I'll send you a private message where I'll post my db.

    CementCarver

  9. #9
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231

    Uploaded

    June7,

    First time using uploading utility, but db is attached.

    CC
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The db errors when opening, doesn't like some code behind the Login form but when I set a breakpoint in the Open event, the form opens as I step through code.

    Your code wants to open form frm_CREATE_PACKER but I don't see a form by that name.

    The code tries to create record (and fails) for AFB Farms Ltd even though it is already in the FULLPACKER table because there is a period (.) at the end of AFB Farms Ltd. in the FULLPACKER table but not in BUSINESS table.

    Consider:
    Code:
    Private Sub AddPacker(strBUSINESS_NAME As String)
     Dim Grower_lnk As Integer
     Dim PackerLicence As String
     If Me.Check_Packer = 0 Then
        MsgBox "Please Select Packer Check Box before Proceeding"
     ElseIf IsNull(DLookup("[Business Name]", "FULLPACKER", "[Business Name] = '" & strBUSINESS_NAME & "'")) Then
        PackerLicence = DMax("[PLICENCE]", "FULLPACKER")
        PackerLicence = PackerLicence + 1
        Grower_lnk = Forms!BUSINESS_MAIN!MEMBER_ID
        'Insert packer record
        CurrentDb.Execute "INSERT INTO fullpacker([Business Name], PLicence, Member_ID) VALUES('" & strBUSINESS_NAME & "', '" & PackerLicence & "', '" & Grower_lnk & "')"
        'Open the form based on where clause
        DoCmd.OpenForm "frm_CREATE_PACKER", , , "[Business Name]='" & strBusiness_Name & "'"
        MsgBox "New Packer Record Has Been Created for: " & strBUSINESS_NAME
     Else
        MsgBox "Packer Record for: " & strBUSINESS_NAME & " already exists, Please proceed with edit session.", vbCritical
     End If
    End Sub
    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.

  11. #11
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Thanks for your review June7. Odd, that that form was not there, it should have been. I must have accidentally deleted it when I was scaling down my db.

    I'll try your suggested code snippet.

    CementCarver

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I do wonder why you are repeating the company names between tables (how did AFB Farms Ltd. get misspelled?). Why not just saving the company ID?
    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.

  13. #13
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    That's just how I got the data and the entire excel was loaded. I just don't use any of the redundant data. You're right, it should be removed.

    CementCarver

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You changed code to use ID instead of name as criteria?
    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.

  15. #15
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    I used your code line for line but I was still getting the same error. But then I noticed that the frm_CREATE_PACKER was not associated with the table, meaning that it was not listed under the table, rather is was in the 'unrelated section', which of course seemed odd to me. When I started to investigate and opened up the unrelated form, it gave me an error regarding it's record source. That is why it appeared to you that the form was not there.

    Once I fixed the record source, your code suggestion worked. Go figure.

    CementCarver

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-Time error 3141
    By CementCarver in forum Programming
    Replies: 5
    Last Post: 06-19-2013, 08:50 AM
  2. Replies: 7
    Last Post: 03-12-2013, 07:18 PM
  3. Replies: 4
    Last Post: 02-13-2013, 10:46 AM
  4. Form Load Time
    By bmschaeffer in forum Forms
    Replies: 1
    Last Post: 10-27-2011, 10:35 AM
  5. ApplyFilter to form on Load error
    By rivereridanus in forum Access
    Replies: 1
    Last Post: 08-18-2011, 12:00 PM

Tags for this Thread

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