Results 1 to 6 of 6
  1. #1
    fragboy
    Guest

    Miscellaneous questions from Access newbie (Access 2000)

    Hi all,

    I'm new to Access and I'm trying to work out whether it's better for my needs than Excel. I've got a few questions, but first some background.

    I've been running a Excel spreadsheet with each line representing an item of work in our team. Each line includes the details of up to six deliverables that are the outcomes of that work item. The spreadsheet is around 100 lines and I sort and filter it in various ways so that we can better understand the total set of work that we are currently or planning to do. If the focus was on resourcing and tracking effort then I would use Project but it's more on collecting and communicating information about the works items so that all the team leaders know what the other teams are doing. I've set up tables in Access for the data -- one table for the works items and one for the deliverables. These tables have a 1 to Many relationship. I've set up a form (with a subform) to enter the data.

    With that background out of the way, there are various things that I have not been able to do (as yet). Any advice would be very much appreciated.

    1. I would like to ensure that there is at least one deliverable row for each works item row. Can I enforce this?
    2. There are specific fields for which data is mandatory. How can I validate for "not empty". (See, I told you I was a newbie. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    3. One of the works items fields is "Date Entered". Can I fill this in automatically as the row is created? And can I then allow the user to change it from this default value?
    4. Another field is "Date Last Modified". Can I ensure that this is always updated whenever the row (or related rows in the deliverables table) are modified? (BTW this field is required so that I can do a report on what has changes have been made in the last week.)
    5. At this stage, I don't want to allow other people to enter data but I do want to be able to provide a flexible reporting mechanism for them. What I was thinking of is a "switchboard" of avaiable reports (like I see in some of the sample apps) but for the life of me I can't work out how to create a switchboard.

    I think that will do for now. I'm sure to have more questions later.

    I'm having few days off <img src=/S/joy.gif border=0 alt=joy width=23 height=23> so please don't be offended if I don't respond to your replies quickly. Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Miscellaneous questions from Access newbie (Access 2000)

    You can do all those things and more in Access, but you need to work your way through the documentation and get a good book on using Access to teach you the basics. They're certainly beyond the scope of the Lounge to teach, so look for either one of the "Using" books or pick up the Step-by-step book from Microsoft Press. Those will teach you the nuts and bolts of building in Access, but make *sure* you read the chapters on normalization because those are the most important concepts in building relational databases. If you don't learn about normalization, you might as well stick with Excel.

    Access organizes data in tables, which are *not* exactly the same as worksheets, no matter how much they look like a worksheet. They have an internal coherence that worksheets lack. Each row is a record, but the equivalent of a row in a worksheet may actually be broken up into records in several tables.

    In Access, you don't normally display the table itself to a user. Instead, you use forms, which are not the same as Excel forms, although they share some common features. From a form, you can control all the things that happen to a record (like inserting the last updated date) that cannot be controlled from the table itself.

    There is a built-in switchboard manager in Access under Tools-->Database Utilities, and it will help you build a switchboard. Then just use the manager to modify it by adding new menus and items. Take a look at the sample databases that came with Access to see some of the things you can do with a switchboard in Access.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Miscellaneous questions from Access newbie (Access 2000)

    1. I would like to ensure that there is at least one deliverable row for each works item row. Can I enforce this?

    The only way is to restrict data entry to a form, in which you will have to have code to ensure it. The database itself won't enforce this.

    ----------------------------------
    2. There are specific fields for which data is mandatory. How can I validate for "not empty". (See, I told you I was a newbie. )

    In Table design, set the Required property to Yes for each such field.

    ---------------------------------------
    3. One of the works items fields is "Date Entered". Can I fill this in automatically as the row is created? And can I then allow the user to change it from this default value?

    Again, in table design, set the Default Value for this field to: =Date
    User can change it.

    --------------------------------------------------------------------------
    4. Another field is "Date Last Modified". Can I ensure that this is always updated whenever the row (or related rows in the deliverables table) are modified? (BTW this field is required so that I can do a report on what has changes have been made in the last week.)

    This can only be done by you when using a form. A simple line of code in your form's BeforeUpdate event does it:
    Me![Date Last Modifie] = Date( )
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    fragboy
    Guest

    Re: Miscellaneous questions from Access newbie (Access 2000)

    Thanks Charlotte and Mark.

    Someone's offered to lend me an Access book, so that should help. In the meantime, you've given me enough to keep going.

    Thanks again,

  5. #5
    fragboy
    Guest

    Re: Miscellaneous questions from Access newbie (Access 2000)

    Mark,

    Thanks again for your earlier help. I've just about got everything working, except the code relating to ensuring that there is at least one "work product " row created for each "work item" record. The code (on the works item form, for which the work products is a subform) is currently:

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me![Date Last Modifed] = Date

    If Me![Work Products]![Work product type].Text = "" Then
    MsgBox ("You have not entered data in the subform! Try again...")
    Cancel = True
    Else
    Cancel = False
    End If

    End Sub

    This stops the item record getting updated if there is no record in the work product subform but, after the I modify the item record in any way, it won't let me move from the main area of the form to the subform. The message keeps popping up.

    Is one way around this to add some code that moves the cursor to the subform?

    I'm probably barking up the wrong tree here. Any advice would be appreciated.

    Thanks,

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Miscellaneous questions from Access newbie (Access 2000)

    Assuming this is a bound form (that is, there is a recordsource), when you move off the mainform (like to a subform), Access will try to save the current mainform record. Even if it didn't, you still would probably have a problem if you tried to write a subform record without a mainform record; Access probably won't like that either, if you have enforced referential integrity.

    One thing you could do is to automatically write a subform record, this could be done via code in the mainform's AfterInsert event. Or, you could put some code in the subform control's OnExit event (this is the control itself that sits on the main form, and not the actual subform), which perhaps warns the user if there are no records in the subform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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