Using a variable in a recordset If statement

[rileyjm]

I'm trying to pass a variable in an If statement within a recordset. If I explicitly enter the variable value, the code works fine. I've tried several variations to try and properly reference the variable 'App1'. Here's my latest snippet of code that shows my problem:

With rst
If ((Application = " ' & App1 & ' ") Or _
(doc.FormFields("DropDown7").Result = " ' & App1 & ' ") Or _
(doc.FormFields("RBITApp1").Result = " ' & App1 & ' ") Or _
(doc.FormFields("RBITApp2").Result = " ' & App1 & ' ") Or _
(doc.FormFields("RBITApp3").Result = " ' & App1 & ' ")) Then


Am I missing a double quote or single quote somewhere?

 

[vbaInet]

You're performing a comparison:

If Application = App1 Or _
   doc.FormFields("DropDown7") = App1 Or _
   doc.FormFields("RBITApp1") = App1 Or _
   doc.FormFields("RBITApp2") = App1 Or _
   doc.FormFields("RBITApp3") = App1 Then

What is .Result? It doesn't exist.

 

[JoanneJames]

1. Have you tried just using App1?

2. Tried " ' " & App1 & " ' " ?

3. Using Option Explicit to make sure var name is correct?

 

[rileyjm]

Just App1 worked. Thanks!!!

 

[vbaInet]

Happy to hear. Just to mention the equivalent of .Result is .Value if that's what you were trying to write. But .Value is optional.

 

[rileyjm]

Ah, Gotcha...I removed the .Result.

Hmmm...now I'm trying to run an Update SQL statement using that same variable App1. It doesn't like it when I don't use any quotes around it so I tried several combos but can't get it to run:

Here's my latest attempt:
'DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET" & "[" & App1 & "]" & "= 0 WHERE [DM #]=" & DM

 

[vbaInet]

DoCmd.RunSQL "UPDATE [tbl_RIF-App Info] SET [" & App1 & "] = 0 WHERE [DM #] = " & DM & ";"

You also needed a space after SET.

 

[rileyjm]

I tried your code but it throws a
Run-Time error '3126': Invalid bracketing of name '[]'.

I've tried a few more variations but can't get it to work...

 

[vbaInet]

What does App1 return? A field name?

 

[rileyjm]

Yes, I have over 100 apps in my database and I reference the fields throughout the code. Sometimes these app names can change so I have them all defined as Public string variables:

For example:

Public App1 As String
App1 = "BDS-ST"

 

[vbaInet]

dim strSQL as string

strSQL = "UPDATE [tbl_RIF-App Info] SET [" & App1 & "] = 0 WHERE [DM #] = " & DM & ";"
DoCmd.RunSQL strSQL

 

[rileyjm]

Thanks again! That worked!!!

 

[vbaInet]

You're welcome!!!