Brug af listeopslag i forespørgselskriterier

Hvis der er rent Access, skal kriteriet se således ud:

[Forms]![Formularnavn]![Feltnavn]

.

Never tried this before but I see no reason why it shouldnt work as long as the contents of [Form].[Udskrifter}.[Listevalg] get converted correctly and thsi si whey the problem will be if any!

In (1,2,3)
In ('a','b','c')

Oh! and of course ] not }

Sorry ... min stavekontrol virker ikke, men kriteriet er naturligvis angivet som mugs foreslår ;)

But it doesn´t work

Jeg har afprøvet en løsning med 2 lister i en formular. Derefter indsat kriterier der refererer til begge lister med "eller"- kriterier. Og det fungerer perfekt.

Læg din e-mail så kan jeg sende den.

mugs>I may be on the wrong track but I understand the questiion to mean a list of values, arent you refering to a lisbox?

SELECT * FROm SomeTable WHERE ID In(1,2,3)

and the lis of values is in a field on the form. Otherwise I dont understand why jensen363 has written Kriterie er : In([Form....

what do you say jensen363?

terry > Yes, I refer to a listbox. And I think it's correct according to the comment 15:33:05. But only the user can explain who's on the right track.

Terry / Mugs > Ja, den ønskede løsning skal hente valgfri værdier ( altså en eller flere ... eller * ) fra en listbox. Det er også den løsning jeg har forsøgt med, men det virker ikke lige ...

Mugs > min mail er jens363borg@mail.tele.dk

and I assume that means a multi select listbox? (you cant without code)

Terry > Correct, I need a Multi selct option ... so please help me

mugs > Sorry, your solution doesn´t work ...

A simple solution is to have an extra field in the table (Yes/no) Then make a form which shows the records from the table (continuous form). The new field will (should) be a check box. Now you can select the records you want to show in the query by checking the checkbox. Now you need to alter the query so that it selects only those where the checkbox is checked (true).

This is a simple soultion which requires no code. Otherwise you will need to build your query in VBA!

I think I will try with some VBA code.
A solution with yes/no marking isn´t a pretty one ... sorry but thanks for trying ...

A simple VBA solution.
Make a temporary table. This contains only the ID (primary key) of the records you select in the listbox. I assume you have a button which you press when you have selected the record. Maybe to open a report!
Now in the on click evenn of the button you empty the temp table.
Then you loop through the seelcted records INSERTing the ID's into the temp table.
Now you alter your query to somethink like

SELECT * FROM YourTable WHERE ID IN (SELECT ID FROM TempTable) .....

Tak for jeres forsøg på at hjælpe, men jeg har fundet en løsning som virker tilfredsstillende ( via Microsoft ). I får alligevel lidt for jeres indsats ;)

thanks!
Any chance of seeing what you found with MS?

Hi Terry

I created a solution based on 4 queries and 2 listboxes (I need to create macth form at least 2 tables)

The fist query (qryDataudtræk_001) is used to select the primary list of options (joining 3 tables)

The 3 tables are :

Kandidat ( name, address … )
P_Kompetence ( 1. priority skills )
S_Kompetence ( 2. priority skills )

The listbox for my first select looks like this :

Private Sub P_Valg_AfterUpdate()

Dim Q_1 As QueryDef, DB As Database
Dim Criteria_1 As String
Dim ctl_1 As Control
Dim Itm_1 As Variant

Set ctl_1 = Me![P_Valg]

For Each Itm_1 In ctl_1.ItemsSelected
    If Len(Criteria_1) = 0 Then
        Criteria_1 = Chr(34) & ctl_1.ItemData(Itm_1) & Chr(34)
    Else
        Criteria_1 = Criteria_1 & "," & Chr(34) & ctl_1.ItemData(Itm_1) _
        & Chr(34)
    End If
   
Next Itm_1

If Len(Criteria_1) = 0 Then
    Itm_1 = MsgBox("Vælg én eller flere IT-kompetencer.", 0, "Intet valg")
Exit Sub

End If

Set DB = CurrentDb()
Set Q_1 = DB.QueryDefs("Multiselect_1")
Q_1.SQL = "Select * From qryDataudtræk_001 Where [P_Kompetence] In(" & Criteria_1");"
               
End Sub

The query Multiselect_1, returns the selected P_Kompetence items (Itm_1) using Criteria_1 from the listbox P_Valg.

My second listbox uses a similar code to acces the query Multiselect_2, that returns the selected S_Kompetence using Criteria_2 from listbox named S_Valg.

Finally I join Multiselect_1 and Multiselect_2 in the last query (one to one relation), the query that I use in the report.

I hope you understand what I have written …. ;)

It works, but I have one minor problem … the last choice from Criteria_1 and Criteria_2 are stored in the memory as long as Access is active …

Yours truly
Lasse

Tak Lasse!

Kan man ikke "tømme" kriterierne i slutningen af koden:

Criteria_1 = Nothing

Men kan ikke umiddelbart tømme criteria, idet query Multiselect_1 og Multiselect_2 genereres som nye query hver gang man aktiverer AfterUpdate. De pågældende query er derfor fortsat gældende, ind til en ny AfterUpdate køres.

Dette efterlader et problem for slutbrugeren, hvis denne tror at intet valg = alle valg ... hvis I forstår ...

Jeg har valgt at løse problemet ved at indføje '*', altså vælg alt i listboksene som default value ... og så sikre at denne selection også er mulig i min kilde.

Er det fuldstændig sort snak ????