Limiting Row Source Property of Multiple Combo Boxes

[doma23]

I have 5 combo boxes which represent certain periods.
The row source of these 5 combo boxes is table tblRefDate.
What I want to do is to make a procedure that when period is inserted in any of these combos will limit periods
shown in other combo boxes, ie. it will exclude the period or periods which are already shown
in one or more of other combos.

I was thinking about making 5 functions and puting them respectively in ROW SOURCE property of the periods.
I was imagining that function should look something like this code under, but I'm still struggling a lot with writing
the string correctly.

Period1 - Row Source: fRow_Period1

Public Function fRow_Period1()
Dim sRow1 as String
 
sRow1 = "SELECT tblRefDate.RefDate from tblRefDate WHERE [RefDate] <>" _
        & me.cboPeriod1 & "WHERE [RefDate] <>" & me.cboPeriod2 & WHERE [RefDate] <>" & me.cboPeriod3
 
fRow_Period1 = sRow1
End Function

Tnx.

[patjones]

To comment specifically on your SQL, and not to suggest one way or the other whether this is the best way to do it, you would need only one WHERE:

sRow1 = "SELECT tblRefDate.RefDate 
         FROM tblRefDate 
         WHERE [RefDate] <> " & Me.cboPeriod1 & " AND [RefDate] <> " & Me.cboPeriod2 & " AND [RefDate] <> " & Me.cboPeriod3

There is a shorthand way of doing this as well:

sRow1 = "SELECT tblRefDate.RefDate 
         FROM tblRefDate 
         WHERE [RefDate] NOT IN (" & Me.cboPeriod1 & ", " & Me.cboPeriod2 & ", " & Me.cboPeriod3 & ")"

Let us know if it works.

Pat

[doma23]

It doesn't work.
I've been playing with it but I keep getting strange messages, ie. the function doesn't exist, can't find this and similar.
But if you know more efficient way to do this, please share. This with functions was just an idea which might miss some logical steps...

[NeoPa]

You don't say, but I'm guessing maybe the [RefDate] field is of type DateTime. That makes the SQL a little more tricky, but Pat's solution is very much on the right lines nevertheless.

We're working on very little information here. Please provide details of what you've tried and what the error message was for each attempt. Telling us that you had various errors in your attempts, but no further details is very frustrating and makes helping you very difficult.

The RowSource of each of your ComboBoxes should be similar to this one for the first (cboPeriod1) :

SELECT [RefDate]
FROM   [tblRefDate]
WHERE  [RafDate] Not In(CDate([Forms]![FormName].[cboPeriod2]),
                        CDate([Forms]![FormName].[cboPeriod3]),
                        CDate([Forms]![FormName].[cboPeriod4]),
                        CDate([Forms]![FormName].[cboPeriod5]))

Each ComboBox should have an AfterUpdate event procedure that calls a .ReQuery() of all the other ComboBoxes.

[patjones]

I echo NeoPa's request that you tell us exactly what the error message says. We'll probably be able to pinpoint the matter very quickly then. Thanks.

Pat

[doma23]

Thank you both guys very much.
And sorry for not providing all the informations. My system on job is in Italian and it takes me a lot of time to decipher everything.
I've expected that somebody will point me other way, as I felt that my idea is not the most efficient one. I've post it just to not give false impressions that I haven't tried anything.
Also these SQL strings in VBA make me want to pull my hair off (which I don't have a lot btw). :)
Over the weekend I was struggling for almost 2 hours to get this line of code correctly:
Checks if data exist in the table based on the value of 3 textboxes from the form

If DCount("*", "[DATA_T]", _
    "(([bank]='" & Form_frmMain.cmbBank & "') AND (" & "[division]='" & Form_frmMain.cmbDivision & "') AND (" & "[ref_date]='" & Converted_Period1 & "'))") > 0 Then
    DataExist1_Period1 = True
End If

Anyway, I finished the tool yesterday and it has all the functionalities it needs to have.
But I wanted to add one more thing - this limitation on combo boxes, and when I started dealing again with SQL strings and error messages on Italian started to pop out, one after another - my head was about to explode.
----------
Anyway, NeoPA your solution worked. It was exactly what I wanted.
The thing is that I wasn't sure if it's possible to refer to the controls directly with the SQL. Somebody told me it needs to be done via functions. Blah...

So, just to repeat, here is the solution.
Periods are saved as text, not as date.

To be put directly into the "Row Source" for each cmbPeriod combobox. Combobox for which you're setting the Row Source should not be included.
cmbPeriod1 - Row Source property

SELECT [Ref_Date] FROM [tblRefDate] 
WHERE  [Ref_Date] Not In
([Forms]![frmMain].[cmbPeriod2],
[Forms]![frmMain].[cmbPeriod3],
[Forms]![frmMain].[cmbPeriod4],
[Forms]![frmMain].[cmbPeriod5])

Also, like NeoPA said, Requery on After Update procedure:

Public Sub cmbPeriod1_AfterUpdate()
Me.cmbPeriod2.Requery
Me.cmbPeriod3.Requery
Me.cmbPeriod4.Requery
Me.cmbPeriod5.Requery
End Sub

[NeoPa]

Doma,

Thank you for this amusing post. I can just imagine your frustration trying to deal with SQL strings within VBA. It's a common problem even experienced programmers struggle with.

Also for posting your solution. It clearly explains that you have fully grasped the concepts needed, which is always good for us to see.

Here's to your remaining hair. May it stay with you always :)

[doma23]

@NeoPa
Hahaha, thank you my friend.
This is the first and only Access forum I'm using.
So far, my problems always found their solutions here, thanks to all of you kind people.
NeoPA, once I'm in London I'm buying a beer!
Cheers!

[NeoPa]

Let me know when. It's quite hard to find me in London unless you know where to look :D