updating an existing record in a form

A

Alan#

Registered User.
Local time
Today, 15:38
Joined
Feb 27, 2014
Messages
14
Hi, i'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and any ideas how to code it and is it similiar to my find data code below. appreciate any help.

Here is my save function code:
Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")

rs.AddNew
'rs("No").Value = txtNo
rs("Deliver Docket").Value = txtDel
rs("Customer").Value = cboCust
rs("Name").Value = txtCust
rs("Order Number").Value = txtOrderNumber
rs("Customer Reference").Value = txtCustRef
rs("County").Value = cboCounty
rs("Location").Value = txtLocation
rs("Phone No").Value = txtPhoneNo
rs("Skip No").Value = txtSkipNo
rs("Date Dropped").Value = txtDateDropped
rs("Truck").Value = cboTruck
rs("Date Collected").Value = txtDateCollected
rs("Date Weighed").Value = txtDateWeighed
rs("Waste Code").Value = cboWasteCodes
rs("Weight").Value = txtWeight
rs("Weigh Ticket").Value = txtWeighTicket
rs("Status").Value = cboStatus
rs("Paid By").Value = cboPaidBy
rs("Invoice No").Value = txtInvoice
rs("VAT Code").Value = cboVatCode
rs("Price").Value = txtPrice
rs("VAT Type").Value = txtVatType
rs("Goods").Value = txtInvoiceGoods
rs("Vat").Value = txtInvoiceVat
rs("Total").Value = txtInvoiceTota

SaveData = True
End Function

and here is my Find Data function code:
Function FindData()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From [Skips Delivered] Where [Deliver Docket] = " & txtDel

Set rs = CurrentDb.OpenRecordset(strSQL)

cboCust = rs("Customer")
txtCust = rs("Name")
txtOrderNumber = rs("Order Number")
txtCustRef = rs("Customer Reference")
cboCounty = rs("County")
txtLocation = rs("Location")
txtPhoneNo = rs("Phone No")
txtSkipNo = rs("Skip No")
txtDateDropped = rs("Date Dropped")
cboTruck = rs("Truck")
txtDateCollected = rs("Date Collected")
txtDateWeighed = rs("Date Weighed")
cboWasteCodes = rs("Waste Code")
txtWeight = rs("Weight")
txtWeighTicket = rs("Weigh Ticket")
cboStatus = rs("Status")
cboPaidBy = rs("Paid By")
txtInvoice = rs("Invoice No")
cboVatCode = rs("VAT Code")
txtPrice = rs("Price")
txtVatType = rs("VAT Type")
txtInvoiceGoods = rs("Goods")
txtInvoiceVat = rs("Vat")
txtInvoiceTotal = rs("Total")
rs.Close
End Function
 
Must be all coded unfortunately, nothing bounded:mad:
 
Okay give another shot with properly indented code, wrapped using Code tags ! I/someone will have a look at it.
 
Code: 
 Function SaveData() As Boolean
  
  Dim db As Database
  Dim rs As DAO.Recordset

  Set db = CurrentDb
  Set rs = db.OpenRecordset("Skips Delivered")

    rs.AddNew
    'rs("No").Value = txtNo
    rs("Deliver Docket").Value = txtDel
    rs("Customer").Value = cboCust
    rs("Name").Value = txtCust
    rs("Order Number").Value = txtOrderNumber
    rs("Customer Reference").Value = txtCustRef
    rs("County").Value = cboCounty
    rs("Location").Value = txtLocation
    rs("Phone No").Value = txtPhoneNo
    rs("Skip No").Value = txtSkipNo
    rs("Date Dropped").Value = txtDateDropped
    rs("Truck").Value = cboTruck
    rs("Date Collected").Value = txtDateCollected
    rs("Date Weighed").Value = txtDateWeighed
    rs("Waste Code").Value = cboWasteCodes
    rs("Weight").Value = txtWeight
    rs("Weigh Ticket").Value = txtWeighTicket
    rs("Status").Value = cboStatus
    rs("Paid By").Value = cboPaidBy
    rs("Invoice No").Value = txtInvoice
    rs("VAT Code").Value = cboVatCode
    rs("Price").Value = txtPrice
    rs("VAT Type").Value = txtVatType
    rs("Goods").Value = txtInvoiceGoods
    rs("Vat").Value = txtInvoiceVat
    rs("Total").Value = txtInvoiceTota
  
 SaveData = True
 End Function
Code: 
Function FindData()

 Dim rs As DAO.Recordset
 Dim strSQL As String

  strSQL = "Select * From [Skips Delivered] Where [Deliver Docket] = " & txtDel
  Set rs = CurrentDb.OpenRecordset(strSQL)

   cboCust = rs("Customer")
   txtCust = rs("Name")
   txtOrderNumber = rs("Order Number")
   txtCustRef = rs("Customer Reference")
   cboCounty = rs("County")
   txtLocation = rs("Location")
   txtPhoneNo = rs("Phone No")
   txtSkipNo = rs("Skip No")
   txtDateDropped = rs("Date Dropped")
   cboTruck = rs("Truck")
   txtDateCollected = rs("Date Collected")
   txtDateWeighed = rs("Date Weighed")
   cboWasteCodes = rs("Waste Code")
   txtWeight = rs("Weight")
   txtWeighTicket = rs("Weigh Ticket")
   cboStatus = rs("Status")
   cboPaidBy = rs("Paid By")
   txtInvoice = rs("Invoice No")
   cboVatCode = rs("VAT Code")
   txtPrice = rs("Price")
   txtVatType = rs("VAT Type")
   txtInvoiceGoods = rs("Goods")
   txtInvoiceVat = rs("Vat")
   txtInvoiceTotal = rs("Total")
   rs.Close
 
End Function
 
I think this should get you sorted !
Code: 
Function SaveData() As Boolean
    Dim rs As DAO.Recordset
[COLOR=Green]    'Where have you delare the txtNo, txtDel, cboCust? 
    'Are these Control Names or Variables?[/COLOR]
    Set rs = CurrentDb.OpenRecordset("Skips Delivered")
    
[B]    [COLOR=Red]rs.FindFirst "rs('No') = " & txtNo
    If rs.NoMatch Then
        rs.AddNew
    Else
        rs.Edit
    End If[/COLOR][/B]
    
    rs("Deliver Docket").Value = txtDel
    rs("Customer").Value = cboCust
    rs("Name").Value = txtCust
    rs("Order Number").Value = txtOrderNumber
    rs("Customer Reference").Value = txtCustRef
    rs("County").Value = cboCounty
    rs("Location").Value = txtLocation
    rs("Phone No").Value = txtPhoneNo
    rs("Skip No").Value = txtSkipNo
    rs("Date Dropped").Value = txtDateDropped
    rs("Truck").Value = cboTruck
    rs("Date Collected").Value = txtDateCollected
    rs("Date Weighed").Value = txtDateWeighed
    rs("Waste Code").Value = cboWasteCodes
    rs("Weight").Value = txtWeight
    rs("Weigh Ticket").Value = txtWeighTicket
    rs("Status").Value = cboStatus
    rs("Paid By").Value = cboPaidBy
    rs("Invoice No").Value = txtInvoice
    rs("VAT Code").Value = cboVatCode
    rs("Price").Value = txtPrice
    rs("VAT Type").Value = txtVatType
    rs("Goods").Value = txtInvoiceGoods
    rs("Vat").Value = txtInvoiceVat
    rs("Total").Value = txtInvoiceTota
    
    [COLOR=Red][B]rs.Update[/B][/COLOR]
    SaveData = True
End Function
I have highlighted my changes.
 
Last edited:
Control Names, thanks paul i'll try that
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom