Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Need help tweaking this VBA. Input box value for a form

    I have a cmd button that when clicked opens asks the user what projectID they want to open and then opens the form based off that input box. This is working but could use some tweaking.

    Private Sub cmdOpenPreviousProject_Click()
    On Error GoTo Err_cmdOpenPreviousProject_Click

    Dim AskProjectID As String

    AskProjectID = InputBox("Please Enter a Valid Project Number", "Project Number Required")

    If AskProjectID = "" Then
    MsgBox "Enter Project Number to Continue", vbInformation, "Project Number Value Needed"
    Else
    DoCmd.OpenForm "frmSpecialProjects", , , "ProjectID=" & AskProjectID, acFormReadOnly

    Exit_cmdOpenPreviousProject_Click:
    Exit Sub

    Err_cmdOpenPreviousProject_Click:
    MsgBox Err.Description
    End If
    End Sub
    After using the button a few times i noticed that when the input box opens it asks for the projectID which is prefect. If you put a projectID in and click okay the world keeps spinning. However if you hit cancel the msgbox pops up saying projectID required. Idealy this would just cancel the event. And if you leave input box empty and hit okay the msg box saying you need a projectID pops up and then just ends the event. Meaning the user needs to then click the button again and enter the value.

    Again this works but I just think it could be tweaked some.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an alternative approach is to use a listbox populated with project numbers
    attach your code to open the form based on that selection
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    an alternative approach is to use a listbox populated with project numbers
    attach your code to open the form based on that selection
    You're right. I do this for my reports in just about every db i build. The problem is the amount of projects will get massive and cumbersome to look through. Also this button lives on my dash board and there really isn't a spot for a list box like this.

    Let me work on this idea b/c i may work. Would still love to see how to clean up the vba code though.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem is that a user may not enter a project number, or arguably an invalid one, hence why I am in favour of the combo/list box with only valid project numbers

    however if you prefer stickingto your design approach, which is after your call.
    I'd suggest you push the inputbox into some form of loop that keeps re asking the question until the user either supplies a project number or some other action. if the user is entering the project number from the keyboard then they could easily supply an invalid number so you could validate the input within the loop.

    one question is how do users cancel the request for project number and equally how do you handle that request.

    Code:
    dim StayInLoop as boolean
    Dim AskProjectID As String
    StayInLoop=true 'forces the code to stay in a loop
    while StayInLoop=true
        AskProjectID = InputBox("Please Enter a Valid Project Number", "Project Number Required","CANCEL")
        'ok we've got the user response, it could be a project number or the default which is 'cancel'
        if ucase(askprojectid) ="CANCEL" then 'user wants to abort this process
            stayinloop=false 'so we can clear the stay in loop flag and handle the abort elsewhere
           'if we were a believer in messy string code we could instead use 'exit sub' here in place of changing the satyinloop variable
        elseif len(askprojectID) >0 then 'we are only going to test the project id if there is soemthign here
            'a blank entry is unacceptable, so we leave the satytinloop as true
            'validate the project number
            'use one of these constructs
            'either
            'if the project id is numeric then test its a valid number before doing the dlookup
            if isnumeric(askprojectid) then 'test the project id IS numeric (assuming it should be)
                if DLookup(myprojectid, mytable , "myprojectid=" & AskProjectID )  <> NULL then 'we have found the project number
                    stayinloop=false
                endif
            endif
            'OR if the projectid column is string or alphanumeric then use htis construct
            if DLookup(myprojectid, mytable , "myprojectid= '" & AskProjectID & "'" )  <> NULL then 'we have found the project number
                stayinloop=false
            endif
        endif
    end while
    'ok so we get to here with a value in askprojectid which can be either
    'CANCEL' OR the proven 'good' project id
    'you'll need to tweak your function to handle this
    if ucase(askprojectid) = 'CANCEL' then exit sub
    'otherwise call your form/report
    the above is air code, untried, untested, comes with no guarantees.. its an example of an approach you may well have to refine to make it work
    you will need to replace
    myprojectid with the name of the project id column in the table
    mytable with the name of the table
    select which of the 'dlookups' you need to use depending on whether the myproject id column is numeric or string
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •