DLookup with 2 fields 1

[InfoNow]

Is there a way to have DLookup using two fields? For example, If fieldA & fieldB existed, then msgbox "duplicates records". cancel, undo...etc. But if fieldA existed but not fieldB then it's ok to go on. What would the DLookup statement look like?

This is the code I've used for looking up one field, but not sure how to make it work with two criterias.
__________________________________________________________
If (Not IsNull(DLookup("[DocNum]", "UsystblTrainReq", _
"[DocNum] ='" & Me!DocNum & "'&quot)) Then
msgbox "Document number has been used."
Undo
Exit Sub

End If
__________________________________________________________

Thanks

 

[GKProgrammer]

You could always just add an And statement to your IF statement add use two separate Dlookup's.

If (Not IsNull(DLookup("[FieldA]", "UsystblTrainReq", _
"[FieldA] ='" & Me!FieldA & "'&quot)) And _
Not IsNull(DLookup("[FieldB]", "UsystblTrainReq", _
"[FieldB] ='" & Me!FieldB & "'&quot)) Then
msgbox "Document number has been used."
Undo
Exit Sub

End If


Regards,
gkprogrammer

 

[OhioSteve]

Yeah, you need two separate tests. GKprogrammer's idea would work. Personally, I would break it down into two smaller pieces of code.

x=0

if dlookup test1=true then
x=x+1
end if

if dlookup test2=true then
x=x+1
end if

if x=2 then
*do something*
end if

My idea is less efficient, but easier to reread, because you don't have one mega-test.

 

[InfoNow]

Thank you for the fast reply.
I am getting "Document number has been used." even the employee does not have the docnum in the table yet.
Here is the code.

If (Not IsNull(DLookup("[EmpNum]", "UsystblTrainReq", _
"[EmpNum] =" & Me!EmpNum & "&quot)) And _
(Not IsNull(DLookup("[DocNum]", "UsystblTrainReq", _
"[DocNum] ='" & Me!DocNum & "'&quot)) Then
msgbox "Document number has been used."
Undo
Exit Sub

End If

 

[Eupher]

Have you specified a default value for your DocNum field? If so, your code will always test true. I also notice you have enclosed the DocNum reference in single quotes but not EmpNum - is DocNum text data, whereas EmpNum is numeric?

Ken S.

 

[InfoNow]

This is in a subform. When I select an employee from the dropdown combo box, it brings the employee's record up in the subform. So, the EmpNum is the common field between the main form and the subform. When I select a new DocNum for the employee in the subform, the EmpNum is automatically filled in. I guess that that is the default value?
Is there any other way?
I have made the EmpNum and the DocNum as the Primary Keys, so, I guess I can live with the Access default message. It would be nice to have a customer msgbox though.
Yes, DocNum is a Text type and the EmpNum is a numeric format.

 

[danvlas]

Well, you could replace the custom Access message by using the Error event of the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022 'duplicate
Response = acDataErrContinue 'suppress Access message
MsgBox "Hey dude, you're duplicating the record!!!"
End Select
End Sub


However, this will be evaluated at the end of data entry, not on the fly.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[InfoNow]

Hi Daniel,

Now that you've mentioned it, I do have an Audit Trail Table. So the data get written to the Audit table before the custom message comes up and an Undo is performed. This is probably not gonna "fly".

Do you have any other suggestions? I do have to have the code in the BeforeUpdate so wrong data entered will not get written to the Audit Table.

Thanks

 

[danvlas]

Why don't you just write data to the Audit table in the AfterUpdate event?

That way you're sure everything is OK...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[InfoNow]

Daniel,

You're star!!!
Using the custom access message in the error event of the form as you suggested, added the Undo at the end and put the write to audit table in the Form_Afterupdate event and that works just as I wanted.

Thank you so much!!

Quang

 

[InfoNow]

Daniel,
I guess I spoke to soon. Looking over my Audit Table, it is not working as expected. By puting the Write Audit in the Form_AfterUpdate, the old value is not written into the table. The OldValue & the NewValue are the same.
Puting the Write Audit in to the DocNum_Afterupdate will write to the table even if the custom message comes up.
So I am back to where I've started again.
Any other ideas?

Thanks

 

[InfoNow]

Don't know if this will have any side affect, but this is what I have to do to get it to work (I hope!).
I have to create another field in the table called EmpDoc. On the DocNum_AfterUpdate event, I included, Me.EmpDoc = ([EmpNum] & "" & [DocNum]).
Finally, in the DocNum_BeforeUpdate event, the code is like this.
If Not IsNull(DLookup("EmpDoc", "UsystblTrainReq", "EmpDoc = '" & ([EmpNum] & "" & [DocNum]) & "'&quot) Then
msgbox "Warning - Document Number already exists", vbInformation
Undo
Exit Sub

End If

WriteAuditUpdate txtTableName, Me.[EmpNum] & " - " & [DocNum], "DocNum", Me.DocNum.OldValue, Me.DocNum.Value

End Sub


Since I couldn't get it to work with two fields using DLookup, I thought I join them first. Am I missing something that I am not aware of? Is this ok to do?

 

[OhioSteve]

I can see that this discussion has progressed considerably since my last login.

I noticed that in one post, you said that EmpNum was a numeric field. That seems unusual to me. You should only make a field numeric if you plan to do mathematical calculations on the data. For example, a zip code field WOULD be a text field, even though it contains no letters. This is because you would never add or multiply two zip codes. Making EmpNum a number field will not cause catastrophic problems, but it is not a good idea. Specifically, it may cause problems with formatting when you print the data.

I noticed that you pack alot of logic into each statement, and you use alot of nesting. That can make it more challenging to debug the code. Also remember that you may need to modify this code in two or three years. You want to keep it simple so that you will understand it then.

I'm a little unclear about your goal. Do you just want to generate document numbers and put them into new records? If so, that is a relatively simple task. I'm sure that if you submit the document number format, we can help you to generate the numbers.

 

[InfoNow]

Hi Steve,

Thanks for the advice. I have always thought that if the field contains only numbers then I should use numeric, but you do have a point. I'll keep that in mind for my next project.

I am new to this programming stuff, so, I make a lot of comments in the code section to remind of what they are and do.

I know that coding is a little messy right now, but I think it is doing what I want it to do for now, until the bose wants something else added. doh!

 

[OhioSteve]

It is easy to become so concerned about a technical issue that you loose focus on the overall goal of the project. It may be helpful both to yourself and us if you wrote out in a paragraph what you want to achieve. I get the impression that you want to do something like this-

You want a main form with a subform. The purpose of the interface is to assign documents to employees. When a pulldown on the main form changes, a new employee record appears in the subform. The supervisor can then assign a document number to that employee. The database will reject the document number if it is already assigned to another employee.

I'm a bit confused about how these document numbers work. I get the impression that the user creates them, and then the system checks to see if they have been used. Basically, the system says "Sorry Charlie, but John used that document number yesterday". If that is the case, then I would keep anything that could be used as a bludgeon away from these users