Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: DLookup (2003)

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DLookup (2003)

    Will DLookup, throw up the #Name error if it finds duplicates in the table it is searching in? I I first used IWO number as the lookup, but discovered there were duplicates. If so, how can I lookup just unique numbers, and if the IWO is not unique, allow the user to enter the data rather than look it up?

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

    Re: DLookup (2003)

    If there are duplicates, DLookup will return the first item that satisfies the condition. If you get #Name, there is an error in the expression.

    If you have a textbox with a formula =DLookup(...) as control source, the user can't enter anything in the text box - calculated controls are automatically locked for editing. You could use code to fill the text box; the user can then edit the contents if desired.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DLookup (2003)

    Thanks Hans. I did what you suggested, and it did work, but when I moved to the next control, the data disappeared. I realized I had code in the BeforeUpdate expression. This relates to <post#=550304>post 550304</post#>, where I needed to check for duplicates. When I removed this code, it worked, but I no longer have my check for duplication. Could I move the code somewhere else, and still have it work?

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

    Re: DLookup (2003)

    I don't know what code you're using, and the attachment in the thread you refer to has been lost, so you'll have to provide more information.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DLookup (2003)

    This is the code:-

    Private Sub ALLOCATED_X_RAY_No_s_BeforeUpdate(Cancel As Integer)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String, strCode As String, strSuffix As String, strStart As String, strEnd As String
    Dim lngStart As Long, lngEnd As Long
    Dim lngSplit As Long
    Set cnn = CurrentProject.Connection
    strCode = Left(ALLOCATED_X_RAY_No_S, 2)
    lngSplit = InStr(ALLOCATED_X_RAY_No_S, "-")
    strStart = Trim(Mid$(ALLOCATED_X_RAY_No_S, 4, lngSplit - 4))
    strEnd = Trim(Mid$(ALLOCATED_X_RAY_No_S, lngSplit + 1))
    If CStr(Val(strStart)) = strStart Then
    strSuffix = ""
    Else
    strSuffix = Right$(strStart, 1)
    End If
    lngStart = Val(strStart)
    lngEnd = Val(strEnd)
    strSQL = "SELECT COUNT(*) FROM [RELEASE NOTE X-RAY DETAILS]WHERE [Part No] = '" & Me![Part No] & "' AND Code = '" & strCode & "' AND [Suffix] = '" & strSuffix & "'"
    strSQL = strSQL & " AND (([Start] <= " & lngStart & " AND [End] >= " & lngStart & ") OR ([Start] <= " & lngEnd & " AND [End] >= " & lngEnd & ") OR ([Start] >= " & lngStart & " AND [End] <= " & lngEnd & "))"
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly
    If rst(0) >= 1 Then
    MsgBox "This serial number range overlaps an existing one. Please double-check and try again."
    Cancel = True
    Else
    Me.Code = strCode
    Me.Start = lngStart
    Me.End = lngEnd
    Me.Suffix = strSuffix
    End If
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    And also, in the OnCurrent expression of the Form

    Private Sub Form_Current()
    [ALLOCATED_X_RAY_No_S] = [Code] & " " & [Start] & [Suffix] & "-" & [End] & [Suffix]

    End Sub

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

    Re: DLookup (2003)

    Do you get the message box

    MsgBox "This serial number range overlaps an existing one. Please double-check and try again."

    after entering data in ALLOCATED_X_RAY_No_s?

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DLookup (2003)

    Not if data is entered using DLookup, using the Batch No as the search criteria. If I entered the Xray Nos directly into the control I do (assuming they are duplicates)

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

    Re: DLookup (2003)

    I don't really understand what's happening, so I can't help without seeing (a stripped down copy of) the database.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DLookup (2003)

    Here's a stripped down copy of the database. As you will see, entering the Batch No brings up the X-ray No as required, but if you click out of the Allocated X-Ray Nos control, and back in, they disappear. Also, if I enter the same Batch No, the duplicate message doesn't come up.
    Attached Files Attached Files

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

    Re: DLookup (2003)

    You must fill the Code, Start, End and Suffix controls too when you fill the Allocated X-Ray No's control:

    Private Sub BATCH_No_AfterUpdate()
    Dim varIWO As Variant
    Dim Cancel As Integer
    varIWO = DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]")
    If (Not IsNull(varIWO)) Then
    Me.ALLOCATED_X_RAY_No_S = varIWO
    ALLOCATED_X_RAY_No_s_BeforeUpdate Cancel
    End If
    End Sub

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DLookup (2003)

    Thanks for that Hans. With a bit of adapting, it's now filling in all the data, just from the Batch No. Thanks again.

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Everythings seems to be working fine, but I've just discovered a downside to the system. We have the database that creates the Xray numbers, and won't allow duplicates, but the IWO number can be the same. We could have IWO No 12345, Xray No's EB 1-20, and IWO No 12345, Xray No's 21-40. DLookup will only find the first instance of the IWO, and when we enter it again, it quite rightly throws up an error, saying these No's have already been used. The user can easily override these numbers, but it would be less prone to errors if we could get it to do it automatically. Is there a way that I can get the lookup to check the next IWO No it comes to for matches, and insert the next sequence of Xray No's?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='robm' post='762133' date='27-Feb-09 11:04'][/quote]
    It's been a long time since the previous posts in this topic. I've forgotten what it's all about. Could you explain clearly and in detail what you want?

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Basically, I had Dlookup a set of Xray No's from another form, based on the IWO matching. Now, because we can have the same IWO No with different Xray No's, it's causing a problem. When the IWO is typed in the form, only the first instance of the IWO is being retrieved. So if we have, say :-

    IWO No Xray
    14958 EB 1-20
    14958 EB 21-30
    14958 EB 31-40

    in one form, and we type in 14958 in the control of the form we want to populate, only EB 1-20 is being brought in. And because we don't allow duplicate Xray No's, typing in 14958 on the next line, brings up the same results, telling us we can't have duplicates.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='robm' post='762177' date='27-Feb-09 14:51'][/quote]
    I've looked at the database, but I get the impression there's something missing. I don't have the slightest idea where the "next" sequence should come from.

Page 1 of 2 12 LastLast

Posting Permissions

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