Re: Stopping new record creation

Tech-Archive recommends: Speed Up your PC by fixing your registry

Thanks for the quick reply. On my search form, frmSearch, there is one
unbound text box, txtJobNumber. There is also a command button, cmdSearch.
When the user enters a job number in txtJobNumber and hits the command button
a form, frmJobInfo, is opened with all the job information. The record
source of frmJobInfo is a stored procedure with JobNumber as a parameter.
The input parameters property of frmJobInfo is
@JobNumber=forms!frmJobSearch!txtJobNumber. What I am looking for is to
return a message saying "record does not exist", or something like that
rather than have frmJobInfo open if there is no record corresponding to
txtJobNumber.

"Baz" wrote:

Presumably txtJobNumber is a bound control, it should not be. If you need a
bound control for the job number, create a second, unbound control for
searching purposes.

"Greg Snidow" <GregSnidow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FFCB902D-C5F3-40D1-94E9-A7A965929D2B@xxxxxxxxxxxxxxxx
Greetings all. I am using ADP 2003 and SQL2K. I have a form with a
stored
procedure as the record source. There is a search form where users type
in a
job number that is passed to the procedure to bring up the record
corresponding to that job number. The job number is the PK of the table
called by the stored procedure. The problem is when users search for a
job
that is not in the system a blank form is displayed, and then when they
try
to leave the form or hit the button to search for another job they get an
error saying 'can not insert null value into table...' This is easily
fixed
by hitting the escape key on the keyboard, but I would like for a new
record
to not be started. Is it possible to return a message saying record does
not
exist rather than start a new record? I currently have the following on
the
forms txtJobNumber before update event to alert users that the number
already
exists, in the event they did not search for it first.

Private Sub txtJobNumber_BeforeUpdate(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rstbljob_info As ADODB.Recordset
Dim strSQL As String
Dim boolDupId As Boolean

strSQL = "Select ewo FROM tbljob_info where ewo = '" & Form.[txtJobNumber]
&
"'"

boolDupId = False
Set rstbljob_info = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rstbljob_info.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
If Not rstbljob_info.EOF Then
boolDupId = True
MsgBox "The Job Number you entered already exists.", vbExclamation
End If
rstbljob_info.Close

Cancel = boolDupId
End Sub

I am not sure exactly how this works, as I found it on the Microsoft help
site and used my table and field names. I think the jist of it is that it
creates a select string based on the text box entry, and if the text entry
is
not the end of the returned recordset it knows the record already exists?
Where could I put something like this to stop a new record from being
created
when the job number being searched for does not exist. Thank you, and
please
let me know what else I need to impart, as I am not sure where I need to
start.



.

Quantcast