How should i save requisition no in a MaterialRequisintion order table

[firoz.raj]

can anybody tell me .How should i store Requistion Nos In MaterialRequisitionorder table and Material Requistion Detail table.
Here is the code what i have wriiten.Why it is breaking on the bold line. i am getting error item cannot be found in the collection corresponding to the requested name or ordinal.

Private Sub Command1_Click()
   If (CheckInput) Then
   End If
 '  On Error GoTo cancel
   Dim row As Integer
   Set rs = New ADODB.Recordset
'       rs.Open " SELECT * FROM mrtemp ", con, adOpenDynamic, adLockOptimistic
        rs.Open "select * from MaterialRequisitionOrder as p inner join MaterialRequisitionDetail as e on e.requsition_no=p.requsition_no", con, adOpenDynamic, adLockOptimistic
   For row = 1 To Grd.Rows
         ' we test before we start to add a new Record
  If Grd.TextMatrix(CLng(row), 1) = "" Then
         ' we only leave the loop !
     Exit For
  End If
     rs.AddNew
     rs.Fields("Dept_name") = Combo1.Text
     rs.Fields("job_no") = Text1.Text
     rs.Fields("Dept_id") = CLng(Text2.Text)
     rs.Fields("emp_name") = Text3.Text
     rs.Fields("delivery_point") = Text4.Text
     rs.Fields("delivery_time") = CStr(MaskEdBox1)
     rs.Fields("delivery_date") = DTPicker2.Value
'     rs.Fields("sug_vendor") = Text7.Text
     rs.Fields("materialreq_date") = DTPicker1.Value
'    rs.Fields("manager") = Text13.Text
     [B]rs.Fields("requsition_no") = Text12.Text[/B]
'    rs.Fields("srno") = Grd.TextMatrix(CLng(row), 0)
     rs.Fields("Material_id") = Grd.TextMatrix(CLng(row), 1)
     rs.Fields("Material_name") = Grd.TextMatrix(CLng(row), 2)
     rs.Fields("unit") = Grd.TextMatrix(CLng(row), 3)
     rs.Fields("Quantity") = Grd.TextMatrix(CLng(row), 4)
'              If rs.Fields("Item_code") = "" Then
'              Exit For
'              Exit Sub
'              End If
'             MsgBox Trim(Grd.TextMatrix(1, 1))
   MsgBox "Commit", vbInformation, Me.Name
   rs.Update
    Next
   rs.Close
'cancel:     MsgBox "Error in Command1_Click()" & vbCrLf & Err.Number & vbCrLf & Err.Description
   End Sub

[javmedia]

This means the field name is not "requsition_no"

Check your database definition (design) and verify the name of the field in the table specified that it exist and is named identically.

You should also never use SELECT * in your query definition above as it has the following problems...

a) it's very slow
b) it does not provide readability in coding
c) it is not very reusable

Even if many say VB is not object oriented, this can be achieved when coding with certain styles.

consider:

SELECT "Dept_name","job_no","Dept_id","emp_name","delivery_point",
"delivery_time","delivery_date","materialreq_date","requsition_no",
"Material_id","Material_name","unit","Quantity"
 FROM "MaterialRequisitionOrder";

Above you specified this is not used, and you use * ALL on an inner join which is even slower.

Adding a criteria and readability increases and the three commented fields where left out.

Consider:

sFieldList = """Dept_name"",""job_no"",""Dept_id"",""emp_name"",""delivery_point"",""
delivery_time"",""delivery_date"",""materialreq_date"",""requsition_no"",""
Material_id"",""Material_name"",""unit"",""Quantity"""

sSQL = "select " & sFieldList & " from MaterialRequisitionOrder as p inner join MaterialRequisitionDetail as e on e.requsition_no=p.requsition_no"

rs.Open sSQL, con, adOpenDynamic, adLockOptimistic

Also try using e.requsition_no instead of the short field name since you specified "AS e"

[firoz.raj]

Now i have changed my sql statement it is working. but it is breaking on rs.Fields("Material_ID") = Grd.TextMatrix(CLng(row), 1)
i don't know why .can you explain why .here is the code what i have
written.

Private Sub Command1_Click()
   If (CheckInput) Then
   End If
 '  On Error GoTo cancel
   Dim row As Integer
   Set rs = New ADODB.Recordset
  '     rs.Open " SELECT * FROM mrtemp ", con, adOpenDynamic, adLockOptimistic
'        rs.Open "select * from MaterialRequisitionOrder as p inner join MaterialRequisitionDetail as e  Inner Join  Materials as m on e.requsition_no=p.requsition_no on m.requsition_no=e.requsition_no", _
'        con, adOpenDynamic, adLockOptimistic
Dim StrSql As String
StrSql = "SELECT p.* FROM (MaterialRequisitionOrder AS p INNER JOIN MaterialRequisitionDetail AS e ON p.requsition_no = e.requsition_no) INNER JOIN Material_Table AS m ON e.Material_ID = m.Material_ID "
rs.Open StrSql, con, adOpenDynamic, adLockOptimistic

   For row = 1 To Grd.Rows
         ' we test before we start to add a new Record
  If Grd.TextMatrix(CLng(row), 1) = "" Then
         ' we only leave the loop !
     Exit For
  End If
     rs.AddNew
'     rs.Fields("Dept_name") = Combo1.Text
     rs.Fields("job_no") = Text1.Text
     rs.Fields("Dept_id") = CLng(Text2.Text)
     rs.Fields("Jobrequested_by") = Text3.Text
     rs.Fields("delivery_point") = Text4.Text
     rs.Fields("delivery_time") = CStr(MaskEdBox1)
     rs.Fields("delivery_date") = DTPicker2.Value
'     rs.Fields("sug_vendor") = Text7.Text
     rs.Fields("materialreq_date") = DTPicker1.Value
'    rs.Fields("manager") = Text13.Text
     rs.Fields("requsition_no") = Text12.Text
'    rs.Fields("srno") = Grd.TextMatrix(CLng(row), 0)
   [B]  rs.Fields("Material_ID") = Grd.TextMatrix(CLng(row), 1)
     rs.Fields("Material_name") = Grd.TextMatrix(CLng(row), 2)
     rs.Fields("unit") = Grd.TextMatrix(CLng(row), 3)
     rs.Fields("Quantity") = Grd.TextMatrix(CLng(row), 4)[/B]
'              If rs.Fields("Item_code") = "" Then
'              Exit For
'              Exit Sub
'              End If
'             MsgBox Trim(Grd.TextMatrix(1, 1))
   MsgBox "Commit", vbInformation, Me.Name
   rs.Update
    Next
   rs.Close
'cancel:     MsgBox "Error in Command1_Click()" & vbCrLf & Err.Number & vbCrLf & Err.Description
   End Sub

[javmedia]

Is this an MS SQL database or MS Access?
It would help if I could see the table def on the database.

Check that the Material_ID column truly is a string in this case as the TextMatrix value defaults to strings.

Or for type casting use...

rs.Fields("Material_ID") = CLng(Grd.TextMatrix(row, 1))

--- extra notes

I would recommend for style in coding that you stick to a fixed format in naming your fields instead of mixing upper/lower case versions in the same table def.

Also, if the Row value is a long you shouldn't do type casting to CLng as that adds another operation and slows your code down.

Avoid type casting like that unless you absolutely have to for example when specifying CDate or CVar's as they can be nasty to work with.

CLng normally dont require it if it is set programmatically as there is no user interaction on that part that can break your code.

[firoz.raj]

right know i have changed the code as per your suggestion.But getting type mismatch error .here is the code what i have written.

Private Sub Command1_Click()
   If (CheckInput) Then
   End If
 '  On Error GoTo cancel
   Dim row As Integer
   Set rs = New ADODB.Recordset
'        rs.Open "select * from MaterialRequisitionOrder as p inner join MaterialRequisitionDetail as e  Inner Join  Materials as m on e.requsition_no=p.requsition_no on m.requsition_no=e.requsition_no", _
'        con, adOpenDynamic, adLockOptimistic
Dim StrSql As String
StrSql = "SELECT p.* FROM (MaterialRequisitionOrder AS p INNER JOIN MaterialRequisitionDetail AS e ON p.requsition_no = e.requsition_no) INNER JOIN Material_Table AS m ON e.Material_ID = m.Material_ID "
rs.Open StrSql, con, adOpenDynamic, adLockOptimistic

   For row = 1 To Grd.Rows
         ' we test before we start to add a new Record
  If Grd.TextMatrix(CLng(row), 1) = "" Then
         ' we only leave the loop !
     Exit For
  End If
     rs.AddNew
'     rs.Fields("Dept_name") = Combo1.Text
     rs.Fields("job_no") = Text1.Text
     rs.Fields("Dept_id") = CLng(Text2.Text)
     rs.Fields("Jobrequested_by") = Text3.Text
     rs.Fields("delivery_point") = Text4.Text
     rs.Fields("delivery_time") = CStr(MaskEdBox1)
     rs.Fields("delivery_date") = DTPicker2.Value
'     rs.Fields("sug_vendor") = Text7.Text
     rs.Fields("materialreq_date") = DTPicker1.Value
'    rs.Fields("manager") = Text13.Text
     rs.Fields("requsition_no") = Text12.Text
'    rs.Fields("srno") = Grd.TextMatrix(CLng(row), 0)
[B]     rs.Fields("Material_ID") = CLng(Grd.TextMatrix(CLng(row), 1))[/B]
     rs.Fields("Material_name") = Grd.TextMatrix(CLng(row), 2)
     rs.Fields("unit") = Grd.TextMatrix(CLng(row), 3)
     rs.Fields("Quantity") = Grd.TextMatrix(CLng(row), 4)
'              If rs.Fields("Item_code") = "" Then
'              Exit For
'              Exit Sub
'              End If
'             MsgBox Trim(Grd.TextMatrix(1, 1))
   MsgBox "Commit", vbInformation, Me.Name
   rs.Update
    Next
   rs.Close
'cancel:     MsgBox "Error in Command1_Click()" & vbCrLf & Err.Number & vbCrLf & Err.Description
   End Sub

[hkdani]

rs.Fields("Material_ID") = CLng(Grd.TextMatrix(CLng(row), 1))

Check the value of that TextMatrix. I would guess that it's a Null value. Put a break at that line of code and use your immediate pane to determine the error.

It would probably be a good idea to include error checking in that procedure to give you some more exact info.

Private Sub MyRoutine()
On error goto MyRoutine_ERROR
' Code .......
' ....

Exit sub

MyRoutine_ERROR:
debug.print err.description, err.number, err.source
resume next
End Sub()