Hi, anybody could help me to find a way where in once I enter a new
badge number to a textbox it will tell me whethere that Number is
already existing or not if it is exist it will display that record on
the form. If not it will add it on the record.
You don't say much about your data structures, but perhaps you need
something like this. Bear in mind that there is no error trapping, and
also I haven't tried running it:-
Private sub txtBadgeNum_Exit(Cancel as Integer)
Dim strSQL as string
Dim dwNewBadgeNum as Long
' close down if there's nothing there
If IsNull(txtBadgeNum.Value) Then
MsgBox "Nothing to look up!"
DoComd.Close acForm, Me.Name
' look up the table to see if it's there:
ElseIf DCount("*", "Badges", "BadgeNum=" & txtBadgeNum) _
= 0 Then
' oh dear, no such record
dwNewBadgeNum = GetNewBadgeNumber() ' this is up to you!!
strSQL = "INSERT INTO Badges(BadgeNum) " & _
"VALUES (" & dwNewBadgeNum & ")"
CurrentDB().Execute strSQL, dbFailOnError
' Now make a query that returns
' just the one record
strSQL = "SELECT * FROM Badges " & _
"WHERE BadgeNum = " & dwNewBadgeNum
' and poke it into the form's recordsource
Me.RecordSource = strSQL
Me.Requery
Else
' OK: we're in business: just make a query that
' returns just the one record
strSQL = "SELECT * FROM Badges " & _
"WHERE BadgeNum = " & txtBadgeNum
' and poke it into the form's recordsource
Me.RecordSource = strSQL
Me.Requery
End If
End Sub
Hope that helps
Tim F
