Table search

[Wayneyh]

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_LostFo cus()
If Me.Postcode = Table!tblSales! PostcodeAnd Date >= Table!tblSales! DateOfVisit + 30 Then
Me.lblOutstandi ng.Visible = True
End If

End Sub

Kindest Regards

Wayne

[ChipR]

What are the fields and field types in your tblSales?
You probably want to use DLookUp to get the values from the table.

[Wayneyh]

The fields in tblsales are Postcode which is a text field and DateOfSale which is a date field

[OldBirdman]

Let's start with some basics:
Table=tblSales has 2 fields, Postcode and DateOfVisit
Form=frmYourFor m should have 4 controls:
txtPostcode TextBox - hidden, bound to Postcode - not good to name same as table field as this generates confusion.
txtPostcodeEntr y - TextBox
txtDateOfVisit TextBox - bound to DateOfVisit
lblOutstanding - Label

Private Sub txtPostcodeEntry_LostFocus()
If Me.txtPostcodeEntry = Me.txtPostcode And Date >= Me.txtDateOfVisit + 30 Then
Me.lblOutstanding.Visible = True
Else
Me.lblOutstanding.Visible = False
End If
End Sub

Me.txtDateOfVis it + 30 might be better as DateAdd("m",1,M e.txtDateOfVisi t)

[Wayneyh]

Thanks for your input

Wayne

[missinglinq]

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)>

.