mrsgwen

I am using Access 2003 as a front end and VB.NET code. I am trying to update a record. I am having problems coming up with the code to do this. I'm sure it's really simple but I just can't figure it out. If anyone can help I would really appreciate it. The code that I have is below. Can you please tell me what is wrong with this code.


Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

If MsgBox("This record will be updated. Do you wish to continue ", vbYesNo + vbQuestion, "PubTrans") = vbYes Then

Dim rstUsers As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim strSQL As String

Set rstUsers = New ADODB.Recordset
strSQL = "select dbo_tbl_Users.* from dbo_tbl_Users " & "where dbo_tbl_Users!EmpRec=" & "'" & Me.cboEmpRec & "'"
rstUsers.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText


If Not IsNull(Me.txtEmpID) Then
rstUsers!EmpID = Me.txtEmpID
Else
MsgBox "Please enter an employee number", vbOKOnly
GoTo Exit_cmdUpdate_Click
End If


If Not IsNull(Me.txtEmpName) Then
rstUsers!EmpName = Me.txtEmpName
Else
MsgBox "Please enter an employee name", vbOKOnly
GoTo Exit_cmdUpdate_Click
End If

If Not IsNull(Me.cboStatus.value) Then
rstUsers!Status = Me.cboStatus.value
Else
MsgBox "Please enter a status for the employee", vbOKOnly
GoTo Exit_cmdUpdate_Click
End If

If Not IsNull(Me.txtUName) Then
rstUsers!UserName = Me.txtUName
Else
MsgBox "Please enter a user name for the employee", vbOKOnly
GoTo Exit_cmdUpdate_Click
End If

If Not IsNull(Me.txtPass) Then
rstUsers!Password = Me.txtPass
Else
MsgBox "Please enter a password for the employee", vbOKOnly
GoTo Exit_cmdUpdate_Click
End If

rstUsers.Update
rstUsers.Close

MsgBox "Employee Record has been updated successfully.", vbOKOnly
DoCmd.Close
cboEmpRec = Null
txtEmpID = Null
txtEmpName = Null
txtPass = Null
cboStatus = Null
txtUName = Null
Set rstUsers = Nothing


Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click
End If

End Sub



Re: Visual Basic General Updating a Record

AlexB-007

It looks like you've got all of the right elements in your code. Are you getting an error

You say you're using Access 2003 as a Front End You can probably accomplish the same thing with a lot less code, if you use a Access bound form. Is there any chance you have a bound form that could be interfering with your unbound update Just throwing out ideas....






Re: Visual Basic General Updating a Record

mrsgwen

Yes I was getting an error. I have changed my code and it updates it but the change is not writing to the SQL table. Yes, Access is the front end it usually does a lot for you but for what I'm doing, I'm having to add a bit of code.

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim rstUsers As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim Msg, Response

Msg = "Do you want to update another record "


rstUsers.Open "dbo_tbl_Users", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

rstUsers.Update

If IsNull(rstUsers!EmpID.value) <> True Then
Me.txtEmpID = rstUsers!EmpID.value
End If

Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then

DoCmd.OpenForm "DPPT Staff Update"
Else
DoCmd.Close
End If

rstUsers.Close

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click


End Sub





Re: Visual Basic General Updating a Record

Bruno Yu - MSFT

mrsgwen,

According to your question on saving the update to Access database, I would like to suggest you to try to use the ADO.NET classes and methods in order to make the solution more simple.

In ADO.NET, you can create and store the in-memory table in DataSet and DataTable, Then use DataAdapter to create a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet.

The following two KB articles in MSDN provide you the example on the issue with further information:

1. How to use Microsoft Visual Basic .NET to connect to a Microsoft Access database and to retrieve data

This step-by-step article describes how to use Microsoft ADO.NET to open a Microsoft Access database by using the OLE DB data provider. You use the OleDbDataAdapter class to obtain rows from the Access database and to insert the rows into a DataSet object. This article describes how to add rows, how to delete rows, and how to update rows in the Access database by using DataSet and OleDbDataAdapter.

2. HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET

This step-by-step article discusses how to retrieve the identity column value from an Access database.

Retrieving the Identity value from a Jet database is different from that of SQL Server, because a Jet database does not support multi-statement batch commands. The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field that is generated on your connection. To run the SELECT @@Identity query, it is recommended that you use another OleDbCommand object. This article describes how to use a second OleDbCommand to retrieve the Identity column value.

Hope that can help you with this kind of problem.