Need better alternatives than the lookup field, suggest me something.

[hvsummer]

As zmbd said that there are better alternatives than the lookup field for my problem, so I create this topic to ask some questions.

Firstly,I did use MVFs to be able to select items as tickbox inside dropdown combobox, so what I can do to replace MVFs with better method ?
I mean what is another way to create form with tickbox inside combobox ?

Secondly, How can I query the normal table without MVFs look like a table with MVFs ?
I mean instead using table content MVFs, now, I use normal table, but query any similar thing grouped inside 1 row without drop perfomace (that method should give better perfomance than MVFs in table)

I need to use MVFs because I manage list of promotions Items.
each promotion campain have 2->20 ItemCodes, within a period time,
If I mannually write the same info for each itemcode, it'll be complex and can be misstype then information will be wrong.
If I use normal table, I can't see the group of Promotion campain as macro-picture... and normal table with 10 promotion campains can have like 200 rows, who can view all of them to manage lol
Any suggestion ?

[Seth Schrock]

I don't believe that there is another way to get checkboxes in a combo box, but you can have a subform that has them. What I normally do in this case would be to have your join table not only have the two foreign keys, but also have a "Selected" field. You then use an insert query to populate the join table with all the possible promotion items (I think that is what you had in your combo box before) with the Selected field having a default value of False. Then the user can just use the checkbox to select the different promotion items for the campaign.

For querying it, you just have to have three criteria: one for each foreign key and one for the Selected field to be True.

[hvsummer]

well, so you use selected field (Boolean field) as altenative :-?
design this as subform SELECT query, then Onclick_event of "save" button set up the docmd.execute "Insert" query into table ? ==

this will take 2 step, I think performance will drop ==

[Seth Schrock]

I've done this many times and there is not a noticeable lag in running the insert query even with a couple hundred records getting added.

[hvsummer]

ok, so input part done
how about query it as group in 1 field :-?
my second question part lol

[Seth Schrock]

Okay, using the table names tblPromotionCampaign and tblCampaignItems (the join table) it would be something like this.

SELECT tblPromotionCampaign.* 
FROM tblPromotionCampaign INNER JOIN tblCampaignItems 
    ON tblPromotionCampaign.PC_ID_pk = tblCampaignItems.PC_ID_fk
WHERE PI_ID_fk = 5 And Selected = True

So this looks for promotion item 5 to be selected.

[hvsummer]

@Seth Schrock:
nah, that simple query can't handle my problem.
I mean I want group those like MVFs.

Result should be Look like this:
ID ------- PCName
1,2,3 -- spring season
8,9 ----- S30

Original look like this:
ID --- PCName
1 --- spring season
2 --- spring season
3--- spring season
8 --- S30
9--- S30

I know there is a way that NeoPa gave in another post, I remember the VBA function can collect and group to like that (DAO or ADO reference)
but since VBA ADO or DAO decrease significantly perfomance I thik I should not use them when I have better solution.

[Seth Schrock]

Unless you know what the maximum number of IDs will be, then the only way is to use VBA. Ideally, you would limit the number of records displayed at a time. This would make the performance hit not noticeable. Allen Browne's ConcatRelated() function works great and doesn't slow down queries until you start hitting over a hundred function calls.

[hvsummer]

each PCName have fixed ID, it'll be smaller than 100 items
to know how much ID represent for PCName, I think we can count it lol.

You say "Unless" then there is a way to do it without VBA ?

[Seth Schrock]

I used Rabbit's Ranking Query method and then use the where clause to get a certain ranking number. However, you have to have a separate subquery for each position. So if you have up to three results, then you need three subqueries.