Dlookup Help Please

[graviz]

I have a table called "TBL_EMAIL" with two columns (State & ROM_Email)

I have a combobox called "CBO_Prop_State" on a form called FRM_Request_Main.

Here's my code:

Dim TestThis As String

TestThis = DCount("[ROM_Email]", "[TBL_EMAIL]", "[State]= " & Forms!frm_request_main!CBO_Prop_State & "")

MsgBox TestThis


I keep getting RTE 2001 "You canceled the previous operation". I've search other threads and none of the solutions are working. Any ideas?

 

[pr2-eugin]

Make sure the ComboBox is returning a String in whcih case you need to correct your DCount.. as.. (highlighted in RED)

DCount("[ROM_Email]", "[TBL_EMAIL]", "[State]= ' " & Forms!frm_request_main!CBO_Prop_State.Value & " ' ")

I believe STATE is a String, so see the RowSource of the ComboBox is returning the First column as String. Check for Typo's (is it [ROM_Email] or [FROM_Email])?

 

[graviz]

Make sure the ComboBox is returning a String in whcih case you need to correct your DCount.. as.. (highlighted in RED)

DCount("[ROM_Email]", "[TBL_EMAIL]", "[State]= ' " & Forms!frm_request_main!CBO_Prop_State.Value & " ' ")

I believe STATE is a String, so see the RowSource of the ComboBox is returning the First column as String. Check for Typo's (is it [ROM_Email] or [FROM_Email])?

I made sure there are no typos and updated my vba with the changes you have in red and no luck.

How do I make sure my combobox is returning a string?

 

[pr2-eugin]

Try breaking the code.. I mean use..

Debug.Print Forms!frm_request_main!CBO_Prop_State.Value
It will show what value it is displaying on the immediate window..

Or also
MsgBox Forms!frm_request_main!CBO_Prop_State.Value

 

[graviz]

It displays the correct value in the combobox.

 

[pr2-eugin]

Returning the correct value? as String? If so have you made the change using a single quotes? does it still not work? paste the Values of BoundColumn, RowSource of the ComboBox if you stil have trouble.

 

[graviz]

When I message boxed the value of the combobox it displayed the value of what is currently selected in the combobox (i.e. CO)

I'm not sure what you mean by "have I made the change using a single quote". If you mean did I change it to what you had above then yes.

Row Source: SELECT [TBL_State_Lookup].[State] FROM [TBL_State_Lookup] ORDER BY [State];

Bound Column: 1

All that's in the TBL_State_Lookup table is all 50 state abbrv.

 

[pr2-eugin]

Hmmm strange, okay.. try the following.. it should work..

Dim str as String
str=Forms!frm_request_main!CBO_Prop_State.Value
DCount("[ROM_Email]", "[TBL_EMAIL]", "[State]= '" & str & "'")

 

[graviz]

Same error. Here's my exact code:

Dim TestThis As String
Dim str As String
str = Forms!frm_request_main!CBO_Prop_State.Value
TestThis = DCount("[ROM_Email]", "[TBL_EMAIL]", "[State]= '" & str & "'")
MsgBox TestThis

Anything else I should check?

 

[pr2-eugin]

What code preceeds this segment of code? Do you anywhere have placed a Undo statement?

 

[graviz]

Public Function testt() and End Function at the end. Nothing else.

 

[pr2-eugin]

This error happens if sometime you have placed a manual DoCmd.Undo somewhere or the previous statement which gives input to the line you are trying to execute was not success.. something like,

* you are trying to access the ComboBox value but in the after update property you might have unassigned the value. simple error somewhere.

Is it possible for you to uplaoad a sample DB removing all sensitive information. Maybe we could have a look.?

 

[graviz]

It was a typo. It took me printing it out to find it. I was missing a single underscore. Thanks again!

 

[pr2-eugin]

You are welcome. Glad you got it all working.