Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
steve728

steve728

Programmer
Joined
Mar 16, 2003
Messages
536
Location
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Could F1 and V1 be put into a two dimensional array? That would be easy to do the above. How are you setting them?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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


 
Upvote 0 Downvote
Maybe something like this. Untested, not debugged.
Code: 
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

 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

hermanlaksko
  • Locked
  • Question Question
Convert PDF file to text
Replies
6
Views
878
hermanlaksko
hermanlaksko
breezett93
  • Locked
  • Question Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open
Replies
14
Views
3K
dhookom
dhookom
Moss100
  • Locked
  • Question Question
Remove everything including the comma from the last comma of a string.
Replies
10
Views
1K
Andrzejek
Andrzejek
Drago1920
  • Locked
  • Question Question
Finding the lowest date
Replies
2
Views
616
Andrzejek
Andrzejek
Zyborg
  • Locked
  • Question Question
Using a variable as reference to a form
Replies
5
Views
1K
Zyborg
Zyborg

Part and Inventory Search

Sponsor

Back
Top