1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

updating a table from a form....

Discussion in 'Business Applications' started by softspoken, Apr 20, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. softspoken

    softspoken Thread Starter

    Joined:
    Mar 22, 2010
    Messages:
    55
    I created a database, split the database, put the front end on multiple users pc's, and now I have an issue.

    The issue is : I have a few fields that are drop down boxes so that they can pick something from the list. There will be times when they will have to key in something because it was not in the drop down box.

    Is there a way to add what was keyed in the form manually , automatically to the table that houses the drop down information?

    I hope that is clear enough....

    (Access 2003)
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, I will discuss it tomorrow.
     
  3. softspoken

    softspoken Thread Starter

    Joined:
    Mar 22, 2010
    Messages:
    55
    ok thanks
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What type of entry do you need to make? If you need to enter more than one field then a Form needs to be opened and the entries made.
    If it is just one field then it can be handled using the Combo box's "On Not in List" property along with the setting the "Limit to List" property to "Yes".
     
  5. softspoken

    softspoken Thread Starter

    Joined:
    Mar 22, 2010
    Messages:
    55
    Its just one field so the Combo box's "On not in List" would be fine. Can you assist me on how to set that up.

    The table that is assigned to the combo box is called "Summary of App Err". Thank you
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    In the combo properties change the "Limit to List" property to "Yes".
    In the combo properties "On Not in List" event procedure click on it and select "Event Procedure" and click the 3 small dots to open the VBA Editor and then Paste this in

    Dim rst As Object, txtName As String

    On Error GoTo errorcatch

    txtName = MsgBox("Add '" & NewData & "' to the list of Clients?", _
    vbQuestion + vbYesNo)
    If txtName = vbYes Then
    ' Add data stored in NewData argument to the Summary of App Err table.
    Set rst = CurrentDb.OpenRecordset("Summary of App Err")
    rst.AddNew
    rst!FieldName = NewData
    rst.Update
    Response = acDataErrAdded ' Requery the combo box list.
    Else
    Response = acDataErrDisplay ' Require the user to select
    End If
    rst.Close
    Set rst = Nothing
    Exit Sub
    errorcatch:
    MsgBox Err.Description

    The line of code starting rst!FieldName, the Fieldname needs ot be changed to the name of the field that will hold the new data in the Table.
     
  7. softspoken

    softspoken Thread Starter

    Joined:
    Mar 22, 2010
    Messages:
    55
    That worked perfectly. Thank you so much for all your help. You have been very helpful from day one and its much appreciated.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    My pleasure.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918101

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice