DLookup in Access Project

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.
 
D

Douglas J. Steele

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])
 
D

Dan M

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...
 
D

david epsom dot com dot au

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...
 
D

Dan M

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Data Project, Dlookup() and A stored Procedure 1
DLookup 1
parameters for access project 1
Copying Forms From One Database to Another and Back Changes Them 4
Using transact SQL in Access project 1
Running an Access project crashes Access 2007 SP1 13
Forms: Referencing a control on a form in an Access project View 1
Creating a new MS Project file from Access 4

Top