Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    13

    Unanswered: project track / log outstanding items list

    Hi,

    For our large project I need to setup database where we would be able
    to track / log outstanding items between the Client and the Contractor.
    (I know there are templates but seems that those cannot accomplish
    what I need).

    Basic idea is this to have a list of outstanding items (maybe just one
    table & corresponding form) containing:
    Issue, date, description, status (open | closed) and responsibility
    ( Client | Contractor | Both ).

    Now, I need action log / history tracking both from the Client AND the
    Contractor.
    Based on the type of issue, there MAY or MAY NOT be any action from
    the Client or the Contractor regardless of the responsibility, meaning
    sometimes action is on Contractor but there is no resolution/reply
    yet. Meanwhile the Client may raise additional remark over the same
    issue, or vice-versa.
    So, some freedom is necessary here and seems that this is what causes
    trouble. Final report should contain not the whole history but just issue and
    last two actions from both sides.

    For that purpose I organized basically 3 tables (& corresponding
    forms) named say: ISSUES, CLIENT and CONTRACTOR

    Tables structures follows:
    ISSUES: Item_id, date, description, status (open | closed) and
    responsibility ( Client | Contractor | Both ).
    CLIENT & CONTRACTOR tables are basically the same, containing:
    Item_id, date and action_description.
    In addition each has it's own unique id.

    Seems that I cannot organize data in a correct way.
    I use autonumber for unique ids. Item_id is passed correctly to related
    Client and Contractor tables.
    But how to pass id from the Client or Contractor to the main ISSUES
    table when I use the form?

    What would be best, how to organize data?
    TIA
    I am using Access 2003.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Without debating the structure or your reasons, to pass the Item_id from the Client or Contractor table to the main ISSUES table using a form, I'd have to know more about how your form is setup (which is what I'm guessing you mean by "organizing" the data).

    What I often do on a form though (when I need to populate the Item_id with a value from elsewhere) is have that Item_id field on the form/subform and set the DefaultValue property =Forms!MyOtherForm!Item_id. When new records are added (usually a subform), the Item_id will automatically be populated with the Item_id defined in the DefaultValue.

    If I need to "filter" data on a form/subform based on an ID field, I'll use an unbound combobox (on my "main" form which returns the Item_id) and then have criteria in the "RecordSource" of the SUBform or my other form where Item_id = " & Forms!MyMainForm!Item_id & "". Then it's a simple me.mysubformname.requery (or Forms!MyOtherFormName.requery) command in the AfterUpdate event of the combobox to show the records based on the Item_id of the combobox. You could even set the DefaultValue of your subform or other form to the combobox Item_id for adding new records.

    I'll usually design a "shell" form so to speak which isn't bound to any recordsource and just has the combobox (or I'll setup a "search" type of form.) Then I have subforms with the criteria described above. There are different ways to design the form though if that's what you mean by "organizing" the data. Otherwise you'll need to get more specific on what you mean by organizing the data. Listboxes on forms are also great ways to "list" data on a form and also set criteria against versus a combobox (ie. someone clicks on an item in the listing and it requeries a subform which has criteria for the recordsource where Item_id = " & Forms!MyMainForm!MyListboxName & "".)
    Last edited by pkstormy; 09-19-09 at 23:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2009
    Posts
    13
    Many thanx for your reply.
    I'll try to describe my structure (where I need help).
    There are 3 tables (tbl_ISSUES, tbl_CLIENT and tbl_CONTRACTOR) controlled by forms frm_ISSUES, frm_CLIENT and frm_CONTRACTOR. The latter 2 (used for action log decription) both are subforms of the main one. Here the main issue Item_id (from tbl_ISSUES) is passed correctly to related Client and Contractor froms/tables.
    tbl_ISSUES has one-to-many relation with tbl_CLIENT. The same is with tbl_CONTRACTOR. (many actions taken over the same issue from all involved).

    Now I need to ensure relation between actions taken between the client and the contractor, which is I suppose one-to-one. This will return the correct report. (otherwise there is a mess with left/right joins which is no good either).
    * Trouble here is that there MAY or MAY NOT be any action from
    the Client or the Contractor regardless of the responsibility.
    So either one shall be able to add comment/action request with no restrictions. This is where one-to-one fails since there may not be action from the other side involved.

    Basically, I need here some Autofill in one-to-one related table/form to produce good results. Say client fills in frm_CLIENT, then frm_CONTRACTOR needs corresponding ID Autofilled (without even touching the form!) just to ensure one-to-one relation. The rest of that form will be blank until reply, if ever.

    That was my idea.
    Or there is another way to organize data to accomplish this task?

  4. #4
    Join Date
    Jun 2009
    Posts
    13
    I believe this is a good example:

    Consider we need database for tracking this thread including my posts and replies.
    First table (and corresponding form) would contain:
    thread_id, subject, thread_date, owner and status (open | closed).
    I believe, we need two more tables (and corresponding forms): one for my posts (thread_id, post_id, post_itself, post_date) and the other for the all replies (thread_id, reply_id, reply_itself, reply_date).
    NOTE: I may put additional necessary for the clarification OR someone else may reply after the last reply. This is exactly the case - I put this post before anybody replied to it! So one shall be able to add comment with no restrictions.


    That was my idea.
    How would you organize data to accomplish this task?

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by newdb
    Basically, I need here some Autofill in one-to-one related table/form to produce good results. Say client fills in frm_CLIENT, then frm_CONTRACTOR needs corresponding ID Autofilled (without even touching the form!) just to ensure one-to-one relation. The rest of that form will be blank until reply, if ever.
    To do this, I'd then design the Append query as mentioned below, with criteria where the Item_Id = Forms!SomeFormName!Item_ID and use that in the append query to add that Item_ID (and Info) to the Contractor table (or Client table), or even the ISSUES table. Then it's a matter of running that query when appropriate and you want the data for a specific Item_ID "copied" from one table to another table.

    On which event you'd want to run the append query is up to you but it could be triggered by anything you want it to be triggered on. (but buttons to do it often work best.)

    In regards as to how to then copy this data from the separate tables to the ISSUES table (or Contractor or Client Table), I'd design a simple append querys to append from the specific Client or Contractor table to the ISSUES table (or vice-versa) with criteria in those queries where the Item_id = Forms!MyDataForm!Item_id (and Item_id will append to Item_id in the ISSUES table in the Append query and you can use an expression "Client" or "Contractor" to populate the Responsibility field depending on the query (or expressions to 'set' other valued during the append process.) Then on whatever event triggers the copying of the data to the ISSUES table (or from the ISSUES table to a Client/Contractor table), your vba code is thus...

    docmd.setwarnings false
    docmd.openquery "MyAppendToISSUESTableQueryNameForContractor"
    docmd.setwarnings true

    or
    docmd.setwarnings false
    docmd.openquery "MyAppendToISSUESTableQueryNameForClient"
    docmd.setwarnings true
    or
    docmd.setwarnings false
    docmd.openquery "MyAppendToClientTableQueryNameForISSUES"
    docmd.setwarnings true

    and you've thus copied the data from one table to another table based on the Item_id showing on the form.
    Last edited by pkstormy; 09-20-09 at 08:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2009
    Posts
    13
    Thankx very much Pkstormy!
    I have one your reply which is not included here? Strange...
    OK, I tried with macro 'SetValue' for the same purpose (before I read your reply). I guess the results are the same? I know it is complex, but briefly please - What would be vba code advantages over macros?
    ------
    Back to my design problem:
    I used threads as an easy to understand example but I DO need 3 separate tables and corresponding forms.
    Tables tbl_posts and tbl_replies (in a real project example are not exactly the same) have autonumber PK and (I believe) should be 1-to-1 related (although the other is usually empty until reply, if there is one ever). If not 1-1 there is a mess with report ie left/right joins if both joined by thread_id. This doesn't work since either party may start post or reply and then the other's autonumber cannot satisfy 1-1 relation.
    So, I was considering to have intermediate ( just a "dummy" ) table
    which will be in 1-to-many relation with tbl_threads. (one thread may
    have many posts and replies). Intermediate table will also have 1-1
    relation with posts and replies ids in corresponding tables. (now autonumber works).
    But still I can't manage to have good results when using forms &
    subforms.
    I use "setValue" to pass ids across the forms (by using 'on click' function in form text box - one wants to add post/reply and needs to click in!) but I cannot force intermediate table to generate autonumber (upon passing ids there - table is still not populated and autonumber PK is not generated).

    In short: is there a way to 'triger' autonumber function based on some other data entry (eg. with data returned by docmd.openquery)?

    What am I doing wrong? Please help.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Anything you can do with Macros, you can do in vba code. The advantages of vba code is that you really get into the "heart" of development work and open up a wider coding set to utilize more powerful features. I couldn't say for sure if there is a distinct speed difference but the coding you can do in vba (such as open, walk-through and manipulate recordsets, more coding options, and a "free-er" type of environment where you have better control over what you want to do and when you want to do it (- search for posts on pros/cons of macros versus vba for more details on other advantages.) Macros are limiting and tend to "mask" and make it difficult to troubleshoot what's really going on for a developer's eyes.

    If you want to perform some type of coding in vba, you utilize the EVENTS tab and it's properties to "trigger" what coding you want to perform based on the event property selected (ie. OnClick, OnDblClick, OnGotFocus, etc..etc...) Get used to looking at the "Properties" box as this is your "control center" for manipulating events of objects on the form. Thus, this allows you to take control so to speak of any type of actions on the form (ie. you can even execute coding in a form's EVENTS such as the OnCurrent Event.) A little trick I do is put in vba coding on an event which has a msgbox "OnClick event of XXX Field fired here." to show when that event has fired on the form as I'm developing/testing.

    To get into the vba coding of an event on the form, (viewing the properties), you click on the object or select it in the dropdown (it can even be the detail section of the background or for the Form itself), then you select [Event Procedure] in the dropdown next to whatever event you want to use. Then click the 3 dots ... next to the dropdown box. This will put you into the vba coding screen (Note: it's always been confusing to me that you have to click the UPPER Right X to close the vba coding form and get back to the form design but get used to it.)

    You may want to start seeing what vba coding looks like by using the wizard to create a button (ie. select anything in the wizard such as "Add New Record", etc...) and then click the 3 dots ... to get into the vba coding to see what the code looks like. You can also learn by looking at other coding examples posted, look at the Northwind's MSAccess database that ships with MSAccess, or get a great book such as "MSAccess Developer's Handbook" by Sybex (they have some great examples on the included cd.) The MSAccess code bank has several examples of some neat little tricks you can utilize.
    Last edited by pkstormy; 09-20-09 at 19:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing I like to do when designing vba code to open and walk through a table's recordset is to first design my SQL "select" statement using the query designer (since it's very easy to use the query wizard to design a query such as a "Find Duplicates" query or other query type.) Or I can easily setup the criteria and other expressions using the query designer. Then I select "SQL" in the upper left corner (ie. View Type) and view the "SQL" statement which I will then copy and paste into my vba coding for any recordset type coding where I'm opening a recordset and manipulating it (and then edit a bit of the syntax such as removing the ")

    Or I may just simply use the query itself in my vba code (such as the examples I gave in the previous posts) and avoid writing a few lines of vba coding to open/manipulate the recordset.

    If you're spending hours troubleshooting a SQL Select statement in vba code (trying to get the syntax correct), you're spending too much time on it and should just design it using the query designer and then copy/paste and edit a bit of the syntax or simply use the query itself. (I've seen too many developer's spend days on just getting a sql select statement correct when that time could have been better spent on form design.)
    Last edited by pkstormy; 09-20-09 at 19:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't think I'd add another table to your schematic. This would "complicate" some of your SQL statements/form design and I don't believe there'd be any great benefits over doing so for your scenario.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    "In short: is there a way to 'triger' autonumber function based on some other data entry (eg. with data returned by docmd.openquery)?"

    You basically "trigger" the creation of an autonumber field on a form (when adding a new record) by using the "Refresh" command in vba code. Typically though a form will usually "refresh" itself after the indexed fields are entered (or required fields.) If for example, you try to refresh a form and a required field is not entered, you'd get an error that the record could not be added due to a required field. You can use this to your advantage to make sure your data is getting added/updated correctly.

    Remember that you DON'T want to use the setvalue against an autonumber field! You let MSAccess do it's thing for creating/updating autonumber fields and you shouldn't be writing any code to do any writing/updating of the autonumber value!! (other than to refresh the form to "generate" the autonumber.)

    If your Item_id is the autonumber in all 3 tables, you'll probably need to change this and make the Item_id an integer value in the designated relational tables. Otherwise you'll have problems again, trying to "update" or "write" to an autonumber field in the relational table. Keep your Item_id fields the same name in each of the tables though as you want to "see" this relationship (I sometimes see a matching fieldname in the relational table called something other than the autonumber fieldname in the main table - this just makes it confusing in your sql select statements.)
    Last edited by pkstormy; 09-20-09 at 18:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Since there are a multitude of different ways to design a form, I can't help you on your form design unless you upload what you've decided it should look like and how it should work.

    Form design layout is key as this is how the user is going to interact with the data! You must "predict" as many possibilities a user might do on a form and be prepared to handle them in code (for example, the user closes a form but doesn't enter the required data fields to add a record.) Form's should be easy to use (and remember that you're designing a form where you are working on it for days/weeks/months where a user perhaps only sees it for an hour or two a day.)

    You should usually plan out how you want the form design to look/function with the users themselves. Almost anything is possible in regards to how user's want a form to look/function. You can even do some neat tricks with how a form looks such as in the example called "Form Manipulation" in the code bank. The things to remember for form/vba coding is that you want it to flow as smooth and simply as possible without over-complicating it or the code behind it (ie. having 50 buttons on a form makes a user search through 50 buttons to find the one they want, having dozens of functions behind the scenes tends to overcomplicate the coding for development, etc...etc...) If you get into enterprise type development where you're designing applications which handle millions of records, things such as how often and when tables are accessed on the form become important along with the user's speed of their connection to the interface (for example, a combobox field with a rowsource has to access/"hit" the table once when the form loads, again for a 2nd combobox/listbox, etc..etc...) Then designing "unbound" forms comes importantly into play. You may want to try designing an "unbound" form on a simple project if you want to get into this level of development. It does take more time developing unbound forms (I'd estimate about 3 times as long) but they are more efficient. Unbound forms are great for multiple users with slow connections to the data source and especially on large datasets.

    Form/field coloring, fonts, bolding, ALIGNMENT, etc...are important! I personally try to keep my "unbound" search type fields on my forms in a yellow or blue background color (to indicate to the user that it's not a "data" field which is updating data in the table.) You also want to pay attention to tab order of the form and the "locked" and "enabled" properties of certain fields you don't want the user to be able to edit (such as the autonumber field.) I spend a good portion of my development time making the form look and work in a simple, logical, easy-flowing way (and little things like field mis-alignment or buttons scattered throughout the form bug me.)

    Again, I may have a "shell" form so to speak which is not bound to any recordset (ie. no recordsource) and then have a subform which is bound to my data table and has criteria where the Item_id = the Item_id in the combobox/listbox. (Note: I also like to set my form's properties to Popup = True and Modal = True.) Note also that you can search the help by pressing F1 after you click in the dropdown of an event to see what that event does. Anyway, then I put an unbound combobox or listbox on this form which has a rowsource based upon my "data" table returning the Item_id value to the combobox/listbox (or I may "set" this value somewhere in code when a user for example, searches by a different combobox or form.) I'll then requery a subform or manipulate the recordsource of that subform when a value is selected in the unbound combobox/listbox or one is "pushed" to it. This is just one of many, many different ways. I may decide that I want a separate "search" form which then opens the "data" form not utilizing any subforms at all (there are a few examples of "search" type forms and other data type form designs in the code bank.) The Northwind database also has some neat/functional looking form designs (just avoid the Switchboard!! - worst wizard MSAccess has and is a poor technique! - ie. your Main Menu form should never be "bound" to a dataset such as a switchboard table.)

    You again though, want to work WITH the user on the form design layout and how it functions.

    As a last note (regarding tables), if possible, you also want to enforce referential integrity by setting up joins between your tables in the Relational design form for your "joining" fields (ie. Item_id). You add your tables in the "Relational" diagram and then join the fields together between the tables. You then click on the join's properties and then tell it what kind of join (ie. 1 to 1 or 1 to many), whether you want to cascade update or delete (delete is the key one as sometimes you may not want to allow deleting a record in the main table unless the user specifically deletes the relational records.) Your situation may or may not be suitable for creating referential integrity (I'd again, have to look at it more closely.) But setting up the actual joins in the Relational diagram will give you the benefits of MSAccess maintaining the integrity between the tables and avoiding "orphaned" records which are usually the culprit for bad totals. It will also prevent bad data entry records such as records entered you don't want to allow in a relational table unless there's a corresponding record in the "main" data table. You can also join on multiple fields between 2 tables but I usually frown on multiple field joins as there's often a better way (double-field joins are extremely slow for retrieving data on large recordsets.)

    Although there's "guidelines" on how to setup tables and joins (ie. Rules of Normalization), you can't always take these "guidelines" as the rule of thumb to always go by. Some situations may dictate that you steer away from the norm due to specific reasons (I've seen several situations where the normalization rules needed to be broken.) Typically though you want to follow these guidelines with basic designs and you don't want to complicate things by following the normalization rules to the nth degree (ie. for example, you wouldn't want to break apart the City, State, and Zipcode into separate tables versus 1 table with person's name and other data - the sql statement would be ridiculous for returning a simple full name and complete address along with complicating all the data entry forms, reports, etc....)

    Again, try to not over-complicate your design by having too many tables involved in the relationships for creating relational records. It makes for a hassle updating/deleting records on the form design (and causes some nasty looking sql select statements.) I personally would recommend avoid adding the "intermediate/dummy" table you proposed in one of your posts. Although it may "seem" like it works with your form design, it's really just a work-around and you've now complicated all your sql select statements. There's a way to do what you want to do with vba code or simply setting up your form correctly. You definitely DON'T want to start complicating your table structure to accomodate your form design!! I've seen that happen way to often.

    You need to get your design on how the form looks/functions setup though. - Hope this helps.
    Last edited by pkstormy; 09-20-09 at 19:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jan 2010
    Posts
    2
    Thanks all for sharing information
    ---------
    quang cao online | quang cao

Posting Permissions

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