Re: Not in List Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

So "HP-TimSWorkList" is the source table that you are adding the new item?
and rs!WorkPlaces is the name of the field in that table?



"Terry" wrote:

> Hi,
> Have a look at my code below and see if it is anything you can alter and
> use.
> Regards
>
>
> Private Sub PlacesOfWork_NotInList(NewData As String, Response As Integer)
> Dim db As DAO.Database, rs As DAO.Recordset
> On Error GoTo Err_Event
>
> ' Prompt user to verify they wish to add new value.
> If MsgBox("Your entry is not in the list, do you wish to add it??", _
> vbYesNo) = vbYes Then
> Set db = CurrentDb
> Set rs = db.OpenRecordset("HP_TimsWorkList", dbOpenDynaset)
> rs.AddNew
> rs!WorkPlaces = Left$(NewData, 50) 'Data is returned by NewData
> here
> rs!SortOrder = 0
> rs.Update
> rs.Close
> Set rs = Nothing
> Set db = Nothing
> 'Response of acDataErrAdded tells the comobox to requery its row
> source
> Response = acDataErrAdded
> Else
> 'Show default message
> Response = acDataErrContinue
> Me.Undo
> 'Response = acDataErrDisplay
> End If
>
> Exit_Event:
> Exit Sub
>
> Err_Event:
> MsgBox "I have encountered an unexpected error." & vbCrLf & _
> "Please advise the System Administrator." & vbCrLf & vbCrLf & _
> "Error: " & Err.Number & " " & Err.Description, vbOKOnly + vbCritical, _
> "Error Encountered"
> Resume Exit_Event
>
> End Sub
>
> "truepantera" <truepantera@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C815F828-E4BC-46B3-9CEA-F4FD69A7875C@xxxxxxxxxxxxxxxx
> >I cannot figure out what is wrong with my code. I am trying to add a not in
> > the list entry to the original table which is the source of the combo box
> > on
> > my form. The source table is "Nametech" which has the field "NameTech that
> > needs to be updated from the current form ""Internal Chart Audit" the
> > combo
> > box name is "Technician_Name" Spot anything wrong with the coding?
> >
> > Private Sub Technician_Name_NotInList(NewData As String, Response As
> > Integer)
> > On Error Resume Next
> > Dim Db As Database, rs As Recordset
> > Dim strmsg As String
> > strmsg = "'" & NewData & "' is not in Current List"
> > strmsg = strmsg & "@Do you want to add them?"
> > strmsg = strmsg & "@Click Yes to link or No to Cancel."
> > If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
> > Response = acDataErrContinue
> > Else
> >
> > Set Db = CurrentDb
> > Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
> > On Error Resume Next
> > rs.AddNew
> > rs!Technician_Name = NewData
> > rs.Update
> >
> > If Err Then
> > MsgBox "An error occurred. Please try again."
> > Response = acDataErrContinue
> > Else
> > Response = acDataErrAdded
> > End If
> > End If
> > rs.Close
> > Set rs = Nothing
> > End Sub
>
>
>
.

Relevant Pages

  • Re: Not In List: SQL adds info, need more information
    ... Private Sub Combo39_NotInList(NewData As String, Response As Integer) ... Dim strSQL As String ... Response = acDataErrContinue ...
    (microsoft.public.access.formscoding)
  • RE: Add Record with combo box
    ... Private Sub JCTARSectionLayer1ID_NotInList(NewData As String, ... Response As Integer) ... Dim strMsg As String ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)
  • RE: Add Record with combo box
    ... Private Sub JCTARSectionLayer1ID_NotInList(NewData As String, ... Response As Integer) ... Dim strMsg As String ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)
  • RE: Combo Box and Limit To List
    ... Private Sub Requesting_Party_NotInList(NewData As String, Response As Integer) ... Dim mbrResponse As VbMsgBoxResult ...
    (microsoft.public.access.formscoding)
  • RE: Add Record with combo box
    ... Private Sub JCTARSectionLayer1ID_NotInList(NewData As String, ... Response As Integer) ... Dim strMsg As String ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)