if-then problem

[vsevolodg]

Dear All,

Might be a stupid question but the value which I assign to a variable in the if-then statement gets lost after 'end if'. Below are 2 examples to illustrate that.

In first example I assign x=1 within if-then statement. Output with Debug.Print is nothing:


Private Sub Command2_Click()

Dim a, x As String

a = [Forms]![F_DB/HIPI5_2]![Combo0]

If a = All Then
x = 1
Debug.Print x
End If



End Sub


In second example I assign x=1 after if-then statement. Output with Debug.Print is '1':

Private Sub Command2_Click()

Dim a, x As String

a = [Forms]![F_DB/HIPI5_2]![Combo0]

If a = All Then
End If

x = 1
Debug.Print x

End Sub


My ultimate goal is to select with a combo box a query which is to be exported. The code was supposed to be the following (there are more options of 'a'):

Private Sub Command2_Click()
Dim a, x As String

Dim db As DAO.Database
Dim b As QueryDef
Set db = Currentdb()

a = [Forms]![F_DB/HIPI5_2]![Combo0]

If a = All Then

Set b = db.QueryDefs("Q_DB/Amounts_Output/CMO")

End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, b, "CMO", True
MsgBox ("Export complete")


End Sub


Thank you for help.

 

[spikepl]

at the top of your code module put

Option Explicit

 

[plog]

the value which I assign to a variable in the if-then statement gets lost after 'end if'

No it doesn't, there's nothing for x to lose because x never gets set. In the first example if the condition is never x never gets set to anything. You can verify this by assigning the x a value prior to the If statement (i.e. x=0).

In the second example you explicity set a value to x without regard to any conditional statement, so it will always have a value.

 

[Tim Curtis]

If you are going to have a number of options for A, I would recommend using a case statement rather than multiple if, then, end ifs

e.g,

Select Case A

Case "All"
b = ......

Case "Some"
b = ....

Case Else
b = ....

End Select

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, b, "CMO", True
MsgBox ("Export complete")

That way you guarantee that b has a value and is not null (which unles you error trap or check with an isnull statement) can gause an error.

Hope that helps

 

[vbaInet]

No it doesn't, there's nothing for x to lose because x never gets set. In the first example if the condition is never x never gets set to anything. You can verify this by assigning the x a value prior to the If statement (i.e. x=0).

... so your condition is never met. Look:

If a = All Then

solve the red bit.

 

[tehNellie]

With no Option Explict set All is a variant Datatype Variable

A is also a variant datatype when you presumably meant it to be a string.

the contents of the variable A doesnt equal the contents of the variable All, which is empty, so you never set a value to x

With Option Explicit set All throws an error as an undeclared variable.