Select Next Record

[eckert1961]

Hello,

I'm not sure if a query or code is the best method to accomplish what I need. I have an Access 2003 database with a main form, Members. On this form I have two checkboxes labeled Ready and Active. I also have a combobox, GradeAttempting.

Currently the control source for the combobox on my Members form is a field in my Members table, GradeAttempting. For the row source I'm using the following query.

SELECT GradeTypes.*, GradeTypes.GradeTypeID, GradeTypes.GradeType FROM 
GradeTypes ORDER BY GradeTypes.GradeTypeID, GradeTypes.GradeType DESC;

The table GradeTypes has a field, SortField,

What I want to do is for members where Active=Yes and Ready=Yes I want to run possibly an Update query or code that will select the next GradeAtempting according to the SortField and then clear both the Active and Ready checkboxes.

Could someone recommend what the best approach would be? Any assistance would be greatly appreciated.

Regards,
Chris

 

[eckert1961]

Hello,

Is any additional information or clarification required? I really would appreciate some assistance.

Regards,
Chris

 

[PNGBill]

Breaking this down we have the first task to find all records where active=yes and ready=yes.

For these records you want to select the next record in GradeTypes and update field GradeAttempting with this value.

Then reset the two fields Active to no and Ready to no.

What is your Sort Field Data - if this is simply 1,2 ,3 4, then an update query can take the current value of GradeAttempting and add 1.

Not sure about when you reach the top

Step 1 is to select the records where Active = Yes and Ready = Yes.
Step 2 is make an update query from the first query and perform the add 1 to the GradeAttempting field and update that record on the respective table.
Step 3 is make another update query from the original select query and update the fields Active & Ready respectively.

The reason I suggest you first do a select query is so the result can be reviewed on the form and then a button will allow you to run the two update queries. a macro could be activated by the button to run both queries.

 

[PNGBill]

Here is FindNextAction from Access Help.
FindNext Action@import url(/Office.css);FindNext Action


You can use the FindNext action to find the next record that meets the criteria specified by the previous FindRecord action or the Find In Field dialog box, available by clicking Find on the Edit menu. You can use the FindNext action to search repeatedly for records. For example, you can move successively through all the records for a specific customer.
Setting
The FindNext action doesn't have any arguments. The FindNext action finds the next record that meets the criteria set either by the FindRecord action or in the Find In Field dialog box. The arguments for the FindRecord action are shared with the options in the Find In Field dialog box.
To set the search criteria, use the FindRecord action. Typically, you enter a FindRecord action in a macro and then use the FindNext action to find succeeding records that meet the same criteria. To search for records only when a certain condition is met, you can enter a conditional expression in the Condition column of the action row for the FindNext action.
Remarks
This action has the same effect as using the Find Next button in the Find In Field dialog box.
Note While the FindRecord action corresponds to the Find command on the Edit menu for tables, queries, and forms, it doesn't correspond to the Find command on the Edit menu in the Module window. You can't use the FindRecord action or FindNext action to search for text in modules.
Tip If you've set the Only Current Field argument of the FindRecord action to Yes, you may need to use the GoToControl action to move the focus to the control containing the data you're searching for before you use the FindNext action.
If the currently selected text is the same as the search text at the time the FindNext macro action is carried out, the search begins immediately following the selection in the same field as the selection, and in the same record. Otherwise the search begins at the start of the current record. This enables you to find multiple instances of the same search criteria that might appear in a single record.
However, note that if you use a command button to run a macro containing the FindNext action, the first instance of the search criteria will be found repeatedly. This is because clicking the command button changes the focus from the field containing the matching value. The FindNext action will then begin searching from the start of the record. The first instance of the search criteria will be found repeatedly in the same record. To avoid this problem, run the macro by using a technique that doesn't change the focus, such as a custom toolbar button or a key combination defined in an AutoKeys macro, or in the macro set the focus to the field containing the search criteria before you carry out the FindNext action.
The same behavior also occurs if you use a command button to run a macro containing the FindRecord action with the Find First argument set to No.
To run the FindNext action in Visual Basic, use the FindNext method of the DoCmd object.

 

[eckert1961]

Hello PNGBill,

Thanks very much for all of the information. Unfortunately, I'm having difficulty understanding all of it.

Step 2 is make an update query from the first query and perform the add 1 to the GradeAttempting field and update that record on the respective table.

In Step 2 how do I add 1 to the GradeAttempting field?

In your 2nd Post you provided information re. FindNext and FindRecord actions. Do I run these commands agains the 2nd query?

I've attached a screen shot of the GradeAttempting combo box to illustrate what I mean about the Next GradeAttempting. For the member in the screen shot, the highlighted GradeAttempting is Adult Shodan. The Next GradeAttempting would be Adult Nidan.

The Grades in the combo box are sorted by the SortField which is simply 1, 2, 3, 4.... In query design, reference the attached screen shot, qryGradeTypes.bmp, I don't have the SortField included, however in Datasheet View, qryGradeTypes Datasheet.bmp, SortField is included. Interestingly enough 2 additional fields, Field0 and Field1, are also displayed in this view. Not sure why that is or how to fix it.

Let me know if additional clarification is required. Thanks.

Regards,
Chris