Results 1 to 3 of 3

same command button on different forms

  1. #1
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    59

    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
    7,754
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,740
    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

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
  •  
Tech Forums: Microsoft Office Forums