VBA IF then Else's

[It_thug]

Okay. I'm still somewhat new to this, and I've been unable to find what I'm looking for. I don't have any code for this at this point, but I can try to demonstrate what I want. (Do not want anyone to write this for me, but nudge me in the right direction.)
TIA

Pseudo-codeish

SQL Query to pull data from a table with 6 fields.
Then, based on the query results

If Field 1 is the only one not null, than run Function1
If Field 1 and 2 are are not null than run Function2
etc...

Also, during this process the results of the query need to somehow populate to the TempVars, because the Functions refer to TempVar values


(Each field is always populated in order, Field 2 cannot be populated if Field 1 is not. )

Is this even possible?

 

[jzwp22]

You need to open a record set based on your query. I usually build the query directly in the code

'set up the connection
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

'set up the recordset
Dim myRS As New ADODB.Recordset
myRS.ActiveConnection = cnn1

'set up the query
dim mySQL as string

'build the query
mySQL= "SELECT ...."

'open the recordset
myRS.open mySQL

'Loop through the recordset
Do until myRS.EOF

IF Not IsNull(myRS!field1) and IsNull(myRS!field2) and IsNull(myRS!field3)...THEN
somevariable=functioncall(somevariable?)
END IF

IF Not IsNull(myRS!field1) and Not IsNull(myRS!field2)....THEN
somevariable=functioncall(somevariable?)
END IF


myRS.movenext
Loop

Now, if the tempvars is a field in the query you built then you have to open the recordset a little differently. If the tempvars is in another table you would open a second recordset or just construct & run an append or update query within the Loop. Not sure how to tell you without additional information.


Just curious why do you have 6 fields that appear to be releated? Could you explain what type of data are in these 6 fields and how they relate to the tempvars?

 

[It_thug]

Just curious why do you have 6 fields that appear to be releated? Could you explain what type of data are in these 6 fields and how they relate to the tempvars?

Thanks, that's a start for this.. I've used those loops before, but never in this fashion. So I appreciate this.

There may be another way to do this, but I can give you the quick answer.

I have a series of functions set up that pull up data in an ad-hoc way. (for up to 10 variables.)

But I'd like to be able to use those functions for something else as well, and am trying to figure out the changes that I'd need for both of them to work.

The process I'm trying to mold into this is where I have the variables that would be used already defined in a table, and then the Form Combobox would choose what Data is chosen.

I'd then like to have it choose which function to use, based on the return value of those (10) fields. (Since they are pre-defined I know that it's a progressive relationship.)

The Ad-hoc functions work exactly as I wish them to (with the SQL Statements built in, etc) and I'm just trying to come up with a way to use them for this process.

The above may be a bit garbled, so I apologize. But I do appreciate your help.

 

[jzwp22]

I was just wondering if the 10 fields should actually be 10 records in a table possibly with a field that describes the type. I didn't know if that type of structure would help to evaluate which function would be used.

I'm glad I was able provide some assistance even though I don't quite understand what you are doing. Post back with additional questions if needed.

 

[It_thug]

I was just wondering if the 10 fields should actually be 10 records in a table possibly with a field that describes the type. I didn't know if that type of structure would help to evaluate which function would be used.

I'm glad I was able provide some assistance even though I don't quite understand what you are doing. Post back with additional questions if needed.

Thanks. I'm not sure how it will all work out yet.
What prompted all of this is that I'm taking 3 different Access Tables with 3 distinct ways of pulling data and combining them all into 1. All three are similar in what they do but not quite the same and they reflect the knowledge from the time I originally built them. So they are all at different levels.

The one I'm converting now was built using 50 different macros that defined the variables used at the time. (Using the combo box and row source to pull the macro from the form).

But I'd rather do it differently this time if I could.

A bit more background.

There are 10 Functions built, basically the same function but each uses a different number of variables (Tempvars that are actually part of the SQL code)

The end result is to have the TempVars defined, and the proper function used.

I'm still working this through in my mind as well. But Once I figure out more I'll let you know. If I get the result I need, I'll post that code up as well.

Thanks again.

 

[Brianwarnock]

From what you have said as to how the fields are populated I think the series of 6 statements like this

IF Not IsNull(myRS!field1) and IsNull(myRS!field2) and IsNull(myRS!field3)...THEN
somevariable=functioncall(somevariable?)
END IF

IF Not IsNull(myRS!field1) and Not IsNull(myRS!field2)....THEN
somevariable=functioncall(somevariable?)
END IF

can be replaced by

If Not IsNull(myRS!field6) Then
Function6
ElseIf Not IsNull(myRS!field5) Then
Function5
ElseIf Not IsNull(myRS!field4) Then
Function4
ElseIf Not IsNull(myRS!field3) Then
Function3
ElseIf Not IsNull(myRS!field) Then
Function2
Else Function1
End If



Brian

.

 

[It_thug]

Thanks much all.... Here is what I ended up with

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRs As New ADODB.Recordset
Dim mySQL As String
myRs.ActiveConnection = myConnection

Dim VarName As String
VarName = TempVars!VarName



mySQL = "SELECT Itemname, ItemDescription, Function, Var_ID1, Var_ID2, Var_ID3, Var_ID4, Var_ID5, Var_ID6, Var_ID7, Var_ID8, Var_ID9, Var_ID10 " & _
"FROM Macros " & _
"WHERE (((Macros.Itemname)='" & VarName & "'));"

myRs.Open mySQL

GetDateRange
TempVars("SV").Value = (myRs!Var_ID1)
TempVars("SV2").Value = (myRs!Var_ID2)
TempVars("SV3").Value = (myRs!Var_ID3)
TempVars("SV4").Value = (myRs!Var_ID4)
TempVars("SV5").Value = (myRs!Var_ID5)
TempVars("SV6").Value = (myRs!Var_ID6)
TempVars("SV7").Value = (myRs!Var_ID7)
TempVars("SV8").Value = (myRs!Var_ID8)
TempVars("SV9").Value = (myRs!Var_ID9)
TempVars("SV10").Value = (myRs!Var_ID10)



If Not IsNull(TempVars!SV6) Then
Variable6
ElseIf Not IsNull(TempVars!SV5) Then
Variable5
ElseIf Not IsNull(TempVars!SV4) Then
Variable4
ElseIf Not IsNull(TempVars!SV3) Then
Variable3
ElseIf Not IsNull(TempVars!SV2) Then
Variable2
Else: Variable1
End If

which works exactly how I needed it to.

 

[It_thug]

couple extra Var_Ids in there right now.. but it's the gist