Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update a field in Access through VB

Posted on 2002-07-19
9
Medium Priority
?
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 240 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
Suggested Courses

715 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