Self-limiting combo

[Bobadopolis]

Hi,

Just a quick question, is it possible to have a combo box in a continuous subform that automatically limits its rows based on the selections made in the other child records/combos (for the same parent record)?

Cheers,

Bobadopolis

 

[llkhoutx]

Yes. Filter the combo box 'Row Source' property.

 

[Bobadopolis]

Hi, thanks for your reply llkhoutx.

I appreciate that the only way to achieve this is through filtering the RowSource, but what do you think is the best way to achieve this? I have tried to make a query as the rowsource that took its criteria from the subform in question but with no success. I referred to the combo boxes directly but this didn't work since the query only seemed to look at the last combo box in subform and hence only removed the record you had selected previously.

I don't really know where to begin writing the SQL for this... it's the clause after the 'where' statement; obviously I cannot refer to the combos directly so a reference to the underlying recordset seems the way to go but I have no idea how to do this!!!

Any suggestions welcome

Regards,

Bobadopolis

 

[llkhoutx]

I don't really know where to begin writing the SQL for this...

One way is to use the query builder, then, when the query is filtering properly, switch to the SQL view to see the resultant sql string.

"Cascading combo boxes" have extensive postings on this site, i.e. search this site for "cascading combo box."

Your resultant code for the main form combo box row source will look something like this:

For a numeric filter:
orms!MainFormName!cbo2.rowsource = "SELECT Field1, Field2, Field3 FROM tbYourTable WHERE FILEDX =" & Forms!MainFormName!subFormName.form!me!cbo1

or

For a text filter:
orms!MainFormName!cbo2.rowsource = "SELECT Field1, Field2, Field3 FROM tbYourTable WHERE FILEDX =" & chr(34) & Forms!MainFormName!subFormName.form!me!cbo1 & chr(34)


I use the chr(34), a quote, in concantenating text because using an acqual quote is sometime complex and tedious.
or

For a date filter:
orms!MainFormName!cbo2.rowsource = "SELECT Field1, Field2, Field3 FROM tbYourTable WHERE FILEDX = #" & Forms!MainFormName!subFormName.form!me!cbo1 & "#"


Insert the appropriate code on the cbo1_AfterUpdate event along with
Forms!MainFormName!cbo2.requery

I suspect your problem might be either in referencing the subform field correctly or in correctly concantenating the subform field in the sql string.

 

[Bobadopolis]

Hi llkhoutx

It appears I have done a bad job in explaining what it is I'm trying to do!

I'm *not* trying to filter one combo box based on the selection made in another. What I'm trying to achieve is a self-limiting combo that would be used in a continuous subform. Basically, I want the combo to have its full recordset available to it in its rowsource when the form/subform loads. Then, as the user makes selections I want these records to be removed from the rowsource.

For example, if the combo initially shows:

Apple
Orange
Pear
Banana

and the user selects orange as the first record in the subform then I want the rowsource for the combo to change to:

Apple
Pear
Banana

Hence making it impossible to have duplicates.

Hope that's a little clearer this time!

Regards,

Bobadopolis

 

[llkhoutx]

The best way to do this is to set the RecordSource table indexing for the instant field to No Duplicates. That is exactly what No Duplicates is meant for, especially with extraordinary long lists.

Alternatively, in the color field BeforeUpdate event do a DLookUp function to see if that value exists. If so, fire an error and reset focus to the field for another f=value to be entered. Note that DLookUp are notoriously slow and that a recordset SnapShot search would generally be faster.

 

[Bobadopolis]

The best way to do this is to set the RecordSource table indexing for the instant field to No Duplicates. That is exactly what No Duplicates is meant for, especially with extraordinary long lists.

But there will be duplicates in the table as a whole as the recodsource is in a table on the many side of a relationship. I don't want duplicates in this field for a particualr foreign key.

Alternatively, in the color field BeforeUpdate event do a DLookUp function to see if that value exists. If so, fire an error and reset focus to the field for another f=value to be entered. Note that DLookUp are notoriously slow and that a recordset SnapShot search would generally be faster.

This may be satisfactory but I'd rather remove the option to duplicate altogether if possible. Not sure what you mean by a "recordset SnapShot search" though...

Many thanks for your advice in this matter

Bobadopolis

 

[llkhoutx]

I don't want duplicates in this field for a particualr foreign key.

Then index the recordsource table by that foreign key and the field you do not want duplicated. Indices can contain multiple fields.

Not sure what you mean by a "recordset SnapShot search . . .

A SnapShot is a copy of a recordset. A Dynaset is the actual recordset.

 

[Bobadopolis]

Indices can contain multiple fields.

Well I didn't know that!! Worked like a charm, though I think the functionality I described would be my preferred choice so I'll stew on that a little longer. In the meantime this will be great! One more thing... is there a way to trap the error that causes Access to bring up the msgbox informing the user of the duplication? Just to make it a little easier for the user to digest...

Many thanks for your help

Bobadopolis

 

[llkhoutx]

. . . is there a way to trap the error . . .

Docmd.setwarnings False

Your code

Docmd.setwarnings True

May keep the subject error from printing. If not, in the normal error message point in the code, test for the error number of the subject error and is match Resume next, i.e.

On Error goto Error_Exit

your code

Exit_Exit:
Exit Sub or function
Error_Exit:
if err.Number = 'the subject error number' then Resume Next
msgbox "UNEXPECTED ERROR NO. (" & ERR.NUMBER & ") " & ERR.DESCRIPTION
Resume Exit_Exit

As I recall you may not be able to suppress the subject error.

 

[Bobadopolis]

Hello!

It appears you cannot surpress the subject error, I've tried extensively with no success! Bit of a pain!

Well, users will just have to deal with it i guess! Unless I find a way around it...

Many thanks for you help with this llkhoutx

Regards,

Bobadopolis