Results 1 to 9 of 9
  1. #1
    jhjarvie is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2008
    Posts
    12

    Select Item from drop down list rather than enter in an input box????

    I have a simple bit of code which I use to force various entrys in specific fields after a trigger event...
    I would like to use a drop down list of set values other than an input box which doesnt offer the user any choice (See last line of the code below)......any ideas??

    If Me.Transaction_Type = "Repair" Then
    Me.Part_No = "TOOL-REPAIR"


    Me.Supplier = "00000"
    Me.Stock_Status = "N/A"
    Me.Qty_Req = "1"
    Me.Work_Description = InputBox("Enter description of the Tool Repair")
    Me.Work_Supplier = InputBox("Enter Supplier Code")


  2. #2
    Yance is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    22

    Smile

    Can you post your project example?

  3. #3
    jhjarvie is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2008
    Posts
    12

    Input Box / Dropdown list Issues

    Maybe the issue is easier put this way.....

    Is it possible to make an input box (using VBA) a pop-up combo box instead?

    i.e: I am using the following code....

    me.AnswerField = InputBox("Select from the following selection (i.e. Mandatory, GMP/SHE)", "Phase 1 Employee Induction")

    However I'd like the users to select the input from a combo box (dropdown list) instead of an input box?

    Is this possible?

  4. #4
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    You'll have to build your own "input box" by creating a new form, with the list of responses in a dropdown combo box, that is called from the "main" form. Here's a snippet of sample code I have in my available notes:

    -------------------------------------------------------------------

    Code for a “pop up” form:

    Invoking “pop up” frmPopUpBox from “master” frmMain:

    Dim cA As String
    Dim stDocName As String
    Dim stLinkCriteria As String

    Me.Tag = “text” ‘ this text is to be accessed by the “pop up”
    stDocName = "frmPopUpBox"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    Accessing “master” from “pop up”:

    Dim cA As String
    cA = Form_frmMain.Tag ‘ retrieves “text”
    cA = Form_frmMain!txtText.Value ‘ retrieves value of text field txtText on “master”
    Me.Tag = "more text" ‘ this text is to be accessed by the “master”
    DoCmd.Close ‘ closes “pop up”

    Accessing “pop up” from “master” after “pop up” closes

    cA = Form_frmPopUpBox.Tag ‘ retrieves “more text”

    --------------------------------------------------------------------

    I can consult my project that uses this tomorrow, at work, for further info.

  5. #5
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Create a new form, with no record source, and give it a dropdown combo box (with a record source equal to a table or query that lists the acceptable values), a command button, and a label that tells the user to select the desired Supplier Code from the combo box.

    I used frmPopUpBox, cboPopUpList, cmdClose, and lblPopUp as the names for the form, combo box, command button, and label. Give the command button this code:

    Private Sub cmdClose_Click()
    '
    ' if no errors, close pop-up form
    '
    On Error GoTo Err_cmdClose_Click

    If Trim(cboPopUpList.Value) = "" Then
    MsgBox "You must indicate a Supplier Code from the list!", vbOKOnly + vbCritical, "Missing Supplier Code"
    Exit Sub
    End If
    DoCmd.Close
    Exit_cmdClose_Click:
    Exit Sub
    Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

    Then your "home form" code becomes:

    ' add these variable definitions in the routine
    Dim stDocName As String, stLinkCriteria As String

    If Me.Transaction_Type = "Repair" Then
    Me.Part_No = "TOOL-REPAIR"
    Me.Supplier = "00000"
    Me.Stock_Status = "N/A"
    Me.Qty_Req = "1"
    Me.Work_Description = InputBox("Enter description of the Tool Repair")
    stDocName = "frmPopUpBox"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    Me.Work_Supplier = Form_frmPopUpBox!cboPopUpList.Value

    That should do what you want.

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Could be an issue with the last line of code

    Me.Work_Supplier = Form_frmPopUpBox!cboPopUpList.Value


    If the form is not loaded it will generate an error. Therefore it should be the popup form that is populating the main form control. such as:

    Code:
     
    Sub frmPopUpBox_Unload()
     
    If Me.CboPopUpList <> "" Then
        Forms("MainFormName")("ControlName") = Me.CboPopUpList
    End If
     
    DoCmd.Close
     
    End Sub
    David

  7. #7
    jhjarvie is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2008
    Posts
    12
    Works Great........However if the user chooses nothing from the drop down list and leaves the feild null, no error or error message is returned

    Private Sub btn_Close_Click()
    '
    ' if no errors, close pop-up form
    '
    On Error GoTo Err_btn_Close_Click
    If Trim(supplierCode.Value) = "" Then
    MsgBox "You must choose a Supplier Code from the list!", vbOKOnly + vbCritical, "Missing Supplier Code"

    Exit Sub
    End If
    DoCmd.Close
    Exit_btn_Close_Click:
    Exit Sub
    Err_btn_Close_Click:
    MsgBox Err.Description
    Resume Exit_btn_Close_Click
    End Sub

    Any help

  8. #8
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Have you tried--

    If IsNull(supplierCode.Value) Then
    MsgBox "You must choose a Supplier Code from the list!", vbOKOnly + vbCritical, "Missing Supplier Code"

    ElseIf Trim(supplierCode.Value) = "" Then

    Alternatively, you could try initializing the value of supplierCode as a zero-length string ("") when the "pop up" form is called.

  9. #9
    jhjarvie is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2008
    Posts
    12
    Yeh, Got it with this code... Thanks

    On Error GoTo Err_btn_Close_Click
    If IsNull(Raised_By.Value) Then
    MsgBox "You must choose a Name from the list!", vbOKOnly + vbCritical, "Missing Name"
    Exit Sub
    End If
    DoCmd.Close
    Exit_btn_Close_Click:
    Exit Sub
    Err_btn_Close_Click:
    MsgBox Err.Description
    Resume Exit_btn_Close_Click

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

Similar Threads

  1. Replies: 0
    Last Post: 08-26-2009, 11:51 AM
  2. drop down list
    By tceicher in forum Access
    Replies: 5
    Last Post: 08-13-2009, 05:41 AM
  3. Combo-box to select item to edit in Form
    By DHavokD in forum Forms
    Replies: 7
    Last Post: 06-05-2009, 01:39 PM
  4. Open Form with a drop down list box
    By Rinehart in forum Forms
    Replies: 0
    Last Post: 08-10-2008, 08:53 PM
  5. I can't create a drop-down list box
    By cpuser in forum Access
    Replies: 4
    Last Post: 02-18-2008, 10:11 AM

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