Looking Up Record, based on Multiple Fields

Question

I'm New to MS Access, I'm working on a project for work at the moment and have run into a snag. I'm not sure if I'm doing this right, I made the mistake of making a simple access db to track my workload, and suddenly i'm "The database guy"

So We've recently started a new product review team within the company, and the database im designing is going to help manage the new part introduction process.

I've got our review sheet designed, which will be called up whenever a part is reviewed. I have not made any relationships to this table because at any time a top level part we've reviewed could be a component to a new kit. So doing master/child parts isn't much of an option. (At least from what i can see)

How i have this sheet being called is as follows. We enter in some top level information in the main form (frmDPARTOP) regarding the review, Customer, Revision, PO, etc.. In a subform (frmDPARPARTS Subform) we list Every Part associated with this, including itself.

I've run into some problems getting the next part to work

I have an unbound text box (Text14)on the subform that is supposed to be looking for records in table (tDPARSHEETS) based on the appropriate Customer, Part_Number, and Revision. If it finds a record on file matching these three fields, its value is set to change to "On Record" or "Needs Review" if its unable to locate a file. However, it is always indicating "Needs Review" .LocalPartNumber and LocalRevision are in the table tDPARSHEETS, I've tried several variations, looking for different items, different ways. I've debug printed the values, and they are correct.

If DLookup("LocalPartNumber", "tDPARSHEET", "[LocalPartNumber]="" & Me.Part_Number & ""AND [LocalRevision]='" & Me.Revision & "'") Then
Text14.Value = "On Record"
Else
Text14.Value = "Need Review"
End If

The Other issue i'm experiencing is based on the same situation. I would like to be able to double click the items listed in the Subform and pull up the record on file if there is one, if there is not, i would like to create one, with the information already provided filled in. I've put a test entry in with the Customer, Part Number, and Revision filled out. The MsgBox Fires telling me the record is on file, However, the OpenForm command opens to a blank form.

Private Sub Customer_DblClick(Cancel As Integer)
If DLookup("LocalPartNumber", "tDPARSHEET", "LocalRevision ='" & Me.Revision & "'") Then
MsgBox "Record Found"
DoCmd.OpenForm "frmDPARSHEET", , , "[LocalPartNumber]="" & Me.Part_Number & ""AND [LocalRevision]='" & Me.Revision & "'"
Else
MsgBox "No Luck"

End If

All fields I'm working with are Text Fields, as our part numbers and revisions often include letters and numbers

I'm sure there is a better way to do this entire process in general, but We are rolling out an entire new ERP system in May, and i'm under the impression that this is meant to hold us over until then. I would like to have it built well enough to work indefinitely (as we have a hard time letting go of things), but in reality this could all be thrown away in 6-8 months

I Appreciate any Advice/Help

Gustav Gustav · Accepted Answer · 2015-11-02T13:16:06

Have in mind please, that DLookup returns Null for "not found". Also, be careful with spaces. Thus:

If IsNull(DLookup("LocalPartNumber", "tDPARSHEET", "[LocalPartNumber]=" & Me.Part_Number & " AND [LocalRevision]='" & Me.Revision & "'")) Then
    Text14.Value = "Need Review"
Else
    Text14.Value = "On Record"
End If

If partnumber is text, you need to wrap that in quotes as well:

If IsNull(DLookup("LocalPartNumber", "tDPARSHEET", "[LocalPartNumber]='" & Me.Part_Number & "' AND [LocalRevision]='" & Me.Revision & "'")) Then
    Text14.Value = "Need Review"
Else
    Text14.Value = "On Record"
End If

Looking Up Record, based on Multiple Fields

1 Answers