Dlookup Sometimes works, somtime doesn't?

[jaad]

I am apologizing in advance if I am not posting this question correctly. I have had a read at the posting guide and will try to see if I can do it right this time.

I have an AfterUpdate event attached to a combo box called ProductCombo. This event is a Dlookup function that looks up the supplier for the product that was chosen from the ProductCombo control and return the supplier name into the supplier control. Funny thing is when I set it up it worked fine. Today I went to enter another entry and it tells me that I have a runtime error 3075 missing operator in query expression 'ProductCombo= 6 inches Stove Beauty Ring'

 Supplier = DLookup("Supplier", "ProductT", "ProductCombo=" & Product)

Hope this came out right. this is the first time I am using this. Cheers and thanks

[ajalwaysus]

What is the value of ProductCombo, is it a text or a number type?
Post the code that is the rowsource for the combo box.

-AJ

[jaad]

Ok, I will try to do that...

SELECT [PODProductSourceQ].[Product] FROM PODProductSourceQ ORDER BY [Product];

[ajalwaysus]

looks like ProductCombo returns text, to confirm, is that how you identified Product in the PODProductSourc eQ table?

So try this if text is the case,

Supplier = DLookup("Supplier", "ProductT", "ProductCombo= '" & Product & "'")

Let me know if this works,
-AJ

[jaad]

That's Affirmative. It is set as text. I've tried to use the ID number instead but after twice moving things around and appending my tables I decided that it was too much headaches and returned to the text only value.

I don't know why it doesn't work???? it was working fine when I first put it in and made 3 purchase orders with it and everything fine. this morning I tried to get another PO out and Poop! no go,

[ChipR]

Instead of:

Supplier = DLookup("Supplier", "ProductT", "ProductCombo= '" & Product & "'")

Try:

Supplier = DLookup("Supplier", "ProductT", "Product= '" & ProductCombo & "'")

[jaad]

Works perfectly ChipR.... Thanks a bunch that was great to see my form living once again. I owe you a pint.

[NeoPa]

Jaad, I'm pleased that's working for you now.

Usually, in a case where values that need to be displayed are not the Primary Index of the table, ComboBoxes are done as multi-columnar. The PK, usually an AutoNumber type field, would be the Bound column and the display value would be included as another column (second in the simplest cases). The .ColumnWidths property would then be set to something like 0cm;4cm. This causes the PK column not to show, and the display column TO show. All the while, the PK is the value returned as the default for the ComboBox control. This value is of most use when trying to use it elsewhere.

Whenever comparing literals (or filtering by them etc) it is important (as AJ indicated originally) to encapsulate the literal values in the characters that indicate their type. For more on this see (Quotes (') and Double-Quotes (") - Where and When to use them & Literal DateTimes and Their Delimiters (#)).

[jaad]

I will read about it Neopa. I am really curious now because I've tried to make sense why there was only one " ' " quotation mark when usually I would put " ' " on both sides like opening it and closing it kind of thing. You guys are fantastic. I;ve learned so much already because of this site... thanks

[NeoPa]

Where do you see only one Jaad? I can't see any where they are alone as you describe. Your original understanding is correct.

[jaad]

In the code that ChipR wrote. There is only a single quote mark. I will put brackets around them to show you where I mean.

Supplier = DLookup("Supplier", "ProductT", "Product=['] " & ProductCombo & " ['] ")

[NeoPa]

The single-quotes that you're referring to are around the value of ProductCombo (I'm sure the spaces are only showing for emphasis as they would not be in the working code). There are two of them clearly. One before, and one after, the value. That is having one on either side as you would expect, so I'm a little confused still.

As far as the use of the single-quote ('), as opposed to the double-quote ("), is concerned, this is explained quite fully in the link I already supplied in post #8.

I hope that is all crystal clear now. Please say if there is anything further you need explained.

[jaad]

You are looking at it from a seasoned veteran point of view Neopa. I don't even posses 1% of your knowledge as far as writting VBA code goes. In my "view" I see only only ( ' ) mark in the reference ("ProductCom bo= ' ") and then a comma and then another reference (& Product & " ' ") where the other one is sitting but in my understanding they aren't related.

this is an exert from Access Developer's Reference Help:

In addition to enclosing the entire string expression in double quotation marks ("), you must also ensure that the textual criteria within the string expression are enclosed in single quotation marks ('). The quotation marks must be included in the strings flanking the expression that references the control on the form.

being a newbie I look at this and I say to myself "Well they say I have to put ( ' )marks - plural. So for me I think there is obviously a mistake in the code that ChipR gave me because there is only one ( ') in one reference and there is only one enclosed at the end of the code in double (").

What is the most difficult to convey to a professional is that people with no knowledge of code writing don't see things the way professional do.

I have embarked on quest to learn VBA coding and been trying really hard to find literature that would explain these basic concepts to me in a way that I will understand. Most of what is written out there say "We show you this example thinking that you have a basic understanding of VBA Codes" and then they tell you about a function or a sub. Meanwhile on my side I look at what is written and I scratch my head like no tomorrow. Why so many brackets, double quotes and single quotes and how on earth can you figure out which funtion to use to write your code in the first place is beyond me. I know there is a manual somewhere that explains all this but all that I can find are intermediate leterature that assume I know about the basic when I don't.

The best possible example I can give you to make an analogy is this:

A builder is going to show me how to build a house and start telling me that we are going to start from the start and do the foundation first. He start to expalin to me that we are going to build forms and then poor concrete in them and tell me how to measure them and then I only have to build it.

One thing the builder forgot to tell me: You can't cut wood with a shovel, you must use a saw to cut the wood that will build your walls that you will poor concrete in.

Then after telling me that I should know that already and reiterate himself that I need a saw to cut to wood he goes off and come back and see that I have accomplish nothing and puzzle why can't I understand such a simple concept.

Truth is... the builder forgot to tell me that I needed to plug the saw in the power socket in order to make it work. So all along me I was trying to rub the saw on while it wasn't even powered. Dim Dumbme(DoubleDu mb) As Double lol

[NeoPa]

That is very well explained Jaad. I try not to make assumptions, but that in itself is very difficult. That is why feedback is so essential. I can explain things in many different ways, depending on the level of understanding of the listener. When you are face-to-face there are far more clues and it is much easier to pitch it correctly.

In this case, I also have to be careful not to seem insulting. I didn't want to indicate that I thought maybe you were missing that point due to inexperience, as some posters have more experience but just make dumb mistakes, or they may know Access well but are really quite useless at expressing their thoughts clearly. Someone like that might feel insulted (even though that wasn't my intention) had I assumed the reason was down to their lack of experience.

Now I know (and believe me, your explanation was very well put), I can work more directly at an appropriate level. I can easily understand how you could have missed what I was pointing out in such a case. In no way does your current lack of experience cause a problem. I started at that point too once of course.

By the way, manuals (physical printed ones) are much rarer nowadays. I find using the help system answers most of my questions about how to work with something. Unfortunately, like any reference material, a certain level of prior knowledge is almost essential even to know what to look up. We can help with that here of course.

[ChipR]

Did you really read Quotes (') and Double-Quotes (") - Where and When to use them? It's like someone plugged in the power saw, put it in your hand, and sat the wood in front of you. With an instruction manual.