Prikazi cijelu temu 03.06.2017 11:55
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Lokacija:Tuzla


Predmet:Re: Query od vise tabela
Evo ti kod.

Bilo je par gresaka u nazivima polja pa popravi.
PreuzmiIzvorni kôd (Visual Basic):
  1. Option Compare Database
  2. Option Explicit
  3. Type Imena
  4. Pt As String            'Imepomone tabele
  5. Pt_Kljuc As String      'Klju
  6. za pod tabelu
  7. Pdt As String           'Imepod tabele
  8. Pdt_Kljuc  As String    'Klju
  9. u pod tabeli
  10. Pt_Podatak As Integer   'Podatak klju
  11. a u pomonoj tabeli
  12. End Type
  13. Dim Db As DAO.Database
  14. Dim RsTemp As DAO.Recordset
  15.  
  16. Function Popuni()
  17. Dim Rs As DAO.Recordset
  18. Dim SQL As String, SQLTemp As String
  19. Dim ID As Integer
  20.  
  21. SQL = "SELECT * FROM tblMain"
  22. SQLTemp = "SELECT * FROM tblTemp"
  23. Set Db = CurrentDb
  24. Set Rs = Db.OpenRecordset(SQL)
  25. Do While Not Rs.EOF
  26. ID = Rs!Id_Main
  27. Set RsTemp = Db.OpenRecordset(SQLTemp)
  28. RsTemp.AddNew
  29. RsTemp!No = Rs!Broj_Dokumenta
  30. RsTemp!Datum_Ukladanja = Rs!Datum_Ukladanja
  31. RsTemp!Godina_kad_je_verovatno_stvoren = Rs!Godina_kad_je_verovatno_stvoren
  32. RsTemp!Datum_kad_je_verovatno_stvoren = Rs!Datum_kad_je_verovatno_stvoren
  33. RsTemp!Naziv = Rs!Naziv
  34. RsTemp!Opis = Rs!Opis
  35. RsTemp!Sacuvan = Rs!Lock
  36. RsTemp!Id_Main = ID
  37. RsTemp.Update
  38. RsTemp.Close
  39. Pomocne_Tabele ID
  40. Rs.MoveNext
  41. Loop
  42.  
  43. End Function
  44.  
  45.  
  46. Sub Pomocne_Tabele(ID As Integer)
  47. Dim Rs As DAO.Recordset, RsPom As DAO.Recordset, Rsdod As DAO.Recordset
  48. Dim SQL As String, SQLPom As String, SQLDod As String, SQLTemp As String
  49. Dim IP As Imena
  50. Dim Broj_Polja As Integer, I As Integer, N As Integer, N1 As Integer
  51. Dim ImePolja As String
  52.  
  53. N1 = 1
  54. SQLTemp = "SELECT * FROM tblTemp WHERE ID_Main=" & ID
  55. SQL = "SELECT * FROM tblTabele"
  56. Set RsTemp = Db.OpenRecordset(SQLTemp)
  57. Set Rs = Db.OpenRecordset(SQL)
  58. Do While Not Rs.EOF
  59. IP.Pdt = Rs.Fields(3)
  60. IP.Pt = Rs.Fields(1)
  61. IP.Pt_Kljuc = Rs.Fields(2)
  62. IP.Pdt_Kljuc = Rs.Fields(4)
  63.  
  64. '*************************
  65. 'Pomona tabela
  66. '*************************
  67.  
  68. RsTemp.MoveFirst
  69.  
  70. SQLPom = "SELECT * FROM " & IP.Pt & " WHERE ID_Main=" & ID
  71. Set RsPom = Db.OpenRecordset(SQLPom)
  72.     N = RsPom.RecordCount
  73.     If N > 0 Then
  74.     RsPom.MoveLast
  75.     RsPom.MoveFirst
  76.     N = RsPom.RecordCount
  77.     End If
  78. REKORD:
  79.     If N1 < N Then
  80.     RsTemp.AddNew
  81.     RsTemp.Update
  82.     N1 = RsTemp.RecordCount
  83.     GoTo REKORD
  84.     End If
  85.     RsTemp.MoveFirst
  86.     Do While Not RsPom.EOF
  87.     RsTemp.Edit
  88.     IP.Pt_Podatak = RsPom(IP.Pt_Kljuc)
  89.     SQLDod = "SELECT * FROM " & IP.Pdt & " WHERE " & IP.Pdt_Kljuc & "=" & IP.Pt_Podatak
  90.     Set Rsdod = Db.OpenRecordset(SQLDod)
  91.     Broj_Polja = Rsdod.Fields.Count - 1
  92.         For I = 1 To Broj_Polja
  93.         ImePolja = Rsdod.Fields(I).Name
  94.         RsTemp(ImePolja) = Format$(Rsdod.Fields(I))
  95.         Next I
  96.     Rsdod.Close
  97.     RsPom.MoveNext
  98.     RsTemp.Update
  99.     RsTemp.MoveNext
  100.     Loop
  101. Rs.MoveNext
  102. RsTemp.MoveFirst
  103. Loop
  104. RsPom.Close
  105. End Sub
Podrška samo putem foruma, jer samo tako i ostali imaju koristi od toga.