Search and Update the record that meets criteria (1 Viewer)

[comep]

Please note that the below code used to retrieve the specific record based on some criteria and then doing update on the selected record but the issue is that the below code is updating all records in the table and i need your kind support to solve the issue.

Private Sub Search_Click()

Me.Filter = "UserID = '" & Me.cboSelected & "'"
Me.FilterOn = True DoEvents

End Sub

Private Sub Add_Click()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("Salary")

Do While Not rs.EOF

rs.Edit
rs("UserID").Value = Me.UserID
rs("Jobs").Value = Me.Jobs
rs("Type").Value = Me.Type
rs("Level").Value = Me.Level
rs("Number").Value = Me.Number
rs.Update
rs.MoveNext

Exit Do

rs.MoveNext

Loop

rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

 

[isladogs]

Your recordset is the entire table so all records are updated.
Change it to use a recordset which includes your filter.
You can do this using a SQL statement or a query.

However you can do the whole thing more efficiently with an update query

 

[comep]

I know but the company which i work on it is not allowed Modules and Macros , that's why i am going to create a code for this and solve the issue which i face in my company
Can you help me find a program which find a record and made update on matched records only ?

 

[isladogs]

I know but the company which i work on it is not allowed Modules and Macros , that's why i am going to create a code for this and solve the issue which i face in my company
Can you help me find a program which find a record and made update on matched records only ?

That doesn't make sense
You are using a form module for the code you posted!

Edit the code you posted in one of the ways I suggested.

I'm answering on my phone so can't supply code at the moment.
However its not difficult
Search this forum or use Google to find something you can adapt

 

[jdraw]

I know but the company which i work on it is not allowed Modules and Macros

How does your Company define module??
Please explain "the code" in your post #1.

 

[arnelgp]

you dont use recordset.Edit, you use recordset.Addnew

Private Sub Add_Click()
dim rs As DAO.Recordset
Dim db As DAO.Database

set db=CurrentDB
set rs = db.OpenRecordSet("Select * From Salary Where (0=1);")

With rs
.AddNew
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Number
.Update
.Close
End With
set rs=Nothing
set db=Nothing
End Sub

 

[comep]

That doesn't make sense
You are using a form module for the code you posted!

Edit the code you posted in one of the ways I suggested.

I'm answering on my phone so can't supply code at the moment.
However its not difficult
Search this forum or use Google to find something you can adapt

Can you please advise me because i am still beginner on this area

 

[arnelgp]

or if you want to Add Or Edit the record:

Private Sub Add_Click()
dim rs As DAO.Recordset
Dim db As DAO.Database

set db=CurrentDB

'Uncomment below if your UserID is numeric
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Me!UserID)

'Uncomment below if your UserID is string
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Chr(34) & Me!UserID & Chr(34))

With rs
If (.BOF AND .EOF) Then
.AddNew
Else
.Edit
End If
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Number
.Update
.Close
End With
set rs=Nothing
set db=Nothing
End Sub

 

[isladogs]

you dont use recordset.Edit, you use recordset.Addnew

The post is specifically for updating existing records.
Therefore using .Edit is correct here

The procedure is confusingly called cmdAdd_Click but is still used to update and not append records.

 

[isladogs]

Can you please advise me because i am still beginner on this area

I won't be back at my computer until this evening uk time.
Someone else may step in but otherwise try searching for this yourself in the meantime.

EDIT use or adapt the code in post 8 which I hadn't seen when I posted this

 

[comep]

or if you want to Add Or Edit the record:

Private Sub Add_Click()
dim rs As DAO.Recordset
Dim db As DAO.Database

set db=CurrentDB

'Uncomment below if your UserID is numeric
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Me!UserID)

'Uncomment below if your UserID is string
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Chr(34) & Me!UserID & Chr(34))

With rs
If (.BOF AND .EOF) Then
.AddNew
Else
.Edit
End If
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Number
.Update
.Close
End With
set rs=Nothing
set db=Nothing
End Sub

Thanks for you but the code is missing the below :

- Go to next record automatically where the record met the criteria.

 

[arnelgp]

Private Sub Add_Click()
dim rs As DAO.Recordset
Dim db As DAO.Database

set db=CurrentDB
6
'Uncomment below if your UserID is numeric
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Me!UserID)

'Uncomment below if your UserID is string
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Chr(34) & Me!UserID & Chr(34))

With rs
If (.BOF AND .EOF) Then
.AddNew
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Num
.update
Else
.movefirst
Do while not .eof
.Edit
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Number
.Update
.movenext
Loop
End If
.Close
End With
set rs=Nothing
set db=Nothing
End Sub

 

[comep]

Private Sub Add_Click()
dim rs As DAO.Recordset
Dim db As DAO.Database

set db=CurrentDB
6
'Uncomment below if your UserID is numeric
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Me!UserID)

'Uncomment below if your UserID is string
'set rs = db.OpenRecordSet("Select * From Salary Where UserID=" & Chr(34) & Me!UserID & Chr(34))

With rs
If (.BOF AND .EOF) Then
.AddNew
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Num
.update
Else
.movefirst
Do while not .eof
.Edit
!UserID=Me!UserID
!Jobs=Me!Jobs
!Type=Me!Type
!Level=Me!Level
!Number=Me!Number
.Update
.movenext
Loop
End If
.Close
End With
set rs=Nothing
set db=Nothing
End Sub

Thanks a lot for you kind help but i want to ask if this code filtering the results based on combo box entry

 

[arnelgp]

Based on ur 1st post ur combo=UserId, then it is filtering.

 

[isladogs]

Sorry for the late reply but I see that arnelgp has been advising on the recordset solution

If you are still interested, here is the equivalent code using an update query
Replace 'Form1' with the name of your form

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = [Forms]![Form1].[Jobs]," & _
    " Salary.Type = [Forms]![Form1].[Type], Salary.Level = [Forms]![Form1].[Level]," & _
    " Salary.Number = [Forms]![Form1].[Number]" & _
    " WHERE (((Salary.UserID)=[Forms]![Form1].[cboStaffID]));"

End Sub

As you can see its much less code
It SHOULD also be faster especially if you have lots of records in your table

However, whichever method you use, you should be aware that these field names Type, Level, Number are reserved words in Access.
Using these is likely to cause you problems at some point.

Rename as e.g. JobType, JobLevel, JobNumber

 

[comep]

Sorry for the late reply but I see that arnelgp has been advising on the recordset solution

If you are still interested, here is the equivalent code using an update query
Replace 'Form1' with the name of your form

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = [Forms]![Form1].[Jobs]," & _
    " Salary.Type = [Forms]![Form1].[Type], Salary.Level = [Forms]![Form1].[Level]," & _
    " Salary.Number = [Forms]![Form1].[Number]" & _
    " WHERE (((Salary.UserID)=[Forms]![Form1].[cboStaffID]));"

End Sub

As you can see its much less code
It SHOULD also be faster especially if you have lots of records in your table

However, whichever method you use, you should be aware that these field names Type, Level, Number are reserved words in Access.
Using these is likely to cause you problems at some point.

Rename as e.g. JobType, JobLevel, JobNumber

Thanks for your help but it returned error 3061 too few parameters, expected 5 , what is it mean ?

 

[isladogs]

Thanks for your help but it returned error 3061 too few parameters, expected 5 , what is it mean ?

It means one of the following:
1. You didn't replace Form1 with your form name

2. I got the wrong names for your field names or control names from your post. PLEASE CHECK

3. I didn't check it properly in which case you'll need to add text delimiters to 5 fields (assuming the fields are all text fields)

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = '" & [Forms]![Form1].[Jobs] & "'," & _
    " Salary.Type = '" & [Forms]![Form1].[Type] & "', Salary.Level = '" & [Forms]![Form1].[Level] & "'," & _
    " Salary.Number = [COLOR="Red"]'[/COLOR]" & [Forms]![Form1].[Number] & "[COLOR="red"]'[/COLOR]" & _
    " WHERE (((Salary.UserID)='" & [Forms]![Form1].[cboStaffID] & "'));"

End Sub

However, I suspect that at least one field is a number e.g. Number!
For number type fields, the delimiter is modified removing the single quotes shown in RED above

e.g.

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = '" & [Forms]![Form1].[Jobs] & "'," & _
    " Salary.Type = '" & [Forms]![Form1].[Type] & "', Salary.Level = '" & [Forms]![Form1].[Level] & "'," & _
    " Salary.Number = " & [Forms]![Form1].[Number] & "" & _
    " WHERE (((Salary.UserID)='" & [Forms]![Form1].[cboStaffID] & "'));"

End Sub

Hopefully one of those will work but if other fields are number type they will also need adapting in the same way.

Try it and if it still doesn't work correctly, please reply with
a) the error message
b) the field names & datatypes (number / text etc)
c) the related control names and your form name

 

[comep]

It means one of the following:
1. I got the wrong names for your field names or control names from your post. PLEASE CHECK

2 I didn't check it properly in which case you'll need to add text delimiters to 5 fields (assuming the fields are all text fields)

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = '" & [Forms]![Form1].[Jobs] & "'," & _
    " Salary.Type = '" & [Forms]![Form1].[Type] & "', Salary.Level = '" & [Forms]![Form1].[Level] & "'," & _
    " Salary.Number = [COLOR="Red"]'[/COLOR]" & [Forms]![Form1].[Number] & "[COLOR="red"]'[/COLOR]" & _
    " WHERE (((Salary.UserID)='" & [Forms]![Form1].[cboStaffID] & "'));"

End Sub

However, I suspect that at least one field is a number e.g. Number!
For number type fields, the delimiter is modified removing the single quotes shown in RED above

e.g.

Private Sub Add_Click()

CurrentDB.Execute "UPDATE Salary SET Salary.Jobs = '" & [Forms]![Form1].[Jobs] & "'," & _
    " Salary.Type = '" & [Forms]![Form1].[Type] & "', Salary.Level = '" & [Forms]![Form1].[Level] & "'," & _
    " Salary.Number = " & [Forms]![Form1].[Number] & "" & _
    " WHERE (((Salary.UserID)='" & [Forms]![Form1].[cboStaffID] & "'));"

End Sub

Hopefully one of those will work but if other fields are number type they will also need adapting in the same way.

Try it and if it still doesn't work correctly, please reply with
a) the error message
b) the field names & datatypes (number / text etc)
c) the related control names and your form name

Thanks a lot its working fine and this due to the quotation

I would like to thank you for all thing to serve me on this issue but i have below two questions :-
- How can prevent users to move to next record using tab button ?
- How can users move to next record which related to same user only with using another button ?

 

[comep]

Also i face an issue as there s a dialog box appeared to save changes manually after each time of closing the form so is there a solution for it

 

[isladogs]

Also i face an issue as there s a dialog box appeared to save changes manually after each time of closing the form so is there a solution for it

Can we deal with that separately

I just tested my new code and it should work IF you have the correct form name, field and control names and correct delimiters depending on data types (text/number)

I'm about to log off but hopefully you can test it now before I do so