Whats wrong with this code?? A97 XP SP2

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks - been round the houses with this code before I am sure, but once
again have a query in code that seems to think there are parameters to
fulfil!

Here's the code:

'Loop through all Library data
Do While Not rs_LibraryData.EOF
If rs_LibraryData![ccr_use_serv_cd] = True Then ' If
Service Code is to be used
str_field_to_apply = "lb_label_1"
'****************************************
str_ccr_to_apply = rs_LibraryData![ccr_ccr]
If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
Like & [str_field_to_apply] & " * "))"
Else ' If Not
Like
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
End If
'****************************************
Else ' If
Component to be used instead
'str_field_to_apply = "fc_main1"
'****************************************
'If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
'strSQL_to_run = ""
'Else ' If Not
Like
'strSQL_to_run = ""
'End If
'****************************************
End If
MsgBox strSQL_to_run
DoCmd.RunSQL (strSQL_to_run)

If rs_LibraryData.EOF Then Exit Do
Loop
Just working on the first part of the If - Loop for now.

************************************************************************************
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
************************************************************************************

This section is the first piece of offending code - and raises a parameter
input box. even though the variables have the correct information in them. I
seem to recall that A97 will not define the parameters in code and needs
some extra input to define the parameters.

Any help on what and where to input this code would be appreciated.

Cheers
 
Hi,
The code is *very* hard to read as posted.
One thing that does jump out is you have =Like in the 1st section.
 
Hi,
This section:

strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)= [str_field_to_apply]))"

You always have to evaluate and concatenate the variable in all versions of Access:

strSQL_to_run = "UPDATE qryFabricCondition SET qryFabricCondition.fc_ccr_value = '" & str_ccr_to_apply] & "'" & _
" WHERE (((qryFabricCondition.lb_label_1)= '" & str_field_to_apply & "'))"

You should be issuing a Debug.Print on *all* of your sql statements so you can easily see where the errors are.

--
HTH
-------
Dan Artuso, MVP


Kahuna said:
Hi Folks - been round the houses with this code before I am sure, but once
again have a query in code that seems to think there are parameters to
fulfil!

Here's the code:

'Loop through all Library data
Do While Not rs_LibraryData.EOF
If rs_LibraryData![ccr_use_serv_cd] = True Then ' If
Service Code is to be used
str_field_to_apply = "lb_label_1"
'****************************************
str_ccr_to_apply = rs_LibraryData![ccr_ccr]
If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
Like & [str_field_to_apply] & " * "))"
Else ' If Not
Like
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
End If
'****************************************
Else ' If
Component to be used instead
'str_field_to_apply = "fc_main1"
'****************************************
'If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
'strSQL_to_run = ""
'Else ' If Not
Like
'strSQL_to_run = ""
'End If
'****************************************
End If
MsgBox strSQL_to_run
DoCmd.RunSQL (strSQL_to_run)

If rs_LibraryData.EOF Then Exit Do
Loop
Just working on the first part of the If - Loop for now.

************************************************************************************
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
************************************************************************************

This section is the first piece of offending code - and raises a parameter
input box. even though the variables have the correct information in them. I
seem to recall that A97 will not define the parameters in code and needs
some extra input to define the parameters.

Any help on what and where to input this code would be appreciated.

Cheers
Click to expand...
 
Thanks Dan - that makes sense, seems like there should be an easier way to
write this mess though??

I know the code is a bit untidy at this point - just growing it and getting
the structure in place, and this is the first run for the routine. Got the
error on the first run through.

BTW Can BuildCriteria be used in this instance??

--
Kahuna
------------
Dan Artuso said:
Hi,
This section:

strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"

You always have to evaluate and concatenate the variable in all versions
of Access:

strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = '" & str_ccr_to_apply] & "'" & _
" WHERE (((qryFabricCondition.lb_label_1)= '"
& str_field_to_apply & "'))"

You should be issuing a Debug.Print on *all* of your sql statements so you
can easily see where the errors are.

--
HTH
-------
Dan Artuso, MVP


Kahuna said:
Hi Folks - been round the houses with this code before I am sure, but
once
again have a query in code that seems to think there are parameters to
fulfil!

Here's the code:

'Loop through all Library data
Do While Not rs_LibraryData.EOF
If rs_LibraryData![ccr_use_serv_cd] = True Then ' If
Service Code is to be used
str_field_to_apply = "lb_label_1"
'****************************************
str_ccr_to_apply = rs_LibraryData![ccr_ccr]
If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
strSQL_to_run = "UPDATE qryFabricCondition
SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
Like & [str_field_to_apply] & " * "))"
Else ' If Not
Like
strSQL_to_run = "UPDATE qryFabricCondition
SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
End If
'****************************************
Else ' If
Component to be used instead
'str_field_to_apply = "fc_main1"
'****************************************
'If rs_LibraryData![ccr_like] = True Then ' If
the
routine should use Like (for wildcards)
'strSQL_to_run = ""
'Else ' If
Not
Like
'strSQL_to_run = ""
'End If
'****************************************
End If
MsgBox strSQL_to_run
DoCmd.RunSQL (strSQL_to_run)

If rs_LibraryData.EOF Then Exit Do
Loop
Just working on the first part of the If - Loop for now.

************************************************************************************
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
************************************************************************************

This section is the first piece of offending code - and raises a
parameter
input box. even though the variables have the correct information in
them. I
seem to recall that A97 will not define the parameters in code and needs
some extra input to define the parameters.

Any help on what and where to input this code would be appreciated.

Cheers
Click to expand...
Click to expand...
 
Hi,
I've never used BuildCriteria, so maybe someone else could comment?

--
HTH
-------
Dan Artuso, MVP


Kahuna said:
Thanks Dan - that makes sense, seems like there should be an easier way to
write this mess though??

I know the code is a bit untidy at this point - just growing it and getting
the structure in place, and this is the first run for the routine. Got the
error on the first run through.

BTW Can BuildCriteria be used in this instance??

--
Kahuna
------------
Dan Artuso said:
Hi,
This section:

strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"

You always have to evaluate and concatenate the variable in all versions
of Access:

strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = '" & str_ccr_to_apply] & "'" & _
" WHERE (((qryFabricCondition.lb_label_1)= '"
& str_field_to_apply & "'))"

You should be issuing a Debug.Print on *all* of your sql statements so you
can easily see where the errors are.

--
HTH
-------
Dan Artuso, MVP


Kahuna said:
Hi Folks - been round the houses with this code before I am sure, but
once
again have a query in code that seems to think there are parameters to
fulfil!

Here's the code:

'Loop through all Library data
Do While Not rs_LibraryData.EOF
If rs_LibraryData![ccr_use_serv_cd] = True Then ' If
Service Code is to be used
str_field_to_apply = "lb_label_1"
'****************************************
str_ccr_to_apply = rs_LibraryData![ccr_ccr]
If rs_LibraryData![ccr_like] = True Then ' If the
routine should use Like (for wildcards)
strSQL_to_run = "UPDATE qryFabricCondition
SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
Like & [str_field_to_apply] & " * "))"
Else ' If Not
Like
strSQL_to_run = "UPDATE qryFabricCondition
SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
End If
'****************************************
Else ' If
Component to be used instead
'str_field_to_apply = "fc_main1"
'****************************************
'If rs_LibraryData![ccr_like] = True Then ' If
the
routine should use Like (for wildcards)
'strSQL_to_run = ""
'Else ' If
Not
Like
'strSQL_to_run = ""
'End If
'****************************************
End If
MsgBox strSQL_to_run
DoCmd.RunSQL (strSQL_to_run)

If rs_LibraryData.EOF Then Exit Do
Loop
Just working on the first part of the If - Loop for now.

************************************************************************************
strSQL_to_run = "UPDATE qryFabricCondition SET
qryFabricCondition.fc_ccr_value = [str_ccr_to_apply]" _
& " WHERE (((qryFabricCondition.lb_label_1)=
[str_field_to_apply]))"
************************************************************************************

This section is the first piece of offending code - and raises a
parameter
input box. even though the variables have the correct information in
them. I
seem to recall that A97 will not define the parameters in code and needs
some extra input to define the parameters.

Any help on what and where to input this code would be appreciated.

Cheers
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dan - that makes sense, seems like there should be an easier
way to write this mess though??
Click to expand...

There are lots of ways: I built a Class to encapsulate the whole lot, so
that it looks like this:

Set sqlTemp = New CSQLCommand
With sqlTemp
.Add "DELETE FROM Subs"
.Add "WHERE YearNum = " & lisCharges.Value
.Add " AND MemberNum = " & Me!MemberNum
' MsgBox .sql
.Execute

End With
Set sqlTemp = Nothing

Hope that helps


Tim F
 
Back
Top