UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> New To Recordset, Access 2013    
 
   
slowpoke
post Jun 13 2019, 03:57 PM
Post#1



Posts: 15
Joined: 15-April 19



Hi there.... I'm new to using recordset...

I have a button on my main form (recordset that points to a query, to set certain fields status).... now I want to be able to pass parameters (ie. unique_id; like a where clause to filter data) to the query while using this recordset button.

I did put a [forms]![fm_work_order].[unique_key] in the Criteria of the Query, while on the form, I was hoping the RecordSet button would automatically transferring the said field over to the query.... instead I got the dreaded error: Runtime Error 3061, Too Few Parameters. Expected 1.

Is it possible to pass parameters into a query using recordset? If so how? Any examples would be greatly appreciated.


Private Sub but_recordset_Click()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qry_rs_work_order_units", dbOpenDynaset, dbSeeChanges)

Do Until rs.EOF

rs.Edit
rs!change_status = "Denied"
rs!value_co_approved = 0
rs!value_co_submitted = 0
rs!fuel_adjuster_eligible_total = 0
rs!change_approved_date = Now()
rs.Update
rs.MoveNext

Loop

End Sub


Go to the top of the page
 
theDBguy
post Jun 13 2019, 03:59 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,727
Joined: 19-June 07
From: SunnySandyEggo


Hi. Take a look at Leigh's Generic Recordset as one possible approach.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jun 13 2019, 04:01 PM
Post#3



Posts: 3,171
Joined: 27-February 09



I hope this is just a dummy example, because there's no reason to use a recordset for this. Using SQL would be a lot faster. Only use recordsets if you can't get the job done using SQL by itself.
Go to the top of the page
 
slowpoke
post Jun 13 2019, 04:13 PM
Post#4



Posts: 15
Joined: 15-April 19



Hmmm... any chance you can elaborate on using SQL... do you have any code examples I can learn from?
Go to the top of the page
 
MadPiet
post Jun 13 2019, 04:27 PM
Post#5



Posts: 3,171
Joined: 27-February 09



What kind of question are you trying to solve/answer? Recordsets have their place, but as I said, but I use them when I have to process a single record at a time. For example, I once had a list of IP addresses in a table that I needed to ping to see if they were active. Ping basically accepts an IP address and returns a value - and it's not possible to send the PING command in a query. But I can do it in VBA one IP address at a time. So I opened an updateable recordset, passed the IP address to the PING function, and saved the result back to my table. I couldn't do this in a query, because I have to process one record at a time - and a query processes the entire set at a time.

CODE
rs.Edit
rs!change_status = "Denied"
rs!value_co_approved = 0
rs!value_co_submitted = 0
rs!fuel_adjuster_eligible_total = 0
rs!change_approved_date = Now()
rs.Update


is the same as this:

CODE
UPDATE MyTable
SET change_status = 'Denied',
      value_co_approved = 0,
      value_co_submitted = 0,
      fueld_adjuster_eligible_total = 0,
      change_approved_date = now()
WHERE <add any filters you applied when opening your recordset here>;


In a nutshell, use SQL as a first option. If you cannot accomplish your goal using just SQL (maybe because you have to process one record at a time for some reason), then VBA is okay. It's just a whole lot faster and easier to use SQL.
This post has been edited by MadPiet: Jun 13 2019, 04:31 PM
Go to the top of the page
 
cheekybuddha
post Jun 13 2019, 04:31 PM
Post#6


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


I guess Piet means something like:
CODE
Private Sub but_recordset_Click()

  Dim strSQL As String

  strSQL = "UPDATE qry_rs_work_order_units SET " & _
              "change_status = 'Denied', " & _
              "value_co_approved = 0, " & _
              "value_co_submitted = 0, " & _
              "fuel_adjuster_eligible_total = 0, " & _
              "change_approved_date = " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & ";"
  CurrentDb.Execute strSQL, dbFailOnError

End Sub


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jun 13 2019, 04:32 PM
Post#7



Posts: 3,171
Joined: 27-February 09



Something like that. Yes. There's just nothing here that requires the use of VBA. You could save that as a normal update query and execute that way.
This post has been edited by MadPiet: Jun 13 2019, 04:38 PM
Go to the top of the page
 
cheekybuddha
post Jun 13 2019, 04:32 PM
Post#8


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Oops! Sorry, Piet, you replied as I was typing. blush.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
slowpoke
post Jun 13 2019, 05:42 PM
Post#9



Posts: 15
Joined: 15-April 19



Thank you.... I think I'm beginning to understand. I appreciate your insight and help.
Go to the top of the page
 
slowpoke
post Jun 13 2019, 06:04 PM
Post#10



Posts: 15
Joined: 15-April 19



Hello David,

Thanks for your code snippet.... that really helped. I have a few questions:

I don't understand why the date is formatted this way... vs [rs!change_approved_date = Now()], is this an SQL thing?

"change_approved_date = " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & ";"


Is it possible to reference a form field with a where clause? I have a field on the form called me.unique_key

Is it as simple as adding to your code:

Where unique_key = [forms]![fm_work_order].[unique_key]
Go to the top of the page
 
cheekybuddha
post Jun 13 2019, 07:22 PM
Post#11


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


>> is this an SQL thing? <<

Yes, SQL expects dates to be passed in an unambiguous format - either US date format mm/dd/yyyy hh:nn:ss or ISO date format yyyy-mm-dd hh:nn:ss.

Also they must be delimited with octothorpes (hash marks #).

This is irrespective of your regional date format.

I used the Format() function to create the date string in the ISO date format and include the octothorpes at the same time.

You could also have passed the function Now() and let the evaluation of the function happen in the SQL engine when the query is executed, but since you are building the SQL string in VBA it's as easy to evaluate the function there.

Of course you can use a WHERE clause with the SQL query, as Piet pointed out. It would look something like:
CODE
  strSQL = "UPDATE qry_rs_work_order_units SET " & _
              "change_status = 'Denied', " & _
              "value_co_approved = 0, " & _
              "value_co_submitted = 0, " & _
              "fuel_adjuster_eligible_total = 0, " & _
              "change_approved_date = " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & _
           " WHERE unique_key = " & Me.unique_key & ";"
  CurrentDb.Execute strSQL, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
slowpoke
post Jun 13 2019, 08:42 PM
Post#12



Posts: 15
Joined: 15-April 19



Thank you David. That where clause worked perfectly fine. Since the unique_key value was alpha, I placed quotes and it worked awesome.

" WHERE unique_key = '" & Me.unique_key & "';"

I'm learning and building my toolbox, I wish to thank you for taking the time to helping me out.

Kudos to you!

Go to the top of the page
 
slowpoke
post Jun 14 2019, 07:52 PM
Post#13



Posts: 15
Joined: 15-April 19



David,

Not sure what I am doing wrong:

When I run the code below, it gives me an Runtime ERROR: 3144, Syntax Error in Update Statement

strSQL = "UPDATE qry_change_co1_to_co1_r1_c1 SET " & _
"scope = 'CO1_R3', " & _
"change_order_no = 'CO1_R3', " & _
"WHERE unique_key = '" & Me.unique_key & "' and change_order_no = 'CO1_R2';"

CurrentDb.Execute strSQL, dbFailOnError


However, if I add: "change_approved_date = " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & _

It runs perfectly fine. I've made sure I have a VBA line break character, a space and underscore. I really don't know what I am missing here.


strSQL = "UPDATE qry_change_co1_to_co1_r1_c1 SET " & _
"scope = 'CO1_R3', " & _
"change_order_no = 'CO1_R3', " & _
"change_approved_date = " & Format(Now, "\#yyyy\-mm\-dd hh:nn:ss\#") & _
"WHERE unique_key = '" & Me.unique_key & "' and change_order_no = 'CO1_R2';"

CurrentDb.Execute strSQL, dbFailOnError


Any ideas?
Go to the top of the page
 
isladogs
post Jun 15 2019, 01:26 AM
Post#14


UtterAccess VIP
Posts: 1,459
Joined: 4-June 18
From: Somerset, UK


in the first code which errors, remove the comma at the end of the third line after the last field to be updated.

--------------------
Go to the top of the page
 
cheekybuddha
post Jun 15 2019, 03:47 AM
Post#15


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Colin has given you the solution - when you removed the third field to be updated (change_approved_date) you neglected to remove also the trailing comma from the previous field in the list (change_order_no).

Note also, you will likely need a space before the 'WHERE' - if you look at the code I provided there is a space at the start of the 'WHERE' line.

A good trick for diagnosing such errors (which we all make!) is to output the string to the Immediate Window (Ctrl+G) so you can inspect it if it doesn't work.
CODE
strSQL = "UPDATE qry_change_co1_to_co1_r1_c1 SET " & _
"scope = 'CO1_R3', " & _
"change_order_no = 'CO1_R3', " & _
"WHERE unique_key = '" & Me.unique_key & "' and change_order_no = 'CO1_R2';"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

With your code here, the output in the Immediate Window would have been:
CODE
UPDATE qry_change_co1_to_co1_r1_c1 SET scope = 'CO1_R3', change_order_no = 'CO1_R3', WHERE unique_key = 'XYZ' and change_order_no = 'CO1_R2';

(I made up the unique key!)

But you can spot the comma before 'WHERE' which is incorrect SQL syntax.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
slowpoke
post Jun 16 2019, 10:15 AM
Post#16



Posts: 15
Joined: 15-April 19



Thank you.... I was going nuts as I can't figure out what I am missing. Appreciate the hand holding while I get rolling.
MW
Go to the top of the page
 
cheekybuddha
post Jun 16 2019, 11:46 AM
Post#17


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


yw.gif

I'm glad we all could help!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 10:41 AM