Dlookup Help

[edonahue]

I am trying to do a dlookup to fill in a description field on a form based on a number that might be stored in either of two other tables. I have CP numbers and A numbers. If the user types a CP number in I want the Dlookup to go to the CP Numbers table. If the user types in an A number I want the Dlookup to go to the AO Numbers table. Here is the code I am using

If Forms![Proposals]![CP_AO_Number] Like "[CP]*" Then
Forms![Proposals]![Description] = DLookup("[CP_Description]", "[CP_Numbers]", "[Forms]![proposals]![cp_ao_number]")
ElseIf Forms![Proposals]![CP_AO_Number] Like "[A]*" Then
Forms![Proposals]![Description] = DLookup("[AO_Description]", "AO_Numbers", "[Forms]![proposals]![cp_ao_number]")

When the user types in a CP number this code returns a description, but not the one that applies to the particular number the user typed in. When the user types in an A number, no description is returned at all.

Can someone please show me the error of my ways?

Thanks!

Erika

 

[ghudson]

I odviously can not test this but give it a try...

If [CP_AO_Number] Like "[CP]*" Then
        [Description] = DLookup("[CP_Description]", "CP_Numbers", "[cp_ao_number]" = " & "'" & Forms!Proposals!cp_ao_number & "'")
ElseIf [CP_AO_Number] Like "[A]*" Then
        [Description] = DLookup("[AO_Description]", "AO_Numbers", "[cp_ao_number]" = " & "'" & Forms!Proposals!cp_ao_number & "'")
End If

Check out DLookup in the Access help files for it will show you the correct syntax. Pay attention to the part about "Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control." in the examples section of the Dlookup.

HTH

 

[Pat Hartman]

Too many ", ghudson . I also prefer the Me. syntax since it provides intellisense.

If Me.CP_AO_Number Like "[CP]*" Then
        [Description] = DLookup("[CP_Description]", "CP_Numbers", "[cp_ao_number] = " & "'" & Me.cp_ao_number & "'")
ElseIf Me.[CP_AO_Number] Like "[A]*" Then
        [Description] = DLookup("[AO_Description]", "AO_Numbers", "[cp_ao_number] = " & "'" & Me.cp_ao_number & "'")
End If

The code above checks for two values and has no default if the CP_AO_Number contains something else. If there really are only two possibilities, you don't need the second IF.

If Me.CP_AO_Number Like "[CP]*" Then
        [Description] = DLookup("[CP_Description]", "CP_Numbers", "[cp_ao_number] = " & "'" & Me.cp_ao_number & "'")
Else
        [Description] = DLookup("[AO_Description]", "AO_Numbers", "[cp_ao_number] = " & "'" & Me.cp_ao_number & "'")
End If

 

[edonahue]

Still stumped

Thanks to both who responded to my plea. The form is still working as in my code. If the number starts with CP it picks up a description that doesn't match the the cp number in the cp numbers table. If the number doesn't start with cp it picks up no description at all.

This time i'm attaching the database.

What do you think??

 

[Pat Hartman]

I can't look at your db right now. Why do you have two lookup tables? If one set of codes is prefixed with "CP" and the other set is prefixed with "A", there will be no duplicates.

 

[adam_fleck]

Maybe this will work

On the Proposals form:

Private Sub CP_AO_Number_Exit(Cancel As Integer)

    If Me.CP_AO_Number Like "CP*" Then
            [Description] = DLookup("[CP_Description]", "CP_Numbers", "[cp_number] = " & "'" & Me.CP_AO_Number & "'")
    Else
            [Description] = DLookup("[AO_Description]", "AO_Numbers", "[ao_number] = " & "'" & Me.CP_AO_Number & "'")
    End If

End Sub

If this is right, the problem was that you didn't specified the field you wanted to look in. For the [CP_Numbers] table you want to look in the [cp_number] field, and in the [AO_Numbers] table you want to look in the [ao_number] field.

That said, I don't know why the code as it was worked for cp numbers but not for ao's. Puzzling.

 

[edonahue]

Thanks, but still not working

Thanks Adam, unfortunately it still doesn't work. It actually doesn't work for CP or for the AO number. If CP is typed in it brings back a description, but not the right one. VERY frustrating.

 

[Pat Hartman]

If you're going to post a db with almost 30 forms, you could at least specifiy which one had the problem.

You should be using combo's to look up the cp or ao descriptions. I didn't see your code although I admit I was not about to open each form until I found it.

You also have a number of tables without primary keys. They ALL need primary keys and RI should be enforced.

 

[edonahue]

Chagrined

Sorry, Pat. You're right. As you can see I'm new at this. I'll follow your advice on the keys, etc. and I'm still thinking about combining the CP and AO table. The problem I've been having is on the Proposals form.

 

[adam_fleck]

Tricky one

The input masks currently on AO numbers and on CP numbers (in the tables but maybe not in the forms) now store the "A0" or "CP" part too.

But, the original input mask you set on CP numbers stored only the numeric part of the user's input, not the "CP" prefix.

This means that although CP numbers show up as e.g. CP0484, only the 0484 part was stored in the CP number field.

So, with things set up this way, you need to change your Dlookup search criterion so that when a CP number is entered the "CP" bit will be stripped off, and only the characters after that will be used in the search. With AO number this doesn't need to be done.

The attched zip, does this, but a better fix might be re-input the CP numbers. Then you can use the alternative line that's been commented out.

 

[edonahue]

Can't thank you enough

Adam,

I'm obviously in over my head, but your help has taught me a great deal. Thanks very much!

Erika Donahue