Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    But I thought you didn't want to update the table when the txtProjectNumber is null??? Do you mean to say that when it's NOT null that the db.execute lines aren't working?

    The line with Err.Raise triggers a custom error, because of how we've set up the error handling as soon as this line executes it will jump to the bottom portion of code under the line ErrHandler: then when it gets to Resume ExitHandler it jumps back up to the code under ExitHandler:

    Your line Me.Requery will never be executed right there. Move that to ABOVE the ExitHandler: because you said only want it to execute when something is saved.

    The line Me.txtProjectNumber.SetFocus also will not be executed when IsNull(Me!txtProjectNumber) = True. As I explained above it skips the code below it. Try changing that to something like this:


    Code:
    If IsNull(Me!txtProjectNumber) Then
        Me.txtProjectNumber.SetFocus
        Err.Raise 1001, , "your error description here"
    End If

  2. #17
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    I can't get past the update query so I went back to what I had and it's also not working....here's the line

    DoCmd.RunSQL "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = Me.[frmRequests].Form.txtProjectNumber"

    it cannot find the project number to update, but it focuses on the project number field.
    Attached Thumbnails Attached Thumbnails image001 (2).png  

  3. #18
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You need to concatenate the control value into the sql string ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #19
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    @kd2017 That worked great! Stuck on the contrary now when the project number is populated it won't update

  5. #20
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    @Welshgasman

    Concatenate?

    DoCmd.RunSQL "Update tblRequests Set tblRequests.[
    cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = [Forms]![NavigationForm]![frmRequests].[ProjectNumber_Label].[txtProjectNumber]"

    ??

  6. #21
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Code:
    DoCmd.RunSQL "Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '" &  [Forms]![NavigationForm]![frmRequests].[ProjectNumber_Label].[txtProjectNumber] & "'"
    


    I tend to put it all into a string varaible so I can then Debug.Print it to see what I get?

    Code:
    Dim strSQL As String
    strSQL = "Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '" &  [Forms]![NavigationForm]![frmRequests].[ProjectNumber_Label].[txtProjectNumber] & "'"
    
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
    

    then comment out/remove the Debug when it is correct.

    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #22
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    Object doesn't support this property or method is the error I'm getting




    Quote Originally Posted by Welshgasman View Post
    Code:
    DoCmd.RunSQL "Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '" &  [Forms]![NavigationForm]![frmRequests].[ProjectNumber_Label].[txtProjectNumber] & "'"
    


    I tend to put it all into a string varaible so I can then Debug.Print it to see what I get?

    Code:
    Dim strSQL As String
    strSQL = "Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '" &  [Forms]![NavigationForm]![frmRequests].[ProjectNumber_Label].[txtProjectNumber] & "'"
    
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
    

    then comment out/remove the Debug when it is correct.


  8. #23
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So show the output of the debug.print?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #24
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    I don't know what you mean, I didn't see that...

  10. #25
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What he's asking you to do is build your string in a seperate variable and print it to the debug window first so you can get a look at what's actually being fed to DoCmd.RunSQL

    Change
    Code:
    DoCmd.RunSQL "some sql code here"
    to this:
    Code:
    Dim strSQL As String
    strSQL = "some sql code here"
    
    Debug.Print strSQL '<---- Print the string so we can see that it's been built correctly
    DoCmd.RunSQL strSQL


    And report back what you see in the 'immediate' window. To see the immediate window in your vba editor go to View -> Immediate Window. For example it should spit something out that looks something like this:
    Code:
    Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '21201'

  11. #26
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    After a few tries, it worked!

    Thank you/

  12. #27
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by kd2017 View Post
    What he's asking you to do is build your string in a seperate variable and print it to the debug window first so you can get a look at what's actually being fed to DoCmd.RunSQL

    Change
    Code:
    DoCmd.RunSQL "some sql code here"
    to this:
    Code:
    Dim strSQL As String
    strSQL = "some sql code here"
    
    Debug.Print strSQL '<---- Print the string so we can see that it's been built correctly
    DoCmd.RunSQL strSQL


    And report back what you see in the 'immediate' window. To see the immediate window in your vba editor go to View -> Immediate Window. For example it should spit something out that looks something like this:
    Code:
    Update tblRequests Set tblRequests.[cboEstimateStatus]  = 'Pending Request' where tblRequests.[txtProjectNumber] = '21201'
    All in post#21
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #28
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Welshgasman View Post
    All in post#21
    Right, in post #24 he said he still didn't understand so I was just trying to illustrate what you were showing them.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exit input box or value is ignored
    By School Boy Error in forum Forms
    Replies: 9
    Last Post: 04-26-2018, 08:27 AM
  2. Compress On Exit
    By Derrick T. Davidson in forum Access
    Replies: 5
    Last Post: 07-31-2014, 09:40 PM
  3. Replies: 2
    Last Post: 12-20-2012, 03:06 PM
  4. How to end/exit a sub from its nested sub?
    By lookingforK in forum Programming
    Replies: 2
    Last Post: 12-10-2012, 02:13 PM
  5. exit
    By slimjen in forum Forms
    Replies: 4
    Last Post: 10-09-2011, 09:30 AM

Tags for this Thread

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