Stopping new record creation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
OK, I understand your setup now.

In the code for cmdSearch:

If IsNull(Dlookup("ewo","tbljob_info","ewo='" & txtJobNumber & "'")) Then
MsgBox "Nope"
Else
DoCmd.OpenForm "frmJobInfo" etc. etc.
End If

Greg Snidow said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
a
job
that is not in the system a blank form is displayed, and then when
Click to expand...
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
Click to expand...
does
not
exist rather than start a new record? I currently have the following
Click to expand...
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 = '" &
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much Baz. I did not know you could incorporate a dlookup in an
event like that. You have helped me do *exactly* what I was trying to do,
and it works like a charm.

Baz said:
OK, I understand your setup now.

In the code for cmdSearch:

If IsNull(Dlookup("ewo","tbljob_info","ewo='" & txtJobNumber & "'")) Then
MsgBox "Nope"
Else
DoCmd.OpenForm "frmJobInfo" etc. etc.
End If

Greg Snidow said:
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 said:
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
CyberDwarf, thanks for the reply. I searched around for this property, and
found lots on google. I could not figure out a way, however, to use it to my
advantage this time. Through this forum I have gotten much better at my SQL,
but my Access programming is still very lacking. Although Baz gave me a
solution I am always looking for something new. Could you maybe give me a
hint as to how I could use HasData, or OnNoData to do what I was trying to
do? Thank you.
 
Back
Top