Match two numbers and then update (1 Viewer)

azhar2006

Registered User.
Local time
Today, 01:52
Joined
Feb 8, 2012
Messages
297
Hello guys
I have a database that my dear friend (@arnelgp) helped me design. It contains two tables, the first is called (A) and the second is called (B). Table (A) is the main table in the database. As for table (B), it is a table that I feed with information for specific fields and use a button on the interface of one of the forms. This button runs the programming instructions in (VBA) where this command updates specific fields in table (A) based on the Statistical_Figure field. It matches the number between the two tables and then updates the fields of this record only.
The problem is that when I apply the code, I get the following error
Thanks everyone
Code:
Private Sub cmdExecute_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmployeeName As String
Dim strRank As String
Dim strStatistical_Figure As String
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryNamesWithNewSpecialization", dbOpenSnapshot)

    If rs.RecordCount < 1 Then
        MsgBox "There is no new data to update."
        Set rs = Nothing
        Set db = Nothing
    Exit Sub
    End If

rs.MoveFirst

    While rs.EOF = False
    
        strEmployeeName = rs![EmployeeName]
        strRank = rs![Rank]
        strStatistical_Figure = rs![Statistical_Figure]
        
        strSQL = "UPDATE TableB SET TableB.Statistical_Figure = '" & strStatistical_Figure & _
        "' WHERE ((TableB.EmployeeName)='" & strEmployeeName & "')"
        
        db.Execute strSQL, dbFailOnError
        
        rs.MoveNext
    
    Wend

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Data updated successfully"

End Sub
899.PNG
 
What error? :(
Thank you dear (Gasman) This is the error message. The line in the code is this. It is a number.

Code:
strStatistical_Figure = rs![Statistical_Figure]
34.PNG
 
So that field does not exist? :(

This is always the issue when you are given code that you do not understand?
You need to take time to try and understand what you have been given, else you learn nothing. :(
 
This is an example of the database I am working on. When I click the Execute button in the form I get an error message
 

Attachments

you don't have strStatistical_Figure on your sample db.
what you have is status_no field.\
 

Attachments

@azhar2006 you've been around here for a lot of years. By now there should be some things that you can do for yourself. You shouldn't need arne to do them for you. "Item not found in the collection" is a pretty specific error message and @Gasman pointed out that there was a missing field. But arne still had to fix the query for you.
 
@azhar2006 you've been around here for a lot of years. By now there should be some things that you can do for yourself. You shouldn't need arne to do them for you. "Item not found in the collection" is a pretty specific error message and @Gasman pointed out that there was a missing field. But arne still had to fix the query for you.
If it is important to tell others what they "should and should not," then I think you should be more gentle. It is a public forum. It was the OPs pleasure to ask, and it was the pleasure of others to respond. No harm, no foul.
 
Sorry, but I firmly believe in my signature, else it would not be my signature. :)

I see it time and time again, if one gets it all handed to them on a plate, they just come back for more.

I want people to work things out for themselves, but with help/pointers from me, even if it just a simple link. Plus I hope they get some satisfaction and encouragement to pursue that method having partially solved it themselves.

My logic being, if I can find it with a simple Google, why can't you. :(

Now if you are too lazy to do so, or want it handed to you on a plate, then others will need to help you, I will be trying to help elsewhere.

Sometimes, you need to be cruel to be kind, and give the person a reality check.
 

Users who are viewing this thread

Back
Top Bottom