DLookup Pulling Wrong Record

[xljunkie]

Ok so I have a table below which lists out various text strings associated with months. A user selects a month bound to the first column from ComboBoxMonth.

MonthNumber	MonthName	ImportMonth	ImportDate
02	January	01	31
03	February	02	28
04	March	03	31
05	April	04	30
06	May	05	31
07	June	06	30
08	July	07	31
09	August	08	31
10	September	09	30
11	October	10	31
12	November	11	30
01	December	12	31

<caption> tblMonths </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>

In my VBA, I have the following:

    ImportMonth = DLookup("ImportMonth", "tblMonths", Me.ComboBoxMonth)
    ImportDate = DLookup("ImportDate", "tblMonths", Me.ComboBoxMonth)

As an example, I've chosen March in my ComboBoxMonth, and that returns a value of "04". When I run the code, the value of ImportMonth = "01" and the value of ImportDate = "31" while the value of Me.ComboBoxMonth is "04". Is there something I'm missing?

 

[xenou]

Hi,
Be sure you know what the bound column in the combobox is (the real "value" of the combobox depends on what field it is bound to, not what it displays visibly).

 

[xljunkie]

It is bound to MonthNumber so "01", "02", "03", etc. The display is MonthName. I will try to figure it out next week.

 

[xljunkie]

Fixed it. I didn't know you needed this complicated syntax for a form object.

    ImportMonth = DLookup("ImportMonth", "tblMonths", "MonthNumber = '" & Forms!FrmFrontPage!ComboBoxMonth & "'")
    ImportDate = DLookup("ImportDate", "tblMonths", "MonthNumber = '" & Forms!FrmFrontPage!ComboBoxMonth & "'")

 

[xenou]

You would not need that syntax if the dlookup formula is in the same form. That's what Me means in your original post. Me is self-referential to the form object.