Conceptual Help - Patient Tracker DB

[DocWomet]

Ladies/Gents,

Trying to build an exercise patient tracking database for my small hospital. Attached JPEG of what my boss is wanting from last exercise (built with painstaking manual effort, even with Excel formulas) and my task now is to automate the process (i.e. why I turned to Access).

In short he's wanting a color-coded depiction of where patients are by injury severity - think of a little flipbook with pictures so when you flip it you get a little animation. Each time snapshot shows where patients are (so you want the "red" patients to move faster than the yellow/green ones).

I've got my data input and queries to help the front end (attachment 2) but am conceptually stuck at the next step for data presentation...

I'm stuck between trying a) Crosstab subform with time parameter that updates with a button click or time interval, b) Exporting the data into Excel and forcing the user to use formulas from there, c) Creating textboxes with DLOOKUPs and conditional formatting (lots of manual effort but I think would look prettiest and avoids Excel transfer)... d) ???

Apologies in advance if this is out of left field or asinine... I really tried to fight the "colored blocks" approach and have some latitude to change the presentation but ultimately have to go with a "flipbook" presentation.

As you can probably guess I'm not a VBA/SQL wizard but am open to try/learn about anything if anyone has an idea bulb go off...

Thanks very much for your consideration.

 

[The_Doc_Man]

This can be done but will take some gyrations. My advice is to listen to Julius Caesar on this one: Divide and conquer.

Break the problem up into pieces parts... data entry, data "movement" (i.e. changes of status), data presentation on-screen, data presentation as a report. I don't presume to know what you want for most of the parts, but I can offer thoughts on the presentation in spreadsheet format.

You can open an application object fo Excel and manipulate it with VBA pretty easily.

If oX is an object and sBNam is the string naming the file, ...

oX.Workbooks.Open FileName:=sBNam, ReadOnly:=False, Editable:=True

Creates a workbook. If lBK is an integer,

oX.Workbooks(lBk).Activate

selects workbook #lBk to activate it. (You can have more than one workbook open at once in Excel so you have to pick one.)

If lNumSh is an integer (because each workbook can have more than one sheet...)

Call oX.Workbooks(lBk).Worksheets.Add(, oX.Workbooks(lBk).Worksheets(lNumSh), 1, xlWorksheet)

Adds a worksheet. However, note also that you might skip that phase since you get three worksheets "for free" when you open a new workbook.

When you are done, where sNewName allows a rename (Save As)...

oX.Workbooks(1).Close FileName:=sNewName, savechanges:=True

To focus on a sheet while the workbook is open,

    oXL.Workbooks(lBk).Worksheets(lSh).Activate
    Set Get_XLS_Sheet = oXL.Workbooks(lBk).ActiveSheet

Makes Get_XLS_Sheet (which is of type Excel.Worksheet) the currently active sheet by choosing sheet lSh for book lBk. You do that for short-cut purposes. You could also pick shorter names, but I get verbose sometimes.

Public Function Get_XLS_Cell(xWrksht As Excel.Worksheet, lRow As Long, lCol As Long) As String

'   in the named worksheet, return the value (a text object) or return a blank.

Dim xVal As String

    xVal = Nz(xWrksht.Cells(lRow, lCol).Value, "")
    Get_XLS_Cell = Trim$(xVal)

End Function

This gets a value from a specified row and column of the worksheet. You can do the reverse, too, by putting something into the value of a given cell.

Now, the payoff... to put string sGrfClosed into a cell, coordinates lXLRow and lXLCol (both integers) and paint it light green...

                    shtXL.Cells(lXLRow, lXLCol).Value = sGrfClosed
                    shtXL.Cells(lXLRow, lXLCol).Interior.ColorIndex = 35

Now, to fill in the blanks: You have to open Excel Help and look into programming issues to find the table of color indexes that are available. In essence, you can select one of 64 pre-defined colors for the color index. There is a way to customize, but if you need more than 64 colors, you've already gone overboard. You can also look up Excel help on programming issues for other things you can do such as deleting specific worksheets (to minimize space in the final presentation sheet), adding title rows, changing font sizes, etc. Remember, even though you are using Access to do this, you need to look at Excel help for the programming tricks, methods, properties, etc.

 

[The_Doc_Man]

My bad - I forgot one extra step that, if you didn't know about it, would result in sudden hair loss via a "ripping it out" method...

In the code window, before you use any of the Excel stuff I mentioned, you have to use the Tools >> References path to get to the list of libraries. Find the Excel Object Library and check that box. THEN you can use the Excel objects I mentioned.