Match two fields in a table against a combo box selection and a text box entry / 2 fields on a form.

[Davism4]

Hi All,

I have a table NonReworkableparts with 2 fields of interest , Program Number and PartNo.

On my form the idea is to select a program number from a combo box and when I type in a part number if it is in the table then it gives out a msgbox.

There will be multiple part numbers, that's why I need to make sure I match against the program also.

I've been playing with DLookup, but I've had no luck so far, this is my latest attempt.

If IsNull(DLookup("[PartNo]", "NonReworkableparts", "[PartNo] = '" & Me.Part_Number & "'")) _
& " AND [Program Number] = '" & Me.Combo87 & "'" Then


Appreciate any help you guys can give.

Best regards,

Mark

[isladogs]

It would help if you had said what happened when you tried that DLookup: Error? 2 missing parameters message?

Are the two fields number datatypes? If so, omit the single quotes

 If IsNull(DLookup("PartNo", "NonReworkableparts", "PartNo = " & Me.Part_Number & ")) & " AND "[Program Number] = " & Me.Combo87 & "" Then

However the Dlookup expression makes no sense to me. It is checking whether the value of the PartNo field is null when the same field is equal to a textbox value. So perhaps the expression should be

 If Nz(Me.Part_Number,"")="" AND "[Program Number] = " & Me.Combo87  Then

[Davism4]

Thanks for answering. My original code gives me: Runtime error 13 Mismatch. It was some code I found that I was playing with.

Basically, what I am trying to do on my form:

1) Select a program from a combo box
2) Enter a part number in a text box

The textbox after update event :

If part number and program on form = part number and program in table then return a message, otherwise do nothing.
The part numbers are alphanumerical

Hope that makes it clearer what I am trying to do.

Thanks again for the help.

[NeoPa]

Hi Mark.

Isladogs has already passed on some basic advice for you. Can you confirm what happened when you tried out their suggestions? It's very hard to progress a problem without such feedback. Extra explanations & clarifications are always good too, but without directing a response to existing suggestions you leave us in the dark to a large extent.

[zmbd]

My two cents

Make it easy:

Two Cascading comboboxes (Link to tutorial) with the second control to either pull-up the part-number information by either drop-down selection, which would prevent one from even selecting a part that isn't part of the project, or by direct entry. If the part number lookup is done by direct entry, then use the NotInList event to popup a message to the user and reset the control.

Easy-peasy-lemon-squeezee.

-Z

[NeoPa]

Hi Z.

Easy (-p-l-s) to say, but I suspect if everyone found such a job to be that easy then we wouldn't be answering such questions quite so often ;-)

@Mark.
Just know we have people here for whom it really is easy so you can be helped, even if you take some mis-steps along the way.