adding/updating numbers, defualt value of Zero

[rpeare]

Ok well you didn't really do anything than add some fields and name them properly, none of the code seemed to have changed but if you're going to want to pick a specific matrix from a list you have to do that.

Here's an example, I didn't test everything but it should work the way you want.

Update Example.zip

As a side note, I imagine you are not always going to want the same set of years to display, like when you roll over to FY 2014 so you may want to consider some modifications including what years you want to show/edit in your matrix, but if you do that you're going to have to modify the code for saving the matrix as well, because right now it deletes the current matrix and resets it with the information that's currently showing on the screen. and you'd likely only want to update records rather than deleting and appending new ones.

[mike02]

I hvaent ever worked with append queries, and a delete query. how do they work in this case? Also, How does it work to add new returns? so if tblMatrix doesnt already have the company ID in it, then how does that work? thanks for the advice/help

[rpeare]

look at the code.

There are delete and append sql statements in there, they work just like select queries but instead of just showing data they alter it.

[mike02]

ok perfect, thanks for the help! on elast quick question, why does the F headings change order? meaning, when i click Get Returns, it switches from decending...
2013
2012
2011...etc.

to

2004
2005
2006... etc

how can I set it up to always leave it in the same order? Im guessing this is where it gets it in order:

sSQL = "SELECT F FROM Tbl_Matrix WHERE FundID = " & MySelector & " GROUP BY F ORDER BY F;"
Set rst = db.OpenRecordset(sSQL)

How would I switch it to be in the original order?
Also, I change the X values from default of zero to no value. How can I make it that it will not update the non filled textboxes? because there is no data for these. I dont want them to be saved.


Thanks!

[rpeare]

sSQL = "SELECT F FROM Tbl_Matrix WHERE FundID = " & MySelector & " GROUP BY F ORDER BY F DESC;"

DESC in the order by clause sorts in descending order.

As far as the default value stuff is concerned. You set that up in your database not me I don't believe, you'd just have to highlight the fields in design view and remove the default value. Just be aware that if you have a column heading and a row heading a record will be created, even if it's null. The only way my original code would not create a record was if there was no column/row heading (T/F values) on the matrix.

[mike02]

Ok, the descending worked For the F Values,

With this, the years then dont match the returns. Im guessing the reason for have your F values flip was because the X data fliped as well?
Is there a way to have the X values flip as well?

DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDeleteMatrixTemp")
DoCmd.OpenQuery ("qryAppendMatrixTemp")
DoCmd.SetWarnings True
Set rst = db.OpenRecordset("MatrixTemp")
rst.MoveFirst


iFCount = 1
iTCount = 0
Do While iFCount <= iMaxFCt
    For Each ctl In Me.Controls
        Debug.Print "INSTR F" & iFCount & "T = " & InStr(ctl.Name, "F" & iFCount & "T")
        If InStr(ctl.Name, "F" & iFCount & "T") > 0 Then
            iTCount = iTCount + 1
            If iTCount <= iMaxTCt Then
                'Debug.Print iTCount & iMaxTCt
                'Debug.Print rst.Fields(iTCount)
                'Debug.Print ctl.Name
                ctl.Value = rst.Fields(iTCount)
            End If
        End If
    Next ctl
    iFCount = iFCount + 1
    iTCount = 0
    rst.MoveNext
Loop

[rpeare]

Look at the table matrixtemp, it's reading that table without doing any modification.

If you want it to have a certain order set your recordset to a query that puts it in the order you want. Replace the line below with a SQL statement that orders your data.

Set rst = db.OpenRecordset("MatrixTemp")

[mike02]

awesome, thanks for all the help! this will come in handy with future projects as well!