Results 1 to 13 of 13
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    same command button on different forms

    Hi,

    I have a command button to mark a recod as deleted on a form. I want tou use the same code on another form; I do not want to repeat the code; I know that I can use the code in Public function and call the function when the command button is clicked.


    My problem is that the forms have different names and I am using the Me. in the code. How can I overcome these two issues?

    Khalil


    Code:
    Private Sub cmdDeleteRecord_Click()
        On Error GoTo ProcError
     
        Dim db As DAO.Database
        Dim strSQL As String
        Dim lngBookID As Long
     
        Set db = CurrentDb()
     
        lngBookID = Nz(Me.txtpkBookId, 0)
        ' lngBookID = [Forms]![frmBooks]![txtpkBookId] 'Or lngBookID = Me.txtpkBookId.
     
        If lngBookID = 0 Then
            MsgBox "Missing Book ID"
        Else
            'lngBookID <> 0
            strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber,"
            strSQL = strSQL & " CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) "
            strSQL = strSQL & " SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages,"
            strSQL = strSQL & " YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries "
            strSQL = strSQL & " FROM qryBooks "
            strSQL = strSQL & " WHERE [pkBookId] = " & lngBookID
     
            Debug.Print "lngBookID: " & lngBookID
            Debug.Print "strSQL =" & vbCrLf & strSQL
     
            'execute the SQL
            db.Execute strSQL, dbFailOnError
        End If
     
    ExitProc:
        Set db = Nothing
        Exit Sub
     
    ProcError: 
        MsgBox "Error " & Err.Number & ": " & Err.Description & ", " & vbCritical, "Message Box Title Text"
        Resume ExitProc
     
    End Sub
    Private Sub cmdDeleteRecord_Click()
        On Error GoTo ProcError
     
        Dim db As DAO.Database
        Dim strSQL As String
        Dim lngBookID As Long
     
        Set db = CurrentDb()
     
        lngBookID = Nz(Me.txtpkBookId, 0)
        ' lngBookID = [Forms]![frmBooks]![txtpkBookId] 'Or lngBookID = Me.txtpkBookId.
     
        If lngBookID = 0 Then
            MsgBox "Missing Book ID"
        Else
            'lngBookID <> 0
            strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber,"
            strSQL = strSQL & " CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) "
            strSQL = strSQL & " SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages,"
            strSQL = strSQL & " YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries "
            strSQL = strSQL & " FROM qryBooks "
            strSQL = strSQL & " WHERE [pkBookId] = " & lngBookID
     
            Debug.Print "lngBookID: " & lngBookID
            Debug.Print "strSQL =" & vbCrLf & strSQL
     
            'execute the SQL
            db.Execute strSQL, dbFailOnError
        End If
     
    ExitProc:
        Set db = Nothing
        Exit Sub
     
    ProcError: 
        MsgBox "Error " & Err.Number & ": " & Err.Description & ", " & vbCritical, "Message Box Title Text"
        Resume ExitProc
     
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put the code into a module, then pass it a parameter...


    'form1
    Private Sub cmdDeleteRecord_Click()
    RunMyCode me.txtpkBookID
    end sub

    'form2
    Private Sub cmdButton2_Click()
    RunMyCode me.txtpkBookID
    end sub


    sub RunMyCode (byval pvID)
    strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber,"
    strSQL = strSQL & " CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) "
    strSQL = strSQL & " SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages,"
    strSQL = strSQL & " YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries "
    strSQL = strSQL & " FROM qryBooks "
    strSQL = strSQL & " WHERE [pkBookId] = " & pvID

    Debug.Print "lngBookID: " &
    pvID
    Debug.Print "strSQL =" & vbCrLf & strSQL

    'execute the SQL
    docmd.runSql strSql
    end sub

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ranman gave you a great solution, I'd just use the Execute method rather than RunSQL, which will throw warnings:

    http://www.baldyweb.com/SQLWarnings.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Cheez is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    9
    Hello all,

    I have a similar scenario here. I was able to get my SQL statement to work on both forms, I also want to change the visibility of a button. how would I do that in the module?


    form1
    Private Sub cmdSQL_Click()


    Run Me.CustomerID


    Forms!CustomerF.Refresh

    End Sub


    form2
    Private Sub cmdSQL_Click()


    Run Me.CustomerID


    Forms!CustomerT.Refresh


    End Sub



    module
    Public Sub Run(ByVal Cid)


    Dim DIR As String

    DoCmd.SetWarnings False


    DIR = "UPDATE CustomerT Set CustomerT.IsActive = true " _
    & "WHERE CustomerT.CustomerID=" & Cid & ""

    DoCmd.RunSQL DIR
    DoCmd.SetWarnings True


    End Sub

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Code:
    Public Sub Run(ByVal Cid)
    
    Dim DIR As String
    
    DoCmd.SetWarnings False
    
    DIR = "UPDATE CustomerT Set CustomerT.IsActive = true " _
    & "WHERE CustomerT.CustomerID=" & Cid & ""
    
    DoCmd.RunSQL DIR
    DoCmd.SetWarnings True
    
    End Sub
    Note that Run and Dir are reserved words and should not be used how you're using them.
    http://allenbrowne.com/AppIssueBadWord.html

    What button and what event or criteria to hide it?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Cheez is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    9
    ok i will change those. i would like to hide a button after the update query has run. I haven't put any code for the button in yet. How would you do that?

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You can't hide a control that has the focus so you need to shift the focus elsewhere.

    Code:
    Me.SomeOtherControl.SetFocus
    Then you can set the visible property to false.

    Code:
    Me.cmdSQL.Visible = False
    
    
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Cheez is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    9
    can this be done in the module? I want to use the same code on another form; I do not want to repeat the code.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    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

  10. #10
    Cheez is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    9
    sorry for cross posting! Got inpatient, won't happen again!

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Cross-posting is okay. It would courteous to reference link of other thread.

    Instead of hi-jacking an old thread, it is better to start your own and reference the old one if you think it would helpful to readers. New threads get more attention.
    Last edited by June7; 03-06-2022 at 09:07 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Cheez is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    9
    ok ty nice to know!

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    We all need something yesterday sometime.
    Just include a link to the other post each time.
    I was not criticising you, just giving other members a heads up, and using a tablet, so minimum typing.
    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

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

Similar Threads

  1. Replies: 4
    Last Post: 09-05-2016, 10:27 AM
  2. Replies: 12
    Last Post: 10-20-2014, 11:22 AM
  3. Add Command Button to Multiple Forms
    By jameslefebv in forum Forms
    Replies: 4
    Last Post: 07-14-2014, 07:07 PM
  4. Access 2010 Forms Command button Question
    By RayMilhon in forum Forms
    Replies: 3
    Last Post: 09-12-2011, 03:38 PM
  5. Replies: 5
    Last Post: 08-06-2009, 11:47 PM

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