Results 1 to 10 of 10
  1. #1
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82

    Button that prompts you for data input before it allows you to continue?


    What I'm trying to achieve is that when the user clicks the button Surplus Asset to prompt them for a Work Order Number and make it required before it continues to run the rest of the code, so if the value is null it won't let the user continue and the only way to stop is to cancel or to enter a work order number. Is this possible? Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is purpose of requiring this? What do you want done with the input - populate field on form? Is WorkOrderNumber another field on the form? Is it set to Required in the table?

    Examples of code with InputBox:
    Code:
            While strTest = "?"
                strTest = InputBox("Select Test Method (Enter 1 thru 5): " & vbCrLf & _
                    "1. T84" & vbCrLf & "2. T100" & vbCrLf & "3. T209" & vbCrLf & "4. T228" & vbCrLf & "5. T304", "Edit Calibrations", "?")
                If strTest Like "[1-5]" Then
                    DoCmd.OpenTable "PycCal" & Choose(strTest, "T84", "T100", "T209", "T228", "T304")
                    If strTest = 4 Then
                        If MsgBox("Do you want to open the T228 spreadsheet?", vbYesNo) = vbYes Then FollowHyperlink ("\\dotatufs02\Crm\Lab\Calibration\SpG Pycnometers\T228.xlsx")
                    End If
                ElseIf strTest <> "" Then
                    MsgBox "Not a valid entry. Select 1 through 5 or Cancel."
                    strTest = "?"
                End If
            Wend
    _____________________________
    
        Do
            strTestTime = InputBox("Enter a time value for test number 800 - Special Testing.")
        Loop Until IsNumeric(strTestTime) Or Len(strTestTime) = 0
    _____________________________
    
        strRDC = IIf(strMode = "Draft", "X", "?")
        While strRDC = "?"
            strRDC = UCase(InputBox("Enter item ID for report distribution:" & vbCrLf & vbCrLf & _
                "  1   - Highway Construction" & vbCrLf & vbCrLf & _
                "  2   - Preconstruction" & vbCrLf & vbCrLf & _
                "  3   - HAP/ACP Mix Designs" & vbCrLf & vbCrLf & _
                "  4   - Northern Region" & vbCrLf & vbCrLf & _
                "  5   - Southeast Region" & vbCrLf & vbCrLf & _
                "  6   - Accepted Products" & vbCrLf & vbCrLf & _
                "  7   - M&O Central Region" & vbCrLf & vbCrLf & _
                "  8   - Special" & vbCrLf & vbCrLf & _
                "  9   - Aviation Construction" & vbCrLf & vbCrLf & _
                "  10  - Concrete Mix" & vbCrLf & vbCrLf & _
                "  11  - AIA Field Maintenance" & vbCrLf & vbCrLf & _
                "  N   - Number File" & vbCrLf & vbCrLf & _
                "  X   - Copy not for distribution nor filing", "Distribution", "X"))
            If (Val(strRDC) > 0 And Val(strRDC) < 12) Or strRDC Like "[N,X]" Then
                'continue with report print
                If IsNumeric(strRDC) Then strRDC = "D" & strRDC
            ElseIf strRDC <> "" Then
                MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
                strRDC = "?"
            Else
                GoTo Exit_proc
            End If
        Wend
    _____________________________
    
                strInput = "?"
                While strInput = "?"
                    strInput = InputBox("Select Flat-Elongated ratios to test." & vbCrLf & vbCrLf & _
                    "     3     1:3" & vbCrLf & vbCrLf & _
                    "     5     1:5" & vbCrLf & vbCrLf & _
                    "     B     Both")
                    If strInput = "3" Or strInput = "5" Or strInput = "B" Then
                        Condition = UCase(strInput)
                    Else
                        MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
                        strInput = "?"
                    End If
                Wend
    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.

  3. #3
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    That's not too hard. If you're using the InputBox to get input you could do this (you'd want to store as string initially):
    workNum = InputBox("Enter the Work Number (or Press Cancel)")
    If workNum <> "" Then
    'code you want to here
    Else
    msgbox "Report cancelled by user"
    End If

    It would work similarly for most input methods. Note that it only stops code in that method unless you use some form of error handling, or if using a form or report, set Cancel = True during the forms OnOpen event.

  4. #4
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    What is purpose of requiring this? What do you want done with the input - populate field on form? Is WorkOrderNumber another field on the form? Is it set to Required in the table?

    Examples of code with InputBox:
    Code:
            While strTest = "?"
                strTest = InputBox("Select Test Method (Enter 1 thru 5): " & vbCrLf & _
                    "1. T84" & vbCrLf & "2. T100" & vbCrLf & "3. T209" & vbCrLf & "4. T228" & vbCrLf & "5. T304", "Edit Calibrations", "?")
                If strTest Like "[1-5]" Then
                    DoCmd.OpenTable "PycCal" & Choose(strTest, "T84", "T100", "T209", "T228", "T304")
                    If strTest = 4 Then
                        If MsgBox("Do you want to open the T228 spreadsheet?", vbYesNo) = vbYes Then FollowHyperlink ("\\dotatufs02\Crm\Lab\Calibration\SpG Pycnometers\T228.xlsx")
                    End If
                ElseIf strTest <> "" Then
                    MsgBox "Not a valid entry. Select 1 through 5 or Cancel."
                    strTest = "?"
                End If
            Wend
    _____________________________
    
        Do
            strTestTime = InputBox("Enter a time value for test number 800 - Special Testing.")
        Loop Until IsNumeric(strTestTime) Or Len(strTestTime) = 0
    _____________________________
    
        strRDC = IIf(strMode = "Draft", "X", "?")
        While strRDC = "?"
            strRDC = UCase(InputBox("Enter item ID for report distribution:" & vbCrLf & vbCrLf & _
                "  1   - Highway Construction" & vbCrLf & vbCrLf & _
                "  2   - Preconstruction" & vbCrLf & vbCrLf & _
                "  3   - HAP/ACP Mix Designs" & vbCrLf & vbCrLf & _
                "  4   - Northern Region" & vbCrLf & vbCrLf & _
                "  5   - Southeast Region" & vbCrLf & vbCrLf & _
                "  6   - Accepted Products" & vbCrLf & vbCrLf & _
                "  7   - M&O Central Region" & vbCrLf & vbCrLf & _
                "  8   - Special" & vbCrLf & vbCrLf & _
                "  9   - Aviation Construction" & vbCrLf & vbCrLf & _
                "  10  - Concrete Mix" & vbCrLf & vbCrLf & _
                "  11  - AIA Field Maintenance" & vbCrLf & vbCrLf & _
                "  N   - Number File" & vbCrLf & vbCrLf & _
                "  X   - Copy not for distribution nor filing", "Distribution", "X"))
            If (Val(strRDC) > 0 And Val(strRDC) < 12) Or strRDC Like "[N,X]" Then
                'continue with report print
                If IsNumeric(strRDC) Then strRDC = "D" & strRDC
            ElseIf strRDC <> "" Then
                MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
                strRDC = "?"
            Else
                GoTo Exit_proc
            End If
        Wend
    _____________________________
    
                strInput = "?"
                While strInput = "?"
                    strInput = InputBox("Select Flat-Elongated ratios to test." & vbCrLf & vbCrLf & _
                    "     3     1:3" & vbCrLf & vbCrLf & _
                    "     5     1:5" & vbCrLf & vbCrLf & _
                    "     B     Both")
                    If strInput = "3" Or strInput = "5" Or strInput = "B" Then
                        Condition = UCase(strInput)
                    Else
                        MsgBox "Not an appropriate entry.", vbApplicationModal, "Entry Error"
                        strInput = "?"
                    End If
                Wend
    The purpose is so the user doesn't forget to put in the work order number so they can track the asset that was surplused, every time a item is surplused there is usually a work order that go's with it so that there is a paper trail. With everyones help here I've made a button called Surplus Asset that will take all the data and related table information linked to the autonumber of the asset and copy it to another table then delete it from the active assets. But Before that happens I want to make sure the user enter's in the work order number, the work order number will be added to the table that the data was copied to, so Assets -> SurplusedAssets and I'll have a field in SurplusedAssets called WorkOrderNumber or something like that.

  5. #5
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by drexasaurus View Post
    That's not too hard. If you're using the InputBox to get input you could do this (you'd want to store as string initially):
    workNum = InputBox("Enter the Work Number (or Press Cancel)")
    If workNum <> "" Then
    'code you want to here
    Else
    msgbox "Report cancelled by user"
    End If

    It would work similarly for most input methods. Note that it only stops code in that method unless you use some form of error handling, or if using a form or report, set Cancel = True during the forms OnOpen event.
    I'll give it a shot and go through what both you and June recommended thanks guys!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why move assets between tables? Could just have another field that indicates asset status.
    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.

  7. #7
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    Why move assets between tables? Could just have another field that indicates asset status.
    I agree with you on this but the people I'm doing this for is picky on this stuff and want it separated from the active data, once something is surplused it's not ever coming back, they want a paper trail. And I'd have to put more filters in my forms to filter out the ones that have been surplused which wouldn't be hard (I think).

    Right now there are three types of assets, Active, Retired, and Surplused.

    Retired I have filtering by if there is a date in the retired date column, although I'm still struggling with my query search to filter retired data, I know it will filter if I put "Is Null" in my query but I wanted to be able to toggle that between "Is Null" and "Not Is Null" and link it with my other filter, I tried a If statement with my checkbox to change the data in a text box and assigned that txtbox to my query but it doesn't seem to work.

    Not trying to change the subject but here's what I did on my query search.

    Private Sub chkRetireFilter()

    If me.ChkRetireFilter = True then
    me.txtRetireFilter.Value = "Is Null"
    else
    me.txtRetireFilter.Value = "Not Is Null"
    end if

    I was hoping to put txtRetireFilter in my query's record source under RetiredDate's criteria, but it didn't work the way I was hoping.

  8. #8
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by 82280zx View Post
    I agree with you on this but the people I'm doing this for is picky on this stuff and want it separated from the active data, once something is surplused it's not ever coming back, they want a paper trail. And I'd have to put more filters in my forms to filter out the ones that have been surplused which wouldn't be hard (I think).

    Right now there are three types of assets, Active, Retired, and Surplused.

    Retired I have filtering by if there is a date in the retired date column, although I'm still struggling with my query search to filter retired data, I know it will filter if I put "Is Null" in my query but I wanted to be able to toggle that between "Is Null" and "Not Is Null" and link it with my other filter, I tried a If statement with my checkbox to change the data in a text box and assigned that txtbox to my query but it doesn't seem to work.

    Not trying to change the subject but here's what I did on my query search.

    Private Sub chkRetireFilter()

    If me.ChkRetireFilter = True then
    me.txtRetireFilter.Value = "Is Null"
    else
    me.txtRetireFilter.Value = "Not Is Null"
    end if

    I was hoping to put txtRetireFilter in my query's record source under RetiredDate's criteria, but it didn't work the way I was hoping.
    I think you just solved a headache of mine by me reading what I just put... I'm going try something and see if this works better, I'll make three categories, Active, Retired, and Surplused and be able to filter by the category. I guess I can also make it so when Surplused is selected to make the work order number required.

    On to one more question,

    I have Assets that have components tied to them, they're in the same table because they're considered an asset, so I have a query and a component Number that I added to assets.

    So I have AssetID which is an auto number, and I linked it in a query with the master field being AssetID and the Child being ComponentID, so I can tell what components/assets is tied to each other, now my question is how can I create a button that makes a new asset on the same form but links AssetID and ComponentID together, I'm currently trying something to see if it works but not sure if it will work, anyways after they get done editing the data I was hoping to bring them back to the Asset they added the component on? I hope that made sense.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You have an AssetComponents table that associates Primary asset with its Component assets?

    Might find this interesting http://www.tomjewett.com/dbdesign/db...=recursive.php
    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.

  10. #10
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    You have an AssetComponents table that associates Primary asset with its Component assets?

    Might find this interesting http://www.tomjewett.com/dbdesign/db...=recursive.php
    That's actually what I'm doing, and it works the way I want it to but I'm trying to make it idiot proof so most of the work is done with a button.

    Example.
    Lets say I have my Asset named Computer 1 Open, you go to the components tab, they can enter the data into the query I have below and it will create a new asset and link that asset to Computer 1. But I would rather them be able to use the form that I designed for asset, so my goal is to have the user click New Component, and it opens to a new asset form, and when they finish editing the new component/asset bring them back to the asset they was working on. And I think I know how to do it but I'm still working on it at the moment.

    Btw the three category filter and the SELECT DISTINCT you taught me worked out great for my filters on my two forms thanks for teaching me that.

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

Similar Threads

  1. Replies: 5
    Last Post: 11-26-2013, 09:20 AM
  2. Replies: 1
    Last Post: 11-15-2011, 07:26 AM
  3. Replies: 1
    Last Post: 09-20-2011, 05:57 AM
  4. Command button to input data in a table
    By wilsgaard in forum Forms
    Replies: 1
    Last Post: 08-07-2011, 07:59 PM
  5. Replies: 5
    Last Post: 07-13-2010, 11:48 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