• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

update a field in Access through VB

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
sjmmajor
Asked:
sjmmajor
  • 5
  • 3
1 Solution
 
gencrossCommented:
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
 
sjmmajorAuthor Commented:
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
 
gencrossCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sjmmajorAuthor Commented:
Still won't update the database.  Got any other clues?
0
 
gencrossCommented:
Do you get an error message?
0
 
sjmmajorAuthor Commented:
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
 
CarterMitchellCommented:
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
 
sjmmajorAuthor Commented:
This didn't work but thanks.  How do I get the points to gencross?  That code worked for me.
0
 
sjmmajorAuthor Commented:
Thanks for your help!  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now