Visible textbox only when adding new record 1

[annie52]

If the user enters a stock number that has been reported before (i.e., it's already in the database), I want to display a textbox Alert. With the following code, I see the Alert many times as I navigate through records. But, I only want the Alert to display when the user is entering a new record. If I navigate to that record later on, I don't want to see the Alert anymore. I don't know how to do this.

If IsNull(Me.Alert) Then
Me.Alert.Visible = False
Else
Me.Alert.Visible = True
End If

 

[annie52]

On second thought, I don't like the way I thought this through. I don't want to have to update a field in the Discrepancies table everytime I get a "second hit" on a stock number.

What I should do is check the Discrepancies table after the user enters a stock number. If the number is found, I want to display a static Alert in a text box. Again, I don't want the message to appear later on, only when the record is first being entered.

 

[PHV]

You may use the Me.NewRecord property and the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya annie52 . . .

Whats the point if the same stock number can be entered anyway?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

How about in the table setting the field to 'Indexed - Yes (No Duplicates)'

HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[annie52]

Hi everyone. Here's what I ended up with and it works great.

Dim strWhere As String
strWhere = " NSN = " & " qryDisrepExist.NSN"

If Me.NewRecord Then
If Not IsNull(DLookup("NSN", "qryDisrepExist", strWhere)) Then
Me.Alert = "Previous discrepancies were reported on this NSN."
Me.Alert.Visible = True
Else
Me.Alert.Visible = False
End If
End If

Thanks, PHV. I'd never used DLookup or NewRecord but you can bet I sure will be using them from now on!

TheAceMan1 and MazeWorX -- To clarify, the stock number (NSN) can be entered more than once on the many side of the relationship. I just wanted to alert the user when there appears to be a history of problems on the item so they can decide whether additional research is warranted.

 

[annie52]

Oops! I forgot to issue a star for PHV earlier. He or she certainly deserves one so here it is.