VBA: ending a function within an if statement

[jong2k4]

I have a vexing problem that I'm encountering here, in an MS Access VBA function, made vexing by the fact that it seems like it should be very simple, but I just can't solve it.

Basically, I just have an if..then..else statement which is designed as a way of verifying that the user has selected the correct file to import a record from and is importing it into the correct place by comparing a number contained within the file to user-inputted testNumber; the two should match. This is not an essential part of the program, but I think it is important because I anticipate most of these records are going to be imported from portable media, each piece belonging to different researchers, so if somebody imported their file into the wrong record it would be hard to fix without going to the database backup, which I don't have great confidence that they will keep regularly when I'm gone.

Currently the way it functions is it simply compares the two numbers, if they match it tells the user so, if they don't it tells the user so and ends the function. Here's the code:

If testNumber = s Then
MsgBox ("The test number you inputted matched the test number of the file you selected, press OK to continue")
Else
MsgBox ("The test number you inputted did not match the test number of the file you selected, please ensure that these values are correct and try again")
End Function
End If

However, the compiler gives an error on the End Function statement, "Block If without End If." I'm not terribly great with VB, but what I think is happening is that when it encounters the End Function, that terminates the If block as well as the function, which is a syntax error because the If block has been terminated without an End If. What can I do to fix this error, or otherwise end the function if a condition is not met?

My current temporary solution is to have the msgbox advise the user to terminate the function by pressing control-pause/break.

[foolishone]

According to page 388 of one of my VB books, try "Exit Function", instead of "End Function".

^fo

[Sapporo]

Originally Posted by jong2k4 I have a vexing problem that I'm encountering here, in an MS Access VBA function, made vexing by the fact that it seems like it should be very simple, but I just can't solve it. Basically, I just have an if..then..else statement which is designed as a way of verifying that the user has selected the correct file to import a record from and is importing it into the correct place by comparing a number contained within the file to user-inputted testNumber; the two should match. This is not an essential part of the program, but I think it is important because I anticipate most of these records are going to be imported from portable media, each piece belonging to different researchers, so if somebody imported their file into the wrong record it would be hard to fix without going to the database backup, which I don't have great confidence that they will keep regularly when I'm gone. Currently the way it functions is it simply compares the two numbers, if they match it tells the user so, if they don't it tells the user so and ends the function. Here's the code: If testNumber = s Then MsgBox ("The test number you inputted matched the test number of the file you selected, press OK to continue") Else MsgBox ("The test number you inputted did not match the test number of the file you selected, please ensure that these values are correct and try again") End Function End If However, the compiler gives an error on the End Function statement, "Block If without End If." I'm not terribly great with VB, but what I think is happening is that when it encounters the End Function, that terminates the If block as well as the function, which is a syntax error because the If block has been terminated without an End If. What can I do to fix this error, or otherwise end the function if a condition is not met? My current temporary solution is to have the msgbox advise the user to terminate the function by pressing control-pause/break.

You need to move the "end if" inside the function by putting the "End If" on a blank line above "End Function"

edit: I see now you did not paste the entire function, I believe that Exit Function will work for what you are trying to do.

[jong2k4]

Exit Function worked quite nicely, thank you both. I thought I had tried it earlier, but there must have been some syntax error in the way when I did, and it works beautifully now.