If IfElse Else - Dont understand why this isnt working

[snoopy]

He guys,

I have a simple ifelse branch (see attached). It works as follows when a user sets a value through a control form and one or another or both field(s) is/are not set (in my case two fields either the first or second or both) a error message occurs (for that I use a other function) which notice that the user should add a value (field1) or activate the checkbox (field 2). So the code is working for field1, but not for field 2, but why isnt that working? The code seems to be very clear as well as clean.

Sorry comments are in German, if one needs English I could translate it. Would be very cool if somebody could help me out.

---
Private Sub Combo471_AfterUpdate()

'Variablendeklarationen
Dim strTitle As String
Dim strPormpt As String
strTitle = "Achtung!"
strPrompt = "Sie haben blablabla angegeben. Jedoch liegt ihr angegebenes blabla (" & Me![subformB].Form![Field1] & ") in der Vergangenheit." _
& vbCrLf & "Bitte prüfen Sie, ob dies so korrekt ist. Andernfalls Bitte ändern Sie das blabla entsprechend."


'Prufen ob Feld blabla leer, bei gleichzeitigen blablabla 3 - 5
If IsNull(Me![subformB].Form![Field1]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
'Wenn dem so ist, Aufruf einer Msg.Box, extern
Select Case MsgBox2(Title:="Achtung", _
Prompt:="Sie haben blablabla " & Me.Combo471 & " angegeben aber kein blabla." & vbCrLf & "Bitte geben Sie ein entsprechendes blabla an", _
Buttons:=vbButton2 + vbInformation, _
UserButton1:="Jahr manuell eintragen", _
UserButton2:="Aktuelles Jahr eintragen")
Case vbButton1
MsgBox "Ok, blabla kann manuell eingetragen werden!", vbOKOnly + vbInformation, "blabla wird so belassen"
Case Else
MsgBox "Aktuelles Jahr wird eingetragen!", vbOKOnly + vbInformation, "Aktuelles Jahr"
Me![subformB].Form![Field1] = Year(Now())
End Select
'Falls nicht zutrifft dann pruefen,ob Hersteller ab blablabla 3 als guck gekennzeichnet ist oder nicht ob
ElseIf IsNull(Me![subformA].Form![marr]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
'Aufruf der selben Messagebox, wie bei anderen Routinen
Select Case MsgBox2(Title:="Achtung", _
Prompt:="Sie haben den blablabla" & Me.Combo471 & " angegeben aber denhossa nicht als guck gekennzeichnet" & vbCrLf & "Bitte kennzeichnen Sie den als guck.", _
Buttons:=vbButton2 + vbInformation, _
UserButton1:="hossa als guck kennzeichnen", _
UserButton2:="marr manuell kennzeichnen")
Case vbButton1
MsgBox "hossa wird als guck gekennzeichnet!", vbOKOnly + vbInformation, "hossa guck"
Me![subformA].Form![marr].Value = True
Case Else
MsgBox "Ok,hossa wird manuell als guck gekennzeichnet!", vbOKOnly + vbInformation, "hossa nicht guck"
End Select
'Als dritte Alternative pruefen, bei blablabla 3 als Fall, ob blabla in der Verangenheit
ElseIf Me!Combo471 = "3" And Me![subformB].Form![Field1] < Year(Now()) Then
Select Case MsgBox2(Title:=strTitle, _
Prompt:=strPrompt, _
Buttons:=vbButton3 + vbInformation, _
UserButton1:="blabla so belassen", _
UserButton2:="Aktuelles Jahr eintragen", _
UserButton3:="Derzeitiges blabla löschen und neues manuell eintragen")
Case vbButton1
MsgBox "Ok, blabla wird so belassen", vbOKOnly + vbInformation, "blabla wird so belassen"


Case vbButton2
MsgBox "Aktuelles Jahr wird eingetragen!", vbOKOnly + vbInformation, "Aktuelles Jahr"
Me![subformB].Form![Field1] = Year(Now())
Case Else
MsgBox "blabla wird gelöscht! Neues kann manuell eingetragen werden", vbOKOnly + vbInformation, "Löschen"
Me![subformB].Form![Field1] = ""
End Select
'Trifft keiner der Situation zu, dann Sub verlassen
Else
Exit Sub
End If
End Sub
---

[snoopy]

Does somebody knows a way to add attachments after posting? Would like to attach the code, because I just recognised that the code is not indented.

[ssanfu]

2nd question: Click on the "Go Advanced" button. It allows you to add attachments (below the text entry area) and has more options in the tool bar. The "#" is the option that allows you to add formatted code. (It doesn't do the formatting). Or you can type "[ code ]" at the start and "[ /code ]" (without the spaces) at the end of the code.



1st question:

If IsNull(Me![subformB].Form![Field1]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then

This is a text string:"3"
This is a number: 3

A text 3 <> a numeric 3 --> ie "3" <> 3
If the PK for the combo box is a Long, try removing the quotes.

Also,

ElseIf Me!Combo471 = "3" And Me![subformB].Form![Field1] < Year(Now()) Then

Try removing the quotes here also.
And "Now()" will work... since you are getting the date, I would use "Date()"; "Now()" includes the time component.

[snoopy]

2nd question: Click on the "Go Advanced" button. It allows you to add attachments (below the text entry area) and has more options in the tool bar. The "#" is the option that allows you to add formatted code. (It doesn't do the formatting). Or you can type "[ code ]" at the start and "[ /code ]" (without the spaces) at the end of the code.

Thanks, the code is attached as a script

1st question:

If IsNull(Me![subformB].Form![Field1]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then

This is a text string:"3"
This is a number: 3

A text 3 <> a numeric 3 --> ie "3" <> 3
If the PK for the combo box is a Long, try removing the quotes.

Its a text field and its working with the first and thirth if statement, which checks the same field as the second. The second is still not working and I don't know why

Also,

ElseIf Me!Combo471 = "3" And Me![subformB].Form![Field1] < Year(Now()) Then

Try removing the quotes here also.
And "Now()" will work... since you are getting the date, I would use "Date()"; "Now()" includes the time component.

script.zip


Ok thanks for optimization advise

[snoopy]

I found the problem. It was th function IsNull(), because you cannout use it for checkboxes. Therefore you have to check for False. But there still some optimizations, which would be fine to implement.

1. There are more than one field Form![marr]. basically its a query, which loops through data and puts out one or more fields. My question how could I implement this situation in vba, so that my code is checking every field and not just the first one?

2. There three different situations, but one situation could follow after the other. I say it clearly, the first and the second if statement could be true at the same time. My code is just checking the first and would ignore the second, but how could code of second if statement also doable?


Would be nice if somebody could give me some adivises, thanks mates

Private Sub Combo471_AfterUpdate()

'Variablendeklarationen
Dim strTitle As String
Dim strPormpt As String
strTitle = "Achtung!"
strPrompt = "Sie haben blablabla angegeben. Jedoch liegt ihr angegebenes blabla (" & Me![subformB].Form![Field1] & ") in der Vergangenheit." _
& vbCrLf & "Bitte prüfen Sie, ob dies so korrekt ist. Andernfalls Bitte ändern Sie das blabla entsprechend."


'Prufen ob Feld blabla leer, bei gleichzeitigen blablabla 3 - 5
If IsNull(Me![subformB].Form![Field1]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
    'Wenn dem so ist, Aufruf einer Msg.Box, extern
        Select Case MsgBox2(Title:="Achtung", _
                Prompt:="Sie haben blablabla " & Me.Combo471 & " angegeben aber kein blabla." & vbCrLf & "Bitte geben Sie ein entsprechendes blabla an", _
                Buttons:=vbButton2 + vbInformation, _
                UserButton1:="Jahr manuell eintragen", _
                UserButton2:="Aktuelles Jahr eintragen")
            Case vbButton1
                MsgBox "Ok, blabla kann manuell eingetragen werden!", vbOKOnly + vbInformation, "blabla wird so belassen"
            Case Else
                MsgBox "Aktuelles Jahr wird eingetragen!", vbOKOnly + vbInformation, "Aktuelles Jahr"
                Me![subformB].Form![Field1] = Year(Date())
        End Select
'Falls nicht zutrifft dann pruefen,ob Hersteller ab blablabla 3 als guck gekennzeichnet ist oder nicht ob
ElseIf (Me![subformA].Form![marr]=False) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
        'Aufruf der selben Messagebox, wie bei anderen Routinen
        Select Case MsgBox2(Title:="Achtung", _
                Prompt:="Sie haben den blablabla" & Me.Combo471 & " angegeben aber denhossa nicht als guck gekennzeichnet" & vbCrLf & "Bitte kennzeichnen Sie den als guck.", _
                Buttons:=vbButton2 + vbInformation, _
                UserButton1:="hossa als guck kennzeichnen", _
                UserButton2:="marr manuell kennzeichnen")
            Case vbButton1
                MsgBox "hossa wird als guck gekennzeichnet!", vbOKOnly + vbInformation, "hossa guck"
                Me![subformA].Form![marr].Value = True
            Case Else
                MsgBox "Ok,hossa wird manuell als guck gekennzeichnet!", vbOKOnly + vbInformation, "hossa nicht guck"
        End Select
'Als dritte Alternative pruefen, bei blablabla 3 als Fall, ob blabla in der Verangenheit
ElseIf Me!Combo471 = "3" And Me![subformB].Form![Field1] < Year(Date()) Then
    Select Case MsgBox2(Title:=strTitle, _
          Prompt:=strPrompt, _
          Buttons:=vbButton3 + vbInformation, _
          UserButton1:="blabla so belassen", _
          UserButton2:="Aktuelles Jahr eintragen", _
          UserButton3:="Derzeitiges blabla löschen und neues manuell eintragen")
        Case vbButton1
            MsgBox "Ok, blabla wird so belassen", vbOKOnly + vbInformation, "blabla wird so belassen"
        Case vbButton2
            MsgBox "Aktuelles Jahr wird eingetragen!", vbOKOnly + vbInformation, "Aktuelles Jahr"
            Me![subformB].Form![Field1] = Year(Date())
        Case Else
            MsgBox "blabla wird gelöscht! Neues kann manuell eingetragen werden", vbOKOnly + vbInformation, "Löschen"
            Me![subformB].Form![Field1] = ""
    End Select
'Trifft keiner der Situation zu, dann Sub verlassen
Else
  Exit Sub
End If
End Sub

[ssanfu]

1)

There are more than one field Form![marr]. basically its a query, which loops through data and puts out one or more fields. My question how could I implement this situation in vba, so that my code is checking every field and not just the first one?

Not sure I understand this. Are you talking fields in a query or controls on a form?

2) Use two (or more) IF() functions. Something like this:

Private Sub Combo471_AfterUpdate()

'Variablendeklarationen
   Dim strTitle As String
   Dim strPormpt As String
   strTitle = "Achtung!"
   strPrompt = "Sie haben blablabla angegeben. Jedoch liegt ihr angegebenes blabla (" & Me![subformB].Form![Field1] & ") in der Vergangenheit." _
               & vbCrLf & "Bitte prüfen Sie, ob dies so korrekt ist. Andernfalls Bitte ändern Sie das blabla entsprechend."


   'Prufen ob Feld blabla leer, bei gleichzeitigen blablabla 3 - 5
   If IsNull(Me![subformB].Form![Field1]) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
      'Wenn dem so ist, Aufruf einer Msg.Box, extern
      Select Case MsgBox2(Title:="Achtung", _
                          Prompt:="Sie haben blablabla " & Me.Combo471 & " angegeben aber kein blabla." & vbCrLf & "Bitte geben Sie ein entsprechendes blabla an", _
                          Buttons:=vbButton2 + vbInformation, _
                          UserButton1:="Jahr manuell eintragen", _
                          UserButton2:="Aktuelles Jahr eintragen")
         Case vbButton1
            MsgBox "Ok, blabla kann manuell eingetragen werden!", vbOKOnly + vbInformation, "blabla wird so belassen"
         Case Else
            MsgBox "Aktuelles Jahr wird eingetragen!", vbOKOnly + vbInformation, "Aktuelles Jahr"
            Me![subformB].Form![Field1] = Year(Date)
      End Select
   End If

   'Falls nicht zutrifft dann pruefen,ob Hersteller ab blablabla 3 als guck gekennzeichnet ist oder nicht ob
   If (Me![subformA].Form![marr] = False) And (Me!Combo471 = "3" Or Me!Combo471 = "4" Or Me!Combo471 = "5") Then
      'Aufruf der selben Messagebox, wie bei anderen Routinen
      Select Case MsgBox2(Title:="Achtung", _
                          Prompt:="Sie haben den blablabla" & Me.Combo471 & " angegeben aber denhossa nicht als guck gekennzeichnet" & vbCrLf & "Bitte kennzeichnen Sie den als guck.", _
                          Buttons:=vbButton2 + vbInformation, _
                          UserButton1:="hossa als guck kennzeichnen", _
                          UserButton2:="marr manuell kennzeichnen")
         Case vbButton1
            '.
            '. more code
            '.
      End Select
   End If

[Kirsti]

Hi, I just noticed...

Right at the beginning of your code you declare:

Dim strPormpt As String
strTitle = "Achtung!"
strPrompt = "Sie haben blablabla angegeben. Jedoch liegt ihr angegebenes blabla (" & Me![subformB].Form![Field1] & ") in der Vergangenheit." _
& vbCrLf & "Bitte prüfen Sie, ob dies so korrekt ist. Andernfalls Bitte ändern Sie das blabla entsprechend."

I haven't looked through the rest of your code, but this typo probably isn't helping!

Kirsti

[snoopy]

Dim strPormpt As String
strTitle = "Achtung!"
strPrompt = "Sie haben blablabla angegeben. Jedoch liegt ihr angegebenes blabla (" & Me![subformB].Form![Field1] & ") in der Vergangenheit." _
& vbCrLf & "Bitte prüfen Sie, ob dies so korrekt ist. Andernfalls Bitte ändern Sie das blabla entsprechend."

I haven't looked through the rest of your code, but this typo probably isn't helping!

Which way is better, declare and write?

[Kirsti]

Hi Snoopy,

I just mean that you have
"Dim strPormpt"

and then "strPrompt"

They are spelt differently... (in the Dim, you have the 'r' and the 'o' round the wrong way)

Kirsti

[ssanfu]

I hate it when I miss things like that.

The top two lines of every code page should be

Option Compare Database
Option Explicit



"Option Explicit" requires that variables be defined before they can be used. Helps catch errors like undeclared and/or misspelled variables. There is a setting that can be set that will add it to any new code pages; for existing code pages, you must add it.

[snoopy]

Hi Snoopy,

I just mean that you have
"Dim strPormpt"

and then "strPrompt"

They are spelt differently... (in the Dim, you have the 'r' and the 'o' round the wrong way)

Kirsti

O thanks I did not see it

I hate it when I miss things like that.

The top two lines of every code page should be

Option Compare Database
Option Explicit



"Option Explicit" requires that variables be defined before they can be used. Helps catch errors like undeclared and/or misspelled variables. There is a setting that can be set that will add it to any new code pages; for existing code pages, you must add it.

Ok, done

problem solved! Thanks guys