Form koppelen aan SelectieQuery

[royvdh65]

Beste mensen,

de laatste tijd ben ik wat aan het experimenteren met Access, waarbij ik een form wil maken met twee comboboxen waarin de gebruiker een selectie kan maken a.d.h.v een ProjectNr en ServiceOrderNr, deze waardes moeten dus via het form meegegeven worden aan de al gerealiseerde query die wel naar behoren werkt, verder moet de waarde in dit geval de status die de query terug geeft terecht komen in een tekstvak in hetzelfde formulier(dit gedeelte heb ik alleen nog niet verwerkt in de code). Alleen ik krijg het formulier hierbij niet aan het werk, dit is mijn code die ik tot nu toe heb:

Private Sub Knop13_Click()
On Error GoTo Err_Knop13_Click

Dim stDocName As String

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim Query10 As DAO.Recordset

Set db = CurrentDb
Set qDef = db.QueryDefs("Query10")

qDef.Parameters("dbolbedrijfVDLGJob2604.ServOrdNr") = "[Forms]![dbolbedrijfVDLGJob2604]![ServiceOrdNr]"
qDef.Parameters("dbolbedrijfVDLGJob2604.JobNr") = "[Forms]![dbolbedrijfVDLGJob2604]![JobNr]"


Set Query10 = qDef.OpenRecordset
QueryDef.Execute

Hierbij krijg ik als foutmelding "Item not found in this collection". Heb ik iets fout gedaan met de parameters die ik mee geef aan de query?

 

[OctaFish]

Nog even afgezien van het feit dat ik het nog nooit op die manier heb gemaakt, en eerlijk gezegd ook niet snap wat je wilt bereiken met de code, zit hier waarschijnlijk een foutje:
qDef.Parameters("dbolbedrijfVDLGJob2604.ServOrdNr") = "[Forms]![dbolbedrijfVDLGJob2604]![ServiceOrdNr]"
Moet misschien zijn:
qDef.Parameters("dbolbedrijfVDLGJob2604.ServOrdNr") = [Forms]![dbolbedrijfVDLGJob2604]![ServiceOrdNr]

 

[royvdh65]

Nu ik terug kijk naar de code van de parameter zal dit waarschijnlijk niet kloppen, iig het gedeelte na het is-teken niet.

qDef.Parameters("dbolbedrijfVDLGJob2604.ServOrdNr") = [Forms]![dbolbedrijfVDLGJob2604]![ServiceOrdNr]

Hierbij wil ik het gedeelte wat tussen haakjes staat verwijzen naar de invoervakken op het form dus het form heet dbolbedrijfVDLGJob2604 en de combobox heet ServOrdNr, het gedeelte na het is-teken moet dus verwijzen naar de query lijkt me? Alleen hoe kan ik deze laten verwijzen naar de query?

 

[OctaFish]

Kun je eerst eens uitleggen wat de bedoeling is? Want eerlijk gezegd snap ik dat nog steeds niet.... Als je een query wilt definiëren op basis van de keuzes die je maakt op het formulier kan het volgens mij een stuk simpeler. In ieder geval is dat een techniek die ik wel beheers...

 

[royvdh65]

Oke ik wil middels een form twee waarden invoeren via comboxen en deze waardes moeten naar de een query gestuurd worden (deze is al gemaakt), waarbij de query met de betreffende waardes in de database gaat zoeken. De uitvoer van deze query zal een record als uitvoer hebben. Deze uitvoer moet weer terecht komen in een tekstvak op hetzelfde forum.

 

[OctaFish]

Ik begin 'm te snappen, op één dingetje na:

De uitvoer van deze query zal een record als uitvoer hebben.

Bij een record denk ik namelijk aan een combinatie van velden; je haalt dus van één record een aantal gegevens op. En die zet je vervolgens in een tekstvak? Worden die gegevens dan gecombineerd? Zo ja, hoe?
Of, meer in de lijn der verwachtingen, haal je uit de tabel één waarde op, die je in het tekstveld zet?

Als dat laatste het geval is, zou ik het op de volgende manier doen:

strSQL="SELECT [ZoekVeld], ServOrdNr,  JobNr FROM [TabelNaam] " 
strSQL=strSQL & "WHERE ServOrdNr= " & Me.ServOrdNr & " AND JobNr =" & Me.JobNr
With CurrentDb.OpenRecordset(strSQL)
     If .RecordCount>0 Then
          Me.TekstVeld=.Fields(0).Value
     End If
End With

Hierbij ga ik er dus wel vanuit, dat er maar één record gevonden wordt...

 

[royvdh65]

Oke dus de waardes/parameters die men in het formulier invoert, worden mee gegeven in het select- statement van de query? dit zullen dus twee waardes betreffen in mijn geval, omdat er twee invoervakken bestaan in mijn forum? Deze twee waardes leiden tot een record.
En in mijn geval klopt het inderdaad dat er telkens maar een record als uitvoer is.

 

[OctaFish]

Dat is althans wat ik begrijp uit jouw verhaal. Alleen dus nog de opmerking of je één veld terug wilt lezen, of een aantal velden. Dat maakt namelijk nog wel wat uit. Je kunt (bijvoorbeeld) met een loopje door alle velden heenlopen, en ze successievelijk toevoegen aan het tekstveld, als je alle velden uit de tabel of query wilt gebruiken.
Als je deze techniek gebruikt, heb je de query ook helemaal niet nodig. De kans dat die per ongeluk wordt veranderd/verwijderd ben je daarmee ook kwijt.

 

[royvdh65]

Oke, dus al ik het goed begrijp maakt het niet uit hoeveel verschillende velden je als invoer hebt, deze moeten gewoon in het select-statement in de query geschreven worden als parameter. En bij de uitvoer maakt het bij de waarden wel uit hoeveel waarden eruit komen om deze vervolgens weer te geven in één tekstveld (is trouwens ook logisch omdat één tekstveld maar één waarde kan bevatten). Een loop kan bij een uitvoer van meerdere waardes gebruikt worden om iedere waarde om en om weer te geven in één tekstvak, begrijp ik dit goed (dit is bij mij dus nu niet het geval)? Maar waardoor zou de query geen nut meer hebben wanneer je een loop gebruikt hiervoor gebruikt?

Trouwens ik heb de query in Access gemaakt zonder met SQL te werken (dus met het scherm waarbij je de tabellen kunt koppelen en criteria op kunt geven). Deze query hoef ik dan ook niet meer aan te roepen als ik hiervan de SQL code kopieer die Access voor mij heeft gemaakt, omdat ik deze SQL code al gebruik in Visual Basic zelf?

 

[OctaFish]

Een loop kan bij een uitvoer van meerdere waardes gebruikt worden om iedere waarde om en om weer te geven in één tekstvak, begrijp ik dit goed (dit is bij mij dus nu niet het geval)?

Dit is niet helemaal wat ik bedoel. Met een loop bedoel ik in dit geval dat je in de output van één record (daar praten we nog steeds over) de inhoud van meerdere velden uit dat record combineert tot één (tekst)string, die je dan vervolgens in één tekstveld op het formulier terugzet. Wat natuurlijk ook kan, is dat je bijvoorbeeld met de query 5 velden ophaalt, en die in 5 verschillende tekstvelden neerzet. Met een <loop> wandel (zal niet twee keer het woord loop gebruiken) je dan door de velden heen.

Deze query hoef ik dan ook niet meer aan te roepen als ik hiervan de SQL code kopieer die Access voor mij heeft gemaakt, omdat ik deze SQL code al gebruik in Visual Basic zelf?

De query die je hebt gemaakt (query10 neem ik aan?) kun je uiteraard ook als basis gebruiken voor de code in mijn voorbeeld. Het maakt op zich niet uit of je een tabel of een (selectie)query als basis gebruikt. Alleen zie je queries terug in je query venster, en door alles op te bouwen in VBA hou je dus een overzichtelijker scherm.
Zelf maak ik vaak een query ook eerst in het query-ontwerpscherm, om hem daarna om te zetten naar VBA. Erg handig als je bijvoorbeeld toevoegqueries maakt, zodat je precies kunt zien op welke plek je de toe te voegen waarden moet vervangen door de formuliervelden.

 

[royvdh65]

Private Sub Knop13_Click()
On Error GoTo Err_Knop13_Click

Dim stDocName As String

Dim db As DAO.Database
Dim strSQL As String

strSQL = "SELECT [JobNr], [ServOrdNr], dboServiceOrder.No_, dboMasterData.LinkedJobNo, dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1.CODEPOSITION FROM dboServiceOrder, dboMasterData"
strSQL = strSQL & "WHERE dboMasterData.LinkedJobNo = " & JobNr & " AND dboServiceOrder.No_ = " & ServOrdNr & ""
With CurrentDb.OpenRecordset(strSQL)
If .RecordCount > 0 Then
CODEPOSITION = Me.Fields(0).Value
End If
End With

Oke ik heb nu de invoertekstvelden en de kolomnamen + de kolomnaam van de uitvoer in het SELECT-statement gezet, verder heb ik achter de FROM de tabellen opgegeven waarbij de betreffende kolomnamen in voorkomen, vervolgens heb ik in de WHERE-clausule de invoertekstvelden (van het form) gezet waarbij gekeken wordt of de invoerwaarden voorkomen in de database en op basis daarvan met die waardes gezocht word in de database. Maar nu is mijn vraag hoe ik de door Access geschreven SQL query in de code hierboven moet verwerken, want de query zelf zit er nog niet in verwerkt (zie door Access geschreven code hieronder). En wat houdt de "Me." precies in, in b.v. "Me.ServOrdNr"?

SELECT dboServiceOrder.No_, dboMasterData.LinkedJobNo, [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION
FROM (dboServiceOrder INNER JOIN dboMasterData ON dboServiceOrder.No_ = dboMasterData.[NAV ItemCode]) INNER JOIN dboMasterData AS [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1] ON dboMasterData.LinkedJobNo = [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].[NAV ItemCode]
WHERE (((dboServiceOrder.No_)=[Forms]![dbolbedrijfVDLGJob2604]![ServiceOrdNr]) AND ((dboMasterData.LinkedJobNo)=[Forms]![dbolbedrijfVDLGJob2604]![JobNr]) AND (([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION)<>"01" Or ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION)="05") AND ((dboMasterData.TableCode)="SERV_ORD") AND (([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].TableCode)="JOB_QZI"));

 

[OctaFish]

Ik heb je code even 'opgeschoond'. Hij zou er zo uit moeten zien...

Dim strSQL As String
Dim stDocName As String

strSQL = "SELECT dboServiceOrder.No_, dboMasterData.LinkedJobNo, [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION " _
    & "FROM (dboServiceOrder INNER JOIN dboMasterData ON dboServiceOrder.No_ = dboMasterData.[NAV ItemCode]) " _
    & "INNER JOIN dboMasterData AS [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1] " _
    & "ON dboMasterData.LinkedJobNo = [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].[NAV ItemCode]" _
    & "WHERE ((dboServiceOrder.No_=" & Me.[ServiceOrdNr] & ") AND (dboMasterData.LinkedJobNo=" & Me.[JobNr] & ") " _
    & "AND ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION<>'01' " _
    & "Or ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION)='05') AND (dboMasterData.TableCode='SERV_ORD') " _
    & "AND ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].TableCode='JOB_QZI'));"

With CurrentDb.OpenRecordset(strSQL)
    If .RecordCount > 0 Then
        Me.CODEPOSITION.Value = .Fields(0).Value
    End If
End With

Als je SQL 'vertaalt' naar VBA, dan moet je de dubbele aanhalingstekens (") vervangen door een enkele (') . Verder gebruik je (althans in dit voorbeeld) velden uit je formulier in de WHERE statements. De waarden daarvan haal je op met Me. Een verwijzing die overigens niet noodzakelijk is, je kunt hem dus ook weglaten. Als je in je code echter Me. intypt, gevolgd door de eerste letters van je object, dan zoekt Access zelf de juiste objecten voor je op, die aan de getypte tekst voldoen. Niet alleen hoef je daardoor minder te typen, de kans dat je typfouten maakt neemt ook aanzienlijk af. In dat geval zie je namelijk je objecten niet meer terug in de lijst, waardoor je gelijk ziet dat je iets fout doet.
Me. is dus een snellere manier om een (tekst)object te vinden, het verlaagt ook de foutenkans.

 

[royvdh65]

OctaFish ondanks de foutmelding die ik nu weer krijg, alvast hartelijk bedankt voor de uitleg en moeite!!
U heeft me een heel eind op weg geholpen, en nog belangrijker ik snap hierdoor het idee erachter weer een stuk beter

De foutmelding die ik nu krijg luidt: "Kan het veld niet vinden waarnaar wordt verwezen in de expressie."
Op internet heb ik nog gegoogled, en daar had ik gevonden dat er waarschijnlijk meerdere formulieren openstaan, dit is bij mij niet het geval.
Enig idee hoe dit komt?

 

[OctaFish]

De procedure zal vast stoppen bij de regel met de fout... Waarschijnlijk is dat ergens in de SQL? Controleer dus de veldnamen; ik heb wat overtollige haakjes verwijderd, dus daar kan het probleem ook zitten.

Een manier om de fout op te sporen is deze:
Voeg deze code toe onder de strSQL regels:
Dim tmp
tmp=Inputbox("","",strSQL)
als je de code nu uitvoert, zal de sql string in een tekstbox komen te staan. Deze kun je kopieren, en straks in een nieuwe query plakken als de procedure wordt beëindigd n.a.v. de fout.
Maak vervolgens een nieuwe query, selecteer geen tabel, en ga vervolgens naar het SQL scherm. Plak daar de code uit de inputbox, en probeer de query uit te voeren. Hij zal dan weer een fout geven, maar nu staat de cursor op de plek (veld) waar het fout gaat. Je kunt nu makkelijker zien wat je aan moet passen in de VBA.

 

[royvdh65]

Private Sub Knop13_Click()
XOn Error GoTo Err_Knop13_Click

Dim strSQL As String
Dim stDocName As String

strSQL = "SELECT dboServiceOrder.No_, dboMasterData.LinkedJobNo, [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION " _
& "FROM (dboServiceOrder INNER JOIN dboMasterData ON dboServiceOrder.No_ = dboMasterData.[NAV ItemCode]) " _
& "INNER JOIN dboMasterData AS [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1] " _
& "ON dboMasterData.LinkedJobNo = [dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].[NAV ItemCode]" _
& "WHERE ((dboServiceOrder.No_=" & Me.[ServOrdNr] & ") AND (dboMasterData.LinkedJobNo=" & Me.[JobNr] & ") " _
& "AND ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION<>'01' " _
& "Or ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION)='05') AND (dboMasterData.TableCode='SERV_ORD') " _
& "AND ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].TableCode='JOB_QZI'));"
Dim tmp
tmp = InputBox("", "", strSQL)


With CurrentDb.OpenRecordset(strSQL)
If .RecordCount > 0 Then
Me.CODEPOSITION.Value = .Fields(0).Value
End If
End With

Exit_Knop13_Click:
Exit Sub

Err_Knop13_Click:
MsgBox Err.Description
Resume Exit_Knop13_Click

End Sub

Bedoel je op de plek wat ik met rood aan heb gegeven? Want hier krijg ik geen tekstbox te zien bij het uitvoeren van het form.
Verder krijg ik een verticaal streepje na het uitvoeren in het begin van de code aangegeven met een X.

 

[OctaFish]

De inputbox staat op de goede plek. Als de uitvoer daar niet komt, dan zit er eerder een foutje. Waar precies wordt de fout geel gemarkeerd dan?

 

[royvdh65]

Er wordt verder ook niets geel gemarkeerd, bij het formulier wat ik erbij het gemaakt, heb ik verder ook geen query meer aangekoppeld omdat deze nu al in de code staat. Dus heb nu gewoon een formulier (in "wizard formulier") gemaakt en hierbij de kolomnamen erin gesleept van de verschillende tabellen waar deze in voor komen. Wanneer ik naar Extra ga en dan kijk bij relaties wordt het goede datamodel ook geopend, dus aan deze zaken zal het waarschijnlijk dan niet liggen. Verder heb ik bij de Rijbronnen van de twee invoervelden van JobNr dit staan: SELECT dbolbedrijfVDLGJob.No_ FROM dbolbedrijfVDLGJob; en bij ServOrdNr dit op dezelfde manier alleen dan voor ServOrdNr natuurlijk. Maar dit heeft er alleen mee te maken dat er een dropdownlijstje komt toch?
Zal er voor de SQLstring eerst nog gevraagd moeten worden, dat hij het huidige datamodel gebruikt? Wanneer ik naar Extra ga komt het juiste datamodel wel gewoon tevoorschijn.

 

[OctaFish]

Je relaties hebben op zich niet zoveel te maken met het probleem... Ik snap overigens de structuur van het formulier nog niet helemaal; je zegt dat het formulier niet is gekoppeld aan een query, maar je hebt het wel gemaakt met de wizard, door velden uit tabellen te slepen. Dat houdt automatisch in dat je een query gebruikt als recordbron.
De code op de keuzelijsten heeft verder niks met het formulier te maken; hier kies je inderdaad alleen de bijbehorende items, en die hebben dus hun eigen recordbron.
Als je op de knop drukt, en de code werkt niet, dan geeft Access aan dat er een fout is, en kun je via <Foutopsporing> naar het VBA scherm, en moet de foutieve regel geel gemarkeerd zijn. Dat zou dus de (combinatie van) de SQL regel(s) kunnen zijn, of een andere regel. Als de SQL string een fout geeft, dan is er een syntax fout gemaakt bij het typen, waardoor de regel an sich niet klopt. Het wil niet zeggen dat de query zelf fout is. Vandaar de inputbox, die de uit te voeren sql 'opvangt'. Als de inputbox al niet verschijnt, dan zit de fout dus eerder in het proces.
Het kan zijn, dat de de DAO declaraties nog in het proces hebt staan, en dat de bibliotheken niet (goed) geladen zijn. Controleer dat dus ook in het VBA scherm, in <Extra>, <Verwijzingen>.

 

[royvdh65]

Missing),], or Item in query expression’((dboServiceOrder.No_=SO0905061155..)AND (dboMasterData.LinkedJobNo=080770090) AND ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSTION<>’01’)Or ([dbo_Modelbedrijf VDLG$MBS_VINCI_QZI_MasterData_1].CODEPOSITION)=’05’) AND (dboMasterData.TableCode=’SERV_ORD’)’.

Dit is de foutmelding die ik op dit moment krijg, de where-clausule mist dus een ),] of item, hierbij heb ik even naar de code gekeken en dacht dat er een haakje miste aangegeven in het rood hierboven. Dit leverde weer de volgende foutmelding op: too few parameter, expected 1, daarbij krijg ik wel een uitvoer in excel alleen met alle records, met het juiste record bovenaan (want mijn query levert maar een record op), hierdoor wordt er dus ook geen status in het textveld van het forum geschreven.

 

[OctaFish]

Ik zie wel dat er spaties ontbreken bij sommige woorden:
SO0905061155..) AND
CODEPOSTION<>’01’) Or
Dus dat kun je eerst proberen. Ik heb in de oorspronkelijke code op haakjes gecontroleerd, maar ik zie zo gauw niks verkeerds. Overigens gebruik je tabelnamen die voor een buitenstaander erg lastig te lezen zijn...