Solved

update a field in Access through VB

Posted on 2002-07-19
9
152 Views
Last Modified: 2010-05-02
I am just testing a 2 field form to see how to update a field in Access from VB.  I know this is basic but I am new at this.  It is going to the right row and getting the right jobnumber from the access database.  I did that so I could see if it was connecting to the database.  But when I change the Text box to reflect the new number I want in its place in the database it doesn't complete the action.  The end result is I want to capture the fields that I have on my form and get them into an access database.

Here is my code.  

Private cn As ADODB.Connection
Private mrscust As ADODB.Recordset


Private Sub Form_Load()
Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testcount\countsplt42.mdb;Persist Security Info=False"
cn.Open

Set mrscust = New ADODB.Recordset
mrscust.LockType = adLockBatchOptimistic
mrscust.CursorLocation = adUseClient
mrscust.CursorType = adOpenKeyset

End Sub
Private Sub Command1_Click()
Dim strSQL As String

    strSQL = "Select * from 22shop_5min where [22shop_5min].id = 2"

mrscust.Source = strSQL
Set mrscust.ActiveConnection = cn
mrscust.Open

Label1.Caption = mrscust.Fields("jobnumber")
mrscust.Fields("jobnumber") = Text1.Text
mrscust.update

End Sub

Thanks for your help in advance!
0
Comment
Question by:sjmmajor
  • 5
  • 3
9 Comments
 
LVL 4

Expert Comment

by:gencross
ID: 7165271
It looks like you are updating the database immediately in the form load event.

Normally I will set a DataChanged flag in the Change event then update the data when the user unloads the form.  I also always use SQL statements to update the database (it is faster), but this way will work as well.

You can also use the LostFocus event in the textbox to update the database, but it is not always guaranteed that it will fire depending on the user action.

Private Sub Text1_Change()
    bDataChanged = True
End Sub

Private Sub Form1_Unload()
    If bDataChanged then
        mrscust.Fields("jobnumber") = Text1.Text
        mrscust.update
    End If
End Sub
0
 

Author Comment

by:sjmmajor
ID: 7165336
I tried this and it still didn't update the record in the database.  I can try the updating using SQL statements if that is the way to go.  I am looking for guidance.
0
 
LVL 4

Accepted Solution

by:
gencross earned 60 total points
ID: 7165368
To update using a SQL statement use the same method as above only once you fill your fields you can close the recordset.  

NOTE: You will also want to open the recordset Forwardonly, readonly.  This will be much faster.

Here is what your code would look like to open the recordset and populate...

Private cn As ADODB.Connection
Private mrscust As ADODB.Recordset


Private Sub Form_Load()
Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testcount\countsplt42.mdb;Persist Security Info=False"
cn.Open

Set mrscust = New ADODB.Recordset
mrscust.LockType = adLockBatchOptimistic
mrscust.CursorLocation = adUseClient
mrscust.CursorType = adOpenForwardOnly

End Sub
Private Sub Command1_Click()
Dim strSQL As String

   strSQL = "Select * from 22shop_5min where [22shop_5min].id = 2"

mrscust.Source = strSQL
Set mrscust.ActiveConnection = cn
mrscust.Open

Label1.Caption = mrscust.Fields("jobnumber")

End Sub

For the update...

Private Sub Text1_Change()
   bDataChanged = True
End Sub

Private Sub Form1_Unload()

   Dim sSQL as String

   If bDataChanged then
       sSQL = "UPDATE 22shop_5min SET jobnumber = " & Text1.Text & " where [22shop_5min].id = 2"
       cn.Execute sSQL
   End If

End Sub
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:sjmmajor
ID: 7165397
Still won't update the database.  Got any other clues?
0
 
LVL 4

Expert Comment

by:gencross
ID: 7165403
Do you get an error message?
0
 

Author Comment

by:sjmmajor
ID: 7165504
Didn't get anything except for a message when I clicked on the command button twice.  It gave me an open error.  I just tried and moved the code that was in the sub form1_unload to the sub text1_change() and it just worked there.  I see the update.  Is there a reason?  The way I have this written is this considered an Disconnected recordset or is that a whole other ballgame?
0
 

Expert Comment

by:CarterMitchell
ID: 7165761
Your problem is simple: you are setting your lock type to Batch Optimistic and using an Update method on the recordset. Two ways to go here:
1. set the lock type to adLockOptimistic OR
2. keep the lock type and use the UpdateBatch method on your ADO recordset
0
 

Author Comment

by:sjmmajor
ID: 7166570
This didn't work but thanks.  How do I get the points to gencross?  That code worked for me.
0
 

Author Comment

by:sjmmajor
ID: 7166573
Thanks for your help!  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question