Updating a table from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form that allows users to view table data, but I set Allow
Edit/Add/Delete to NO because I don't want them directly modifying the data.
Instead I have a button that I want to code to make the modifications to the
table that I need. How would I go about doing this in the code for
form_buttonClick()? Can I refer to the table from the form and do some kind
of UPDATE query?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using VBA you'd iterate thru the controls that contain data for the
table & create an SQL string to act on the table. If the data is a new
row, you'd use an INSERT INTO query. If the data was an old row, you'd
use an UPDATE query. E.g.:

Private Sub CommandButton_Click()

Const SQL_UPDATE = "UPDATE table SET Amount= "
Const SQL_INSERT = "INSERT INTO table (AccountID, Amount) VALUES ("

Dim strSQL As String
Dim strWhere As String

If Not IsNull(Me!AccountID) Then
strWhere = "AccountID = " & Me!AccountID
End If

If Not IsNull(Me!Amount) Then
If m_fIsNew = True Then ' Module flag denotes new or old data
strSQL = SQL_INSERT & Me!AccountID & "," & Me!Amount & ")"
Else
strSQL = SQL_UPDATE & Me!Amount & " WHERE " & strWhere
End If
End If

If Len(strSQL)>0 Then
CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksPfIechKqOuFEgEQJBeACgjJqRSI8N6YQXEkQhzvY6of5b9WsAoP6+
Hffe4gbmYXO/MBfSVINj7qvL
=a9HW
-----END PGP SIGNATURE-----
 
Back
Top