Problems with sorting records on a form (1 Viewer)

[hammerva]

Hello again.

I am trying to sort records from a form in ascending order by Status Number (which is a non key field on the table). But it keeps sorting by the autonumber Meeting Number key field. I set the "Order By" on form to Status Number.

What is going on here.

 

[Rich]

Either right click on the field order asc, click save a few times, or use a query and base the form on a query

 

[Pat Hartman]

It is better practice to use a query as the recordsource for forms and reports. You can add an order by clause to the query.

 

[hammerva]

Okay I changed the recordsource to a query. But when I tried to update a field on a particular record. I get the following error:

The setting you entered isn't valid for this property.

What I usually do is add a command saying "
If Me.Dirty then Me.Dirty = False" so that the process activates the BeforeUpdate events. But it looks like the error is occurring after the BeforeUpdate event is passed. Do I need to set something up in the query builder to allow updates?

I included the code.

 

[Pat Hartman]

If Me.Dirty then Me.Dirty = False

is obtuse. If you've never seen that particular line of code before, it does not indicate in any way that a record is being saved. If you want to save the current record, use the command that saves the current record -

DoCmd.RunCommand acCmdSaveRecord

There isn't any question what it does.

Open the query itself and try to do the update you are attempting from the form. If it works, the problem is with the form. Are you sure you didn't change anything besides the recordsource. Queries are automatically updatable unles you have included something that would make them not updatable. If your original recordsource was a table and all you did was create a query that produced the same recordset, you should not be having a problem.

 

[hammerva]

I tried opening the query and updating record and it allowed me to do it. So obviously it is something in the form layout. I replaced the me.Dirty phrase with

"DoCmd.RunCommand acCmdSaveRecord". I put a breakpoint in the Update click. Right after I execute the RunCommand I get the following error:

The command or action 'Save Record' isn't available now.

* You may be in a read-only database or an unconverted database
* The type of object the action applies to isn't currently selected


Not sure how the recordsource is read-only right now or not currently selected? Do I need to go to the current record before the RunCommand process? I have my allow edits property set to 'Yes".

By the way, I am doing this in Access 97 if it helps in the explanation.

Thanks for all the help Pat even though it doesn't seem like I am understanding it well.

 

[Pat Hartman]

That error usually means that you are trying to save the record from within the scope of the BeforeUpdate event. Access is telling you that it is already in the process of saving the record so you can't ask to save it at this time.

As a matter of course, you don't need to force Access to save records. Access automatically saves records whenever the current record pointer is moved. The only time you would normally need to force save a record is if you had a form button that opened a report or another form and you wanted to make sure that any changes to the current record were saved prior to opening the form or report.