Recordset

[Wahab]

Please help me in writing the recordset - rst..
I want to open rst based on invoices table ie. tInvoices (having InvNo.
CustomerID, SalesAmt (all fields are in Number format). I have one form which
is having 3 controls namely Invoice No, Customer ID and Sales Amount with one
button to save the record. I want to open rst after entering the Invoice No.
if invoice number is present in tInvoices then rst will bring the data on
form for editing after editing I will save the record by clicking the save
button. I don’t want to two buttons for save and editing I want to manage
editing and adding new records from one button which is the Save button.
How I will manage this one?

 

[Tim Ferguson]

Please help me in writing the recordset - rst..
I want to open rst based on invoices table ie. tInvoices (having
InvNo. CustomerID, SalesAmt (all fields are in Number format). I have
one form which is having 3 controls namely Invoice No, Customer ID and
Sales Amount with one button to save the record. I want to open rst
after entering the Invoice No. if invoice number is present in
tInvoices then rst will bring the data on form for editing after
editing I will save the record by clicking the save button. I don’t
want to two buttons for save and editing I want to manage editing and
adding new records from one button which is the Save button. How I
will manage this one?

Private Sub txtInvNo_AfterUpdate()

dim jetSQL as string

' make sure you have reference to DAO not ADO
dim rst as recordset

' don't bother if there's nothing to read
if len(txtInvNo)=0 then
txtCustomerID.Value = Null
txtSamesAmt.Value = Null

' don't bother doing anything else
allDone = True

else
' create the query
jetSQL = "SELECT CustomerID, SalesAmt " & vbNewLine & _
"FROM Tinvoices " & vbNewLine & _
"WHERE InvNo = " & txtInvNo.Text

' get the data
set rst = currentdb().OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly)

if rst.BOF Then
' no record: make a new one
dim jetInsert as string

' big problem: we need error trapping on the typed
' -in value of the txtInvNo, not least to prevent
' SQL injection security problems etc etc
'
jetInsert = "INSERT INTO Tinvoices (InvNo) " & _
"VALUES (" & txtInvNo.Text & ");"
currentdb().execute jetInsert, dbFailOnError

' now get the new record back
set rst = currentdb().OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly)
end if

' and use the fields
txtCustomerID.Value = rst("CustomerID")
txtSalesAmt.Value = rst("SalesAmt")

end if
end sub


.... or something like this. It's air code so treat it with due caution
and suspicion. Hope it helps though. The button click event will
presumably create an UPDATE TinVoices command to put the new data into
the record.



Tim F

 

[Wahab]

Thank you very much Mr. Tim; it works ; this is totally new for me, if
invoice no is present in data base it is bringing for editing. Please , i
want to see how you are writing codes on save button so it will add new
record or if record is edited the same will be saved.
Again thank you very much

 

[Tim Ferguson]

Thank you very much Mr. Tim; it works ; this is totally new for me, if
invoice no is present in data base it is bringing for editing. Please
, i want to see how you are writing codes on save button so it will
add new record or if record is edited the same will be saved.
Again thank you very much

Once again, this is "air code" so don't trust it without testing:

private sub cmdSave_Click

dim jetSQL as string

' lots of code here to make sure the text boxes contain
' legal and valid entries

jetSQL = "UPDATE TinVoices " & _
"SET CustomerID = " & txtCustomerID.Value & ", " & _
" SalesAmt = " & txtSalesAmt.Value & " " & _
"WHERE InvNo = " & txtInvNo.Value

' better error trap this
On Error Resume Next
currentdb().execute jetSQL, dbFailOnError

If Err.Number <> 0 Then
msgbox "Error " & Err.Number & ": " & Err.Description

Else
' saved okay, blank everything
txtInvNo = null
txtCustomerID = null
txtSalesAmt = null

End If
end sub


Mind you, I still think it's easier to use the built in Access
databinding. Why buy a dog and do your own barking?

B Wishes


Tim F