By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Table Search from LostFocus

P: 67
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
Feb 9 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
You may have to create a recordset and step through it like:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM tblSales WHERE PostCode = " & Postcode
  2. Set records = DBEngine(0)(0).OpenRecordset(strSQL)
  3. strMessage = ""
  4. While not records.EOF
  5.   If records!Paid = False AND records!DateOfVisit < (Date - 30) then
  6.     strMessage = strMessage & VbCrLf & records!CustomerID
  7.   End If
  8. Wend
  9. If strMessage > "" Then
  10.   MsgBox "Outstanding Balance: " & strMessage
  11. 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.
Feb 9 '09 #2

P: 67
Thanks ChipR

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

Regards

Wayne
Feb 9 '09 #3

P: 67
Hi ChipR

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

Please advise
Expand|Select|Wrap|Line Numbers
  1. Private Sub Postcode_LostFocus()
  2. strSQL = "SELECT * FROM tblSales WHERE PostCode = " & Postcode
  3. Set records = DBEngine(0)(0).OpenRecordset(strSQL)    -    Runtime error 3061
  4. strMessage = ""
  5. While Not records.EOF
  6.   If records!Paid = False And records!DateOfVisit < (Date - 30) Then
  7.     strMessage = strMessage & vbCrLf & records!InvoiceNumber
  8.   End If
  9. Wend
  10. If strMessage > "" Then
  11.   MsgBox "Outstanding Balance: " & strMessage
  12. End If
  13.  
  14. End Sub
  15.  
  16. Regards 
  17. Wayne
Feb 10 '09 #4

P: 90
Probably not the best / quickest method, but I'd make a query here. Something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerID 
  2. FROM tblSales 
  3. 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
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(DLookup("CustomerID","QueryName")) Then
  2. MsgBox "Outstanding Balance!"
  3. End If
Feb 10 '09 #5

P: 67
Hello mandanarchi

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

Any ideas

Wayne
Feb 10 '09 #6

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

Try that and see if that one works.
Feb 10 '09 #7

P: 67
Thanks mandanarchi

That works great for me

Wayne
Feb 10 '09 #8

P: 90
No problem =)
Glad I could help.
Feb 10 '09 #9

missinglinq
Expert 2.5K+
P: 3,532
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)>
Feb 10 '09 #10

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. strWhere = "([PostCode]='" & Me.PostCode & "') AND " & _
  4.            "([DateOfVisit]<(Date()-30)) AND " & _
  5.            "(NOT [Paid])"
  6. If Not IsNull(DLookup("[CustomerID]", "[tblSales]", strWhere)) Then
  7.   MsgBox "Outstanding Balance!"
  8. End If
Feb 16 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.