Dlookup - Multiple items in the [Criteria]

[larochy]

Hi,

I'm trying to do a Dlookup with multiple items in the [Criteria] section and
it's not working. Here is the code:

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
[Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]=
[tblProjectRates]![RoleName]" And "[ProjectID] =
[tblProjectRates]![ProjectID]")
Else
Me.Rate = [Text51]
End If
End Sub

I store rates in a table called [tblProjectRates] and the rate is tied to a
RoleName and the specific ProjectID. When the user updates the [role]
ComboBox in the form, I want it to automatically populate the [Rate] field
with the appropriate rate based on the ProjectID and the Role stored in the
Project Rates table.

Any code corrections would be much appreciated.

Thanks

 

[John W. Vinson]

I store rates in a table called [tblProjectRates] and the rate is tied to a
RoleName and the specific ProjectID. When the user updates the [role]
ComboBox in the form, I want it to automatically populate the [Rate] field
with the appropriate rate based on the ProjectID and the Role stored in the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an apostrope
' character, and that ProjectID is a Number datatype.

 

[larochy]

Thanks for the quick response John. Project ID is a text field, would that
change the code?

I store rates in a table called [tblProjectRates] and the rate is tied to a
RoleName and the specific ProjectID. When the user updates the [role]
ComboBox in the form, I want it to automatically populate the [Rate] field
with the appropriate rate based on the ProjectID and the Role stored in the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an apostrope
' character, and that ProjectID is a Number datatype.

 

[Douglas J. Steele]

Yes. You'd need quotes around the value being used:

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the quick response John. Project ID is a text field, would
that
change the code?

I store rates in a table called [tblProjectRates] and the rate is tied
to a
RoleName and the specific ProjectID. When the user updates the [role]
ComboBox in the form, I want it to automatically populate the [Rate]
field
with the appropriate rate based on the ProjectID and the Role stored in
the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an
apostrope
' character, and that ProjectID is a Number datatype.

 

[larochy]

Thanks for the help. It's giving me a run time error now as follows:

Run time error code '2465':

Database can't find the field 'l' referred to in your expression

When I click Debug, it highlights this statement as the problem. Any
suggestions?

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")

Yes. You'd need quotes around the value being used:

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the quick response John. Project ID is a text field, would
that
change the code?

On Thu, 4 Sep 2008 23:18:01 -0700, larochy
<[email protected]>
wrote:

I store rates in a table called [tblProjectRates] and the rate is tied
to a
RoleName and the specific ProjectID. When the user updates the [role]
ComboBox in the form, I want it to automatically populate the [Rate]
field
with the appropriate rate based on the ProjectID and the Role stored in
the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an
apostrope
' character, and that ProjectID is a Number datatype.

 

[Douglas J. Steele]

Hold on. I didn't look that closely at what you were doing before!

Unfortunately, John's advice was incorrect. You cannot refer to fields in a
table like that. In other words, the references to
[tblProjectRates]![RoleName] and [tblProjectRates]![ProjectID] are
incorrect. Assuming that the RoleName and ProjectID are contained on
controls on your form, use

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
Me![RoleName] & "' And [ProjectID] = '" & _
Me![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the help. It's giving me a run time error now as follows:

Run time error code '2465':

Database can't find the field 'l' referred to in your expression

When I click Debug, it highlights this statement as the problem. Any
suggestions?

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")

Yes. You'd need quotes around the value being used:

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the quick response John. Project ID is a text field, would
that
change the code?

:

On Thu, 4 Sep 2008 23:18:01 -0700, larochy
<[email protected]>
wrote:

I store rates in a table called [tblProjectRates] and the rate is
tied
to a
RoleName and the specific ProjectID. When the user updates the
[role]
ComboBox in the form, I want it to automatically populate the [Rate]
field
with the appropriate rate based on the ProjectID and the Role stored
in
the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an
apostrope
' character, and that ProjectID is a Number datatype.

 

[larochy]

Thank you!!! That worked...everyone's help is much appreciated.

Hold on. I didn't look that closely at what you were doing before!

Unfortunately, John's advice was incorrect. You cannot refer to fields in a
table like that. In other words, the references to
[tblProjectRates]![RoleName] and [tblProjectRates]![ProjectID] are
incorrect. Assuming that the RoleName and ProjectID are contained on
controls on your form, use

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
Me![RoleName] & "' And [ProjectID] = '" & _
Me![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the help. It's giving me a run time error now as follows:

Run time error code '2465':

Database can't find the field 'l' referred to in your expression

When I click Debug, it highlights this statement as the problem. Any
suggestions?

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")

Yes. You'd need quotes around the value being used:

Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = '" & _
[tblProjectRates]![ProjectID] & "'")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the quick response John. Project ID is a text field, would
that
change the code?

:

On Thu, 4 Sep 2008 23:18:01 -0700, larochy
<[email protected]>
wrote:

I store rates in a table called [tblProjectRates] and the rate is
tied
to a
RoleName and the specific ProjectID. When the user updates the
[role]
ComboBox in the form, I want it to automatically populate the [Rate]
field
with the appropriate rate based on the ProjectID and the Role stored
in
the
Project Rates table.

A few errors here. Try

Private Sub Role_AfterUpdate()
If Me.SKU = "UNCR-3" Then
Me![Rate] = DLookup("[Rate]", "[tblProjectRates]", "[Role]= '" & _
[tblProjectRates]![RoleName] & "' And [ProjectID] = " & _
[tblProjectRates]![ProjectID])
Else
Me!Rate = Me![Text51]
End If
End Sub

This assumes that RoleName is a Text field that will not contain an
apostrope
' character, and that ProjectID is a Number datatype.

 

[John W. Vinson]

Unfortunately, John's advice was incorrect. You cannot refer to fields in a
table like that.

Thanks for the catch Douglas! Copying existing code without looking at it will
catch ya...

 

[Douglas J. Steele]

Thanks for the catch Douglas! Copying existing code without looking at it
will
catch ya...

Been there, done that. Should I bring your t-shirt to the next summit? <g>