Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Response Search (97)

    Hello All,

    I want to take a User response from an input box and compare it to a field in a table...if there is a match, provide the response....I have the code for the above providing there is a match, however, if there isn't a match I get an error.

    What I would like to do is add .25 to the user response until there is a match. This is the part I need help with. I believe I need a loop code and have read up on it and can't quite get it to work....could someone please provide some suggestions for me. If a loop code is not required, I'm open to anything.

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: User Response Search (97)

    Yes, you could provide a loop as long as the table is sorted into the amount field.
    But what are you trying to do? You could maybe have a combo box that could display the amount from the table in amount order so it would be easy for the user to select. But I'll bet right here and now, you don't want to allow them to do that.
    What is this request, some form of Lotto (only joshin')?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Response Search (97)

    Patt,

    The msg box allows the user to input a Number, say 35. The idea is 35 is used as a parameter so to speak in a field that the user can't see. If the number exists it returns another value that is already calculated corresponding to it. All is well if the number already exists. However, if the number doesn't exist, is where my problem comes in. It maybe that 35.25 exists therefore, I would like code to first determine if the user response first exist, if so, return the calculated response, if not, start incrementing the user response by .25 until it does.

    By preference and privacy, I don't want to provide this information to the user via a combo box........as well as in time the combo box would get very lengthy with it's selections.

    If you could give me a jump start on the loop code I would need I would appreciate it. Below is what I have already and it's not working:

    Dim strMsg As String, strInput As String, strHW As String, Result As String
    Dim intEP As Integer, intHW As Integer
    Dim Counter
    Counter = 0
    strInput = InputBox("Enter the Number of Hours to Calculate")
    strHW = strInput
    Me.txtHW = strHW
    Result = (Eval("DLookup(""[ActualNetPay]"", ""tblEPvsAP"",""[HrsWorked]=forms!frmHWtest2!txtHW"") Is Not Null"))
    If IsNull(Result) Then
    strHW = strInput + 0.25
    Do Until strHW >= intHW
    Result = (Eval("DLookup(""[ANP]"", ""tblEPvsAP"",""[HWd]=forms!frmHWtest2!txtHW"") Is Not Null"))
    Loop
    Exit Sub
    End If
    intEP = Result
    Me.txtEP.Visible = True
    Me.txtEP = intEP
    Me.TimerInterval = 3000
    End Sub

    Hope this is clear and I appreciate your help.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: User Response Search (97)

    I would tend to change the loop to:
    Dim dbs as Database, rs as Recordset, strSQL as String
    Set dbs = CurrentDB
    strHW = strInput
    Me!txtHW = strHW
    strSQL = "SELECT ActualNetPay FROM tblEPvsAP WHERE HrsWorked = forms!frmHWtest2!txtHW"
    Set rs = dbs.OpenRecordset(strSQL)
    Do While rs.EOF
    strHW = strHW + 0.25
    Me!txtHW = strHW
    Set rs = dbs.OpenRecordset(strSQL)
    If Not rs.EOF then Exit Do
    Loop
    Set rs = Nothing
    Set dbs = Nothing

    You will probably have to put a limiter in the loop as it could go on endlessly.
    As an alternative, If there are not too many entries in the table, I would tend to read thru the table sorted on ActualNetPay and when you get to the EOF then you have run out of records.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Response Search (97)

    Perhaps you don't need a loop at all. How about

    strHW = DMin("[HrsWorked]", "[tblEPvsAP]", "[HrsWorked] >= " & Forms!frmHWtest2!txtHW)
    intEP = DLookup("[ANP]", "[tblEPvsAP]", "[HoursWorked] = " & strHW)

    The first statement finds the lowest value of HrsWorked in the table that is greater than or equal to txtHw.
    The second statement looks up the corresponding value of ANP.

Posting Permissions

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