DLookUp returning first record when I need a specific record 2

[christophercubitt]

Private Sub Contact_ID_Exit(Cancel As Integer)

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = " & Contact_ID)

End Sub

I am getting the DLookUp to work, but its returning the first result in the table. My code is above. how do I make it more specific to access the Company Name of the Contact ID entered on the form?

 

[vbajock]

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = " & Contact_ID)

If contact_id is a string, then you need single quotes

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = '" & Contact_ID+"'")

 

[TheAceMan1]

How are ya christophercubitt . . .

[blue]vbajock[/blue] has you ... only id use:

[blue]Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = [purple][b]'" & Contact_ID & "'"[/b][/purple])

  [COLOR=DarkGoldenRod][b]See Ya! .  .  .  .  .  .[/b][/color]

Be sure to see thread181-473997  [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886  [blue]Worthy Reading![/blue] [thumbsup2]

 

[TheAceMan1]

So Sorry ... shoulb be:

[blue]Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = [purple][b]'" & Contact_ID & "'"[/b][/purple])[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Johnnycat1]

I am dealing with the same issue only on a Dsum not a Dlookup. I have been fine making it work when using a string value but have struggled becuase the criteria that I am using is an autonumber integer. When I change the PPID to a string value it gives me a type mismatch and when use the integer I just get the first value.

Chris, are you using string values or another data type?

my code looks like this:

Dim IntPPID As Integer
Dim strSubcontractAmount As String
Dim rstProjectPricingEndSheet As DAO.Recordset

IntPPID = Forms![FrmProjectPricing]![PPID]

strSubcontractAmount = DSum("[SubcontractPrice]", "TblProjectPricingSubcontracts", IntPPID = [PPID])

Set rstProjectPricingEndSheet = CurrentDb.OpenRecordset("TblProjectPricingEndSheet", dbOpenDynaset)

With rstProjectPricingEndSheet
.FindFirst [PPID] = IntPPID
.Edit
![Subcontracts] = strSubcontractAmount
.Update

End With

Any suggestions?

 

[MajP]

Johnny,
First you should ensure that the following words are always at the top of any module.
Option Explicit

This foreces you to declare variables, and will catch a lot of problems.
Your problem, which has been hinted to above is here
..., IntPPID = [PPID])
The criteria needs to be passed as a string. So to repeat what has AceMan has already said.
it the field is numeric:
...,"[MyFieldName] = " & myControlName)

if the field is a text type
...,"[MyFieldName] = '" & myControlName & "'")

if it is a date
...,"[MyFieldName] = #" & myControlName & "#")

 

[TheAceMan1]

How are ya Johnnycat1 . . .

Surely [blue]SubcontractPrice[/blue] is numeric!

[blue]Change: Dim strSubcontractAmount As String
To    : Dim [purple][b]cur[/b][/purple]SubcontractAmount As [purple][b]Currency[/b][/purple][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Johnnycat1 . . .

I forgot to mention that you should always start your own thread. This gives you maximum exposure in the forum instead of you being hidden in a thread!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[vbajock]

strSubcontractAmount = DSum("[SubcontractPrice]", "TblProjectPricingSubcontracts", IntPPID = [PPID])

Should be

strSubcontractAmount = DSum("[SubcontractPrice]", "TblProjectPricingSubcontracts", IntPPID =Str$(! [PPID]))

 

[vbajock]

oops, missed a quote mark, this is it

strSubcontractAmount = DSum("[SubcontractPrice]", "TblProjectPricingSubcontracts", "IntPPID ="+Str$(! [PPID]))

 

[MajP]

vbajock,
strike three your out.

 

[vbajock]

Please feel free to bite me.

 

[vbajock]

2 Aceman1 & and + are interchangeable, makes no difference which one you use

 

[MajP]

2 Aceman1 & and + are interchangeable, makes no difference which one you use

No there are not, and can cause big problems in certain situations.

string & Null = string
string + Null = Null

string & number = string
string + number = likely a type mismatch error

 

[TheAceMan1]

vbajock . . .

Apparently you've never looked at VBA help on the [blue]+ Operator[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

To see what problems that would cause. Use your own example

Company_Name = DLookup("CompanyName", "Contacts", "[Contact_ID] = '" & Contact_ID+"'")

Public Sub testPlus()
 Dim contact_ID As Variant
 contact_ID = 123
'Debug.Print "[Contact_ID] = '" & contact_ID + "'"
 contact_ID = "ABC"
Debug.Print "[Contact_ID] = '" & contact_ID + "'"
 contact_ID = Null
Debug.Print "[Contact_ID] = '" & contact_ID + "'"
Debug.Print "[Contact_ID] = '" & contact_ID & "'"
End Sub

Contact_ID = 123 causes a type mismatch and code fails
(although you did not suggest this for numeric, just to show they are not interchangeable)

contact_ID = Null results in:
[Contact_ID] = '
and your dlookup fails
Using the second & in this case as AceMan suggested results in:
[Contact_ID] = ''
and the dlookup works, finding no records.

 

[gcDataTechnology]

Christophercubitt, did you get the dlookup working and is the answer in this thread that solved your issue?

Second I was unclear on what the original problem was. Was the look up returning the first record that matched your criteria (indicating there are several records that would match), or did it return the first record in the table not necessarily matching your search criteria?

Learnin', Growin' and Failing Forward

 

[TheAceMan1]

How are ya gcDataTechnology . . .

[blue] 17 Dec 10 12:45
I am getting the DLookUp to work, but [purple]its returning the first result in the table[/purple].[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[gcDataTechnology]

Thanks for responding TheAceMan1.

From what I've read that's its job. It returns only the first record that matches the criteria.

If this table were to hold mutliple records with the same criteria would it then be subject to the way the table has been sorted as apposed to when the record was entered?

I use this function to search tables that hold only unique values because I didn't want to struggle with returning past records.

Learnin', Growin' and Failing Forward

 

[vbajock]

Aceman, try it yourself, I've got ten years of code writing it either way. It makes absolutely no difference.