Edit information in Table Rows

misstry

New Member
Joined
Jun 19, 2017
Messages
6
Hello,

I am having a heck of a time getting this to work. I am attempting to click the button, open the database and change existing information on the "Scrubbed RO's" table. I have tried many different ways and now am receiving the "Type Mismatch" error. Before, it would change data in the rows but it was not for the correct RO#.

Private Sub Command46_Click()
On Error GoTo Command46_Click_Err
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Scrubbed ROs")

Count_of_RO = DCount("[RO #]", "Scrubbed ROs", "[RO #]='" & Me.RO_NUMBER & "'")

If Count_of_RO < 1 Then 'Check to see if record already exists
MsgBox "RO not Scrubbed. Use SCRUB RO Button"

Exit Sub
Else

With rs 'Add all record information from form
.Edit
.Fields("Scrub Code") = Me.ScrubCode
.Fields("Comments") = Me.ScrubComments
.Update
.Close
End With

If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
End If

MsgBox rs


DoCmd.Close
Command46_Click_Exit:
Exit Sub
Command46_Click_Err:
MsgBox Error$
Resume Command46_Click_Exit
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,

This looks like a problem:
Code:
MsgBox rs

rs is a recordset object. Msgbox function expects a string value. Not sure what you are trying to message here but this line probably should be removed or corrected.

Note that as a rule you want to comment out your error handling when testing so you can know exactly what line the error is occurring on (error messages are good for telling you what but not where ... and for that matter, not always great at even telling you what).
 
Last edited:
Upvote 0
Thank you so much! That solved one issue.

Now it will run the code and let me update; however, it updates the wrong row. For example, I need PO# 3004732 to update two fields in that row with the information I just entered on the form. So I click the button and it updates. Only it is not updating the correct row, it updated PO# 3004721. How do I get this to update the specific PO that I request? This is the new code now. I no longer have any errors.


Private Sub Command46_Click()
On Error GoTo Command46_Click_Err
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Scrubbed ROs")

Count_of_RO = DCount("[RO #]", "Scrubbed ROs", "[RO #]='" & Me.RO_NUMBER & "'")

If Count_of_RO < 1 Then 'Check to see if record already exists
MsgBox "RO not Scrubbed. Use SCRUB RO Button"

Exit Sub
Else

With rs 'Add all record information from form
.Edit
.Fields("Scrub Code") = Me.ScrubCode
.Fields("Comments") = Me.ScrubComments
.Update
.Close
End With

If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
End If

MsgBox "RO Updated"

DoCmd.Close
Command46_Click_Exit:
Exit Sub
Command46_Click_Err:
MsgBox Error$
Resume Command46_Click_Exit
End Sub


Hi,

This looks like a problem:
Code:
MsgBox rs

rs is a recordset object. Msgbox function expects a string value. Not sure what you are trying to message here but this line probably should be removed or corrected.

Note that as a rule you want to comment out your error handling when testing so you can know exactly what line the error is occurring on (error messages are good for telling you what but not where ... and for that matter, not always great at even telling you what).
 
Upvote 0
You need to tell the code what record to update. I don't see that anywhere in your code at all. Which is very scary - I guess you have very little knowledge about how DAO recordsets work. Why are you using a DAO recordset here at all? What are you trying to do?
 
Upvote 0
Hi Xenou, I am sorry if you are getting frustrated with me. But you are correct. I have extremely very little knowledge and was thrown into this. I was told to try and duplicate another database but it is not entirely the same so I have run into some road blocks. I am only using the DAO recordset because this is what the other database had. I don't know how to tell the code what record to update or how to communicate in this forum about it properly. That is why I am coming here for help. Basically, what I need to do is take information from a form regarding a specific record in this case PO#, I will enter the information on that form and then click the button (Command 46). That button will override anything in the two fields that I need to update on the Scrubbed ROs table. These fields are named Scrub Code and Comments. I am willing to pay for the help as well so I can get this project completed and to have a better understanding of how this works.

You need to tell the code what record to update. I don't see that anywhere in your code at all. Which is very scary - I guess you have very little knowledge about how DAO recordsets work. Why are you using a DAO recordset here at all? What are you trying to do?
 
Upvote 0
The most usual situation for Access is you just create a normal form bound to the table. Then you can update records simply by typing something into the field. Using recordsets is not necessary at all - but clearly you need to know how they work if you do use them.

If you are going to be opening a recordset and editing a record this way, I guess one way (among many others) would be to open the recordset right from the start to be opened with the correct record.

I was assuming that "Scrubbed ROs" is a table but maybe it's a query? Anyway whatever that "Scrubbed ROs" thing is, the first record in it is what you are updating. So if its a query it must be a query that opens to the same record as the one that's in your form.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top