Dcount Error Msg

S

Steve Stad

I am using a dcount to lookup the "Prod1_Mgr" in a table named
"tbl_Org-Div-Prod-Mgr" where the value in the 'Prod1combo' box on the form is
equal to the Product_Title in the lookup table "tbl_Org-Div-Prod-Mgr". The
look up table contains the fields "Prod_Title" and "Prod_Mgr_Pri". I want
to enter the value in Prod_Mgr_Pri fld in the look up table into the
Prod1_Mgr field on my form where the value in the Prod1combo box on the form
equals the "Product_Title" in the lookup form.
I am using the after update event of the Prod1combo box. Here is the code..

Private Sub Prod1combo_AfterUpdate()
Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1combo = '" &
[PROD_TITLE] & "'")
End Sub

I am getting an error msg saying the DB can't find the field '|' referred to
in your expression. Do I have a syntax error?

fyi... the control source for the Prod1combo box is Prod1. Prod1combo is
the name of the combo box.
 
J

Jeanette Cunningham

Yes, there is a syntax error with the 3rd part of the DLookup statement.
- marked with **

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", **"Prod1combo = '"
&
[PROD_TITLE] & "'")


Try the code below:

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '" &
[PROD_TITLE] & "'")


Add the Nz function for the cases where the dlookup can't find any match.


Prod1_Mgr = Nz(DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '" &
[PROD_TITLE] & "'"),"Nothing found")


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
S

Steve Stad

Jeanette,

Thank you for your reply to my Dcount inquiry. I expanded my lookup table
by filling in all fields for Prod1_Mgr, and rearranged the last part of the
statement so 'Prod_Title' in the lookup table = the value in the Prod1combo
box on form - as below.

Prod1_Mgr = DLookup("Prod1_Mgr", "tblLU_ProdTitle-Mgr", "Prod_Title = '" &
[Prod1combo] & "'")

I run the code on the After_Update() event for the Prod1combo box on the form.
I have to repeat this process/code 10 times because I have 10 products in 10
combo boxes with 10 Product Mgrs uniquely corresponding to the respective
product field.

Steve

Jeanette Cunningham said:
Yes, there is a syntax error with the 3rd part of the DLookup statement.
- marked with **

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", **"Prod1combo = '"
&
[PROD_TITLE] & "'")


Try the code below:

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '" &
[PROD_TITLE] & "'")


Add the Nz function for the cases where the dlookup can't find any match.


Prod1_Mgr = Nz(DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '" &
[PROD_TITLE] & "'"),"Nothing found")


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Steve Stad said:
I am using a dcount to lookup the "Prod1_Mgr" in a table named
"tbl_Org-Div-Prod-Mgr" where the value in the 'Prod1combo' box on the form
is
equal to the Product_Title in the lookup table "tbl_Org-Div-Prod-Mgr".
The
look up table contains the fields "Prod_Title" and "Prod_Mgr_Pri". I
want
to enter the value in Prod_Mgr_Pri fld in the look up table into the
Prod1_Mgr field on my form where the value in the Prod1combo box on the
form
equals the "Product_Title" in the lookup form.
I am using the after update event of the Prod1combo box. Here is the
code..

Private Sub Prod1combo_AfterUpdate()
Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1combo = '"
&
[PROD_TITLE] & "'")
End Sub

I am getting an error msg saying the DB can't find the field '|' referred
to
in your expression. Do I have a syntax error?

fyi... the control source for the Prod1combo box is Prod1. Prod1combo is
the name of the combo box.
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

Ouch! that is a lot of work.
I was assuming that there was only 1 combo as would be more usual.

I see that the way you have set up the tables for your database, is making
more work for you in building the forms.
The database design would benefit from being more normalized.

You can read up on designing and normalizing databases.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Steve Stad said:
Jeanette,

Thank you for your reply to my Dcount inquiry. I expanded my lookup table
by filling in all fields for Prod1_Mgr, and rearranged the last part of
the
statement so 'Prod_Title' in the lookup table = the value in the
Prod1combo
box on form - as below.

Prod1_Mgr = DLookup("Prod1_Mgr", "tblLU_ProdTitle-Mgr", "Prod_Title = '"
&
[Prod1combo] & "'")

I run the code on the After_Update() event for the Prod1combo box on the
form.
I have to repeat this process/code 10 times because I have 10 products in
10
combo boxes with 10 Product Mgrs uniquely corresponding to the respective
product field.

Steve

Jeanette Cunningham said:
Yes, there is a syntax error with the 3rd part of the DLookup statement.
- marked with **

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", **"Prod1combo =
'"
&
[PROD_TITLE] & "'")


Try the code below:

Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '" &
[PROD_TITLE] & "'")


Add the Nz function for the cases where the dlookup can't find any match.


Prod1_Mgr = Nz(DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1 = '"
&
[PROD_TITLE] & "'"),"Nothing found")


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Steve Stad said:
I am using a dcount to lookup the "Prod1_Mgr" in a table named
"tbl_Org-Div-Prod-Mgr" where the value in the 'Prod1combo' box on the
form
is
equal to the Product_Title in the lookup table "tbl_Org-Div-Prod-Mgr".
The
look up table contains the fields "Prod_Title" and "Prod_Mgr_Pri". I
want
to enter the value in Prod_Mgr_Pri fld in the look up table into the
Prod1_Mgr field on my form where the value in the Prod1combo box on the
form
equals the "Product_Title" in the lookup form.
I am using the after update event of the Prod1combo box. Here is the
code..

Private Sub Prod1combo_AfterUpdate()
Prod1_Mgr = DLookup("Prod1_Mgr", "tbl_Org-Div-Prod-Mgr", "Prod1combo =
'"
&
[PROD_TITLE] & "'")
End Sub

I am getting an error msg saying the DB can't find the field '|'
referred
to
in your expression. Do I have a syntax error?

fyi... the control source for the Prod1combo box is Prod1. Prod1combo
is
the name of the combo box.
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

Dynamic Naming of Form Button 3
DCount error 5
DCount Help 3
Can I use group by clause in DCount 10
Help with dCount - “Data Type mismatch in criteria expression†2
Dcount error 2
dcount() query 1
DlookUP...Dcount... 2

Top