[Access] iskanje z formom / querijem

Primer iskanja 1

Primer iskanja 2

access forms search

Princip pa bi bil tak. Podatke moraš imeti v eni tabeli, s primarnim ključem. Nato narediš formo za iskanje podatkov po tej tabeli. Po vpisu vsake črke (dogodek AfterUpdate, se mi zdi) v iskalni niz preverjaš rezultate v tabeli s SQL stavkom s parametrom LIKE, npr.

SELECT * FROM tabela WHERE ime_stolpca LIKE '%iskani_niz%';

V enem listboxu prikazuješ rezultate z obveznim primarnim ključem. Če potrebuješ še nadaljno obdelavo, povežeš vrstico listboxa s primarnim ključem tabele.

krofko in zagy... hvala za odgovor, ampak to nekak ni to kar sem jaz iskal (iskanje po eni tablici je enostavno (CTRL+F)), čeprav je tisti search dobr vseeno pa pobere preveč časa. Včeraj sem se igral z nečim kar je moj nekdanji sodelavec naredil in sem predelal njegov "script" in mi je ratal, ampak nisem poštekal kako je to naredil...

kreiral je eno tabelco in en query. Naredil je form v katerega je ta query vstavljen kot "subform". Ko vtipkaš iskan kriterij v form se v queriju spodaj izpišejo rezultati... je pa res, da je to iskanje po enem polju (po enem in istem polju v več tabelcah).

če se da komu "poigrat" lahko postam kodo.

@zagy
Evo.. da definiram moj problem:

imam dve tablici, ki sta med seboj povezani z one-to-many relacijo. Tablica "stranke" vsebuje imena podjetij s katerimi sodelujemo (naslov, poštno številko, mesto, centralni telefon), druga tablica "clients" pa imena zaposlenih v teh firmah (ime in priimek, direktni telefon, drugi telefon, gsm, faks).
Moj cilj je narediti form v katerega bom vpisal recimo ime firme in mi bo izpisal vse zaposlene v tej firmi (z telefonskimi ...) ali pa ime človeka, ki ga iščem in da se mi zopet izpišejo vse osebe s tem imenom (in seveda z telefonskimi številkami).
Vem, da se to naredi z select querijem ali pa samo z funkcijo find, ampak jaz bi si delo rad nekoliko olajšal (in seveda zakompliciral stvar, da se še kaj naučim)

Podoben "problem" ja rešil moj nekdanji sodelavec, le da je imel vključeno iskanje po več tablicah (samo po enem polju v tablici). Kaj je naredil...
1) posebno tablico imenovano tblImena s polji (municipality_code; ulica; brick) in query imenovan qrySpojevi s polji (code; ime ulice; brick).
2) form (kamor je dal qrySpojevi v subform) s praznim poljem in gumbkom "išči". Ko vtipkaš v prazno polje ime ulice gre potem koda iskat po vseh tablicah in prikaže vse ulice, ki vsebujejo iskani niz. To "skripto" sem predelal, da deluje, ampak je ne razumem in je ne morem ponoviti ne da bi uporabljal njegovo kodo in njegovo poimenovanje tablice tblImena in querija qrySpojevi.

koda pa je sledeča:

Private Sub Command9_GotFocus()
Dim OK As Boolean
Dim Db As Database
On Error GoTo Err_Odustani
OK = False

If Val(Left(Me.UpisImena, 5)) > 0 Then
ImeLijeka = ""
municipality_codee = UpisImena
Else
ImeLijeka = UpisImena
municipality_codee = ""
End If
UpisImena = ""
DoCmd.Hourglass True
Me.Application.Echo True
DoCmd.SetWarnings False

Set Db = CurrentDb
BeginTrans
OK = True
Db.Execute ("DELETE * FROM tblImena;")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Brezovica.municipality_code, " _
& "ul_Brezovica.ulica, " & Chr(34) _
& "Brezovica" & Chr(34) & " AS brick FROM " _
& "ul_Brezovica WHERE " _
& "ul_Brezovica.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Brezovica.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Crnomerec.municipality_code, " _
& "ul_Crnomerec.ulica, " & Chr(34) _
& "Črnomerec" & Chr(34) & " AS brick FROM " _
& "ul_Crnomerec WHERE " _
& "ul_Crnomerec.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Crnomerec.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_DonjaDubrava.municipality_code, " _
& "ul_DonjaDubrava.ulica, " & Chr(34) _
& "Donja Dubrava" & Chr(34) & " AS brick FROM " _
& "ul_DonjaDubrava WHERE " _
& "ul_DonjaDubrava.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_DonjaDubrava.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_DonjiGrad.municipality_code, " _
& "ul_DonjiGrad.ulica, " & Chr(34) _
& "Donji Grad" & Chr(34) & " AS brick FROM " _
& "ul_DonjiGrad WHERE " _
& "ul_DonjiGrad.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_DonjiGrad.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_GornjaDubrava.municipality_code, " _
& "ul_GornjaDubrava.ulica, " & Chr(34) _
& "Gornja Dubrava" & Chr(34) & " AS brick FROM " _
& "ul_GornjaDubrava WHERE " _
& "ul_GornjaDubrava.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_GornjaDubrava.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Maksimir.municipality_code, " _
& "ul_Maksimir.ulica, " & Chr(34) _
& "Maksimir" & Chr(34) & " AS brick FROM " _
& "ul_Maksimir WHERE " _
& "ul_Maksimir.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Maksimir.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_NoviZGistok.municipality_code, " _
& "ul_NoviZGistok.ulica, " & Chr(34) _
& "Novi ZG istok" & Chr(34) & " AS brick FROM " _
& "ul_NoviZGistok WHERE " _
& "ul_NoviZGistok.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_NoviZGistok.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_NoviZGzapad.municipality_code, " _
& "ul_NoviZGzapad.ulica, " & Chr(34) _
& "Novi ZG zapad" & Chr(34) & " AS brick FROM " _
& "ul_NoviZGzapad WHERE " _
& "ul_NoviZGzapad.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_NoviZGzapad.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Podslijeme.municipality_code, " _
& "ul_Podslijeme.ulica, " & Chr(34) _
& "Podslijeme" & Chr(34) & " AS brick FROM " _
& "ul_Podslijeme WHERE " _
& "ul_Podslijeme.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Podslijeme.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Sesvete.municipality_code, " _
& "ul_Sesvete.ulica, " & Chr(34) _
& "Sesvete" & Chr(34) & " AS brick FROM " _
& "ul_Sesvete WHERE " _
& "ul_Sesvete.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Sesvete.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

Db.Execute ("INSERT INTO tblImena ( municipality_code, ulica, brick ) SELECT " _
& "ul_Trnje.municipality_code, " _
& "ul_Trnje.ulica, " & Chr(34) _
& "Trnje" & Chr(34) & " AS brick FROM " _
& "ul_Trnje WHERE " _
& "ul_Trnje.municipality_code Like " & Chr(34) & Chr(42) & municipality_codee & Chr(42) & Chr(34) & " AND " _
& "ul_Trnje.ulica Like " & Chr(34) & Chr(42) & ImeLijeka & Chr(42) & Chr(34) & ";")

CommitTrans
Db.Close
OK = False

DoCmd.SetWarnings True
Me.qrySpojevitablica.Requery
Me.UpisImena.SetFocus

Exit_Odustani:
ImeLijeka = ""
municipality_codee = ""
DoCmd.Hourglass False
Me.Application.Echo True
Exit Sub

Err_Odustani:
MsgBox Err.Description
If OK = True Then
Rollback
Db.Close
End If
Resume Exit_Odustani
End Sub

Evo... to je vse... hvala ker pomagaš in ker se ti da ukvarjat z mano ubogim nevednežem :)