Results 1 to 7 of 7
  1. #1
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35

    Event Procedure won't execute into an If..Then segment

    In a form, when I update a combo box field, I want to grab some extra fields from the lookup table and populate some fields on the current form. Here is my code...

    Private Sub Project_ID_AfterUpdate()
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    If Project_ID <> Null Then
    strSQL = "SELECT STO_ID, ProjectNo, InfraProjectNo"
    strSQL = strSQL & " FROM tblProjects"
    strSQL = strSQL & " WHERE Project_ID = '" & Me.Project_ID & "';"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    ProjectNo = rs!ProjectNo
    InfraProjectNo = rs!InfraProjectNo


    STO_ID = rs!STO_ID
    Me.Refresh
    End If
    End Sub

    It won't fall into the If Project_ID <> Null Then code which I checked with a breakpoint inside the If..End. I put a breakpoint on the If statement itself and mouse over the values. Project_ID = 16 and Null = Null. Any ideas? Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can't test for Null that way. Try

    If Not IsNull(Project_ID) Then

    or

    If Len(Project_ID & vbNullString) > 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    Thanks!!! That put me into the if statement. Greatly appreciated!
    Now I have another problem. I'm receiving a type mismatch error on the Set rs statement. STO_ID is a long, ProjectNo is a string, InfraProjectNo is a string, and Project_ID is a long. Any ideas? Thanks sooo much!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Another way of doing (your first post) is to bring those fields into your combobox row source and make them hidden. Then in the AfterUpdate you can say
    fieldname=Me.Project_ID.Column(x) - where x is the column number (starts with 0).

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Project_ID is a long
    you have wrapped it in quotes as if it were a string

    '" & Me.Project_ID & "';
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    I couldn't get this method to work as only one of the fields recognized the .Column() attribute. Sorry!

  7. #7
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    I removed the quotes so it's not being recognized as a string. I over came a few more hurdles including the need to use the nz() function and it's working. Thanks to all for your support. Greatly appreciated!

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

Similar Threads

  1. Execute based on event and value
    By netguy in forum Programming
    Replies: 11
    Last Post: 09-27-2014, 12:29 AM
  2. Replies: 1
    Last Post: 03-29-2014, 07:46 PM
  3. execute two queries in single CLICK event
    By jhargram in forum Queries
    Replies: 1
    Last Post: 07-25-2012, 06:17 AM
  4. Replies: 3
    Last Post: 05-07-2012, 12:17 PM
  5. Execute MySQL Stored Procedure with Access 2010 VBA?
    By DanielHofer in forum Programming
    Replies: 5
    Last Post: 01-23-2012, 01:08 PM

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