Edit information in Table Rows

[misstry]

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

 

[xenou]

Hi,

This looks like a problem:

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).

 

[misstry]

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:

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).

 

[xenou]

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?

 

[misstry]

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?

 

[xenou]

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.