VBA to Write Status on a Query (1 Viewer)

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Hi, I'm trying to code a function that writes directly the status on a query field when called.

The code I wrote is just for test as for now and looks like this:

Public Function Status(qry As String) As String

Dim db As DAO.Database
Dim rs As ADODB.Recordset
Dim sResult As String

On Error GoTo ProcErr

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM Relevos WHERE PK_Proceso =" & qry & " ;")
sResult = "Bing"
Status2 = sResult

CleanUp:
Set rs = Nothing
Set oConn = Nothing
Exit Function
' End Select
ProcErr:
' insert error handler
Resume CleanUp

End Function

I just want a column that shows "bing" for everyrow, because I am trying to check my code, but the thing is if I set the rs it comes empty. If I delete the Setting for the rs, then It shows "bing".

The idea was that the function should check the content from the different fields in the query recordset and return a value based on that, but if I can't even get it to show thins that should always be displayed I am doing something wrong.

Any idea? I can't understand why it came empty.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Probably the reason why "bing" is not showing up when you have Set rs included is because there's an error at that point, and the code skips all the way to the error handler to clean up. What are you passing as the qry argument?
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Is the number of the PK, it should at least work with one result, but alas it does not.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Is the number of the PK, it should at least work with one result, but alas it does not.
Have you tried stepping through your code to see what is the actual problem?
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
I tried changing a lot of things inside the line of the rs, but never achieve something that allowed the code to show the next line.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
I tried changing a lot of things inside the line of the rs, but never achieve something that allowed the code to show the next line.
Try taking out your error handler for now and let us know if you get an error message.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Error 13, type mismatch
Try changing this line:
Code:
Set rs = db.OpenRecordset("SELECT * FROM Relevos WHERE PK_Proceso ='" & qry & "';")
Please note, I added single quotes to delimit the value you're passing in qry. This is assuming PK_Proceso is a Text field.
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Now it says Error 3464, Data Type Mismatch in criteria expression.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Now it says Error 3464, Data Type Mismatch in criteria expression.
Hi. Do you know which line was highlighted when you debug the code? I made an assumption in my previous post. Can you please verify if it was correct or not? What is the data type of the field in the table?
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Oh, I tought that maybe was that on the table the field is a long, so I change it on the module, but now it's giving me error 13.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Oh, I tought that maybe was that on the table the field is a long, so I change it on the module, but now it's giving me error 13.
Okay, that means you need to change this part:
Rich (BB code):
Public Function Status(qry As String) As String
into this:
Rich (BB code):
Public Function Status(qry As Long) As String
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
I just did it and its giving me error 13. I tried writing numbers directly but it's also giving me error 13
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
I just did it and its giving me error 13. I tried writing numbers directly but it's also giving me error 13
Well, did you put back the code I changed the first time? You'll need to remove the single quotes now, because those are just for Text fields. Just put back your code the way it was and just change the part I showed you in the last post.
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Still, it's giving me error 13, I tried it on an empty database query but it still is giving error 13.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358
Still, it's giving me error 13, I tried it on an empty database query but it still is giving error 13.
Okay, I think we need to try it out for ourselves. Are you able to post a demo copy of your database with test data?
 

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Yeah, I'm using this to check the code before using in the real db. thanks In advance
 

Attachments

  • Database6.accdb
    444 KB · Views: 125

theDBguy

I’m here to help
Staff member
Local time
Today, 10:20
Joined
Oct 29, 2018
Messages
21,358

Attachments

  • Database6.zip
    20.3 KB · Views: 134

biofaku

Member
Local time
Today, 14:20
Joined
May 15, 2020
Messages
66
Oh, I see the problem, thanks DBguy, I think I can go alone from here!
 

Users who are viewing this thread

Top Bottom