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.

Access 2007 Form - edit database record

Discussion in 'Business Applications' started by Charmian, Mar 9, 2012.

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

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    195
    Question:
    I have a form that shows the user all their inputs for the day in a list box - they can click on a record and be presented with that input so that they may make changes and edit their input.

    The coding to collect the primary key from their list works - I can show it on the new form BUT
    how do I open the table so that it populates the form. Or do I have to collect all the fields in embedded SQL in VB - assign them to unbound fields in the form and when the user has finished editing, I have to use VB to update the fields.

    I really hope there is an easier way to do this than the VB way.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
  3. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    195
    I have them going through a menu type form (modal) that they put in dates and that populates the listbox on that form. They click to select a line from the listbox and then I want to take them to that record so that they can update it.
    What is a Continuous forms mode form? I think this may be a case of me not knowing what I don't know.
    Which if its not a huge VB piece of code to populate the form, I'm keen.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    The form looks like a datasheet form, but it retains the interactions that a normal single form has, which datasheet view does not have.
    You can also have "Calculations" in Continuous" forms with totals etc in the footer.
    The key to populating your form with "they put in dates and that populates the listbox" would be to create a query that uses the same criteria to supply the records for the form.
    Or you could show all the records in the form and then Filter them using the "they put in dates and that populates the listbox"
     
  5. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    195
    Do you have a link to a tut or example of the use of a Continuous form - so that I can navigate and learn - because the solution I have found will work - it will be a total horror to work with, so anything more elegant and easier will be useful.
    Does it have to look like a datasheet or can you make it prettier? If not, they will need to live with it, as it's just meant to be a check on their work and correcting any input errors.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
  7. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    195
    Hi - the continuous form looks good. Is there a way to filter it programatically? I have the user's id and I have the dates they have requested. These are variables in VB - can I use these variables to filter the on load of the form - using a sql in vb?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    yes there is "a way to filter it programatically", in fact there are a few methods :-
    1. use query criteria and requery the subform.
    2. Use VBA SQL and reset the subform's record source to the sql.
    3. my favourite - use VBA to set the subform's Filter.
     
  9. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    195
    Am trying your option 3. This is what I've put - but it's not correct because its not working. What should I be setting up and is it on Form_filter or on load. The msg box for Form filter doesn't even show. When I put the same coding in "on load" I get the message box , but the form is empty.
    Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
    MsgBox ("in form_filter")
    Me.Filter = "(cdate BETWEEN " & mystartdate & " AND " & myenddate & ") AND ccode = " & mytillet
    Me.FilterOn = True
    End Sub
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,671
    Getting the Filter syntax correct is quite difficult.
    But the main thing is to put the code in something you are sure will run, the best thing is a "Filter data" Command button, with another one to "Show all Records".
    I would add error trapping code to what you have and then start with just 1 filter item and then slowly build up the filter statement.
     
  11. 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/1044478

  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