Results 1 to 7 of 7
  1. #1
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480

    Angry Nulls problem, hmm

    This app is to send relevant data to a web browser it does this.. However, if one of the three text boxes on the form is blank. it sends null to the browser, messing up the search. The code below is kind of the result of me trying numerous things, whever methodology works I will be applying to all three text boxes.

    What I have tried and the results



    If Forms("frmmainnew").txtlnmFirst <> "" Then
    Result.. Still sends in the Null to IE. I imagine because Null is not <>
    If Forms("frmmainnew").txtLNMuid.Value = Null Then
    Result.. eitherway, empty textbox or not... the statement is never Null
    If Forms("frmmainnew").txtlnmLast Is Null Then
    Result.. The statement is ALWAYS NULL, even if the txtbox has something in it.


    Code:
      If Forms("frmmainnew").txtLNMuid.Value = Null Then
            GoTo skipUid:
            End If
      oLnM.Document.all.Item("firstResponder").Value = Forms("frmmainnew").txtLNMuid.Value
    skipUid:
            If Forms("frmmainnew").txtlnmLast Is Null Then
            GoTo skipLast:
            End If
     oLnM.Document.all.Item("$TextField$1").Value = Forms("frmmainnew").txtlnmLast.Value
    skipLast:
            If Forms("frmmainnew").txtlnmFirst <> "" Then
         oLnM.Document.all.Item("$TextField$2").Value = Forms("frmmainnew").txtlnmFirst.Value
            End If
    
    
    
    
    PauseApp 0.4
    oLnM.Document.all.Item("searchButton").Click
    I have read a lot about nulls, but unable to resolve this issue. Any advice or guidance is more then appreciated.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You should look into both the IsNull() and NZ() functions. PS: *nothing* is ever equal to Null, including Null. Null is undefined so Null <> Null.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Alright, going to look into isnull and NZ. Will follow up today.

  4. #4
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Still having problems... I might not be grasping the full NZ function thing... I am going to post the code in its original form... then the butchered attempts I've been making.

    Code:
    Sub LnMpwReset()
    
       Dim oLnM As InternetExplorer
       Dim User, Pass As String
       Dim strUser, strPass As String
    
       On Error Resume Next
       User = DLookup("[chrWebuser]", "tblWebCreds", "chrWebTools='Labs Meds Admin'")
       Pass = DLookup("[chrWebpass]", "tblWebCreds", "chrWebTools='Labs Meds Admin'")
    
       If Err.Number = 94 Then
          MsgBox "Username or Password is missing", vbCritical, "LIFT MESSAGE"
          DoCmd.OpenForm "frmWebCreds"
          Exit Sub
       End If
       Set oLnM = Login("Login site", True)
       If InStr(oLnM.Document.Body.innertext, "You have multiple sessions open") > 0 Then
          oLnM.Document.all.Item("SubmitContinueSession").Click
          Call SleepIE(oLnM)
       End If
       oLnM.Document.all.Item("j_username").Value = User
       oLnM.Document.all.Item("j_password").Value = Pass
       oLnM.Document.all.Item("login").Click
       ''''''''''''''''wrong password
       Call SleepIE(oLnM)
       If InStr(oLnM.Document.Body.innertext, "Login Failed") > 0 Then
          oLnM.Navigate "javascript:alert('Please Check your credentials.');"
          Exit Sub
       End If
       '''''''''''''''multiple sessions
       If InStr(oLnM.Document.Body.innertext, "You have multiple sessions open. Continuing will end your previous session") > 0 Then
          oLnM.Document.all.Item("SubmitContinueSession").Click
          Call SleepIE(oLnM)
       End If
       Call SleepIE(oLnM)
       oLnM.Navigate "Website we are going to"
    
       Call SleepIE(oLnM)
       PauseApp 0.4
       oLnM.Document.all.Item("clearButton").Click
       Call SleepIE(oLnM)
       If Forms("frmmainnew").txtLNMuid <> "" Then
          oLnM.Document.all.Item("firstResponder").Value = Forms("frmmainnew").txtLNMuid.Value
       End If
       If Forms("frmmainnew").txtlnmLast <> "" Then
          oLnM.Document.all.Item("$TextField$1").Value = Forms("frmmainnew").txtlnmLast.Value
       End If
       If Forms("frmmainnew").txtlnmFirst <> "" Then
          oLnM.Document.all.Item("$TextField$2").Value = Forms("frmmainnew").txtlnmFirst.Value
       End If
       PauseApp 0.4
       oLnM.Document.all.Item("searchButton").Click
    
    End Sub
    The code above, if ("frmmainnew").txtLNMuid was blank on the form, it was sending "null" to the browser.. I have tried a handful of things... My last attempt at understanding null and NZ made me think I needed to dim those textboxes as variant so I could use the nz variant..


    Code:
    Sub LnMpwReset()
    
       Dim oLnM As InternetExplorer
       Dim User, Pass As String
       Dim strUser, strPass As String
       Dim UiD, LastN, FirstN As Variant
       On Error GoTo Err:
       UiD = Forms("frmmainnew").txtLNMuid.Value
       LastN = Forms("frmmainnew").txtlnmLast.Value
       FirstN = Forms("frmmainnew").txtlnmFirst.Value
    Errecs:
     
       On Error Resume Next
       User = DLookup("[chrWebuser]", "tblWebCreds", "chrWebTools='Labs Meds Admin'")
       Pass = DLookup("[chrWebpass]", "tblWebCreds", "chrWebTools='Labs Meds Admin'")
    
       If Err.Number = 94 Then
          MsgBox "Username or Password is missing", vbCritical, "LIFT MESSAGE"
          DoCmd.OpenForm "frmWebCreds"
          Exit Sub
       End If
       Set oLnM = Login("Login site", True)
       If InStr(oLnM.Document.Body.innertext, "You have multiple sessions open") > 0 Then
          oLnM.Document.all.Item("SubmitContinueSession").Click
          Call SleepIE(oLnM)
       End If
       oLnM.Document.all.Item("j_username").Value = User
       oLnM.Document.all.Item("j_password").Value = Pass
       oLnM.Document.all.Item("login").Click
       ''''''''''''''''wrong password
       Call SleepIE(oLnM)
       If InStr(oLnM.Document.Body.innertext, "Login Failed") > 0 Then
          oLnM.Navigate "javascript:alert('Please Check your credentials.');"
          Exit Sub
       End If
       '''''''''''''''multiple sessions
       If InStr(oLnM.Document.Body.innertext, "You have multiple sessions open. Continuing will end your previous session") > 0 Then
          oLnM.Document.all.Item("SubmitContinueSession").Click
          Call SleepIE(oLnM)
       End If
       Call SleepIE(oLnM)
       oLnM.Navigate "Website we are going to"
    
       Call SleepIE(oLnM)
       PauseApp 0.4
       oLnM.Document.all.Item("clearButton").Click
       Call SleepIE(oLnM)
    
       If UiD = "" Then
          GoTo skipUid:
       End If
       oLnM.Document.all.Item("firstResponder").Value = UiD
    skipUid:
       If Forms("frmmainnew").txtlnmLast Is Null Then
          GoTo skipLast:
       End If
       oLnM.Document.all.Item("$TextField$1").Value = LastN
    skipLast:
       If Forms("frmmainnew").txtlnmFirst <> "" Then
          GoTo skipfirst:
       End If
       oLnM.Document.all.Item("$TextField$2").Value = FirstN
    skipfirst:
    
       PauseApp 0.4
       oLnM.Document.all.Item("searchButton").Click
    Err:
       If UiD = Nz(UiD, "") Then
       End If
       If LastN = Nz(LastN, "") Then
       End If
       If FirstN = Nz(FirstN, "") Then
       End If
       GoTo Errecs:
    End Sub
    The code above here is my attempting a lot of different methods. While searching null and NZ, I find alot of things ment for inside queries but not a whole lot in actual VBA code.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Several comments:

    1) Nz() would be used like:
    If Nz(Forms("frmmainnew").txtlnmFirst,"") <> "" Then

    2) This line: Dim User, Pass As String
    actually defines the variables as: Dim User As Variant, Pass As String
    You need to be explicit in VBA.

    3) GoTo's drive me nuts. I've been using structured code so long that I have a great deal of difficulty following GoTo's and do not use them myself except for the On Error clause.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Alright, Once again.. Where do I send the check? That worked... I agree on the Goto's. This is the first time using them for anything other then error handling. I was REALLY grasping at straws. It seems to be working on my machine locally... I am still hazzy on the technique... but its something I will get more exp at while gaining exposure to these kinds of issues.

    Code:
          If Nz(Forms("frmmainnew").txtLNMuid, "") <> "" Then
            oLnM.Document.all.Item("firstResponder").Value = Forms("frmmainnew").txtLNMuid.Value
            GoTo skipUid:
            End If
    
    
    skipUid:
           If Nz(Forms("frmmainnew").txtlnmLast, "") <> "" Then
         oLnM.Document.all.Item("$TextField$1").Value = Forms("frmmainnew").txtlnmLast.Value
            GoTo skipLast:
            End If
     
    skipLast:
           If Nz(Forms("frmmainnew").txtlnmFirst, "") <> "" Then
            oLnM.Document.all.Item("$TextField$2").Value = Forms("frmmainnew").txtlnmFirst.Value
            GoTo skipfirst:
            End If
    
    
    skipfirst:
    PauseApp 0.4
    oLnM.Document.all.Item("searchButton").Click
    It worked here, going to throw it on a couple users machines for a quick test.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Just send the check to your favorite charity...or maybe just have a beer on me.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count Query w/o ignoring Nulls
    By Dulanic in forum Queries
    Replies: 3
    Last Post: 03-21-2012, 11:56 AM
  2. SUM in regards to nulls
    By detlion1643 in forum Access
    Replies: 5
    Last Post: 02-03-2010, 08:50 AM
  3. Replies: 2
    Last Post: 01-18-2010, 11:52 AM
  4. Sort Order Variation with nulls
    By jonsuns7 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 03:10 AM
  5. GetRows - Invalid Use of Nulls
    By Wannabe_Pro in forum Programming
    Replies: 3
    Last Post: 07-22-2009, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums