Solved

update a field in Access through VB

Posted on 2002-07-19
9
146 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sjmmajor
Comment Utility
Still won't update the database.  Got any other clues?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:gencross
Comment Utility
Do you get an error message?
0
 

Author Comment

by:sjmmajor
Comment Utility
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
Comment Utility
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
Comment Utility
This didn't work but thanks.  How do I get the points to gencross?  That code worked for me.
0
 

Author Comment

by:sjmmajor
Comment Utility
Thanks for your help!  
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now