IF Then Statements...HELP Please 1

[zishan619]

Hi everyone:
I have an if then statement that looks like this:
If DLookup("[account]", "tblFAP_Avoidance", "[account] =" & "'" & Forms![frmFAP_Avoidance]!account & "'&quot Then

msgbox "x", vbYesNo, "Admin" = vbYes
ElseIf vbYes Then

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
End if
I keep getting a Type Mismatch
And when it does work it does the same for Yes or No.
It ends the statement.Help Anyone
Thanks
Z

 

[judgehopkins]

Could you post the whole code?

E.g., I do not see where you declared strSQL. We can't solve the problem unless we see the entire code, okay?

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[zishan619]

The Whole Code:
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim strSQL As String
Dim strManager As String
Dim msg As String
Dim x As String
Dim Z As String

x = "This Account Number has been submitted already. Would you like to Submit it again?"
strSQL = "UPDATE tblFAP_Avoidance SET tblFAP_Avoidance.Status = '1' WHERE (((tblFAP_Avoidance.Analyst)=MYLogin()) AND ((tblFAP_Avoidance.account)=[Forms]![frmFAP_UPDATE]![account]));"

If DLookup("[account]", "tblFAP_Avoidance", "[account] =" & "'" & Forms![frmFAP_Avoidance]!account & "'&quot Then

msgbox("x", vbYesNo, "Admin&quot = vbYes
ElseIf vbOK Then

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFAP_Avoidance&quot

With rst
.AddNew
!account = Me![account]
!name1 = Me![name1]
!cycle = Me![cycle]
!Status = Me![Status]
!Avoidance_Amount = Me![Avoidance_Amount]
!Avoidance_Date = Me![Avoidance_Date]
!Analyst = Me![Analyst]
!Avoidance_Reason = Me![Avoidance_Reason]
!discover_date = Me![discover_date]
!open_date = Me![open_date]
!start_bal = Me![start_bal]

.Update

End With

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
msgbox "Your Avoidance has been submitted to the Manager Queue", vbInformation, "Admin"
Else
Exit Sub
End If

DoCmd.SetWarnings True

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
msgbox err.Description
Resume Exit_cmdSubmit_Click


End Sub

 

[judgehopkins]

I am not sure your syntax is correct. My programming skills are limited...I am trying to learn, just like you. Read the code below that works for me and also the article below.

I suspect you are trying to use a variable or variant you have not declared in your line: If DLookup("[account]", "tblFAP_Avoidance", "[account] =" & "'" & Forms![frmFAP_Avoidance]!account & "'&quot Then

http://www.experts-exchange.com/Manager/Databases/MS_Access/Q_20640502.html#8674197

Private Sub OrderNumber_AfterUpdate()
'This prevents a duplicate order number
'OrderNumber is a text field
Dim varTemp As Variant
'Initializes variant
varTemp = DLookup("[OrderNumber]", "Order", _
"[OrderNumber] = Forms![Order]![OrderNumber]&quot
'Checks to see if this order number has been used before
If varTemp = Forms!Order!OrderNumber Then
'If order number has been used before
Me!Refinance.SetFocus
Me!OrderNumber.SetFocus
'An Access bug makes you set the focus to the prior control on a form
'before you can re-set the focus to the control you want it on
Me.Undo
'Clears OrderNumber control on the form
MsgBox "You have entered a duplicate order number. Enter another.", _
vbOKOnly, "Duplicate Order Number Found"
End If

End Sub


"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[zishan619]

Well see the DLookUp Function Works.
Now I have a problem with the msgbox Function:
msgbox("This Account Number has been submitted already.Would you like to Submit it again?", vbOKCancel, "Admin&quot = vbCancel
It Is telling me
Compile Error
Function Call on Left-Hand Side of assignment must return Variant or Object.
HUH????
Hey Thank you for helping me out JudgeHopkins.

 

[OhioSteve]

I do not know the exact source of the problem. However, why use a code-intensive solution? If they hit this event, just kick off a macro. The macro can take them to a form that handles the situation.

 

[judgehopkins]

zishan619, it looks like the error message you are getting about the messagebox confirms my suspicions.

Look at the code that works for me; you are not doing the same thing as I did.

As to the debate over whether you should use macros, see thread181-293590!

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[janetlyn]

Judge, hello again. Onto other problems. My problem is similar except when someone selects a button I would like a message box to appear asking if they want one or two or they can cancel. If they want one it opens one form, if they want two it opens a different form. I got the message box part from above but cannot discern where the answer would go or how to get the program to stop until there is a choice made. Is it in your code and I'm just not seeing it? A good possibility. Thanks for your help. Janet Lyn

 

[judgehopkins]

jl, snippet that may come in use; I had already pasted it here while I went searching for the forms thread...which you found.

So, apropos of nothing, here's some code:

Private Sub Form_Unload(Cancel As Integer)
'When user clicks close, this confirmation box pops up;
'if user selects no, then Access returns user to the form
If MsgBox("Are you sure you want to exit?", _
vbYesNo, "Do you want to close?") = vbNo Then
    Cancel = True
End If

End Sub

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[duck72]

Regarding the MsgBox problem, I THINK it was already addressed. For clarity, you want

MyAnswer = MsgBox("Well?",vbYesNo)

which means "set MyAnswer equal to the value of MsgBox,"

rather than the reverse, which asks Access to set the function value equal to the current value of MyAnswer.

Jim Beard

 

[janetlyn]

Judge, after reviewing your code, trying to write my own and then using Access Help, I realized that what I want to do is not appropriate for message boxes. So I believe I will try a pop-up window. Never have done one of those before either, but it can't be that hard can it? Jim, I have absolutely no idea what you are saying. But, that is because I am ignorant. Thanks for your help guys, Janet Lyn

 

[judgehopkins]

A popup window is actually a form in Microsoft Access. When you use a pop up window, set its properties to border and modal. This means it will look like a dialog box and modal means the user has to do something before the form is closed.

You will have to make your own message and command buttons, etc., to go on the form, depending on what you want to do.

Take a look at Pop Up Forms--Build Your Own:

http://www.utteraccess.com/forums/s...=235185&page=0&view=collapsed&sb=5&o=&fpart=1

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[janetlyn]

Man you are an angel. Thank you so much. Maybe now it won't be so hard. Star to ya. Janet Lyn