VBA, What am I doing wrong?

Alle
#1 / 4

What am I doing wrong?

Can someone here please tell me why my queries won't run?
I am trying to run a update query based on what the
strInput is.

Function TableUpdate()

Dim strMsg1 As String
Dim strMsg2 As String
Dim strInput As String
Dim strQuery As String

strMsg1 = "Please type in the product line that you
want to be updated."
strInput = InputBox(Prompt:=strMsg1, Title:="Update
Tables")
strMsg2 = "You either did not specify a correct
product line or you left the line blank. Please click the
Retry button to restart or Cancel to exit."

If Len(strInput) < 2 Then Exit Function
'Runs a specific query based on the strInput3 Input box.
Select Case strQuery
Case "OPS"
strQuery = "qry_OPSUpdate"
Case "Extruded Foam"
strQuery = "qry_ExtrudedFoamUpdate"
Case "Cold Cup"
strQuery = "qry_ColdCupUpdate"
Case "Conex Deli"
strQuery = "qry_ConexUpdate"
Case "Impact Dinnerware"
strQuery = "qry_ImpactDinnerwareUpdate"
Case "Thermoform"
strQuery = "qry_ThermoFormUpdate"
Case "HIPS"
strQuery = "qry_HIPSUpdate"
Case Else
If MsgBox(strMsg2, vbRetryCancel, "Error!") =
vbRetry Then
Call TableUpdate
Else: Exit Function
End If
End Select
End Function

Any ideas why?

Sun, 21 Nov 2004 04:47:28 GMT

John
#2 / 4

What am I doing wrong?

It appears to me that you are setting the strQuery but
never running it. Betgween End Select and End Function
add this line.
DoCmd.OpenQuery strQuery
That will run the query.

Quote:

>-----Original Message-----
>Can someone here please tell me why my queries won't run?
>I am trying to run a update query based on what the
>strInput is.

>Function TableUpdate()

> Dim strMsg1 As String
> Dim strMsg2 As String
> Dim strInput As String
> Dim strQuery As String

> strMsg1 = "Please type in the product line that you
>want to be updated."
> strInput = InputBox(Prompt:=strMsg1, Title:="Update
>Tables")
> strMsg2 = "You either did not specify a correct
>product line or you left the line blank. Please click the
>Retry button to restart or Cancel to exit."

> If Len(strInput) < 2 Then Exit Function
>'Runs a specific query based on the strInput3 Input box.
> Select Case strQuery
> Case "OPS"
> strQuery = "qry_OPSUpdate"
> Case "Extruded Foam"
> strQuery = "qry_ExtrudedFoamUpdate"
> Case "Cold Cup"
> strQuery = "qry_ColdCupUpdate"
> Case "Conex Deli"
> strQuery = "qry_ConexUpdate"
> Case "Impact Dinnerware"
> strQuery = "qry_ImpactDinnerwareUpdate"
> Case "Thermoform"
> strQuery = "qry_ThermoFormUpdate"
> Case "HIPS"
> strQuery = "qry_HIPSUpdate"
> Case Else
> If MsgBox(strMsg2, vbRetryCancel, "Error!") =
>vbRetry Then
> Call TableUpdate
> Else: Exit Function
> End If
> End Select
>End Function

>Any ideas why?

>.

Sun, 21 Nov 2004 05:02:20 GMT

Ed Terlou
#3 / 4

What am I doing wrong?

Allen,
1) the select case strquery should be select case stringput
2) you have to execute the query by DoCmd.OpenQuery strQuery

Ed

Quote:

> Can someone here please tell me why my queries won't run?
> I am trying to run a update query based on what the
> strInput is.

> Function TableUpdate()

> Dim strMsg1 As String
> Dim strMsg2 As String
> Dim strInput As String
> Dim strQuery As String

> strMsg1 = "Please type in the product line that you
> want to be updated."
> strInput = InputBox(Prompt:=strMsg1, Title:="Update
> Tables")
> strMsg2 = "You either did not specify a correct
> product line or you left the line blank. Please click the
> Retry button to restart or Cancel to exit."

> If Len(strInput) < 2 Then Exit Function
> 'Runs a specific query based on the strInput3 Input box.
> Select Case strQuery
> Case "OPS"
> strQuery = "qry_OPSUpdate"
> Case "Extruded Foam"
> strQuery = "qry_ExtrudedFoamUpdate"
> Case "Cold Cup"
> strQuery = "qry_ColdCupUpdate"
> Case "Conex Deli"
> strQuery = "qry_ConexUpdate"
> Case "Impact Dinnerware"
> strQuery = "qry_ImpactDinnerwareUpdate"
> Case "Thermoform"
> strQuery = "qry_ThermoFormUpdate"
> Case "HIPS"
> strQuery = "qry_HIPSUpdate"
> Case Else
> If MsgBox(strMsg2, vbRetryCancel, "Error!") =
> vbRetry Then
> Call TableUpdate
> Else: Exit Function
> End If
> End Select
> End Function

> Any ideas why?

Sun, 21 Nov 2004 17:23:27 GMT

Graham R Seac
#4 / 4

What am I doing wrong?

Allen,

I showed Alen how to do this yesterday. He left out the most important piece
of code:
If Len(strQuery) > 0 Then DoCmd.OpenQuery strQuery

Graham R Seach
Microsoft Access MVP
Sydney, Australia

Quote:

> Can someone here please tell me why my queries won't run?
> I am trying to run a update query based on what the
> strInput is.

> Function TableUpdate()

> Dim strMsg1 As String
> Dim strMsg2 As String
> Dim strInput As String
> Dim strQuery As String

> strMsg1 = "Please type in the product line that you
> want to be updated."
> strInput = InputBox(Prompt:=strMsg1, Title:="Update
> Tables")
> strMsg2 = "You either did not specify a correct
> product line or you left the line blank. Please click the
> Retry button to restart or Cancel to exit."

> If Len(strInput) < 2 Then Exit Function
> 'Runs a specific query based on the strInput3 Input box.
> Select Case strQuery
> Case "OPS"
> strQuery = "qry_OPSUpdate"
> Case "Extruded Foam"
> strQuery = "qry_ExtrudedFoamUpdate"
> Case "Cold Cup"
> strQuery = "qry_ColdCupUpdate"
> Case "Conex Deli"
> strQuery = "qry_ConexUpdate"
> Case "Impact Dinnerware"
> strQuery = "qry_ImpactDinnerwareUpdate"
> Case "Thermoform"
> strQuery = "qry_ThermoFormUpdate"
> Case "HIPS"
> strQuery = "qry_HIPSUpdate"
> Case Else
> If MsgBox(strMsg2, vbRetryCancel, "Error!") =
> vbRetry Then
> Call TableUpdate
> Else: Exit Function
> End If
> End Select
> End Function

> Any ideas why?

Sun, 21 Nov 2004 22:26:27 GMT