Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Question Unanswered: Form - How to recreate look from Excel?

    I have a very large excel spreadsheet that several users use. They separate events (Event#) by creating a row (and coloring it gray). See link to picture of spreadsheet.

    Pictures by asumpter - Photobucket

    They are wanting to have this and the previous years converted to a database since several people access this at the same time. Problem is, they want to keep the look and feel of how it is now.


    ***How can I create a form in Access that recreates this look, without the gray line, yet gives the space between the "grouping's"?***

    They want to see everything, but not have it all mashed together they way it would be in a regular dataform.

    - Multiple Items Form (that comes the closest to what I like, but I can't find a way to separate the groups).
    - Pivot Table is just too messy and complicated for the users that will end up using this.


    Any ideas? I've been bashing my head in trying to come up with a solution that works.

    Thanks!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can try using forms in datasheet mode and conditional formating. You can also keep Excel as the interface and use Access as a data store (personally I would prefer SQL Server as a data store), and perhaps build a 3-tier model: [SQL Server] --> [Access] --> [Excel] or [Access] --> [Access] --> [Excel].
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    3
    Would there be anyway to shade the Odd Event # (Row B) and Not the Even Event#?

    How would one go about doing that? Sounds like what you mentioned as far as "conditional formatting" but I have no experience doing that. Can you point me in the right direction as far as figuring out how to do that?

    Thanks!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Open a form in design mode then right-click on a text box control and select Conditional Formatting in the contextual menu. From there the interface is very similar to the one you find in Excel.
    Have a nice day!

  5. #5
    Join Date
    Nov 2010
    Posts
    3
    I was able to figure out that part. What I am unable to figure out though, is how to create one based on whether or not a number in a ____ is an odd or even number and to shade that entire row a different color based on that.

    1. New Rule for Event #.
    (Check values in the current record or use an expression) I'm guessing to use that one, not the compare to other records? unless it's compare against other records in the same column.)

    Now the part that I'm stuck on is where to go from there?

    It fives an option to Format only cells... but I've been digging in every bit of it, and I can't find anything that mentions odd/even, and if I did, then how do I go about telling it to change the shade of that entire line to a darker gray?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For changing the format when the contents of [Field1] is even:
    Code:
    Condition: "Expression is" [Field1] Mod 2 = 0
    And of course for an odd value:
    Code:
    Condition: "Expression is" [Field1] Mod 2 = 1
    Have a nice day!

Posting Permissions

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