Update Query that checks if a record exists

[pauldt123]

Hi guys, I was wondering if anyone could help me with the following:

I have a form which is driven by a "select customer" combo list.

Within this form there is a child form linked on customer ID which enables a user to raise an issue relating specifically to that customer.

This child form is based on a simple "Issues Table" with the following fields "Customer ID", Issue Category, Issue Detail, Raised By, Issue Closed, Issue Closed By.

I need a query (which I will run on the main form's open event, "add issue" on click event, and "issue closed by" aterupdate event) which will check to see if there are any records in the "Issues Table", which match the customer id of the customer currently selected in the main combo list, where the "issue closed" checkbox has not been ticked. If such a record exists I would like another checkbox on the main form called something like "unresolved issue exists" to be set to "-1", if there are no such records I would like "unresolved issue exists" to be set to "0".

I hope this is clear. I'm struggling with it because I have an inadequate understanding of how to query specific records in a table, based on a given criteria (i.e. "customer id"). I'm keen to learn, so if anyone has a link explaining this it would be greatly appreciated.

You all do a great job, and provide an invaluable service. Thanks in advance for your help.

 

[randy700]

How about...

If IsNull(DLookUp("CustomerID", "tblIssues", "CustomerID = " & txtCustomerID)) Then
   [COLOR=green]Code for NO RECORD[/color]
Else
   [COLOR=green]Code for RECORD FOUND[/color]
End If

Randy

 

[pauldt123]

Hi Randy,

Thanks for the reply, but as far as I can tell, the above is merely checking if there is an existing record in the issues table, with a customer ID that matches the currently selected customer. I need the code to check if there is/are any existing matching records (as the above code does), and then check if "issue closed" is null for any of these records. Essentially I need to know if there are any matching records in the Issues table that haven't been closed. I hope this makes sense.

Thanks again

 

[randy700]

Add to your WHERE clause...

If IsNull(DLookUp("CustomerID", "tblIssues", "CustomerID = " & txtCustomerID [COLOR=red]& " AND IssueClose = 0"[/color])) Then
   [COLOR=green]Code for NO RECORD[/color]
Else
   [COLOR=green]Code for RECORD FOUND[/color]
End If

Randy

 

[pauldt123]

Hi Randy,

I tried that code but it's returning a runtime error 2001, "you cancelled the previous operation".

I've pasted the code I've tried below. [Cast ID] is the customer ID, and "companyid2" is the textbox on the main form which contains the company ID of the currently selected company.

If IsNull(DLookup("[Cast ID]", "Issues", "[Cast ID] = " & Me.companyid2 & " AND [Issue Closed] = 0")) Then

Can you see where I'm going wrong with this?

Thanks

Paul

 

[randy700]

Your code looks right, unless [Cast ID] is text. In that case, you would need to enclose it with single quotes...
"[Cast ID] = '" & me.companyid2 & "' AND [Issue Closed] = 0"))

Randy

 

[pauldt123]

Hi Randy,

When I hover the pointer over the companyid2 part of the code it already returns the expected value, so I don't think the problem lies there. I tried the single quotes and it threw up a compile error "Expected: expression".

Any other suggestions to get this one working?

Thanks again Randy, I really appreciate your help.

Paul