Reduce the limit list of Combo box values (1 Viewer)

[nirmal]

Can we reduce the limit list of Combo box values without actually reducing the values in the table.

For example,

Suppose I have a table XYZ having 100 values from 1 to 100.
Now I have a form with ten combo boxes, where I want to have the same table XYZ as row source to all the ten combo boxes, but can I have
combobox A = 1 to 10 values
combobox B = 11 to 20 values
.....
combobox J = 91 to 100 values

Here, the combo boxes values should be limited to the values from the same table

It is just a doubt which I have in my mind, so that instead of creating 10 tables, I can mange with one table

 

[MajP]

SELECT TOP 5 tblData.Id, tblData.Full_Name
FROM tblData;

2nd five

SELECT TOP 5 tblData.Id, tblData.Full_Name
FROM tblData
WHERE tblData.id Not In(select Top 5 B.ID from tblData as B);

3rd group of 5

SELECT TOP 5 tblData.Id, tblData.Full_Name
FROM tblData
WHERE tblData.id Not In(select Top 10 B.ID from tblData as B);

etc.

 

[theDBguy]

If you can limit your row source using a criteria, then yes, you can do that. Otherwise, we'll need to know what your data looks like to give you a different approach.

Edit: What @MajP said. Too slow...

 

[nirmal]

Sir, I am attaching the ComboBox Db1
Here i have made two forms 1Concatenate_frm and Concatenate_frm , whereby I have used 5 tables with indexed values as RowSource in 1Concatenate_frm and and used Select query(SELECT TOP 5 Data_tbl.ID, Data_tbl.Names AS A FROM Data_tbl; ) as rowSource in Concatenate_frm using Data_tblhaving all values.

In Concatenate_frm
txtResult =([cbo1]+" ") & ("< "+[cbo2]+" ") & ("< "+[cbo3]+" ") & ("< "+[cbo4]+" ") & ("< "+[cbo5])

In 1Concatenate_frm
txtResult = =([cbo1]+" ") & ("< "+[cbo2]+" ") & ("is of "+CStr(Nz([txtAgeYear]))+".") & (""+CStr(Nz([txtAgeMonths]))+" age") & ("< "+[cbo3]+" ") & ("< "+[cbo4]+" ") & ("< "+[cbo5])

The things I had encountered are
1. In both forms, the Result box is not giving the text value, instead giving index values
2. 1Concatenate_frm , if the txtAgeYear and txtAgeMonth values are null or empty, still the prefix and postfix values highlighted bold above are reflecting in the result box, which I don't want to have.

So what I am trying to get is
1. Can we use a indexed single table as in Concatenate_frm, whereby I get a text value in the result box, I store the index value in my table
2. In 1Concatenate_frm, the result box should not show the prefix and postfix of the CStr() txtboxes if the values are Null / Empty


So, please anyone can HELP me

 

[arnelgp]

test your forms.

 

[nirmal]

Yes Sir the results are good, but in the 1Concatenate_frm, after I insert values in txtAgeYear & txtAgeMonth I get the expected result, but after that if I delete the same values other than that of other comboboxes, then still is of remains in the result box.
And, Sir can we make a single table instead of 5 different tables

Sir learning arrays is more difficult for me ( I always find it difficult to understand), is there any other function for it

 

[Uncle Gizmo]

I think you are going about it the wrong way.

I suggest adding an extra field to your table a boolean, "checkbox" field.

List your table in datasheet view in a subform on a main form. Select the items you want with the checkbox field. Run a routine that concatenates the selections. This will avoid the problems you are having.

Edit:-
This example on the Nifty Access website goes most of the way to doing what you want, it's just missing the checkbox field. You should be able to adapt the code to suit your particular requirements.

Display Field Data Horizontally - Nifty Access

Transform, Concatenate Examples A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done. Display Field...

www.niftyaccess.com

Edit 2
Create a query based on your table which only returns records where the "checkbox boolean field" is true. Open the SQL view of the query and copy the SQL out and replace this line:-

SELECT tblContact.MailToHere FROM tblContact

In this Code:-

Public Function fConcatList()
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim strSQL As String
Dim strText As String

strSQL = "SELECT tblContact.MailToHere FROM tblContact" 'This is just a query like you would build
                                                        'in the query builder, but it is in text format
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest

    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!MailToHere
                Else
                strText = strText & ", " & RS!MailToHere
            End If
        RS.MoveNext
    Loop

    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    fConcatList = strText

End Function      'fConcatList

 

[arnelgp]

added "group" field to Data_tbl and use it as rowsource of your combo.

 

[nirmal]

Sir, I tested the Db, still there is minor problem.
if txtAgeYear and txtAgeMonths are blank, there is no '<' symbol in between value of combo2 and combo 3

 

[arnelgp]

this is as far as i can go.
use your imagination.

 

[nirmal]

Sir I am trying and learning arrays
I could use your guidelines to develop my concept.
One more thing Sir, can we use the SQL code of a query directly in the VBA code, so it becomes less likely to be edited by someone else.