Verify data with Dlookup

D

David

Hello,
I'm creating a workorder database for our office, so we can keep track of
our broken computers. Also, so we can be sure we are not calling our help
desk for the same problem on one computer.

That last line is what I need help on.

So,
Using On Click I'm trying to figure out how to check the table against the
current fields to see if the the assett tag for the computer and problem have
been reported. If there is a match, a message box appears with some info
I asssume using dlookup would achieve what I need, but I dont know what to
code.

If someone could help me with syntax of this procedure. I could then take it
from there.

Thank you
David
 
K

Klatuu

I think you can do what you want, but the problem is I don't know what you
want. I would guess you would know the asset tag number, but then are you
looking for a specific ticket number or do you want to return a ticket
number, and can there be more than one ticket number for the computer?
The issue here is that DLookup will only return one value. If there are
more than 1 ticket for a computer, you will only find the first one the
DLookup encounters in the table.
 
D

David

Klatuu,
Thank you for the fast reply. Ok, basically when a command button is
pressed I would like a query(I said table at first by mistake) checked. If
the current asset tag number and the current problem are found in the same
record a message box appears. Saying something like "This issue has been
reported. "

Since there would be multible tickets for each asset tag number. The query
only shows open tickets And since dlookup shows the most recent record, the
message to the user would be correct in saying that this issue has been
reported.

Again, Thank You
David
 
K

Klatuu

The problem you have here is that the DLookup can't be used on a query that
requires a parameter. What may work would be to create an SQL statement to
find what you are looking for. This should be easy because it is only a
variation on the query that is the record source for your form. Then call
the query and see if you get a RecordCount <> 0. If you do, then it has been
reported.

Dim strSQL as String
Dim rstReported as RecordSet

strSQL = "Select * FROM.....ect;"
Set rstReported = CurrentDB.OpenRecordSet(strSQL)
If rstReported.RecordCount <> 0 Then
MsgBox "This problem has been reported"
End If
rstReported.Close
Set rstReported = Nothing

WARNING!!!!!! The above is untested "air" code, but will give you an idea
that may work.
 
D

David

Hello,again

sorry to be bother, I still think Dlookup is the way to go for my situation.
The procedure refers to a table not a query now.
I came up with this code, but something is wrong. I get a Type Mismatch Error

If IsNull(DLookup("TCompNum", "WorkOrderTbl", _
"ProblemLst=" & Me.ProblemLst & "'" And "TCompNum='"_
& Me.TCompNum & "'")) Then
MsgBox "It Works"
End If

Any help would be great.

Thank you, Again
Dave
 
A

Allen Browne

The Type Mismatch indicates that the delimiters in the 3rd argument are not
correct.

To help get the results sorted out, I suggest you declare a String to use
for the 3rd argument:
Dim strWhere As String
Then you can build the string, and see what's wrong with the results by
adding:
Debug.Print strWhere
Then when the code fails, you can look in the Immediate window (Ctrl+G) and
see what you got. The results should look like the WHERE clause of a query.

So, if ProblemLst and TCompNum are Text fields (not Number fields), try:
strWhere = "(ProblemLst = """ & Me.ProblemLst & _
""") AND (TCompNum = """ & Me.TCompNum & """)"

If they are number fields, try:
strWhere = "(ProblemLst = " & Nz(Me.ProblemLst,0) & _
") AND (TCompNum = " & Nz(Me.TCompNum,0) & ")"

The Nz() with the numbers is so you don't finish up with a mal-formed string
if either of the numbers is Null.

There's some further explanation about DLookup() in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
 
Top