DLookup in Access Project

  • Thread starter Thread starter Dan M
  • Start date Start date
D

Dan M

In redesigning an Access 97 database into an XP project
(SQL Server 2K back end), I'm having difficulty with
textboxes that use DLookup. And it is incredible the lack
of help available from Microsoft Access Help, the KB and
MSDN Support. Custom textbox controlsources just aren't
the same between a database and a project, but they put so
little effort into alerting us and spelling out the
differences.

If I don't specify criteria, it works, pulling the first
price of the first record:
DLookup("[ProductRetailPrice]", "tblRetailPrices")

If I hardcode my criteria, it works:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
1")

If I reference an Access object (form, textbox, etc.), it
doesn't work:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[txtProductID]")
OR
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[Form]![txtProductID]")
(Please don't tell me my syntax is wrong, objects in
Access projects have trouble referencing other objects)

If I build the controlsource string in VBA, it still
doesn't work:
lngProductID = txtProductID
txtRetailPrice.ControlSource = "DLookup
('[ProductRetailPrice]', 'tblRetailPrices', '[ProductID]
= " & lngProductID & "')"

Any insight would be greatly appreciated. And any sources
for help on differences between databases and projects,
especially related to form object properties, would be
even more appreciated.
 
I think you'll find that

lngProductID = txtProductID

isn't actually assigning a value to lngProductID: it's assuming txtProductID
is a variable, not a control on your form.

You need to qualify txtProductID:

DLookup("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] = " &
Me![txtProductID])
 
Thanks Doug. I still believe that attempting to pull data
into a control by referencing another control (when in an
Access PROJECT) doesn't work. That's why there is no
Build button next to the ControlSource property in the
property sheet.

I tried your example and got #Name. I know my references
are right, and I tried many variations of it to be sure I
got the syntax right.

It's funny that hardcoding my textbox controlsource in
design mode works fine, but setting the controlsource
property in VBA to EXACTLY the same thing to get the
desired result in runtime doesn't work.

Here's the actual VBA code, so you can see that I even got
the quotes within quotes right.

txtWhsl.ControlSource = "DLookup(" & """" & "[Price]"
& """" & "," & """" & "tblProductPricesWHOLESALE" & """"
& "," & """" & "[LineNumber] = 16" & """" & ")"
-----Original Message-----
I think you'll find that

lngProductID = txtProductID

isn't actually assigning a value to lngProductID: it's assuming txtProductID
is a variable, not a control on your form.

You need to qualify txtProductID:

DLookup
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID]
= " &
Me![txtProductID])


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dan M said:
In redesigning an Access 97 database into an XP project
(SQL Server 2K back end), I'm having difficulty with
textboxes that use DLookup. And it is incredible the lack
of help available from Microsoft Access Help, the KB and
MSDN Support. Custom textbox controlsources just aren't
the same between a database and a project, but they put so
little effort into alerting us and spelling out the
differences.

If I don't specify criteria, it works, pulling the first
price of the first record:
DLookup("[ProductRetailPrice]", "tblRetailPrices")

If I hardcode my criteria, it works:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
1")

If I reference an Access object (form, textbox, etc.), it
doesn't work:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[txtProductID]")
OR
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[Form]![txtProductID]")
(Please don't tell me my syntax is wrong, objects in
Access projects have trouble referencing other objects)

If I build the controlsource string in VBA, it still
doesn't work:
lngProductID = txtProductID
txtRetailPrice.ControlSource = "DLookup
('[ProductRetailPrice]', 'tblRetailPrices', '[ProductID]
= " & lngProductID & "')"

Any insight would be greatly appreciated. And any sources
for help on differences between databases and projects,
especially related to form object properties, would be
even more appreciated.
Click to expand...


.
Click to expand...
 
I'm not familiar with projects, but I think you will find
that you have left the "=" out of the start of your string
when you are trying to use VBA to assign the string to
the controlsource property:

txtWhsl.ControlSource = "=DLookup(" & """" & "[Price]"

(david)

Dan M said:
Thanks Doug. I still believe that attempting to pull data
into a control by referencing another control (when in an
Access PROJECT) doesn't work. That's why there is no
Build button next to the ControlSource property in the
property sheet.

I tried your example and got #Name. I know my references
are right, and I tried many variations of it to be sure I
got the syntax right.

It's funny that hardcoding my textbox controlsource in
design mode works fine, but setting the controlsource
property in VBA to EXACTLY the same thing to get the
desired result in runtime doesn't work.

Here's the actual VBA code, so you can see that I even got
the quotes within quotes right.

txtWhsl.ControlSource = "DLookup(" & """" & "[Price]"
& """" & "," & """" & "tblProductPricesWHOLESALE" & """"
& "," & """" & "[LineNumber] = 16" & """" & ")"
-----Original Message-----
I think you'll find that

lngProductID = txtProductID

isn't actually assigning a value to lngProductID: it's assuming txtProductID
is a variable, not a control on your form.

You need to qualify txtProductID:

DLookup
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID]
= " &
Me![txtProductID])


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dan M said:
In redesigning an Access 97 database into an XP project
(SQL Server 2K back end), I'm having difficulty with
textboxes that use DLookup. And it is incredible the lack
of help available from Microsoft Access Help, the KB and
MSDN Support. Custom textbox controlsources just aren't
the same between a database and a project, but they put so
little effort into alerting us and spelling out the
differences.

If I don't specify criteria, it works, pulling the first
price of the first record:
DLookup("[ProductRetailPrice]", "tblRetailPrices")

If I hardcode my criteria, it works:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
1")

If I reference an Access object (form, textbox, etc.), it
doesn't work:
DLookup
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[txtProductID]")
OR
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[Form]![txtProductID]")
(Please don't tell me my syntax is wrong, objects in
Access projects have trouble referencing other objects)

If I build the controlsource string in VBA, it still
doesn't work:
lngProductID = txtProductID
txtRetailPrice.ControlSource = "DLookup
('[ProductRetailPrice]', 'tblRetailPrices', '[ProductID]
= " & lngProductID & "')"

Any insight would be greatly appreciated. And any sources
for help on differences between databases and projects,
especially related to form object properties, would be
even more appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
OMG! And I thought the syntax of DLookup was hard enough
already. I've never seen a property of type string that
needs an = inside the string when setting it in VBA.
Thanks David, it worked.
-----Original Message-----
I'm not familiar with projects, but I think you will find
that you have left the "=" out of the start of your string
when you are trying to use VBA to assign the string to
the controlsource property:

txtWhsl.ControlSource = "=DLookup(" & """" & "[Price]"

(david)

Dan M said:
Thanks Doug. I still believe that attempting to pull data
into a control by referencing another control (when in an
Access PROJECT) doesn't work. That's why there is no
Build button next to the ControlSource property in the
property sheet.

I tried your example and got #Name. I know my references
are right, and I tried many variations of it to be sure I
got the syntax right.

It's funny that hardcoding my textbox controlsource in
design mode works fine, but setting the controlsource
property in VBA to EXACTLY the same thing to get the
desired result in runtime doesn't work.

Here's the actual VBA code, so you can see that I even got
the quotes within quotes right.

txtWhsl.ControlSource = "DLookup(" & """" & "[Price]"
& """" & "," & """" & "tblProductPricesWHOLESALE" & """"
& "," & """" & "[LineNumber] = 16" & """" & ")"
-----Original Message-----
I think you'll find that

lngProductID = txtProductID

isn't actually assigning a value to lngProductID: it's assuming txtProductID
is a variable, not a control on your form.

You need to qualify txtProductID:

DLookup
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID]
= " &
Me![txtProductID])


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



In redesigning an Access 97 database into an XP project
(SQL Server 2K back end), I'm having difficulty with
textboxes that use DLookup. And it is incredible the lack
of help available from Microsoft Access Help, the KB and
MSDN Support. Custom textbox controlsources just aren't
the same between a database and a project, but they
Click to expand...
put
so
little effort into alerting us and spelling out the
differences.

If I don't specify criteria, it works, pulling the first
price of the first record:
DLookup("[ProductRetailPrice]", "tblRetailPrices")

If I hardcode my criteria, it works:
DLookup
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
1")

If I reference an Access object (form, textbox,
Click to expand...
etc.),
it
doesn't work:
DLookup
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[txtProductID]")
OR
Click to expand...
("[ProductRetailPrice]", "tblRetailPrices", "[ProductID] =
[Form]![txtProductID]")
(Please don't tell me my syntax is wrong, objects in
Access projects have trouble referencing other objects)

If I build the controlsource string in VBA, it still
doesn't work:
lngProductID = txtProductID
txtRetailPrice.ControlSource = "DLookup
('[ProductRetailPrice]', 'tblRetailPrices', '[ProductID]
= " & lngProductID & "')"

Any insight would be greatly appreciated. And any sources
for help on differences between databases and projects,
especially related to form object properties, would be
even more appreciated.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top