Results 1 to 11 of 11
  1. #1
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16

    Not in List Event - add value to table and opens form to complete record information

    Hi All,



    I have a form "frmJobRecord" which has a combo-box on it to select Customers "cboCustomer" this is a lookup to a Customers table "tblCustomers". What I'm wanting to do is, when a value is entered into "cboCustomer" and is not in the list a msg box comes up saying it is not in the list etc, gives a option to add it to the list (add to "tblCustomers") but also opens a second form "frmDiabox_NewCustomer" where the complete Customer information can be entered and save. Then on saving and closing the "frmDiabox_NewCustomer" form I'd like the customer just added to be available in "cboCustomer" combo-box.

    This is the code I have so far, I just need to get it to open the "frmDiabox_NewCustomer" form to the record entered in the "cboCustomer" combo-box.

    Code:
    Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
    On Error GoTo cboCustomer_NotInList_Err
        Dim intAnswer As Integer
        Dim strSQL As String
        intAnswer = MsgBox("The Customer " & Chr(34) & NewData & _
            Chr(34) & " is not currently listed." & vbCrLf & _
            "Would you like to add this Customer now?" _
            , vbQuestion + vbYesNo, "Customer Unavaliable")
        If intAnswer = vbYes Then
            strSQL = "INSERT INTO tblCustomers([CustomerName]) " & _
                     "VALUES ('" & NewData & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            MsgBox "Please complete new customer information" _
                , vbInformation, "Complete New Customer Information"
            Response = acDataErrAdded
        Else
            MsgBox "Please choose a Customer from the list." _
                , vbInformation, "Customer Unavalible"
            Response = acDataErrContinue
        End If
    cboCustomer_NotInList_Exit:
        Exit Sub
    cboCustomer_NotInList_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume cboCustomer_NotInList_Exit
    End Sub
    Any help would be really appreciated

  2. #2
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi carlie017

    this is what I have in my combo box

    Code:
    Private Sub NoteTypeCBOBox_NotInList(NewData As String, Response As Integer)
    
       On Error GoTo NoteTypeCBOBox_NotInList_Error
    
       Dim AddNoteType
       AddNoteType = MsgBox("Would You Like To Add " & _
          StrConv(NewData, vbProperCase) & " As A New Note Type? ", vbYesNo)
    
       'Visual Basic gives you VBYes and VBNo Constants
       ' that you can use to find out what button was clicked.
       If AddNoteType = vbNo Then
          ' cancel the edit.
          NoteTypeCBOBox.Undo
          ' tell access not to show the error message you have already delt with it
          Response = acDataErrContinue
       Else
          ' tell access not to show the error message you have already delt with it
          Response = acDataErrContinue
          'tell access not to worry as you are adding the missing contact
          Response = acDataErrAdded
          ' open the add contact form with these details
          DoCmd.OpenForm "frmCustomersAddNoteTypes", , , , , acDialog, NewData
          'cancel the edit. thats because you need to refresh the list before you can select the new Product
          NoteTypeCBOBox.Undo
          'refresh the list
          NoteTypeCBOBox.Requery
          
          'now find the Product from the newly created list using dlookup
          NoteTypeCBOBox = DLookup("[CustomerNoteID]", "[tblCustomerNotesType]", "[NoteType]='" & NewData & "'")
          
          ' tell access not to show the error message if the dataform is cancelled
          Response = acDataErrContinue
          
       End If
    
       On Error GoTo 0
       Exit Sub
    NoteTypeCBOBox_NotInList_Error:
       MsgBox "Error Has Been Found " & Err.Number & " (" & Err.Description & ") In Procedure (NoteTypeCBOBox_NotInList) Of (Sub Form_frmCustomersAddNote)"
    End Sub
    basically what happens is is the text entered into the combo box is not in the list a msg box fires asking if they want to enter it, if not the entry is cancelled
    if they do it opens a new form were the entry can be entered, along with a description in my case

    hope this helps

    Steve

  3. #3
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    Thanks Steve,

    Will this carry across the text entered into the combo-box on the new record entry?

    For example, if the user enters a customer name 'ABC' into the combo-box which is not in the list, will the code open the new customer form with 'ABC' already entered into the customer name, and from there the user can enter the rest of the customer information.

    I was trying to achieve this by the following, but could get the new customer form to open for the rest of the information to be added.

    Code:
     If intAnswer = vbYes Then
            strSQL = "INSERT INTO tblCustomers([CustomerName]) " & _
                     "VALUES ('" & NewData & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            MsgBox "Please complete new customer information" _
                , vbInformation, "Complete New Customer Information"
            Response = acDataErrAdded

  4. #4
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    on the "frmCustomersAddNoteTypes" that opens the text entered into the "NoteTypeCBOBox" is carried over by using this code

    Code:
    CustomerNoteTypeTXTBox = OpenArgs
    on the frmCustomersAddNotesTypes onload event

    Steve

  5. #5
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    Thanks Steve,

    the new customer form "frmDiabox_NewCustomer" is used to enter a new customer from scratch as well. As it, this form opens for different locations, one location is on this not in list event on the Customer combo-box and the other location is from a 'Add New Customer' command button. If I use the onload event will it not be looking for the value in cboCustomer combo-box every time the "frmDiabox_NewCustomer" form is opened?

    Thanks,
    Carlie

  6. #6
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    ok that's fine what you can do is check to see if the form is open using this,

    Code:
    If Application.CurrentProject.AllForms(YOURFORMNAMEHERE).IsLoaded = True Then
            if it is put your code here
        Else
           do something else
        End If
    that way you can call code to run depending on weather the correct form is already open

    Steve

  7. #7
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    Okay, I'm not sure I've done this right now...

    this is the code i have in the on load event for the new customer form "frmDiaBox_NewCustomer"

    Code:
    Private Sub Form_Load()
    If Application.CurrentProject.AllForms(frmJobRecord).IsLoaded = True Then
        CustomerName = OpenArgs
        Else
        DoCmd.GoToRecord , , acNewRec
    End If
    End Sub
    Its just opening a new record though (the "frmJobRecord" was open), not bringing over the text entered on the combo-box "cboCustomer" on the Job Record form "frmJobRecord" which is where the below code is

    Code:
    Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
     
       On Error GoTo cboCustomer_NotInList_Error
     
       Dim AddCustomer
       AddCustomer = MsgBox("Would You Like To Add " & _
          StrConv(NewData, vbProperCase) & " as a new customer? ", vbYesNo)
     
       If AddCustomer = vbNo Then
          cboCustomer.Undo
          Response = acDataErrContinue
       Else
          Response = acDataErrContinue
          Response = acDataErrAdded
          DoCmd.OpenForm "frmDiabox_NewCustomer", , , , , acDialog, NewData
          cboCustomer.Undo
          cboCustomer.Requery
         
          cboCustomer = DLookup("[CustomerName]", "[tblCustomers]", "[CustomerName]='" & NewData & "'")
         
          ' tell access not to show the error message if the dataform is cancelled
          Response = acDataErrContinue
         
       End If
     
       On Error GoTo 0
       Exit Sub
    cboCustomer_NotInList_Error:
       MsgBox "Error Has Been Found " & Err.Number & " (" & Err.Description & ") In Procedure (cboCustomer_NotInList) Of (frmDiabox_NewCustomer)"
    End Sub

  8. #8
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    are the form data , data entry properties set to "Yes"?

    if so change to "No" and try the running it again

    if the form data entries are set to yes, it will allow you to enter new entries every time the form opens

    if the form is open as data entry will be set to No allowing you to see what you entered in the pervious form
    if the form is not open the if statement will set the form into data entry mode allowing you to manually enter data



    Steve
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  9. #9
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    The Data > Data Entry is set to no
    Click image for larger version. 

Name:	Capture.JPG 
Views:	8 
Size:	34.6 KB 
ID:	28956

    I just double checked it and the text from frmJobRecord.cboCustomer still isn't coming across to frmDiabox_NewCustomer.CustomerName

  10. #10
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Mate

    Just sent you a PM

    Steve

  11. #11
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    HI Mate

    sent you another PM, your e-mail has not arrived

    Steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-14-2017, 03:17 PM
  2. Replies: 4
    Last Post: 06-09-2016, 06:00 AM
  3. Replies: 4
    Last Post: 12-16-2014, 05:08 PM
  4. Replies: 4
    Last Post: 10-27-2014, 10:26 AM
  5. Replies: 8
    Last Post: 05-16-2014, 01:21 PM

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