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

Table search

P: 67
Hello all

Below is some code. This is the only way i know how to say what i want to happen. Could someone please re-code this so that it works please.

Private Sub Postcode_LostFocus()
If Me.Postcode = Table!tblSales!PostcodeAnd Date >= Table!tblSales!DateOfVisit + 30 Then
Me.lblOutstanding.Visible = True
End If

End Sub

Kindest Regards

Feb 6 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,287
What are the fields and field types in your tblSales?
You probably want to use DLookUp to get the values from the table.
Feb 6 '09 #2

P: 67
The fields in tblsales are Postcode which is a text field and DateOfSale which is a date field
Feb 6 '09 #3

P: 675
Let's start with some basics:
Table=tblSales has 2 fields, Postcode and DateOfVisit
Form=frmYourForm should have 4 controls:
txtPostcode TextBox - hidden, bound to Postcode - not good to name same as table field as this generates confusion.
txtPostcodeEntry - TextBox
txtDateOfVisit TextBox - bound to DateOfVisit
lblOutstanding - Label
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtPostcodeEntry_LostFocus()
  2. If Me.txtPostcodeEntry = Me.txtPostcode And Date >= Me.txtDateOfVisit + 30 Then
  3. Me.lblOutstanding.Visible = True
  4. Else
  5. Me.lblOutstanding.Visible = False
  6. End If
  7. End Sub
Me.txtDateOfVisit + 30 might be better as DateAdd("m",1,Me.txtDateOfVisit)
Feb 13 '09 #4

P: 67
Thanks for your input

Feb 13 '09 #5

Expert 2.5K+
P: 3,532
While we're on basics, this kind of code is better being placed in the textbox AfterUpdate event, rather than its LostFocus event.

The AfterUpdate event fires if a blank textbox has had data entered, or if the data has been edited. This is what you're trying to do.

The LostFocus event fires every time you tab thru the field, even if nothing changes, which is a waste of processing time.

For this type of formatting, you also need to include the code in the Form_Current event, so that as you move from record to record, the lblOutstanding label appears or is invisible, as is appropriate.

Linq ;0)>

Feb 14 '09 #6

Post your reply

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