DLookup

[mischa]

Hello again,

I would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.

If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.

I found multiple examples on the internet for using multiple criteria but something similar to what I require. Therefore I lookforward if someone is able to help me solve this issue.

Thanks in advance!

 

[pr2-eugin]

The General: DLookup Usage Samples are listed.

If you want specific help, give us more information or show the code you have come up with so far !

 

[elizabath]

You can use DLookup, but it will take a while for 2million+ rows, and all it really does is bypass the SQL engine...

Access is designed to handle relational tables, SOO in the example You used I'm assuming when You say "another worksheet will just have a random id column" that each "Id" COULD repeat and You need the "new column" to show the appropriate "CC" for each "ID".

Each Worksheet becomes a TABLE.
You build a query which includes BOTH tables and LINKS them at ID (drag ID from one table and "drop" it on the other in the Query Design window). The result will show You all of the "ID"s on the 2nd table and the appropriate (related) "CC".

 

[mischa]

You can use DLookup, but it will take a while for 2million+ rows, and all it really does is bypass the SQL engine...

Access is designed to handle relational tables, SOO in the example You used I'm assuming when You say "another worksheet will just have a random id column" that each "Id" COULD repeat and You need the "new column" to show the appropriate "CC" for each "ID".

Each Worksheet becomes a TABLE.
You build a query which includes BOTH tables and LINKS them at ID (drag ID from one table and "drop" it on the other in the Query Design window). The result will show You all of the "ID"s on the 2nd table and the appropriate (related) "CC".

Thanks for the quick replies!

Elizabath, you gave me a new idea how I could do this more easily using an existing query I build. The function I am currently using is:

        If lstLinkCategory.Value = "Components" And lstLinkToCategory.Value = "Products" Then
            ComponentLinkProductCheck = DLookup("ProductPN", "qryShowComponentLinks", "ProductPN=lstLinkPart.Value")
            If ComponentLinkProductCheck <> 0 Then
                MsgBox "Link already exist"
                Exit Sub
            Else
                Exit Sub
            End If
        End If

At the moment I get an error due to the fact that the DLookup generates a value which is Null and I don't know what I did wrong

 

[pr2-eugin]

Okay for starters, you have to look through the link I provided in Post#2 as you need to concatenate the Value to the Criteria not include it. Is ProductPN a Number type/Text type/Date type? based on that, the criteria should look like..

"ProductPN = " & Me.lstLinkPart)    [COLOR=Green]'Number[/COLOR]
"ProductPN = '" & Me.lstLinkPart & "'")    [COLOR=Green]'Text[/COLOR]
"ProductPN = " & Format(Me.lstLinkPart, "\#mm\/dd\/yyyy\#"))   [COLOR=Green] 'Date[/COLOR]

respectively..

Next you would be better of using DCount that DLookup. If you insist on using DLookup, I suggest you wrap it with Nz function.

 

[mischa]

Okay for starters, you have to look through the link I provided in Post#2 as you need to concatenate the Value to the Criteria not include it. Is ProductPN a Number type/Text type/Date type? based on that, the criteria should look like..

"ProductPN = " & Me.lstLinkPart)    [COLOR=Green]'Number[/COLOR]
"ProductPN = '" & Me.lstLinkPart & "'")    [COLOR=Green]'Text[/COLOR]
"ProductPN = " & Format(Me.lstLinkPart, "\#mm\/dd\/yyyy\#"))   [COLOR=Green] 'Date[/COLOR]

respectively..

Next you would be better of using DCount that DLookup. If you insist on using DLookup, I suggest you wrap it with Nz function.

Thanks for the info pr2-eugin

The suggestion for DCount sounds good to me so I implemented this into the database which works perfectly!

 

[pr2-eugin]

Glad to have helped ! Good Luck !