Can someone help me with some code involving lookup?

[DanicaDear]

I tried to manipulate some code that was based on making two selections and then looking up the third. Now I have just one item to select and want my database to provide a lookup. I took the code (pasted at the end for reference) and came up with this:

Option Compare Database
Option Explicit

Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY")
  
End If
End Sub

Private Sub cboClass_Change()
     Call ChangeRowSource
End Sub

End Sub

The code actually fires without bugs (which is a huge accomplishment for me) but it returns only one commodity number no matter what I enter. So it's not working. I suspect my error is at the end of the longest line...it had ticks and quotes that I deleted and those things get me every time.
Also, I researched online before asking the question, and the internet kept directing me to a wizard. I'm way too far in my DB to invoke that. Relationships are defined, tables, queries, and forms are made, etc.

Thanks in advance to the generous person who helps me!

Just for reference, here is the wonderful and working code I began with. cboCLASS no longer exists in new DB.

Option Compare Database
Option Explicit

Private Sub cboClass_AfterUpdate()
If Not IsNull(Me![CBOclass]) Then
  Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![CBOclass] & "' ORDER BY CInt(IIf(IsNumeric(Right([SIZE],1)),[SIZE],Left([SIZE],Len([SIZE])-1)));"
End If
End Sub
  
Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![CBOclass]) And Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![CBOclass] & _
                       "' And [SIZE] = '" & Me![cboSIZE] & "'")
End If
End Sub
 
Private Sub cboClass_Change()
     Call ChangeRowSource
End Sub
  
Private Sub cboSize_Change()
    Call ChangeRowSource
End Sub
  
Private Sub ChangeRowSource()
   With Me
        If Not IsNull(.CBOclass) And Not IsNull(.cboSIZE) Then
            .txtCommodityNum = DLookup("[COMMODITY_NUM]", _
                                       "tblCOMMODITY", _
                                       "[CLASS] = '" & .CBOclass & "' And " & _
                                       "[SIZE] = '" & .cboSIZE & "'")
        End If
    End With
End Sub

[DanicaDear]

Not sure why my [CODE] inserts aren't working like I'm used to.

[Seth Schrock]

In answer to your second post, it is probably because your end insert has the slash as \ instead of / which is what is inserted when you use the <CODE/> button.

In answer to your first post, you are correct that the error lies in your longest line. DLookup can only return one value and always selects the first record that it finds. Since your DLookup() function doesn't have any criteria, it will always return the value in the first record of the table. You need to add a criteria so that it will find the record you want and then it should work.

[DanicaDear]

I feel like I am getting closer but still not there.

Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", [BLANK_SIZE] = '" & Me![cboSIZE] & "')
  
End If
End Sub

Got any more advice? I'll not be shameful in admitting I don't fully understand ' " and Me! But I did look online for more help and I think I'm getting close. :-)

[Seth Schrock]

No need to be ashamed of not understanding. That is why we are here.

Try this in place of line 3:

Me!txtCommodityNum = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = " & "'" & Me![cboSIZE] & "'")

I don't deal too much with text criteria like this, so I'm not totally sure on what is needed with the double and single quotes.

Me! just refers to the current form. After the bang (the exclamation point), you have control names such as txtCommodityNum . This is instead of the full reference Forms!form_name!txtCommodityNu m

I feel like I have read an article about the use of double and single quotes, but I can't find it right now. Hopefully an expert can get in here and explain it to you (and me :)

[DanicaDear]

Thanks Seth. Not working yet. It's not calling the debugger, but it's not updating the field either. I know we're close. We can see if someone else's eye can catch it. I have plenty of time...I am always very grateful for the help I receive on Bytes.

[Seth Schrock]

Try this:

Me!txtCommodityNum = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = '" & Me![cboSIZE] & "'")

I found a website that listed a DLookup with a text criteria and this is what their syntax was.

[DanicaDear]

Seth, thank you again. Tried that with same result. Code is firing but not updating my field. I can follow that matching it to the original code. So I wonder if I deleted too much from the original code. I found a reference to cbo_Class in my first pasting of the code. I deleted that too; now I'm down to this: (Perhaps I have oversimplified this?)

Option Compare Database
Option Explicit

Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = " & "'" & Me![cboSIZE] & "'")
  
End If
End Sub

Private Sub cboSIZE_BeforeUpdate(Cancel As Integer)

End Sub

[NeoPa]

Hi Danica. Feel free to give me a call. I'd love to talk again anyway, and I'm up for hours yet anyway ;-)

Onto the issue. First off, Seth seems to be doing a decent job. Especially as he's still still learning much of it himself. So far all the advice has been good.

Let me start though, by getting you to take a step back and to ask yourself exactly what you require. How many items do you want to filter on, and what type are they all? String values and numeric values are handled quite differently in SQL (and SQL is what filters are written in). Here's an explanatory link - Quotes (') and Double-Quotes (") - Where and When to use them. That further links you to other references related to filtering (or WHERE clause specification).

You also need to know exactly which controls in your filter criteria are mandatory and which are optional.

Only when we know all of that detail can we advise properly on what you need.

PS. The link explains all about the quotes. Me, is a special object reference found in all Form and Report object modules that always refers to the associated object. So, in Form_frmA's object module Me refers to Form_frmA, whereas in Form_frmB's object module Me refers to Form_frmB. I hope that explains it clearly.

[DanicaDear]

Hi NeoPa! Can't call you tonight I'm afraid. It's the football national championship and my team is playing in the game! :-)

Oh, you have just reminded me how little I am. I don't know the answers to your questions but that has never stopped me before. LOL. On a brigher note, I do understand the Me explanation.
I *think* I am filtering on one item and nothing is numeric so I guess that means these items are strings. :-) You have never let my inability to answer your question stop me from getting the answer I seek. You have an amazing ability to keep on simplifying it until our intellects meet. Sorry you have to travel so far. LOL!! :-D
Hopefully we will talk soon.

Thanks to you AND Seth.

[NeoPa]

Another night then Danica. I'll look forward to it, but I'm playing football tomorrow night myself (different football from yours I expect, but never mind). Good luck for tonight anyway. I hope your team wins :-)

As for your form, you refer in the OP to making two selections. I expect these are the controls we're talking about. What are the names of the controls, and the fields they are supposed to match? Are both necessary or could only one, or even neither, be selected in some circumstances. It seems they are both string values. That helps, but we need a little more before we can proceed.

We will be looking to produce a filter string that fits the following pattern :

([Field1] = 'XXX') AND ([Field2] = 'YYY')

Sometimes not all of that will be necessary though, hence the greater details required.

[DanicaDear]

I understand the confusion. Great news. I *think* this is going to be very simple for your great mind. ;-)
The original piece of code was from a more complicated DB and it had two filters. However I stole the code because I lack the ability to write it, but can dangerously manipulate it, and what I'm aiming for NOW is one simple filter. I want to choose a size, and generate a commodity number.

[NeoPa]

That's not quite what I was after Danica, but let me see if I can piece together what we do have and see if we can work from there. If I go astray then let me know where of course.

You are looking to find a record where some size field matches a size control on your form. There is only one criteria required and it must be selected in order to determine the commodity. From earlier posts I can determine (Again, correct if I misread.) that the control is called [cboSize] and the field is called [BLANK_SIZE]. You have also stated that you believe [BLANK_SIZE] to be a text field rather than numeric.

If all that is correct then the following code should do what you need :

Option Compare Database
Option Explicit
 
Private Sub cboSize_AfterUpdate()
    Dim strWhere As String

    With Me
        If IsNull(.cboSIZE) Then
            .txtCommodityNum = Null
        Else
            strWhere = Replace("([BLANK_SIZE]='%S')", "%S", .cboSize)
            .txtCommodityNum = DLookup(Expr:="[BLANK_COMMODITY_NUM]", _
                                       Domain:="[tblBLANK_COMMODITY]", _
                                       Criteria:=strWhere)
        End If
    End With
End Sub

[DanicaDear]

Yes yes. Magic again! SCORE!!

[NeoPa]

You've been watching too much football my dear (or should that be Danica ;-)). Anyway, I'm always pleased to help :-)