This is completely unnecessary:
Code:
... where doctype='" & "INV" & "'", ...
Just use:
Code:
... where doctype = 'INV'", ...
This creates a Variant:
You should specify the data type. Plus, it is never used in the code. Why don't you just delete it altogether?
Where is this number that is misbehaving? (I can't see where in the code that item is.)
You will find your code much easier to troubleshoot if you do a few things.
Capitalize all SQL keywords.
Use the bang operator when refering to recordset fields (rs!fld rather than rs("fld")).
Use a With / End With block when you are going to refer to the same object over and over. (In my adaptation, that requires using the bang operator, or using .Fields("fld"))
Put blank lines between logical groups of statements (when the task shifts).
Break up extremely long lines of code with the line-continuation character.
If you are only returning a field or 2, use a specific field list, rather than *.
I lined up your referencing of fields from shortest name to longest name. This makes it harder to accidentally overlook a field in the list. Shortest to longest name; then within each field-length grouping, shortest to longest overall statement; then alphabetical.
Your error handler could have left recordsets open, so I added code to ensure that cannot happen.
Use the shortest name for objects that will allow recognizing what they are, and use capitalization. This rsinvoicedetailmaterial is hard to read, and unnecessary.
rsInvoiceDetailMaterial is better.
rsInvoiceDtlMtl is better.
rsInvDtlMtl even better. Just as explicit, much, much easier to recognize when coming back to modify the code.
Use indentation for blocks of code [u]every time</u>. Your life will be [u]
so</u> much easier, as will the lives of anyone who has to work on what you wrote.
I have not noticed anything wrong here, so I await your answer to my question, above.
Code:
Private Sub mi()
On Error GoTo Er
rsDocMst.Open "SELECT * FROM docmst WHERE doctype='INV'", db, _
adOpenForwardOnly, adLockReadOnly
rsInvoiceDtlMtl.Open "invoicedetailmaterial", db, _
adOpenDynamic, adLockOptimistic
rsMI.Open "SELECT * FROM MaterialIssue " & _
"WHERE jobid = '" & Trim(txtJobID.Text) & "'", db, _
adOpenForwardOnly, adLockReadOnly
Do Until rsMI.EOF
With rsInvoiceDtlMtl
rsItmMst.Open "SELECT sprname, uom " & _
"FROM itemmaster " & _
"WHERE sprnum = ' " & Trim(rsMI!sprnum) & "' " & _
" AND make = ' " & Trim(rsMI!make) & "'", _
db, _
adOpenDynamic
.AddNew
If rsItmMst.EOF = False Then
!sprname = rsItmMst!sprname
!uom = rsItmMst!uom
End If
rsItmMst.Close
!qty = rsMI!miqty
!make = rsMI!make
!jobid = Trim(txtJobID.Text)
!invno = rsDocMst!lastnum + 1
!trdate = Date
!sprnum = rsMI!sprnum
!userid = frmmain.Label1.Caption
!amount = Round(rsMI!rate * rsMI!miqty, 3)
!invdate = dtpInvdate.Value
!sprrate = Round(rsMI!rate, 3)
!profitpercentage = Val(txtProfit.Text)
.Update
End With
rsMI.MoveNext
Loop
rsMI.Close
rsDocMst.Close
rsInvoiceDtlMtl.Close
Res: Exit Sub
Er: MsgBox Err.Number & ", """ & Err.Description & """", vbCritical
If rsMI.State <> adStateClosed Then rsMI.Close
If rsItmMst.State <> adStateClosed Then rsItmMst.Close
If rsDocMst.State <> adStateClosed Then rsDocMst.Close
If rsInvoiceDtlMtl.State <> adStateClosed Then rsInvoiceDtlMtl.Close
Resume Res
End Sub
Incorrect Record updating in access
