Results 1 to 6 of 6
  1. #1
    webfactoryuk is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    25

    How to create form that posts to new table each time

    Hi,

    Im new to Access so apologise if this is a noobie question and Im just looking for a pointer in the right direction anyway.

    I normally make websites but Im currently build a warehouse inspection reporting system in Access.

    Got my Forms all setup, posting to tables and spitting out a nice report.



    What I need it to do is wipe the tables clean each time for the new inspection bit keep the old data saved somewhere.

    Basically the inspectors will obviously visit more than once client and as it stands at the moment, printing the report will print all they're reports off as one.

    I was thinking they could just have a blank copy, copy it and rename the copy and use that each time but is there a way to automate this?

    Thanks in advance.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would not wipe the tables clean. You would keep old and new data together in the table and just extract what you need using a query with the appropriate criteria.

    You can have also have a form where the user selects or inputs the criteria they need and use that to filter the record source of the report.

  3. #3
    webfactoryuk is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    25
    Sounds like a much more versatile version of what I have!

    Trouble is it is also a bit above me.

    To do what you say you would need to have a field with a unique ID for each report/session where data was being entered wouldnt you?

    And thats how you would call just that info for the report?

    I can work it out as far as creating a Clients table and assigning IDs there.

    But how do I make it so you dont have to select the client ID everytime you enter a new record for the report?

    Thanks for your reply!

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes, you would need a unique ID for each record. This is called a primary key field in a relational database. Typically, I use a field with an autonumber datatype for the primary key field so I don't have to worry about assigning a unique number. In fact, your users should never see the unique number. That unique number is also what joins one record to its related records in another table (primary key ---> foreign key relationship).


    But how do I make it so you dont have to select the client ID everytime you enter a new record for the report?
    You would use a combo box that would include the unique clientID field and the client name. The user would only see the client name in the combo box, but the clientID will be used to filter the report.

    I believe that your application is more complex than what I have described above, but it would follow the same basic principles.

    I don't know all of the details of what you are doing with your application (perhaps you can provide more details), but from what I understand, you have client and each client can have many inspections. Since a client can have many inspections, you have a one-to-many relationship which requires at least 2 tables

    tblClient
    -pkClientID primary key, autonumber
    -txtClientName
    other fields related to the client

    tblClientInspections
    -pkClientInspectID primary key, autonumber
    -fkClientID foreign key to tblClient, long number datatype
    -dteInspect (inspection date)

    I assume that you inspect items of some type, so more tables will probably be needed...

  5. #5
    webfactoryuk is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    25
    I think I just about understand Primary keys or atleast what they are used for.

    Basically the report is for a warehouse inspection or more specifically the racking and storage.

    Ideally without knowing how any of this would work the system would be like this.

    Create Client Screen (only needed if client isnt already in database)

    New Report Screen where you select the client and enter date of the inspection, name of consultant etc.

    Then you enter details about warehouse components, storage components etc.

    End result is a report.

    As it stands I have it so you can create the report but you have to start with a blank database so there is no way of looking at the past reports (unless you look at the PDFs generated from when the inspection last took place and the system was used before).

    Your method obviously allows for a much more advanced system! I can almost see how it should work.....



    Edit - forgot to add, Ideally the inspector should only have to select the client once each report. There could be thousands of components (inspections can take days) and having to select it everytime would be a pain I imagine!

    One advantage to my current basic system lol.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As it stands I have it so you can create the report but you have to start with a blank database so there is no way of looking at the past reports (unless you look at the PDFs generated from when the inspection last took place and the system was used before).
    You have to start somewhere, so at first the tables will have no data. Of course, you can always take your PDFs and go back and enter historical data.

    I would recommend not working on forms and reports until your table structure is properly set up. The table structure is like the foundation for a house, without a solid foundation, things will fall apart.

    You may want to check out this site for an overview of normalization.

    And this site for some tutorials that you might find helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-20-2010, 01:27 AM
  2. Automatically delete posts
    By carstenhdk in forum Access
    Replies: 4
    Last Post: 06-09-2010, 02:41 AM
  3. How To Create A 'One Time Use' Form
    By dr_destructo in forum Forms
    Replies: 6
    Last Post: 04-08-2010, 08:02 AM
  4. Create PDF for each record in table/form
    By ChrisCMU in forum Forms
    Replies: 15
    Last Post: 07-28-2009, 01:52 PM
  5. Replies: 3
    Last Post: 06-01-2009, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums