Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Post Unanswered: In a Rush! $80 for Newby Help!

    Please help, Iím fairly new to Access and while trying to build my first large database I have run into a few problems. I am running into problems updating the tables correctly and setting requirements. I have built the primary ďformĒ and built 2 tables for it, plus drop down lists for almost every field. Unfortunately the CDís I have donít cover much more than the basics. I am currently in Iraq and dont have much internet time, so if your the fist to answer the questions FULLY, I'll send $20 to a paypal account of your choice for each one you get.

    For the form I need to enter data into 2 tables.
    In my first table the columns are listed as Date, company, vehicle number, vehicle type, timefrom1, timeto1, timefrom2, timeto2, timefrom3, timeto3, and total hrs. My first table is used to track the vehicles use. My second table columns are listed as Date, job completed, ssn, A, B, C, D, and E. This table is used to track work completed.

    The form is difficult for me because instead of entering 1 record at a time, it needs to enter 1 vehicle record and numerous personnel records at the same time. The form below is similar to the one Iím working on, but a smaller version.

    Letís say the form needs to be layed out is like this:

    Date, Company, Vehicle type, vehicle number, job completed,

    Time From, Time To,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E (Each line should enter as 3 records)
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Time From, Time to,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Time From, Time to,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Total hours,
    And the enter data button.

    WHAT IíM TRYING TO DO:

    This form above would allow entries for multiple people in groups of 3ís. Meaning someoneís name could be used in the first 3 name lines, and the second 3 name lines, but not twice in the same set of 3. Also, each A,B,C,D (and E for each) should be an entry with the SSN to the left of it. Meaning that each row with the SSN should act as 3 records input. (I will need to access a list where I can later sort through records where it will list SSN, Date, Vehicle type, A, B, C, D, and E for each record entered.)

    Hereís where my biggest problems are, First, whenever I enter a SSN, The SSN pops up in all the SSN fields, instead of allowing me to make entries for different people. It also does the same for the A,B,C,D, and E fields. Regardless of what line I put information in, it pops up in all of them. I do know this is because all of them are bound to the same columns, and is a table problem, but donít know how to fix it.



    PROBLEM 1) Iím sure I can build a table with tons of columns, SSN1, A1, A2, A3, B1,B2, etc. then doing an append and delete, but Iíve heard that creating tables like this is never a good idea and may cause problems later down the road. If this is true, I am completely stumped on how else to go about this.

    Q1) Is there any way to tell the form that I need each of these rows, or field groups, to be separate entries? Or must I create the long list of columns and use some type of query/macro to sort it out?



    PROBLEM 2) I have only been able to get the vehicle type to update on one of the tables when entered, but cant seem to figure out how to get it to save on both tables when entered.

    Q2) How do I get the information for a record field to update on more than one table?



    PROBLEM 3) The fields A, B, C, D and E should only be able to receive an entry if the SSN to their left has an entry in it.

    Q3) How do I make a requirement like this possible?



    And my last problemÖ.
    I need to make sure that once an entry is in the system that there are no multiple entries. In other words if vehicle #12 has a form logged in for todays date, and one of the entries is from 09:00 to 10:30, it shouldnít allow another entry for vehicle #12 for todays date between 10:00 to 11:00 on the same form, or any form later input..

    Q4) How in the world could I accomplish this?

    I would appreciate any assistance offered, Thank you much.

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Your form needs to be an unbound form to get teh results you want. Are you familiar with working with recordsets?

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I have no PayPall so dont worry, just want to help....

    Q1) dont create a table A1,a2,a3 and so on this indeed will get you into trouble, Rather create a table in a 1-many relationship wher you only have 1 name field.

    Q2) Depending on your settup there are many ways. But i think you are looking to "enforce referential integrity".
    Go to the relationship window, add your tables and create the relationships you want(dragging the fields like you do when creating a query)
    In creating the relationships you can click a check box "Enforce referential integrity" Click it and Click "Cascade update related fields".
    This will allow you to change the maintable ID which will then "Copy down" into related tables. All
    You might also want to click "Cascade deleted"

    Q3) By using references and subforms you can fill the SSN automaticaly at the moment anyone enters any information

    Q4) Using another table for bookings this will allow you to use a query and some VBA to check the validity of the data entered.

    If your unable to figure it try posting the db (in zip file) and include some testing data. I am willing to have a go at this to create that which you so desire.

    Regards

    The Mailman

  4. #4
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    ~~~edited / double-post~~~
    Last edited by inzzane; 01-26-04 at 07:30.

  5. #5
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Thanks for the help, I'll definitely try what you suggested. I've attached a zip of the program that I have so far, to give you a better idea if I've miscommunicated anything.
    There might be some tables that dont make much sense, but are for future queries/forms. All which should be simple once I get this one working.

    The plan was to have UIC (a company ID) update the Vehicle models (Aircraft Type) which would then update the Serial numbers available in that unit.

    The SSN/NAME/PID are 3 different ways a name can be input. The user would only need to input 1 of the 3 on the form for identifying the personnel.

    The ammunition at the bottom will be totaled into a table.
    ( Date/Model/Serial #/762/20mm/30mm/40mm/rocket/hellfire ).
    This will be used for a query/report down the road.

    Yes, I will be looking to enforce referential integrity. After I igure out this form, my intent is to design a form for the users to maintain personnel information. It will also calculate the information input by this form fer each person. When the person leaves the company, the information for that persons time will also be deleted.


    Once again, Thanks
    Last edited by inzzane; 01-26-04 at 07:29.

  6. #6
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    here's the current file.
    The form in question is called ENTER DASH12.
    Attached Files Attached Files
    Last edited by inzzane; 01-26-04 at 08:39.

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Just a comment on the site of your DB.

    DONT use spaces or special characters in names of tables/forms/fields/query's and such except for those places where users will see it.....

    It WILL get you into trouble sooner or later if you do.

  8. #8
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Originally posted by namliam
    Just a comment on the site of your DB.

    DONT use spaces or special characters in names of tables/forms/fields/query's and such except for those places where users will see it.....

    It WILL get you into trouble sooner or later if you do.
    Thanx, I just read about the standardized names, ie..frm/tbl/etc.. and plan on changing them all to that format.

  9. #9
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Still abit confused on some of this, cant seem to get the data to save yet

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Time problem

    I am strapped for time at the moment. I have the file... but not the time to dive into into it at the moment.

    I will later this week (i hope), just thought i would let you know i am still alive and well....

    Greets

  11. #11
    Join Date
    Jan 2004
    Posts
    184

    Re: In a Rush! $80 for Newby Help!

    I just wanted to share with you a control featured on download.com that might help you I have been using that has made my life easier:

    http://download.com.com/3000-2404-10...ml?tag=lst-0-1

    After selecting the CursorType, CursorLocation, LockType and Disconnected status of your Recordset (there are defaults). I bind my controls and that is it All the complexities of ADO are hidden from the user which is you.

    Then I can Navigate, Add New, Delete, Refresh, Search and Save Changes to my recordsets without any other code. That is really it, no other code is needed.

    This works in any environment VB, Access, VC++ etc...

    Let me know what you think.

    Originally posted by inzzane
    Please help, Iím fairly new to Access and while trying to build my first large database I have run into a few problems. I am running into problems updating the tables correctly and setting requirements. I have built the primary ďformĒ and built 2 tables for it, plus drop down lists for almost every field. Unfortunately the CDís I have donít cover much more than the basics. I am currently in Iraq and dont have much internet time, so if your the fist to answer the questions FULLY, I'll send $20 to a paypal account of your choice for each one you get.

    For the form I need to enter data into 2 tables.
    In my first table the columns are listed as Date, company, vehicle number, vehicle type, timefrom1, timeto1, timefrom2, timeto2, timefrom3, timeto3, and total hrs. My first table is used to track the vehicles use. My second table columns are listed as Date, job completed, ssn, A, B, C, D, and E. This table is used to track work completed.

    The form is difficult for me because instead of entering 1 record at a time, it needs to enter 1 vehicle record and numerous personnel records at the same time. The form below is similar to the one Iím working on, but a smaller version.

    Letís say the form needs to be layed out is like this:

    Date, Company, Vehicle type, vehicle number, job completed,

    Time From, Time To,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E (Each line should enter as 3 records)
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Time From, Time to,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Time From, Time to,
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    SSN, A,B,C,D,A,B,C,D,A,B,C,D,E
    Total hours,
    And the enter data button.

    WHAT IíM TRYING TO DO:

    This form above would allow entries for multiple people in groups of 3ís. Meaning someoneís name could be used in the first 3 name lines, and the second 3 name lines, but not twice in the same set of 3. Also, each A,B,C,D (and E for each) should be an entry with the SSN to the left of it. Meaning that each row with the SSN should act as 3 records input. (I will need to access a list where I can later sort through records where it will list SSN, Date, Vehicle type, A, B, C, D, and E for each record entered.)

    Hereís where my biggest problems are, First, whenever I enter a SSN, The SSN pops up in all the SSN fields, instead of allowing me to make entries for different people. It also does the same for the A,B,C,D, and E fields. Regardless of what line I put information in, it pops up in all of them. I do know this is because all of them are bound to the same columns, and is a table problem, but donít know how to fix it.



    PROBLEM 1) Iím sure I can build a table with tons of columns, SSN1, A1, A2, A3, B1,B2, etc. then doing an append and delete, but Iíve heard that creating tables like this is never a good idea and may cause problems later down the road. If this is true, I am completely stumped on how else to go about this.

    Q1) Is there any way to tell the form that I need each of these rows, or field groups, to be separate entries? Or must I create the long list of columns and use some type of query/macro to sort it out?



    PROBLEM 2) I have only been able to get the vehicle type to update on one of the tables when entered, but cant seem to figure out how to get it to save on both tables when entered.

    Q2) How do I get the information for a record field to update on more than one table?



    PROBLEM 3) The fields A, B, C, D and E should only be able to receive an entry if the SSN to their left has an entry in it.

    Q3) How do I make a requirement like this possible?



    And my last problemÖ.
    I need to make sure that once an entry is in the system that there are no multiple entries. In other words if vehicle #12 has a form logged in for todays date, and one of the entries is from 09:00 to 10:30, it shouldnít allow another entry for vehicle #12 for todays date between 10:00 to 11:00 on the same form, or any form later input..

    Q4) How in the world could I accomplish this?

    I would appreciate any assistance offered, Thank you much.

Posting Permissions

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