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
> Recordset With Parameter, Access 2013    
 
   
Leah
post Mar 13 2018, 01:34 PM
Post#1



Posts: 738
Joined: 22-February 00
From: New York, New York


I know there is stuff out there on this, but I am having trouble following how to do this. Rather than creating numerous separate queries with the parameter hard coded for each practice areas, I passed the practice area to the form using Me.txtpa = PArst(0) and based the recordset on the query for one practice area at a time. However when I do that I get the message "Too few parameters expected 1." Is there a way to passing a parameter to the recordset and thus handle this in an elegant manner?

Thanks

Leah


Set PArst = CurrentDb.OpenRecordset("Practice_Area Look Up")

Do While Not PArst.EOF

Me.txtpa = PArst(0)

Set rst = CurrentDb.OpenRecordset("Matter List for Practice Area on Form")
rst.MoveFirst
counter = 1
Do Until rst.EOF
rst.Edit
rst!Rank = counter
rst.Update
rst.MoveNext
counter = counter + 1
Loop

Loop

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Mar 13 2018, 01:45 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,422
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah,

Try using Leigh's Generic Recordset.

Hope it helps...

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


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Leah,

Can you explain what you're doing more fully? Are you just trying to number the "matters" for multiple sets of "matters" that belong to various "practice areas"? I'm not clear what the form's purpose is, if this is the case. If so, you could create a query that is sorted by the practice area's unique ID, and then sorted by whichever field(s) determine the order you will assign numbers. Then, it's just a matter of looping through the records in one Recordset. When you encounter a new practice area, reset the rank counter.

It might help if you explained what a "matter" is, and how they're related to "practice areas", and how you determine the sort order for the ranking.

Hope this helps,

Dennis

EDIT: Oh wait, is that a typo? Is that supposed to be "MASTER list"? In that case, do you just have a list of "practice areas" and you're ranking them with one long list of unique rank values? Or are practice areas grouped in some way, where the rank gets reset?

-D

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Leah
post Mar 13 2018, 04:58 PM
Post#4



Posts: 738
Joined: 22-February 00
From: New York, New York


Actually the rank gets reset and I was able to get rid of double do loop and the parameter. The matter is list sorted by practice area and descending fees. I will leave the fancier stuff for when I might really need it parameter stuff for when I might really need it.

Set rst = CurrentDb.OpenRecordset("Matter List for Practice Area")
rst.MoveFirst
counter = 0
Me.txtpa = rst(3)
Do Until rst.EOF
If Me.txtpa = rst(3) Then
counter = counter + 1
Else
counter = 1
End If
Me.txtpa = rst(3)
rst.Edit
rst!Rank = counter
rst.Update
rst.MoveNext

Loop

Thank you both for your prompt responses.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
sneakerhead22
post Mar 14 2018, 08:30 AM
Post#5



Posts: 53
Joined: 18-July 17



CODE
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
Set db = CurrentDb
Set qdf =  db.QueryDefs("Matter List for Practice Area")
'qdf.Parameters("your paramater/critera").value = YOUR VALUE
qdf.Parameters(0).value = [color="#2E8B57"]"YOUR PARAMATER VALUE"[/color]
Set rst = qdf.OpenRecordset()
rst.MoveFirst
counter = 0
Me.txtpa = rst(3)
Do Until rst.EOF
If Me.txtpa = rst(3) Then
counter = counter + 1
Else
counter = 1
End If
Me.txtpa = rst(3)
rst.Edit
rst!Rank = counter
rst.Update
rst.MoveNext
Go to the top of the page
 
Leah
post Mar 14 2018, 08:59 AM
Post#6



Posts: 738
Joined: 22-February 00
From: New York, New York


Thanks, this may come in useful in the future as I slowly get more adventuresome with my code.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
doctor9
post Mar 14 2018, 01:20 PM
Post#7


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Leah,

Here's a basic subroutine for a rank that needs to reset which doesn't require a form control to work:

CODE
    Dim rstPA As DAO.Recordset, intRank As Integer
    Dim varCurrentGroup As Variant

    Set rstPAt = CurrentDb.OpenRecordset("qryPracticeAreas")
    rstPA.MoveFirst
    strCurrentGroup = ""
    
'   Loop through the records
    While Not rstPA.EOF
        
        If rstPA!NameOfFieldThatDefinesAGroup <> varCurrentGroup Then
'           New group! Reset the rank numbering, and remember the current group
            intRank = 1
            varCurrentGroup = rstPA!NameOfFieldThatDefinesAGroup
        End If
        
'       Assign the rank number to the table field
        rstPA.Edit
        rstPA!Rank = intRank
        rstPA.Update

        intRank = intRank + 1
        rstPA.MoveNext
    Wend

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Leah
post Mar 14 2018, 02:48 PM
Post#8



Posts: 738
Joined: 22-February 00
From: New York, New York


Wow, thanks for your help. I have a feeling that all of this will come in handy as I usually use workarounds for these things, especially for crosstab reports so I hope that this stuff in principle can be applied there.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th June 2018 - 09:40 PM