Microsoft Access help required

Discussion in 'Computer Software & Operating Systems' started by HowPts, Sep 14, 2007.

  1. HowPts

    HowPts
    Standard Member

    Joined:
    May 7, 2007
    Messages:
    185
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    18
    Ratings:
    +2
    I wonder if any kind soul can help, I know access is very complex but I would like to be able to select a record from a list in a one form and then click a button which in turn will open a new form window with the selected record loaded in it for editing
     
  2. cerebros

    cerebros
    Active Member

    Joined:
    Dec 27, 2002
    Messages:
    1,366
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    48
    Location:
    Leicester
    Ratings:
    +43
    There are a couple of ways you could do this depending on what method you want to use to perform the updates (i.e. using VB coded SQL statements, making use of recordsets etc), and whether you're actually wanting to edit your underlying record or need to create a new updated instance but be able to back-track to the old version if need be.

    Also depends on how "properly" you want to achieve it.. I'm more of a hack'n'code merchant so I'd probably not be your first choice of help, although I'll lend a hand if no-one else steps in
     
  3. HowPts

    HowPts
    Standard Member

    Joined:
    May 7, 2007
    Messages:
    185
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    18
    Ratings:
    +2
    Hi cerebros thanks for the reply, even though I was quite lost, this database has been built of the years and added to, it uses an old search method where the ‘ causes a run error 3075, unless you type ‘’ in other words to search for the word Fred’s it would have to be searched Fred’’s I mention this to give you some idea how odd this database and forms are,.
    To start with I simple want to select a record have it pop up in a new window so I can edit that record close it and it will be reflected in the original form, at present I simple open the edit form search for the record and edit the record and close the form
     
  4. cerebros

    cerebros
    Active Member

    Joined:
    Dec 27, 2002
    Messages:
    1,366
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    48
    Location:
    Leicester
    Ratings:
    +43
    Ok. I'm assuming that on the first form where you're picking the record to update, the list where you make that selection is:

    1) A list box or combo box fed from either the root table or a query, and that the first column of the list is the primary key

    2) The list is actually a continuous forms subform fed from the root table or a query, and that the somewhere on the subform is a textbox (probably hidden) that contains the primary key value for the record.

    (if not you might need to provide a lot more detail so that poor hackers like me can figure out what's going on)

    If either of these is the case, create a new query that contains all the fields for the record that you need to update, include the primary key field. under the criteria for the primary key field you need to refer to the value in your list.
    To make things as simple as possible we'll just refer to the control (i.e. the list) on the form

    if scenario 1 applies then just enter the following:
    [Forms]![formname]![listname]
    if scenario 2 applies then
    [Forms]![formname]![subformname]![textboxname]

    When you run the query only the record selected in the list should show (make sure you've selected something in the list first).

    Create a new form with that query as the Record Source and lay out the fields as appropriate.

    Now go to your previous form and find the button you want to use to open the next form and go to its properties. From the "Events" button pick either the "Click" or "Double-click" event (depending on whatever is standard for the database) and click on the button to the right that has the "..." on it. From the list you are presented with, pick "Code Builder".

    You'll want to put in a bit of code to check that a value has been picked from the list (otherwise you could open the form and find there isn't a record) and that tells the user to pick a value if they haven't.

    When your conditional test shows that the user has selected a value, you need to use the OpenForm command to open the form where you'll be editing the record. The Vb code will take the form of:
    DoCmd.OpenForm ("2ndformname", acNormal, , , acDialog)
    This will open your new form (which here is called 2ndformname) in form view but also make it modal (the acDialog bit) so you can't switch back to another form without closing 2ndformname first.

    Save this and now try it out and your new form should hopefully open with the details of the record you want to edit
     
  5. cerebros

    cerebros
    Active Member

    Joined:
    Dec 27, 2002
    Messages:
    1,366
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    48
    Location:
    Leicester
    Ratings:
    +43
    Now on your new form I'd suggest creating a couple of new buttons, one called "Save" and the other called "Close".

    In the code for "Close" use whichever method is normally used in the database.

    Next, assuming all the fields you need to update are in the same table, create an Update query using the table you will be updating as the source.

    For the field which is the primary key of the table, under criteria use the same reference to the list on your first form that I showed in my last post.

    For the other fields, where it says "Update to" enter the name of the control on your new form that relates to that field. Save the query (I'll assume for this example it's called Query2)

    Now go to your new form and create a On Click or On Double-CLick event for the "Save" button. We now need to put some code in to run the update query when you have finished editing the record. To do this you need to use the "OpenQuery" method as follows:

    DoCmd.OpenQuery "Query2"

    This will run the update query. Next you need to refresh the list on your original form to reflect the changes. To do this you just need to apply the Requery method to the control reference. i.e.

    [Forms]![formname]![listname].requery

    finally, we need to close the form, so use whatever code is normally used in the database for this
     
  6. cerebros

    cerebros
    Active Member

    Joined:
    Dec 27, 2002
    Messages:
    1,366
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    48
    Location:
    Leicester
    Ratings:
    +43
    That should be it.

    It's not the best way in the world, but it should work assuming all the fields you need to update are in one table (if not it gets a lot, lot more involved).

    Also, the issue you're having with the apostrophes is down to Access' string handling - this is a right royal pain in the ass (as can date handling be if you refuse to be bludgeoned into using the US date system of month, day, year)
     
  7. HowPts

    HowPts
    Standard Member

    Joined:
    May 7, 2007
    Messages:
    185
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    18
    Ratings:
    +2
    Your help is more then appreciated but your light years ahead of me, most of my database is built up dissecting the Microsoft sample databases, I can understand the tables forms quires and the macro’s I can cope with but the programming using the visual basic properties is a nightwear for me what I need is a working database I can dissect and see how it works, I have printed out your posts and try to see how far I get, I hope you don’t think I have wasted your time like I said I really appreciate it.
     
  8. HowPts

    HowPts
    Standard Member

    Joined:
    May 7, 2007
    Messages:
    185
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    18
    Ratings:
    +2
    I can email you my database with a lot of records deleted to reduce the size, then you can see what a mess I have made!


    A basic description of the database.
    The database is a single table linked to the forms by a couple of query's depending on which form is opened, each has it's own specific fields in and sorted by the appropriate field.
    The forms that the records are picked from all have a subform (with a space for command buttons) the main part of the form holds the search boxes for the appropriate fields to be searched.
    This is also true for the edit form but has more fields (details of each record) this also has it'd search boxes because I need to find the record again (hence the original posting asking how I can send an appropriate record for editing.
     

Share This Page

Loading...