CODEWITHSUNDEEP
ms-accessvba
Corey
Corey

Reputation: 1

VBA can't get if, elseif, else statement to work

I am trying to use a form field to check a table column value to prequalify a query to run this is the code that I have but I am getting a run time error.

' Set Warnings
DoCmd.SetWarnings False
If Forms!FrmCopyRoutingMenu!TextTarget = tblQuoteSection1Lines!tblQuoteMstrID Then
    MsgBox ("Quote Section 1 Already Started! Please Copy Individual Sections.")

ElseIf Forms!FrmCopyRoutingMenu!TextTarget = Table!tblQuoteSection2Lines!tblQuoteMstrID Then
    MsgBox ("Quote Section 2 Already Started! Please Copy Individual Sections.")

ElseIf Forms!FrmCopyRoutingMenu!TextTarget = Table!tblQuoteSection3Lines!tblQuoteMstrID Then
    MsgBox ("Quote Section 3 Already Started! Please Copy Individual Sections.")

ElseIf Forms!FrmCopyRoutingMenu!TextTarget = Table!tblQuoteSection4Lines!tblQuoteMstrID Then
    MsgBox ("Quote Section 4 Already Started! Please Copy Individual Sections.")

ElseIf Forms!FrmCopyRoutingMenu!TextTarget = Table!tblQuoteSection5Lines!tblQuoteMstrID Then
    MsgBox ("Quote Section 5 Already Started! Please Copy Individual Sections.")

Else

Upvotes: 0

Views: 321

Answers (1)

Leroy
Leroy

Reputation: 644

Do you want to check if the value in TextTarget is anywhere in the ID field of each table? Does this work?

Dim target As String
Dim Found As Boolean

target = Forms!FrmCopyRoutingMenu!TextTarget
Found = False

For i = 1 To 5
If DCount("tblQuoteMstrID", "tblQuoteSection" & i & "Lines", "tblQuoteMstrID = '" & target & "'") > 0 Then
    MsgBox ("Quote Section " & i & " Already Started! Please Copy Individual Sections.")
    Found = True
End If
Next i

If Not Found Then
    'code to run query goes here
End If

Upvotes: 2

Related Questions