Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Location
    USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is my debut post here at Woody's. Hope my asking for help with this is appropriate for this forum. I want to create something that executes On Close of a certain form and creates a new record based upon the one that is closing. I have no idea how to code this with VB for my Access 2007 application. I have attached the specs. Perhaps if someone can kickstart me with the first of nine outcomes, then I can do the rest. Thanks and happy weekend.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Here is an example of what the code could look like. You need to have a reference to the Microsoft DAO 3.6 Object Library if you're using an .mdb database, or to the Microsoft Office 12.0 Access Database Engine Object Libary if you're using an .accdb database.

    Code:
    Private Sub Form_Unload(Cancel As Integer)
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("CaseLog", dbOpenDynaset)
      If Not IsNull(Me.published) And Me.Determ = "AR Prelim" Then
    	rst.AddNew
    	rst!Deadline = Me.published + 120
    	rst!Odline = Me.published + 120
    	rst!Product = Me.Product
    	rst!POR = Me.POR
    	rst!Determ = "AR Final"
    	rst!Statutory = True
    	rst!Ofc = Me.Ofc
    	rst!SAsst = Me.SAsst
    	rst!PMSC = Me.PMSC
    	rst!Ctype = Me.Ctype
    	rst.Update
      ElseIf Not IsNull(Me.FullySigned) And Me.Determ = "Inv Prelim" And _
    	  Not Me.Product = "CVD" Then
    	...
    	...
      End If
    
    ExitHandler:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

  3. #3
    New Lounger
    Join Date
    Sep 2009
    Location
    USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, thank you very much. I can work with this. Mine is an .accdb. My concerns are that I do not understand the reference to the Microsoft Office 12.0 Access Database Engine Object Libary (how is that done?) and (uh oh) does mine need to be a trusted database in order to do this (as mine is not)? Thank you again for getting me started, and for whatever further help you give.

    [quote name='HansV' post='793950' date='18-Sep-2009 18:04']Welcome to the Lounge!

    Here is an example of what the code could look like. You need to have a reference to the Microsoft DAO 3.6 Object Library if you're using an .mdb database, or to the Microsoft Office 12.0 Access Database Engine Object Libary if you're using an .accdb database.

    Code:
    Private Sub Form_Unload(Cancel As Integer)
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("CaseLog", dbOpenDynaset)
      If Not IsNull(Me.published) And Me.Determ = "AR Prelim" Then
    	rst.AddNew
    	rst!Deadline = Me.published + 120
    	rst!Odline = Me.published + 120
    	rst!Product = Me.Product
    	rst!POR = Me.POR
    	rst!Determ = "AR Final"
    	rst!Statutory = True
    	rst!Ofc = Me.Ofc
    	rst!SAsst = Me.SAsst
    	rst!PMSC = Me.PMSC
    	rst!Ctype = Me.Ctype
    	rst.Update
      ElseIf Not IsNull(Me.FullySigned) And Me.Determ = "Inv Prelim" And _
    	  Not Me.Product = "CVD" Then
    	...
    	...
      End If
    
    ExitHandler:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub
    [/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can set a reference in the Visual Basic Editor by selecting Tools | References...
    Chances are that the reference to the Microsoft Office 12.0 Access Database Engine Object Library is already set (i.e. its check box is ticked), but if not, scroll down until you see it, then tick its check box and click OK.

    Your database will probably have to be in a trusted location for VBA code to work, but others can tell you more about that - I don't have Access 2007 myself.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='qstpete' post='794256' date='21-Sep-2009 07:02'].... and (uh oh) does mine need to be a trusted database in order to do this (as mine is not)? Thank you again for getting me started, and for whatever further help you give.[/quote]
    Yes, you do need to set your trusted locations in order for VBA to execute without warning you every time. That is done using the Access Options on the bottom of Office Button Menu. Choose Trust Center near the bottom of the left-hand pane, and then click the Trust Center Settings button. Finally choose Trusted Locations in the Trust Center, and add the location of your database. Note that this is set on a per installation basis, so if you have several workstations that will use a database, you need to change the Trust Center Settings on each workstation.
    Wendell

  6. #6
    New Lounger
    Join Date
    Sep 2009
    Location
    USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Wendell and Hans. It took me a few days to plug it in and test it, but it works real nice. I greatly appreciate the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •