Eval() dosen't seem to be the solution? 1

[steve728]

I want to use a counter increment so that I can loop F1 to F5 I don't want to create 3 (actually I'm trying to avoid creating 50) If/EndIf blocks of code. Can someone help me?

' Initialized memvars
F1 = "PO_Num"
V1 = "0001"
F2 = "Vendor_Num"
V2 = "0002"
F3 = "PO_Num"
V3 = "0001"
TableName = "MyTable"
QueryStr = "UPDATE " & Tablename & " SET "
mCtr = 1

Do While mCtr <= 3
If Not IsNull( Eval("F" & Trim(Str(mCtr)) ) Then
QueryStr = QueryStr & Eval(FValue & " = '" & Eval(VValue) & "';"
End If
mCtr = mCtr + 1
Loop

The way that it works:
Do While mCtr <= 3
If Not IsNull(F1) Then
QueryStr = QueryStr & F1 & " = '" & V1 & "';"
End If
If Not IsNull(F2) Then
QueryStr = QueryStr & F2 & " = '" & V2 & "';"
End If
If Not IsNull(F3) Then
QueryStr = QueryStr & F3 & " = '" & V3 & "';"
End If
mCtr = mCtr + 1
Loop

 

[steve728]

I meant to say "I want to use a counter increment so that I can loop F1 to F3".

Sorry. Please come to my rescue cyber buddies.
This is a really important one for me.

Steve

 

[MajP]

Could F1 and V1 be put into a two dimensional array? That would be easy to do the above. How are you setting them?

 

[MajP]

If it could then.

Public Sub defineArray()
Dim myArray(1 To 3, 1 To 2) As String
myArray(1, 1) = "PO_Num"
myArray(1, 2) = "0001"
myArray(2, 1) = "Vendor_Num"
myArray(2, 2) = "0002"
myArray(3, 1) = "PO_Num"
myArray(3, 2) = "0003"
Debug.Print queryStr(myArray)
End Sub

Public Function queryStr(myArray() As String) As String
Dim iCounter As Integer
For iCounter = 1 To UBound(myArray)
If Not myArray(iCounter, 1) = "" Then
queryStr = queryStr & " " & myArray(iCounter, 1) & " = " & myArray(iCounter, 2) & ";"
End If
Next iCounter
If Right(queryStr, 1) = ";" Then
queryStr = Left(queryStr, Len(queryStr) - 1)
End If
End Function

output
PO_Num = 0001; Vendor_Num = 0002; PO_Num = 0003

But if
myArray(1, 1) = "PO_Num"
myArray(1, 2) = "0001"
'myArray(2, 1) = "Vendor_Num"
myArray(2, 2) = "0002"
myArray(3, 1) = "PO_Num"
myArray(3, 2) = "0003"

Then output
PO_Num = 0001; PO_Num = 0003

 

[steve728]

Wow! Talk about helpful details. Thank you so much! I'll try it in the morning. I'm pooped now. Thank you sooo
much again! You obviously get a star! I'll let you know how I do.

Steve G.

 

[steve728]

Before hitting the sack, I'll give you the basic code I created. I pray that you can show me what I'm doing wrong.
God Bless you buddy!

Public Function VBAUpdateMethodBU(MODE As String, Tablename As String, Where1 As String, Key1 As Variant, ParamArray Arguments() As Variant)

If Trim(MODE) = "" Then
MsgBox "The MODE field must be filled in!"
Exit Function
ElseIf Trim(Tablename) = "" Then
MsgBox "The TableName field must be filled in!"
Exit Function
Else
MODE = Trim(UCase(MODE))
End If

Dim mArgs As Integer, QueryStr As String, mLen As Integer

' We need a way to create only the memvars that are required based on the user's input
' For now, until I can get some help, I am just setting up enough for 5 field/DataReplacements
' I need to be able to handle up to approximate 40-50 sets!
Dim F1 As String, F2 As String, F3 As String, F4 As String, F5 As String
Dim v1 As Variant, V2 As Variant, V3 As Variant, V4 As Variant, V5 As Variant


' Use UBound function to determine upper limit of ParamArray()
' Note that entering one field/data yields a UBound(Arguments())+1 value of 2
' entering two field/data yields a UBound(Arguments())+1 value of 4
' entering three field/data yields a UBound(Arguments())+1 value of 6, etc.

For mArgs = 0 To UBound(Arguments())

' The following allows up to 26 field udpates. Add more when confirmed that this works.
' Hopefully Danny and I will figure out a way create only the what we need on the fly based
' on what was entered into the ParamArray() above using the value of UBound(Arguments()) as a basis.

Select Case mArgs + 1
Case 1
F1 = Arguments(mArgs) ' First field value of ParamArray
Case 2
v1 = Arguments(mArgs) ' First data update value of ParamArray
Case 3
F2 = Arguments(mArgs) ' Second field value of ParamArray
Case 4
V2 = Arguments(mArgs) ' Second data update value of ParamArray, etc.
Case 5
F3 = Arguments(mArgs)
Case 6
V3 = Arguments(mArgs)
Case 7
F4 = Arguments(mArgs)
Case 8
V4 = Arguments(mArgs)
Case 9
F5 = Arguments(mArgs)
Case 10
V5 = Arguments(mArgs)
End Select

Next mArgs

If Trim(UCase(MODE)) = "UPDATE" Then

QueryStr = "UPDATE " & Tablename & " SET "
' I definitely need a Foxpro type macro substitution to help reduce the following loop!
' Maybe an array like MajP is suggesting!!!
Do While True

If Trim(F1) <> "" Then ' First pair of field/update data
QueryStr = QueryStr & F1
Select Case TypeName(v1)
Case "String"
QueryStr = QueryStr & " = '" & v1 & "';"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & v1 & ";"
Case "Date"
QueryStr = QueryStr & " = #" & v1 & "#;"
End Select
Else
MsgBox "You MUST submit at least one field to update!"
Exit Function
End If

If Trim(F2) <> "" Then ' Second pair of field/update data
' Remove the semicolon from the existing query string first.
mLen = Len(QueryStr)
QueryStr = Left(QueryStr, (mLen - 1))
QueryStr = QueryStr & ", " & F2
Select Case TypeName(V2)
Case "String"
QueryStr = QueryStr & " = '" & V2 & "';"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & V2 & ";"
Case "Date"
QueryStr = QueryStr & " = #" & V2 & "#;"
End Select
Else ' If no more parameters to update, exit loop
Exit Do
End If

If Trim(F3) <> "" Then
' Remove the semicolon from the existing query string first.
mLen = Len(QueryStr)
QueryStr = Left(QueryStr, (mLen - 1))
QueryStr = QueryStr & ", " & F3
Select Case TypeName(V3)
Case "String"
QueryStr = QueryStr & " = '" & V3 & "';"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & V3 & ";"
Case "Date"
QueryStr = QueryStr & " = #" & V3 & "#;"
End Select
Else ' If no more parameters to update, exit loop
Exit Do
End If

If Trim(F4) <> "" Then
' Remove the semicolon from the existing query string first.
mLen = Len(QueryStr)
QueryStr = Left(QueryStr, (mLen - 1))
QueryStr = QueryStr & ", " & F4
Select Case TypeName(V4)
Case "String"
QueryStr = QueryStr & " = '" & V4 & "';"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & V4 & ";"
Case "Date"
QueryStr = QueryStr & " = #" & V4 & "#;"
End Select
Else ' If no more parameters to update, exit loop
Exit Do
End If

If Trim(F5) <> "" Then
' Remove the semicolon from the existing query string first.
mLen = Len(QueryStr)
QueryStr = Left(QueryStr, (mLen - 1))
QueryStr = QueryStr & ", " & F5
Select Case TypeName(V5)
Case "String"
QueryStr = QueryStr & " = '" & V5 & "';"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & V5 & ";"
Case "Date"
QueryStr = QueryStr & " = #" & V5 & "#;"
End Select
Else ' If no more parameters to update, exit loop
Exit Do
End If

Loop

Else
' I'll finish this based on the same technique used above for "UPDATE" once
' I determine how to shorten the code with a equivalent of Foxpro macro substitution
'QueryStr = "INSERT INTO " & TableName & " (" & F1 & ") Values (" & v1

End If

Call OpenConnection
CurrentProject.Connection.Execute (QueryStr), , 129
adoConnection.Close

End Function

 

[MajP]

Maybe something like this. Untested, not debugged.

Public Function VBAUpdateMethodBU(MODE As String, Tablename As String, Where1 As String, Key1 As Variant, ParamArray varArguments() As Variant) As String
   'Arguments could be a reserved word. Confusing as a variable name.
      
   Dim mArgs As Integer
   QueryStr As String
   mLen As Integer
   Dim intParamCounter As Integer
   If Trim(MODE) = "" Then
      MsgBox "The MODE field must be filled in!"
      Exit Function
   ElseIf Trim(Tablename) = "" Then
      MsgBox "The TableName field must be filled in!"
      Exit Function
   Else
      MODE = Trim(UCase(MODE))
   End If
   If Trim(UCase(MODE)) = "UPDATE" Then
      QueryStr = "UPDATE " & Tablename & " SET "
   End If
   If IsNull(varArguments()) Then
     'Not sure if this is how you check to see if the array is empty
     'you will have to try
     MsgBox "You MUST submit at least one field to update!"
     Exit Function
   Else
   For intParamCounter = 0 To UBound(Arguments)
    'If I understand the parameters are passed in as
    'F1,V1,F2,V2,F3,V3 etc as
      If Not Trim(varArguments(intParamCounter) & " ") = "" Then
         QueryStr = QueryStr & varArguments(intParamCounter) 'F values
         Select Case VarType(varArguments(intParamCounter + 1)) 'V values
            Case vbString
               QueryStr = QueryStr & " = '" & varArguments(intParamCounter + 1) & "',"
            Case vbDate
               QueryStr = QueryStr & " = #" & varArguments(intParamCounter + 1) & "#,"
            Case Else
               QueryStr = QueryStr & " = " & varArguments(intParamCounter + 1) & ","
         End Select
      End If
      Next intParmCounter
   End If
   QueryStr = Left(QueryStr, Len(QueryStr - 1)) 'strip off the comma
   'not sure about the syntax, but do not think you need a semicolon
   Call OpenConnection
   CurrentProject.connection.Execute (QueryStr), , 129
   ADOConnection.Close
   VBAUpdateMethodBU = QueryStr 'for debug purposes
End Function

 

[steve728]

Wow! I'm so anxious to apply this code. I have a friend that is very familiar with arrays. I'm sure that with the latest code you sent me plus his help I will acheive my objectives. If I succeed I will be sending in another donation to Tek-Tips! I so appreciate you cyber buddies out there! You've been so helpful! On and on...

I'll let you know what the results are.

Steve G.
P.S. I gave you another well deserved star!

 

[steve728]

Well MajP, you really helped me. Below is the final code that works:

Public Function VBAUpdateMethod(MODE As String, Tablename As String, Where1 As String, Key1 As Variant, ParamArray Arguments() As Variant)

Dim mArgs As Integer, QueryStr As String, mLen As Integer, IntParamCounter As Integer, mIncrement As Integer

If Trim(MODE) = "" Then
MsgBox "The MODE field must be filled in!"
Exit Function
ElseIf Trim(Tablename) = "" Then
MsgBox "The TableName field must be filled in!"
Exit Function
Else
MODE = Trim(UCase(MODE))
End If

If Trim(UCase(MODE)) = "UPDATE" Then
QueryStr = "UPDATE " & Tablename & " SET "
Else
QueryStr = "INSERT INTO " & Tablename & " ("
End If

If IsNull(Arguments()) Then
MsgBox "You MUST submit at least one field to update!"
Exit Function
Else
Dim mPass As Integer
mPass = 0
MsgBox "UBound(Arguments) = " & UBound(Arguments)
mCtr = 0

For IntParamCounter = 0 To UBound(Arguments)
If mPass > 1 Then
mIncrement = mIncrement + 1
Else
mIncrement = 1
End If
If (IntParamCounter + mIncrement) > UBound(Arguments) Then
Exit For
End If

If Trim(Arguments(IntParamCounter) & " ") <> "" Then
If mPass > 0 Then
QueryStr = QueryStr & Arguments(IntParamCounter + mIncrement) 'Field name
Else
QueryStr = QueryStr & Arguments(IntParamCounter) 'Field name
End If

If mPass > 0 Then

Select Case TypeName(Arguments(IntParamCounter + (mIncrement + 1))) 'Replacement data value
Case "String"
QueryStr = QueryStr & " = '" & Arguments(IntParamCounter + (mIncrement + 1)) & "',"
Case "Date"
QueryStr = QueryStr & " = #" & Arguments(IntParamCounter + (mIncrement + 1)) & "#,"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & Arguments(IntParamCounter + (mIncrement + 1)) & ","
End Select

Else

Select Case TypeName(Arguments(IntParamCounter)) 'Replacement data value
Case "String"
QueryStr = QueryStr & " = '" & Arguments(IntParamCounter + mIncrement) & "',"
Case "Date"
QueryStr = QueryStr & " = #" & Arguments(IntParamCounter + mIncrement) & "#,"
Case "Integer", "Double", "Boolean"
QueryStr = QueryStr & " = " & Arguments(IntParamCounter + mIncrement) & ","
End Select

End If

End If
mPass = mPass + 1
Next IntParamCounter

End If

mLen = Len(QueryStr)
QueryStr = Left(QueryStr, (mLen - 1)) 'remove the trailing comma
Call OpenConnection
CurrentProject.Connection.Execute (QueryStr), , 129
adoConnection.Close

End Function

Thank you so much Buddy!
Now I need to donate some $ to Tek-Tips

Steve

 

[MajP]

Glad to help.