dlookup issue

[chappy68]

Where is my problem? I have been trying to solve this for about an hour now to no avail.

If Me.cboCategory.Value = 2 Then
DLookup("ClientYrEndMo","tblClientInfo", "[ClientNum]="& me.cboAcctNumber)

If the value in the combo box selection is 2, I want to determine the year end month from field "ClientYrEndMo" in table "tblClientInfo" where "ClientNum" in tblClientInfo = cboAcctNumber in my current form.

Please help. Driving me nuts.

 

[bob fitz]

Hi
I don't really think this is the problem but you do have a space missing between the red characters.

DLookup("ClientYrEndMo","tblClientInfo", "[ClientNum]=[B][COLOR=red]"&[/COLOR][/B] me.cboAcctNumber)

Where are you using this? Could it be that you have it in an event that is not firing.

 

[chappy68]

Entered the space and the problem is the same. The event is "On Change". I am receiving the problem while I am in the VBA screen. I don't even get a chance for the event not to fire.

By the way, I am getting a "Compile error: Expected:=". I am also using Access 2007.

I appreciate the try. Thanks Bob.

 

[bob fitz]

Can you post all the code of your On Change event

 

[missinglinq]

Serial numbers, account numbers and such are usually defined as Text Datatypes since they aren't used for mathematical operations. If ClientNum is Text, the correct syntax would be

"[ClientNum]='"& me.cboAcctNumber & "'"

Linq ;0)>

 

[chappy68]

Missinglinq,

Yes it is a text field. I had tried this and forgot to save it this way. Tried again with the proper coding, and it still is not working. I am sure I have a single or double quote or small piece of code incorrect.

Bob Fitz,

Here is the full code behind the On Change event. The red text is where the debugging stops and gives me the error.

Private Sub cboCategory_Change()
Me.cboSubCategory.Requery
Me.txtDatePerClientInfo.Visible = False
Me.lblFromClientInfo.Visible = False
'Check value of cboCategory in current form to see if this
'is "income tax" to then provide info from ClientInfo
If Me.cboCategory.Value = 2 Then
DLookup("ClientYrEndMo","tblClientInfo", _
"[ClientNum]='" & me.cboAcctNumber & "'")
'Make txtDatePerClientInfo and lblFromClientInfo visible again
Me.txtDatePerClientInfo.Visible = True
Me.lblFromClientInfo.Visible = True
End If
End Sub

 

[bob fitz]

Hi

Private Sub cboCategory_Change()
Me.cboSubCategory.Requery
Me.txtDatePerClientInfo.Visible = False
Me.lblFromClientInfo.Visible = False
'Check value of cboCategory in current form to see if this
'is "income tax" to then provide info from ClientInfo
If Me.cboCategory.Value = 2 Then
DLookup("ClientYrEndMo","tblClientInfo", _
"[ClientNum]='" & me.cboAcctNumber & "'")
'Make txtDatePerClientInfo and lblFromClientInfo visible again
Me.txtDatePerClientInfo.Visible = True
Me.lblFromClientInfo.Visible = True
End If
End Sub

When I made my last post I first made the point that missinglinq made, but then realized that there must me something else wrong as this would not give a compile error, so I changed my post to request the code.
As far as I can see, you are not assigning the result from the DLookup function to anything. I think you need something like this:

Private Sub cboCategory_Change()
Me.cboSubCategory.Requery
Me.txtDatePerClientInfo.Visible = False
Me.lblFromClientInfo.Visible = False
[COLOR=black]'Check value of cboCategory in current form to see if this[/COLOR]
[COLOR=black]'is "income tax" to then provide info from ClientInfo[/COLOR]
If Me.cboCategory.Value = 2 Then
[COLOR=red][B][COLOR=blue]Me.NameOfCtrlOnForm =[/COLOR][/B] DLookup("ClientYrEndMo","tblClientInfo", _
"[ClientNum]='" & me.cboAcctNumber & "'")[/COLOR]
'Make txtDatePerClientInfo and lblFromClientInfo visible again
Me.txtDatePerClientInfo.Visible = True
Me.lblFromClientInfo.Visible = True
End If
End Sub

 

[missinglinq]

Bob is exactly right! I was just walking out the door when I read/posted on this and missed that point, but you have to assign the result of the DLookup() to a Field, Variable or Control, as he said.

And if the Field is Text, you'll need the new syntax I gave you, as well.

Linq ;0)>

 

[chappy68]

That did the trick. Thanks for the help.

Can I ask one last question? How do I read the last section of the dlookup?

"[ClientNum]='" & me.cboAcctNumber & "'"

Specifically, what are the single quotes, double quotes and the & sign doing for me? I understand the ClientNum field is being made to equal the selection in cboAcctNumber but what are the ", ' and & doing for me?

 

[bob fitz]

Hi

I'm sure there must be many proper expalations of this on the web, and if I can find one I'll post a link but in the mean time this is my understaning of it:

If the value in your control "cboAcctNumber" is a number, then the extra quote and double qoutes are NOT required. If the value is actually a string (all be it that it looks like a number then the extra quotes and double quotes ARE required.

 

[bob fitz]

Hi

Allen Browne has some very good stuff on all aspects of Access at
http://allenbrowne.com/tips.html

Take a look at this link where he speaks about DLookup
http://allenbrowne.com/casu-07.html


Another very good place for tips is
http://access.mvps.org/access/

and for Dlookup there
http://access.mvps.org/access/general/gen0018.htm

 

[chappy68]

Thanks for all your help. Truly appreciated.

 

[chappy68]

Bob Fitz and Missinglinq,

You guys have been a great resource. Can I just ask one more question.

tblClientInfo has a field ClientYrEndMo which is a combo box from tblYearEnd. ClientYrEndMo is bound by the ID number (column 0) but there is another column for description of the month (column 1)

The if statement is returning the ID number for ClientYrEndMo. I would like to get the description instead which is column 1 in the combo box. After some looking around it appears it can be done with .column(1). Where do I place this in this if statement?

If Me.cboCategory.Value = 2 Then
Me.txtDatePerClientInfo = DLookup("ClientYrEndMo", "tblClientInfo", _
"[ClientNum]='" & Me.cboAcctNumber & "'")

I have tried it after "ClientYrEndMo" with and without braces. I am a little confused. I found a few examples but none that fit my scenario.

I promise, this is the last question (on this subject).

 

[bob fitz]

Hi

If you want the column(1) value in the the text box there is no need for DLookup. Just use:

Me.txtDatePerClientInfo = Me.cboAcctNumber.column(1)

I promise, this is the last question (on this subject).

I really don't mind how many questions there are. Just want to help, if we can .