Table Search from LostFocus

[Wayneyh]

Hello Everyone

I have tblSales Which has CustomerID, Address1, Address2, Town, County, Postcode, Phone, DateOfVisit Fields and Paid chkbox. I have frmNewSales which is linked to tblSales.

What i am trying to do is:- when a user enters a Postcode into the postcode field and tabs away from the field. I want the OnLostFocus event to search tblSales to find any records with that postcode and if the DateOfVisit is over 30 days and Paid is unchecked it will show a msgbox "Outstanding Balance"

Hope this makes sense

Regards
Wayne

[ChipR]

You may have to create a recordset and step through it like:

strSQL = "SELECT * FROM tblSales WHERE PostCode = " & Postcode
Set records = DBEngine(0)(0).OpenRecordset(strSQL)
strMessage = ""
While not records.EOF
  If records!Paid = False AND records!DateOfVisit < (Date - 30) then
    strMessage = strMessage & VbCrLf & records!CustomerID
  End If
Wend
If strMessage > "" Then
  MsgBox "Outstanding Balance: " & strMessage
End If

I think checkbox fields can be compared to True/False. You could also filter out the Paid and Date fields in the SQL string, but either way, this should give you the general idea.

[Wayneyh]

Thanks ChipR

I will try it and let you know if it works for me

Regards

Wayne

[Wayneyh]

Hi ChipR

I tried the code below but line 3 came back with runtime error

Please advise

Private Sub Postcode_LostFocus()
strSQL = "SELECT * FROM tblSales WHERE PostCode = " & Postcode
Set records = DBEngine(0)(0).OpenRecordset(strSQL)    -    Runtime error 3061
strMessage = ""
While Not records.EOF
  If records!Paid = False And records!DateOfVisit < (Date - 30) Then
    strMessage = strMessage & vbCrLf & records!InvoiceNumber
  End If
Wend
If strMessage > "" Then
  MsgBox "Outstanding Balance: " & strMessage
End If
 
End Sub
 
Regards 
Wayne

[mandanarchi]

Probably not the best / quickest method, but I'd make a query here. Something like:

SELECT CustomerID 
FROM tblSales 
WHERE Postcode=[forms]![frmNewSales]![PostCode] And CustomerID=[Forms]![frmNewSales]![CustomerID] And DateOfVisit >Date()-30 AND Paid=0;

I'm no SQL genius, that might need a little fudging to get it right.
That's based on your postcode text box being called 'PostCode', and also that you have the customer ID field on the form too (even if it's hidden or locked) called 'CustomerID'.
Then the code for your OnLostFocus would be similar to

If Not IsNull(DLookup("CustomerID","QueryName")) Then
MsgBox "Outstanding Balance!"
End If

[Wayneyh]

Hello mandanarchi

I can't get your method to work at all,

Any ideas

Wayne

[mandanarchi]

Darnit. Just spotted a mistake.
The >Date()-30 should be <Date()-30

Try that and see if that one works.

[Wayneyh]

Thanks mandanarchi

That works great for me

Wayne

[mandanarchi]

No problem =)
Glad I could help.

[missinglinq]

Note that you should probably place this kind of code in the AfterUpdate event of the textbox rather than the OnLostFocus event.

Doing this will cause the message box to appear only if you enter/edit the PostCode field.

Using the OnLostFocus event will bring up the message box even if you tab thru the field without entering /editing the postcode.

Linq ;0)>

[NeoPa]

Good point there by Linq. I strongly advise you take note of it Wayne.

@Mandi:
That's good stuff. Nice to see you offering answers here :)

I would offer a similar solution which avoids the need for creating a query specially :

Dim strWhere As String
 
strWhere = "([PostCode]='" & Me.PostCode & "') AND " & _
           "([DateOfVisit]<(Date()-30)) AND " & _
           "(NOT [Paid])"
If Not IsNull(DLookup("[CustomerID]", "[tblSales]", strWhere)) Then
  MsgBox "Outstanding Balance!"
End If