What's wrong with this code?

[gerard1979]

code below is written in Access 2002.
Error: some required parameters forgotten

Strange detail: I have the same code for updating autonumbers. give no troubles!

I hope someone can help me,
Tnx in advance

Private Sub SelToewijzing_Click()
On Error GoTo err_selToewijzing

Dim strBericht As String
Dim Buildstr, strSQL, werknr As String
Dim cnn As Connection
Dim aantal As Integer
Dim varpositie As Variant

MsgBox "werkt nog niet"
Exit Sub
'controleer of een waarde is geselecteerd in ToewijzenAan
If IsNull(ToewijzenAan) Or ToewijzenAan.Value = "" Then
BerichtWeergeven "U moet een werknemer selecteren om de offertes aan toe te wijzen."
Exit Sub
End If
'Zorg dat de selectie gemaakt is
If OfferteLijst.ItemsSelected.Count = 0 Then
BerichtWeergeven "U moet offertes selecteren om toe te wijzen aan " & Me.ToewijzenAan.Column(0)
Exit Sub
End If

'geef een zandloper weer tijdens het toewijzen
DoCmd.Hourglass True

Buildstr = ""
'loop door de lijst van geselecteerde projecten heen
For Each varpositie In OfferteLijst.ItemsSelected
werknr = OfferteLijst.ItemData(varpositie)
OfferteLijst.Selected(varpositie) = False
Buildstr = Buildstr & "werknummer = '" & werknr & "' or "
Next varpositie
Buildstr = Mid(Buildstr, 1, Len(Buildstr) - 4)
Set cnn = CurrentProject.Connection
cnn.BeginTrans
strSQL = "UPDATE offerte SET [gevolgd_door] = '" & ToewijzenAan & "' WHERE" & Buildstr
cnn.Execute strSQL, aantal
If Bevestigen("U staat op het punt om " & aantal & " offertes toe te wijzen aan " & ToewijzenAan.Column(0) & "." & vbNewLine _
& "Wilt u hiermee doorgaan?&quot Then
cnn.CommitTrans
Else
cnn.RollbackTrans
DoCmd.Hourglass False
Exit Sub
End If
'werk het formulier bij
DoCmd.Hourglass False
Offvan = ToewijzenAan
ToewijzenAan = Null
Set cnn = Nothing
OfferteLijst.Requery
AantalInLijst = OfferteLijst.ListCount


err_selToewijzing:
If Err.Number = 0 Then
Response = acDataErrContinue
Else
MsgBox Err.Description
cnn.RollbackTrans
DoCmd.Hourglass False
Exit Sub
End If
End Sub

 

[hermanlaksko]

I have had a look at you code and it lookes fine to me.
But as I do not have you App you should let us know where the code crashes, let us know what line that gives you the problem.

 

[gerard1979]

cnn.Execute ask for more required parameters, very strange
same code works fine with autonumbers but with strings seems not correct.
I have no idea and I'm very interested in people who had the same problem and tackles it

 

[btaber]

After the strSql, do a debug.print strSql, and check the SQL string, you may be missing a quote somewhere

 

[gerard1979]

the StrSQL must be correct
I debug the code and he give the error in cnn.execute
this is in a lower line.
Any other suggestions?
Thnx
Gerard

 

[ppj]

btbaber may be correct. StrSQL is a string when building - so it may be a valid string but when passed to an execute command it may not be a valid SQL statement. Just by quick glance at your code put a space after the keyword WHERE. It may also need brackets to stop further ambiguity. Hope this helps.

 

[gerard1979]

I'm wondering ur sharp eyes mr jjp! You're right about the space, had already correct it. but what about the ambiguaty (can't understand the word, sorry, but english isn't my motherlanguage)

 

[ppj]

In your code I am sure the ambiguity would not come into it. I recently had in mind a similar problem when working with logical AND and OR operators using brackets makes sure that you get what you request. Sorry for confusing you. Did the space solve your problem?

 

[gerard1979]

nope.

 

[ppj]

I would first proceed with btaber's offer. Check on field names being miss-spelt you don't know how many times I have introduced a parameter by spelling-spelling a field name.

Another peculiar problem I have come across is to change the order the references appear in.

Another problem hit before, that may help: What type of database are you connecting to, if Access finish the SQL off by adding ";" at the end - this worked in one of my past tasks.

 

[btaber]

You do need the semi-colin ; after the statement, access is expecting it. If you can do the debug, post the SQL it returns so we can take a look

 

[gerard1979]

This is the debugged string:
UPDATE offerte SET [gevolgd_door] = 'DK' WHERE werknummer = '0.09.315' or werknummer = '0.09.434' or werknummer = '1.00.228';
The problem is: Exactly the same code is working in the following example;
loop door de lijst van geselecteerde projecten heen
For Each varpositie In ProjectenLijst.ItemsSelected
nummer = ProjectenLijst.ItemData(varpositie)
ProjectenLijst.Selected(varpositie) = False
Buildstr = Buildstr & "leadnummer = " & nummer & " or "
Next varpositie
Buildstr = Mid(Buildstr, 1, Len(Buildstr) - 4)
Set cnn = CurrentProject.Connection
cnn.BeginTrans
strSQL = "UPDATE PROJECT SET gevolgd_door = '" & ToewijzenAan.Column(1) & _
"' WHERE " & Buildstr
cnn.Execute strSQL, aantal
If Bevestigen("U staat op het punt om " & aantal & " projecten toe te wijzen aan " & ToewijzenAan.Column(0) & "." & vbNewLine _
& "Wilt u hiermee doorgaan?&quot Then
cnn.CommitTrans
Else
cnn.RollbackTrans
DoCmd.Hourglass False
Exit Sub
end if

some dutch text in it, but code must be clear.

I can't understand this kind of problem
in the execute he asked for more required parameters
Gerard

 

[gerard1979]

thnx to both of u.
There was a misspelled field (@##$#*(&*&$
was confused by the error description, so hadn't check-out good enough.
A better look gives the result.
Some simple things cost to much time,

Gerard