Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Question Unanswered: Update Table data without opening new form?

    In MS Access 2002, I have a form with an unbound control that uses a query as a row source to a simple one column table. Right now if I want to update the table data, I have to open another form and ADD the new data...
    How can I double-click on the control on the form and ADD new data into the table directly on the main form????

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Generaly, if a piece of data is not contained within the list of a ComboBox you, or the user, are informed of this fact. But this will only happen if the Limit To List property for the ComboBox is set to Yes. Although you don't have to limit your ComboBox entries, it is never the less a better way to handle things in your particular case.

    In a lot of cases DB developers like to give a simple custom message that is displayed to the user if any particular item does not exist within the Combo list and within that message, inform the user to Double-Click on the control so as to display the required Form in order to add the data if so desired. There are obvious reasons for this with the largest being that, there is almost always more that one piece of data pertaining to the item within the Combo list and we generaly want all the data or information supplied. Also, if a simple Double-Click is available to add the supplied items to table then you will need to have mechanisms in place to prevent duplicate data.

    In your case, there is only one column of data in your related table so this really is'nt an issue. In any case, to automatically handle the insertion of table data, it should be done through the Not In List event. Again In your case this is how you might handle it:

    Code:
    Private Sub MyComboBoxName_NotInList(NewData As String, Response As Integer)
       ' Inform the user with a custom message that the data provided
       ' can not be found within the Combo List and ask if they would
       ' like to add the data.
       If MsgBox("The data provided can not be located within the table " & _
    			 "for this list. " & vbCrLf & "Do you want to add this data to the table?", _
    			 vbExclamation + vbYesNo, "Data Not In Table") = vbYes Then
    	  ' If YES then prepare to insert the supplied data via SQL string.
    	  ' Declare the variable we will use to hold our SQL String.
    	  Dim SQLstrg As String
    	  ' Fill the Variable with our SQL String.
    	  SQLstrg = "INSERT INTO [myComboTableName] VALUES ('" & NewData & "')"
    	  ' Turn OFF the Access Warning message system.
    	  DoCmd.SetWarnings False
    	  ' Run the SQL command located within our SQL String.
    	  DoCmd.RunSQL SQLstrg
    	  ' Turn ON the Access Warning message system.
    	  DoCmd.SetWarnings True
    	  ' Refresh the ComboBox List. You don't have to save the current
    	  ' record by using this method. You will of course need to change
    	  ' the names in this Query to those that match what you are using
    	  ' in your ComboBox.
    	  Me.MyComboBoxName.RowSource = "SELECT myComboTableName.ComboBoxItemInTable FROM myComboTableName ORDER BY myComboTableName.ComboBoxItemInTable;"
    	  ' Inform the Event that we have handled the problem and that 
    	  ' data was added to the list.
    	  Response = acDataErrAdded
       Else
    	  ' If NOT then...
    	  ' Inform the Event to ignore the problem. We've taken care of it
    	  ' so to speak.
    	  Response = acDataErrContinue
       End If
    End Sub
    Simple as that.
    If you're bent on using the Double-Click idea then you might do it this way...

    Code:
    Private Sub MyComboName_NotInList(NewData As String, Response As Integer)
       ' Inform the user of what to do if the Item supplied 
       ' can not be found within the Combo list.
       MsgBox "The data provided can not be located within the table " & _
    		  "for this list. " & vbCrLf & "If you want to add this data " & _
    		  "to the table then Double-Click on the entry box.", _
    		  vbInformation, "Data Not In List"
       ' Inform the Event that we've handled the problem.
       Response = acDataErrContinue
    End Sub
     
     
    Private Sub MyComboBoxName_DblClick(Cancel As Integer)
       ' See if the data item is already contained within our table.
       If Nz(DLookup("[ComboBoxItemInTable]", "[myComboTableName]", "[ComboBoxItemInTable] = '" & Me.MyComboBoxName.Text & "'"), "") > "" Then
    	  ' If so then inform the user as such.
    	  MsgBox "The data you are trying to add to the table already exists there.", _
    			 vbExclamation, "Data Already Exists"
    	  ' Exit the Double-Cliick Event
    	  Exit Sub
       End If
       ' Ask the user if He/She is sure they want to add the data.
       If MsgBox("Are you sure you want to add this data to the list?", _
    			 vbQuestion + vbYesNo, "Add Item To List?") = vbYes Then
    	  ' If YES then...   
    	  ' Declare the variable we will use to hold our SQL String.
    	  Dim SQLstrg As String
    	  ' Fill the Variable with our SQL String.
    	  SQLstrg = "INSERT INTO [myComboTableName] VALUES ('" & Me.MyComboBoxName.Text & "')"
    	  ' Turn OFF the Access Warning message system.
    	  DoCmd.SetWarnings False
    	  ' Run the SQL command located within our SQL String.
    	  DoCmd.RunSQL SQLstrg
    	  ' Turn ON the Access Warning message system.
    	  DoCmd.SetWarnings True
    	  ' Refresh the ComboBox List. You don't have to save the current
    	  ' record by using this method. You will of course need to change
    	  ' the names in this Query to those that match what you are using
    	  ' in your ComboBox.
    	  Me.MyComboBoxName.RowSource = "SELECT myComboTableName.ComboBoxItemInTable FROM myComboTableName ORDER BY myComboTableName.ComboBoxItemInTable;"
       End If
    End Sub
    This may look rather complicated...but it realy is'nt. Hope this helps....

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Hey Thanks! It works perfect, just what I needed to do.

Posting Permissions

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