VBA query giving weird results...

[Seb]

Hi all

I was wondering if you could guide me in the right direction....else, I might tear my hair out!

ok, I have a form, with a subform. Based on 2 fields on the subform, I refresh the results in the subform.
These fields are:
CBOclientYTD:

SELECT tblClients.ID, tblClients.Client FROM tblClients;

CBOfiscal:

SELECT (Year(DateAdd("m",6,tblMargin.Week))) AS Fiscal
FROM tblMargin
GROUP BY (Year(DateAdd("m",6,tblMargin.Week)));

on update event (for both fields) I run the following VBA:

Dim strSQL As String

    Client = Me.cboClientYTD
    Fiscal = Me.cboFiscal
           
    If IsNull(Fiscal) And IsNull(Client) Then
        where = ""

    Else
    If Not IsNull(Client) And IsNull(Fiscal) Then
        where = " WHERE tblMargin.client = " & Client
       Else
    If IsNull(Client) And Not IsNull(Fiscal) Then
        where = " WHERE Year(DateAdd('m',6,tblMargin.Week)) = " & Fiscal
    Else
    If Not IsNull(Client) And Not IsNull(Fiscal) Then
        where = " WHERE Year(DateAdd('m',6,tblMargin.Week)) = " & Fiscal & " AND tblMargin.client = " & Client
    End If
    End If
    End If
    End If
    strSQL = "SELECT Year(DateAdd('m',6,tblMargin.Week)) AS Fiscal, tblClients.Client, sum(tblMargin.Hours) AS SumOfHours, Sum(tblMargin.Income) As SumOfIncome, Sum(tblMargin.wAGES) AS SumOfwAGES, Sum(tblMargin.Super) AS SumOfSuper"
    strSQL = strSQL & " FROM tblClients LEFT JOIN tblMargin ON tblClients.ID = tblMargin.Client"
    strSQL = strSQL & where
    strSQL = strSQL & " GROUP BY Year(DateAdd('m',6,tblMargin.Week)), tblClients.Client"
    strSQL = strSQL & " ORDER BY Year(DateAdd('m',6,tblMargin.Week)), tblClients.Client"
    'MsgBox Fiscal
    'MsgBox Client
    
Form_qryMarginYTD.RecordSource = strSQL

the problem is, when I select a client name from the drop down or a fiscal year from the drop down, or both, the first record in the table is updated to the value in the cboClientYTD field.

so, if record 1 client = 20, and I filter the records to show only those for client 30, it will display all the correct records,but in the background it updates record 1 client to 30....

I cant figure it out

 

[pbaldy]

It sounds like those combos should not have a control source (they should be unbound).

 

[Seb]

Thanks Paul.
the controls (combo boxes) are not bound. they do have record sources tho. would this be an issue?
when the form loads, the subform runs it's own query to return all results.
As the combo boxes are update, VBA code runs to update the subform query.

does this make sense?

thanks

Seb

 

[pbaldy]

If changing the combo for a search changes the value of a record, the combo must be bound. What is its control source property? It should be blank, but I'm guessing it's a field name.

 

[Seb]

hi Paul
that's what I figured too, except they're not bound....
but you made me think, and I noticed my form was bound to that table....for no reason! so I unbound it, and KAPOW! it works

thanks mate

 

[pbaldy]

The form being bound shouldn't matter; something weird was going on. In any case, glad you have it working.