No value give for one or more required parameters 2

[jadams0173]

I'm trying to run the query from VBA using Currentproject.Connection.Execute method and I get the error "No value given for one or more required parameters". Here is the query from code and a debug.print of the query.

sqlUpdate = "UPDATE " & TempTable & " SET " & TempTable & ".PRIMARYT = IIf([Forms]![FRMMAIN]![TB] ='T','" & Val([Forms]![FRMMAIN]![PrimLine]) & "','" & Val([Forms]![FRMMAIN]![PRIMARYT]) & "')," & TempTable & ".PRIMARYB = IIf([Forms]![FRMMAIN]![TB]='B','" & Val([Forms]![FRMMAIN]![PrimLine]) & "','" & Val([Forms]![FRMMAIN]![PRIMARYB]) & "') " _
    & "WHERE (((" & TempTable & ".MO_NUMBER)='" & txtMO & "') AND ((" & TempTable & ".TB)<>'X'))"

'DoCmd.RunSQL sqlUpdate, 129
Debug.Print sqlUpdate
CurrentProject.Connection.Execute sqlUpdate, , 129

Here is the debug.print

UPDATE SCHEDULE SET SCHEDULE.PRIMARYT = IIf([Forms]![FRMMAIN]![TB] ='T','97','0'),SCHEDULE.PRIMARYB = IIf([Forms]![FRMMAIN]![TB]='B','97','0') WHERE (((SCHEDULE.MO_NUMBER)='123456') AND ((SCHEDULE.TB)<>'X'))

I think it is this

([Forms]![FRMMAIN]![TB]='B'

that is causing the problem. But if I use

Val(([Forms]![FRMMAIN]![TB])='B'

it evaluates to 0 and not T or B since Val returns a Double.


I tried to have patience but it took to long! -DW

 

[Remou]

You are not allowed to refer to the form in the sql statement when you are using it is this way, you need an actual value:

[tt]sqlUpdate = "UPDATE " & TempTable & " SET " & TempTable & ".PRIMARYT = " _
& IIf([Forms]![FRMMAIN]![TB] ="T",Val([Forms]![FRMMAIN]![PrimLine]),Val([Forms]![FRMMAIN]![PRIMARYT])) _
& " TempTable & ".PRIMARYB = " _
& IIf([Forms]![FRMMAIN]![TB]="B",Val([Forms]![FRMMAIN]![PrimLine]),Val([Forms]![FRMMAIN]![PRIMARYB])) _
& " WHERE (((" & TempTable & ".MO_NUMBER)='" & txtMO & "') AND ((" & TempTable & ".TB)<>'X'))"[/tt]

 

[jadams0173]

Hey Remou! Thanks for the reply. I was pretty sure that was the case. But when I try to use the Val function on

[Forms]![FRMMAIN]![TB]="B" it returns 0 and the value of the text box. How do I get the actual value of the text box TB?

I tried to have patience but it took to long! -DW

 

[Remou]

Why are you using Val? Should this not end up reading:

[tt]UPDATE SCHEDULE SET SCHEDULE.PRIMARYT = '97',SCHEDULE.PRIMARYB = '97' WHERE (((SCHEDULE.MO_NUMBER)='123456') AND ((SCHEDULE.TB)<>'X'))[/tt]

According to whether IIf([Forms]![FRMMAIN]![TB] ="T" or not.

Ok, I see I missed some quotes:
[tt]sqlUpdate = "UPDATE " & TempTable & " SET " & TempTable & ".PRIMARYT = [red]'[/red]" _
& IIf([Forms]![FRMMAIN]![TB] ="T",Val([Forms]![FRMMAIN]![PrimLine]),Val([Forms]![FRMMAIN]![PRIMARYT])) _
& "[red]'[/red] TempTable & ".PRIMARYB = [red]'[/red]" _
& IIf([Forms]![FRMMAIN]![TB]="B",Val([Forms]![FRMMAIN]![PrimLine]),Val([Forms]![FRMMAIN]![PRIMARYB])) _
& "[red]'[/red] WHERE (((" & TempTable & ".MO_NUMBER)='" & txtMO & "') AND ((" & TempTable & ".TB)<>'X'))"[/tt]

Why are you using Val with [Forms]![FRMMAIN]![PrimLine]. What does PrimLine contain?

 

[jadams0173]

Hey Remou.

>>Why are you using Val with [Forms]![FRMMAIN]![PrimLine]. What does PrimLine contain?

PrimLine is a txt box on frmMain that holds a number and is stored as text in the temptable.


I'm using Val to get the actual values from the form to try to make the query run us the connection ojbect method. The query runs fine with Docmd.Runsql.

If I substitute T for IIf([Forms]![FRMMAIN]![TB]='T' to read IIF('T' = 'T',something,somthingelse) it works.

I'll have a look see at the missing quotes.

I tried to have patience but it took to long! -DW

 

[Remou]

Did you try with the SQL I suggested?

 

[jadams0173]

oops...I thought I had missed some quotes.

Why are you using Val? Should this not end up reading:

UPDATE SCHEDULE SET SCHEDULE.PRIMARYT = '97',SCHEDULE.PRIMARYB = '97' WHERE (((SCHEDULE.MO_NUMBER)='123456') AND ((SCHEDULE.TB)<>'X'))

No. The query should evaluate whether we are dealing with the T or B. If it is T then I update a value in the table with what the user put into the form ([PrimLine]) for T, but do not change B(PrimaryB) and the inverse of that is true if the value is B.

I tried to have patience but it took to long! -DW

 

[Remou]

I should have said 'something like'. Have a look at the line and see if it is returning what you expect.

 

[PHV]

And what about this ?

With Forms!FRMMAIN
  sqlUpdate = "UPDATE " & TempTable & " SET PRIMARYT='" _
 & .Controls(IIf(!TB = "T", "PrimLine", "PRIMARYT")) & "'" _
 & ",PRIMARYB='" & .Controls(IIf(!TB = "B", "PrimLine", "PRIMARYB")) & "'" _
 & " WHERE MO_NUMBER='" & txtMO & "' AND TB<>'X'"
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV your solution worked! Remou thanks for the help! Stars for the both of you.

I tried to have patience but it took to long! -DW

 

[jadams0173]

PHV,

Just out of curiosity, could my original query been made to work or is this the only practical solution?

I tried to have patience but it took to long! -DW

 

[PHV]

In fact, Remou missed a comma:
& "'[!],[/!] TempTable & ".PRIMARYB = '" _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886