Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2008
    Posts
    12

    Unanswered: Having a problem with forms

    Hi all, I need your help,

    I have'nt got a clue... I have setup a database and i have created 3 forms to enter info. I have created a button on each form to navigate between each.

    The problem is as more records are created and when i click to open the other forms the record goes back to 1, I want to be able to open the form on the record number that I am currently working on.

    When I click on the button the current form closes and the required form opens.

    PLEASE PLEASE PLEASE HELP ME (pulling out my hair)

    thnx Moss

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Go to Help on Access tool bar and search on Filter

    Different ways of filtering allow you to open another form whereby a criteria on that form matches the one on the form you are opening from, usually based on an ID field that both records share.

    You are better off to get very well aquainted with filtering methods as opposed to a simple quick answer because filtering/record selection is one of the foundations of making a working data base

  3. #3
    Join Date
    Jan 2008
    Posts
    12
    hi mike im using 3 different forms to create the record as there are a lot of fields to fill in. Trying to make it simple for my work collegues to enter the info. but i click on the button and the new form opens showing record 1, i would like it to either show blank fields if the info has not yet been entered or show existing info if they have. relative to the record im working on.

    I might not be making sense

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Either way you are into filtering or record selection. Your different forms are only giving you a different view of the same record and therefore Form2 has to open on the same record as Form1 but it is displaying different fields of the same record.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    hi mike im using 3 different forms to create the record as there are a lot of fields to fill in.
    That can't be a good idea. Just how many fields are being filled in? Surely it would be better to just use one form and a tab control.

    Nonetheless you can open a form to a specific record by applying a filter during the OpenForm command.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    It really has me beat why people don't buy "at least" one book on Access.

    This is the response I gave him in a reply to his PM. I hope it has helped.

    --------------------------------------------------------------------------------

    When you open a form it first opens and displays all records in the table or query which it is based on. At the bottom of the form it will say 1 of 66 or 1 of 5000 as the case might be.

    What you need to do is "filter" the record or records you want out of the whole display.

    If you go to your form in Design view and right click on either the buttons, fields, labels or the area of the screen that is outside the body of the form and then click Properties, a box will open. On that box there are tabs, click on the tab for Event. If you have Access 2007 I think the properties box is off to one side. I don't have Access 2007, so if you do and an't find Properties then post and ask.

    When you see Event you see a list of things such as On Click, On Lost Focus and so on. These are the events that will trigger VBA or macros.

    Your present button will have very basic VBA to simply open a form. But what is missing is a "Where Clause".

    A macro or VBA is written that will provide the "where clause" or the VBA or macro will open the second form and change its record source to a query where the criteria of the query is information or data in a field on the form you are opening from.

    If you could actually speak to Access then you would say......please open my other form and then find a record in that form where the value of Fieldxyx is the same as the value of "Fieldabc" (Or Fieldxyz) on my first form.

    The most common reference will be to a unique ID field. If it was not unique, such as lastname etc. then the seond form might display a few records. Do you habve an autonumber field or some other ID field?

    You need to get a couple of books on Access

  7. #7
    Join Date
    Jan 2008
    Posts
    12
    The reason why i have so many fieldss its all information eg name, addy next of kin their addy, wether tests have been taken info from other forms basically info where i have to use memo cus of the size in the end it all merges into 15 pages . I have now got a book called Access VBA programming, but sometimes confirmation of what i think im learning is better provided by people. I do apprieciate ur help

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you have real lots of fields in a table then you need to carefully examine whether some of pieces of data should be in another table.

    Look for fields/data that can have multiple instances and especially where the number of instances is an unknown. For example......next of kin

    Do a search on Google for Normalisation Access and you will see some easy to understand examples of table structures. If you don't have your table structure at least basically right then it can prove to be a real pain further down the track.

  9. #9
    Join Date
    Jan 2008
    Posts
    12
    I think im trying to run before i can walk. this book that i have got doesnt even tell me about filters. I know u wanna shoot me ..... heheheheeh ok i will check out some tutorial sites on how to apply filters in vba. Unless of course. u wanna get more stressed with me and guide me.

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    This is basically the order to "build the house"

    Tables

    The tables are like the foundations of the house. Like the house foundations, you don't see the tables in normal use of the data base but if the the foundations are weak, then the real nice house will fall down.

    For your tables you start with what you might call your maintable. This will have the basic details of whoever or whatever is to be the main entity.

    In general, the fields for the maintable will be for data that applies to all entries. Everyone has a first and last name, a date of birth, a gender etc. No one has two dates of birth or two genders. Everyone is either a smoker ir non smoker. However, next of kin, test results, details of meetings or file notes will vary with people.

    The classic example used for Access is Customers and Orders. There is one customer but the could be no orders, one order or lots of orders that relate to that customer.

    In your maintable you have a field for ID. The Access autonumber field is designed for this purpose. It will automatically a unique number each time a new record is added. There will be no duplicates of ID number in the maintable. But the orders table or next of kin table or the appointment details table, will have the customers ID number repeated.

    Access is based on tables/records having a One to Many relationship. Thus in the main table, the customer or your main entity has one record and never more than one record. That one record relates to Many Records in a related table.

    Let's say we tried to include children of the main entity as part of the main entity's record. Then we would need to have Child1Gender, Child1FirstName, Child1DateBirth and then Child2Gender, Child2FirstName, Child2DateBirth etc. But how many fields would we make allowance for. Think of the difficulty with searches. Access also limits you to 255 fields in a table and you might run out of fields. However, if we have children details in another table then it does not matter whether the person has no children, one child or 10 children.

    If you don't have yur data base at least half right on a One to Many table setup then you will be going down a road that ends with a swamp and you will need to turn around start again.

    Queries

    Select Queries look like tables and froms can are more often than not based on queries. Queries select records from the table based on a criteria that you supply. Thus a query can display all the records where the gender is male, the date of birth is greater than March 1 1980 etc. Queries also allow for doing calculations or altering how data that you entered is displayed. You can create extra fields in queries that are based on the data in the other fields. Thus such a field might have a 1 when certain conditions exist on the record or a 0 for other conditions.

    There are other query types called Append, Delete, Update and MakeTable. These alter data in a table based on a criteria you supply. For example, under the criteria you supply and Append query might add certain records from one table to another table and then under the same criteria a Delete query deletes those same records. Thus you have moved a batch of records from one table to another.

    Queries can join tables and the joing of tables is at the heart of Access. Thus a table that has next of kin can be joined to the table that has the main details of the person. This is done with the ID number. Imagine a doctor has one box of cards with each patient's details. He has another box with a card for each visit. Some patients might have 1 car or 21 cards in the "patient visit" box. If was Access then when pulled out a patient card it could relate that card to the cards for that patient in the "patient visit" box

    Forms, VB and Macros

    If we were to stick with the house analogy then forms, VB and macros would be all the things in the house that are not the bricks and mortar.

    Forms are important because they don't just make for a nice readable display. The forms carry the macros or VBA.

    Basically, macros or VBA simply automate tasks. If we go back to your original post, then VBA or macos will automatically find the record you want in the other form.

    What makes tables and queries critical is that they are at the foundation level of the house and so changing them at a later date can be a big deal. Macros or VBA are a bit like the roof of the house. It is 100 times easier to change 20 roof tiles than it is to change one brick in the foundations.

    Basically, the large majority of macros or VBA are referring to fields on a form which of course will trace back to a field in a table. So changes in a table at a later date can mean a lot of stuff will fall over Trust me on this one

    There are a few things that are done with macros or VBA, whereby you if can do them then you can have quite a complex data base.

    1) Being able to open a form and find a specific record or a selected number of records. This can be done by a "filter system" or changing the record source on the form being opened......to a query that gets its criteria based on data that is on the form from where you are opening the second form.

    2) Being able to change the value of a field. This can be inserting data in one field from another field. It can also be a calculation such as the current date plus 30 days etc.

    3) Making appearance changes on a form and especially making a field or label visible or invisible. All the various formatting you do when making a form can also be done with macros or VBA. A macro or VBA will even change the dimensions or postion of a field on a form.

    4) Setting condtions. These are the the conditions, usually based on data in fields that determine what part of the VBA or macro executes or even which VBA or macro runs. For example, when "clicking" to open another form you might have a warning sign pop up because the date in a field is more than 60 days before the current date.

    However, where macros, VBA and their conditions are less critical than tables and queries is they lend themselves to refinement without having to tear the whole data base apart. Also, no matter how nifty and tricky you can get with macros or VBA, you can still be limited if your table structure is junk.

  11. #11
    Join Date
    Jan 2008
    Posts
    12
    phew... so how do I get a value from one field in one form so that the applyfilter can use that value to open the other form?

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    DoCmd.OpenForm "Form2", acNormal, "", "[Forms2Field]=[Forms]![Form1]![Form1Field]", acEdit, acNormal

    Macro

    OpenForm
    Form2
    [Form2Field]=[Forms]![Form1]![Form1Field]
    Edit
    Normal

    You might want to add a save record in front of either
    Last edited by Mike375; 01-27-08 at 15:21.

  13. #13
    Join Date
    Jan 2008
    Posts
    12

    Cool the pain in the butts data base

    If u can take a look at my database then u will see what im on about, I do thank u loads and im so sorry for being a pest. the book that i have is crap.

    It should open up on the first form

    u dont need to worry about the save print button as that opens a word file that i use to merge

    I know u will look at this and think OMG what a mess but hey Im not an expert like u lot

    moss
    Attached Files Attached Files

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I just had a quick look at it.

    Obvioulsy the navigation between forms needs to be fixed.

    Your two tables seem to be one table that is split. Your form is on a query that joins both of them so perhaps you could just have one table. Given that one table is called Sheet1 I assume it was brought across from Excel.

    I would prefer field names that are done as one word. If you do make any changes in the table (s) it is better to do it now as it can be a real pain down the track when you have layers of forms, macros/VBA.

    Do some of the some of the fields such as Disability require other data if the person answers Yes. Type of disability, degree and duration, more than one disability, workers compensation......physical or mental etc.

    If Age Now is meant to be current then it should be calculated. If in fact it is referreing to their age at starting then it should be calculate and called Age at Start. However, I would still have it calculated so that the person entering the data did not need to work at age now, that is, a calculated age would be inserted into the table.

    If you wish to use the data base to be able to correlate different things eg. all the people with xyz Career Preferences, who got the name of the program from leaflets etc and etc. then some of your fields will be limiting. Naturally data such as Career Preferences needs to be written put perhaps you could put most combinations into a drop down list or have a number go in another field for a category of Career Preferences.

    If you retain the two tables then you might Enforce Referential Integrity and Cascade Delete. That will mean if you delete a record in Sheet1 you will delete the other part of the record in the other table.

    It appears that your data base is really a computerised version of a card or manilla folder system. If that is the case then it is basically OK.

    Your forms should have the Close button (x) removed.

    Some changes I would make for myself, but they are personal rather than functional:

    1) Have the forms the same size and opening the same distance from the top of the screen.

    2) The data entering screen I would have "less busy" Quite a few of your fields are similar categories of data. For example, starting date, which work centre etc. I would have one of these on the data entry form and then another small form would pop open to allow the other data to be entered. There is no problem with the person having to make a decision as the form can be opened when a field loses focus or gets focus. You will often find that people who are newer to your screens initially prefer less busy screens and pop open forms with an instruction on the top.

    3) For people who don't do huge amounts of data entry it can be easier to enter John Andrews in one field. Same deal for address. Left/Right functions producing query fields can break the data up. Although l imagine data entry for you does not amount to huge numbers.

    4) Probably more functional than personal, but you need an index of some type.

    5) Since the data entry for each person is extensive I imagine that sometimes it will not be completed at one sitting. If so you need a setup that takes you back to non completed records.

    6) I find data entry of this type (probably someone on the phone etc) is better if a "notes" screen can be used. This is done with a separate table so each note is a different record and with time/date. If such a note is made then a label on the main form is made visible so that everyone knows there is additional information. There is no end to what you can Like you I have several data types that require 250 character text fields or memo fields. I like to store that data in a related table so as I can use a category word or number. For example, I have lots of file notes made and each note is given a category from a drop down list. But this type of thing is only of use for extensive searches, correlation data etc.

  15. #15
    Join Date
    Jan 2008
    Posts
    12
    So.... There are lots for me to do. I have been using the database for my self cus i know its hard for other people to follow./ I just hope that I can impliment the changes that u suggest without causing problems. But Mike I hope to dont mind helping me out. it gives you something to do while ur winter draws near. if u can suggest any good books. I know how to build, query and report, but thats just basic stuff. Anyway Monday morning got to get off to work and use the database to help me fill in all them bloody forms. Have a good day

    moss

Posting Permissions

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