Ok, ive looked through the history, and check microsoft, ive got this so far

[Crilen007]

Sub cmbDiv_NotInList(NewData As String, Response As Integer)
         Dim DB As Database
         Dim RS As Recordset
         Dim Msg As String
         Dim CR As String: CR = Chr$(13)

         ' Exit Sub if the user cleared the selection.
         If NewData = "" Then Exit Sub

         ' Ask if the user wants to add the new customer.
         Msg = "'" & NewData & "' is not in the list." & CR & CR
         Msg = Msg & "Do you want to add it?"
         If MsgBox(Msg, 32 + 4) = 7 Then
            ' If the user chooses No, instruct the user to try again.
            Response = DATA_ERRCONTINUE
            MsgBox "Please try again."
         Else
            ' If the user does not choose No, create a new record in the
            ' Customer table.
            On Error Resume Next

            ' Open the Customer table.
            Set DB = DBEngine.Workspaces(0).Databases(0)
            Set RS = DB.OpenRecordset("Division", DB_OPEN_DYNASET)

            RS.AddNew
               RS![DivisionName] = NewData
            RS.Update

            ' If an error occurred while adding the record...
            If Err Then
               ' ...instruct the user to try again.
               Response = DATA_ERRCONTINUE
Beep:                MsgBox Error$, 48
               MsgBox "Please try again."
            Else
               ' If no error occurred, add the element to the combo box
               ' list.
               Response = DATA_ERRADDED
            End If

         End If
      End Sub

When i enter the data and press enter, it goes to debug and selects "Dim DB as Database"


Am i using old code?
Im using access 2002, when i type dim DB as , database doesnt show up in the list. any ideas?

Thanks

 

[Rich]

Dim DB As DAO.Database

 

[Crilen007]

that didnt work either, still does the same thing.. stupid access lol this can be a pain

 

[Crilen007]

Private Sub Combo0_NotInList(NewData As String, response As Integer)
Dim strInput As String
response = MsgBox("Must: " & NewData & " be added", vbYesNo, "New color")
If response = vbYes Then
       DoCmd.SetWarnings False
       DoCmd.RunSQL "INSERT INTO tblDiv (Entry) VALUES ('" & NewData & "');"
       DoCmd.SetWarnings True
       'Force new value to show
        'Me.Combo0.RowSource = "SELECT tblDiv.Entry FROM tblDiv;"
       'Me.Combo0 = NewData
       response = acDataErrContinue
   End If


End Sub

This works, but i have to close and open the form again for it to refresh the combo box, ive added this and it still does not work

Private Sub Combo0_AfterUpdate()
Forms![frmTest].Requery
End Sub

 

[Kevin_S]

Your running into problems because the default setting for Access 2002 is ADO and the code you are using is DAO... You can either modify the code to the new ADO standard or (more simply) set DAO as a code reference. To do this open the code in the code module and then select Tools -- References from the db menu. In the reference list scroll and find the reference for DAO (Microsoft DAO 3.6 Object Library) and turn it on by clicked the tick box. After you set this reference the code should run without hanging on Dim db as database...

HTH,
Kevin

 

[bradcccs]

To have "NewData" appear in your combo, you will need to requery the combo (not the form).

Me.Combo0.requery

(Or you could refresh the form - Me.FrmTest.refresh )

I think the combo requery is preferable.

Brad.