checking repeating data in database

[firoz.raj]

i want if requisition no is already there message should come.requistion no is already there .Here is the code what
i have written.i don't know why it is not working.any help
would be greately appreciated.

Private Sub btSave_Click()
Dim row As Integer, bEdit As Boolean
Dim StrSql As String, sqlQueryOrder  As String, sqlQueryDetail As String
Dim rsOrder As ADODB.Recordset, rsDetail As ADODB.Recordset
' is the maindata filled
If (Not CheckInput) Then Exit Sub
On Error GoTo Open_Err

Set rsOrder = New ADODB.Recordset
Set rsDetail = New ADODB.Recordset
sqlQueryOrder = "SELECT * FROM MaterialRequisitionOrder" & _
" Where (((MaterialRequisitionOrder.requsition_no) = " & ReqNo.Text & "))"
Debug.Print sqlQueryOrder
sqlQueryDetail = "SELECT MaterialRequisitionDetail.* FROM MaterialRequisitionDetail"
Debug.Print sqlQueryDetail
' we need to open both tables
rsOrder.Open sqlQueryOrder, con, adOpenDynamic, adLockOptimistic
rsDetail.Open sqlQueryDetail, con, adOpenDynamic, adLockOptimistic
' are both tables opened successfully
If rsOrder.State = adStateOpen And rsDetail.State = adStateOpen Then
     ' we start an Transaction
     con.BeginTrans
     On Error GoTo Err_Hnd
    'At first all data we need in the order table
     If CheckOrderExists(rsOrder) Then
        bEdit = True
     Else
        ' it is a new record so we cannot add it twice
        rsOrder.AddNew
        bEdit = False
     End If
     rsOrder.Fields("job_no") = TxtJobNo.Text
     rsOrder.Fields("Dept_id") = CLng(TxtDep_ID.Text)
     rsOrder.Fields("requestedBy_ID") = TxtRequBy.Text
     rsOrder.Fields("delivery_point") = TxtDel_Point.Text
     rsOrder.Fields("delivery_time") = CStr(MaskEdBox1)
     rsOrder.Fields("delivery_date") = DTPicker2.Value
     rsOrder.Fields("suggested_supplierID") = TxtSugSupplier.Text
     rsOrder.Fields("materialreq_date") = DTPicker1.Value
     If Not bEdit Then
        rsOrder.Fields("requsition_no") = ReqNo.Text
     End If
     rsOrder.Update
     ' now the details if they exist delete them
     If bEdit And CheckDetailsExists(rsDetail) Then
          ' we remove them all
          If Not RemoveDetails(rsDetail) Then
             con.RollbackTrans
             GoTo Leave_Point
          End If
     End If
     For row = 1 To Grd.Rows - 1
           ' we test before we start to add a new Record
           ' so we dont add an empty line
       If Grd.TextMatrix(CLng(row), 1) = "" Then
           ' we only leave the loop !
           Exit For
       End If
       ' it doesn't exist so we add it
    [B]   If ReqNo.Text = rsDetail.Fields("requistion_no") Then 
        MsgBox("requistion no is already exists") 
       Exit Sub 
       Else 
       end if[/B]
       rsDetail.AddNew
       rsDetail.Fields("requsition_no") = ReqNo.Text
       rsDetail.Fields("Material_ID") = CLng(Grd.TextMatrix(CLng(row), 1))
       rsDetail.Fields("Quantity") = CLng(Grd.TextMatrix(CLng(row), 4))
       rsDetail.Update
    Next
    con.CommitTrans
    MsgBox "Commit", vbInformation, Me.Name
End If
Leave_Point:
If rsDetail.State = adStateOpen Then
   rsDetail.Close
End If

If rsOrder.State = adStateOpen Then
   rsOrder.Close
End If
Set rsOrder = Nothing
Set rsDetail = Nothing
Exit Sub
Open_Err:
  MsgBox Err.Description
  Err.Clear
  ' reset error Handle
  On Error GoTo 0
  ' leave the program
  GoTo Leave_Point
Err_Hnd:
  con.RollbackTrans
  MsgBox Err.Description
  Err.Clear
  ' reset error Handle
  On Error GoTo 0
  ' leave the program
  GoTo Leave_Point
End Sub

[vb5prgrmr]

If rsOrder. BOF = False AND rsOrder.EOF = False AND rsOrder.RecordCount <> 0 Then
  MsgBox "Order Number Exists!"
End If

[firoz.raj]

If rsOrder. BOF = False AND rsOrder.EOF = False AND rsOrder.RecordCount <> 0 Then
  MsgBox "Order Number Exists!"
End If

did you check database it is a relational table.Materialrequistionorder with materialrequistiondetail both
table have common field requistion no how should i check both table
at the same time.

[hkdani]

MsgBox("requistion no is already exists")

That line of code shouldn't work, period.
You should leave out the parenthesis.

MsgBox "Requisition number already exists", vbInformation, "Requisition Status"

Or

dim intMessage as integer

intMessage = MsgBox("Requisition number already exists.")

Other than that, if you could put a break point at that line and post what the values from ReqNo.Text = rsDetail.Fields("requistion_no") yield, that would be helpful in debugging this.

[firoz.raj]

That line of code shouldn't work, period.
You should leave out the parenthesis.

MsgBox "Requisition number already exists", vbInformation, "Requisition Status"

Or

dim intMessage as integer

intMessage = MsgBox("Requisition number already exists.")

Other than that, if you could put a break point at that line and post what the values from ReqNo.Text = rsDetail.Fields("requistion_no") yield, that would be helpful in debugging this.

i got err.description in immediate window.Item cannot be found in the collection corresponding to the requested name or ordinal.

[vb5prgrmr]

did you check database it is a relational table.Materialrequistionorder with materialrequistiondetail both
table have common field requistion no how should i check both table
at the same time.

Strait from your DB...

SELECT MaterialRequisitionDetail.*, MaterialRequisitionOrder.*
FROM MaterialRequisitionOrder INNER JOIN MaterialRequisitionDetail ON MaterialRequisitionOrder.requsition_no = MaterialRequisitionDetail.requsition_no;

Execute your testing rs and if it fails an if statement like in the earlier post then the records do not exist. However the if statement above will also tell you if it exists.

Good Luck

[hkdani]

Item cannot be found in the collection corresponding to the requested name or ordinal.

Please, be specific. What item?
if while in the Immediate Window you try this, then

?rsDetail.Fields("requistion_no")

you should see the value of the field. If not, then you may have the name spelled wrong. Check your recordset field names, to see if the field you're looking for actually exists as you have it spelled.

Also put a break at this line, hit F8 to advance one line and see in the Immediate Window if you can pull up any values: rsOrder.Open sqlQueryOrder, con, adOpenDynamic, adLockOptimistic

' Immediate Window
?rsOrder.Fields.Count

Should tell you if your open statement worked by showing you that there actually exist fields in your recordset. You can then

MoveNext
?rsOrder.Fields("SomeFieldName")

in the immediate window to see if you're actually getting your data.