none
Open form, prompt for value to edit record RRS feed

  • Question

  • I'm attempting to add below SQL query to cmd button, so that user gets prompted for the ID of the record to edit.

    SELECT tbl_EQT_List.*

    FROM tbl_EQT_List

    WHERE (((tbl_EQT_List.eqtEquipmentID)=[eqtEquipmentID:]));

    Private Sub cmd_frm_eqt_List_Edit_Click()

    On Error GoTo Err_cmd_frm_eqt_List_Edit_Click

        Dim stDocName As String

       

        stDocName = "frm_eqt_Edit_Eqt"

        DoCmd.Close

        DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal

    Exit_cmd_frm_eqt_List_Edit_Click:

        Exit Sub

      

    Err_cmd_frm_eqt_List_Edit_Click:

        MsgBox Err.Description

        Resume Exit_cmd_frm_eqt_List_Edit_Click

    Friday, February 16, 2018 7:36 PM

Answers

  • Hi Andy,

    A better approach is to use a "search" form, which can validate if the user input is valid or not before even opening the edit form just in case there is no matching ID.

    To prompt the user for an entry, you can use a simple Inputbox() command and then open the form with a WhereCondition argument using the entered ID. For example:

    Dim lngID As Long
    
    lngID = CLng(InputBox("Enter ID to edit."))
    
    If lngID <> 0 Then
         DoCmd.OpenForm stDocName, , , "ID=" & lngID
    End If
    

    Hope it helps...

    • Marked as answer by Andy Diaz Friday, February 16, 2018 8:53 PM
    Friday, February 16, 2018 7:44 PM

All replies

  • Hi Andy,

    A better approach is to use a "search" form, which can validate if the user input is valid or not before even opening the edit form just in case there is no matching ID.

    To prompt the user for an entry, you can use a simple Inputbox() command and then open the form with a WhereCondition argument using the entered ID. For example:

    Dim lngID As Long
    
    lngID = CLng(InputBox("Enter ID to edit."))
    
    If lngID <> 0 Then
         DoCmd.OpenForm stDocName, , , "ID=" & lngID
    End If
    

    Hope it helps...

    • Marked as answer by Andy Diaz Friday, February 16, 2018 8:53 PM
    Friday, February 16, 2018 7:44 PM
  • Since the user will always know the ID# of record to edit, I've decided to just create the query and use the query in FilterName argument.

    DoCmd.OpenForm stDocName, , "qry_Prompt_eqtID", , acFormEdit, acWindowNormal

    Great tip, I will start using search forms for my other forms.

    Thank you!

    Friday, February 16, 2018 8:52 PM
  • Cool. Glad to hear you got it sorted out. Good luck with your project.
    Friday, February 16, 2018 9:13 PM
  • lngID = CLng(InputBox("Enter ID to edit."))
    

    Hi Andy, .theDBguy,

    As InputBox returns a String value (including ZLS), you first have to test for a numerical value before converting it with CLng, e.g.

    input_str = InputBox("Enter ID to edit.")
    If (IsNumeric(input_str)) Then lngID = CLng(input_str)

    Imb.

    Friday, February 16, 2018 11:00 PM