Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble with VBA event (Access 2000)

    I'm trying to use this code for the on Not In List Event

    Private Sub cboOrg_NotInList(NewData As String, Response As Integer)

    Dim strMessage As String
    Dim dbsOrg As Database
    Dim rstOrgs As Recordset

    strMessage = "Are you sure '" & NewData & _
    "' to do this ?"


    If Confirm(strMessage) Then

    'Open the organization table and add the NewData Value
    Set dbsOrg = CurrentDb
    Set rstOrgs = dbsOrg.OpenRecordset("Organization")
    rstOrgs.AddNew
    rstOrgs!Organization = NewData
    rstOrgs.Update
    Response = acDataErrAdded
    Else
    Response = acDataErrDisplay
    End If

    End Sub




    I get an error on the

    Dim dbsOrg As Database

    telling me that I should use a user defined type and not a project. Have any idea what's wrong?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble with VBA event (Access 2000)

    I wonder if it's a references problem.
    Go to any code module and Tools/References to check if there are any missing references.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Got it

    Got one problem solved. Now just gotta work on getting the data to store in the database correctly.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Got it

    I take it that you had forgotten to set the reference to the DAO 3.6 library. If you have both DAO and ADO references set, you would be advised to get into the habit of declaring objects as DAO.Database and DAO.Recordset. Otherwise, just declaring an object as a recordset will default to whichever reference is first in the list, and the two don't have the same methods and properties.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got it

    I use this in my combo's, it may help you.
    I've alterred to suit yours as best as I can.
    I usually create a little pop up admin form to enter extra data relevant to the new data.
    Hope this helps.

    Private Sub cboOrg_NotInList(NewData As String, Response As Integer)
    Dim STRSQL As String, X As Integer
    Dim LinkCriteria As String
    Dim strMsgTitle As String
    strMsgTitle = "!!"
    X = MsgBox("Are You Sure You Want To Do This?", vbYesNo, strMsgTitle)
    If X = vbYes Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    'Open an admin form of your choice to enter relevant data
    stDocName = "frmYourFormName"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    STRSQL = "Insert Into tblYourTable ([txtYourFieldName]) values ('" & NewData & "')"
    CurrentDb.Execute STRSQL, dbFailOnError
    LinkCriteria = "[txtYourFieldName] = '" & Me!cboOrg.Text & "'"
    DoCmd.OpenForm "frmYourFormName", , , LinkCriteria
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub

Posting Permissions

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