Next Record Issue

[DCV0204]

Ok, I can not figure out what is wrong with my code. When I try to go to the next record, I either get an error message or nothing happens. Would you mind taking a look at my code within my dbase to see why I can not get to the next record.
Thanks.

[June7]

Which form is involved? What steps do I take to replicate the issue?

[Missinglinq]

I can only find two 'next record' buttons; one has code to close a Form behind it:

DoCmd.Close acForm, "Email_PrescriberReview", acSaveNo

and the other has no code or macro at all!

[DCV0204]

Sorry, i had accidently removed the code I had for the next button.
It is like the form needs to be refreshed or something before the next and previous buttons will work.
I am attaching the dbase with the code. Click "Initial Review" button to open the "PrescriberReview" Form.
The previous button is still based off the wizard macro.
Thanks.

[June7]

The Previous button is embedded macro with an error handler to deal with situation when the first record already has focus.

The Next button is VBA code without any error handler.

The form opens with 5 records but for some reason does not allow navigation, not even with the navigation bar.

Do not understand relationship between Status and Non-Registered_Prescribers tables. Can each prescriber have more than one record in Status?

There is code in the form current event that sets value of Send_ltr field. This puts the record in edit mode. Can't move from record until it is saved. I tried DoCmd.RunCommand acCmdSaveRecord but that triggers error 2115 - The macro or function set to the BeforeUpdate or Validation Rule ...

So I commented out all the lines dealing with setting value of Send_ltr and the form allows navigation.

[DCV0204]

Yes, 5 records is correct as these are records for testing an not the actual data.

Yes, the providers status can change overtime so based on the date in the Status table I can determine the prescribers current status. I wanted to keep the previous status in tact. Therefore, Non-Registered_Prescribers to Status is a 1 to many relationship.

Did you send the file back with your comments?
Thanks,
Lisa

[June7]

No, just do the same thing I did. Comment out the relevant lines.

Also, should not include the Status table in the form RecordSource. Once you get more than one Status record for each prescriber, the form will show multiple records for each prescriber.

Then you should fix the VBA for the Next button.

[DCV0204]

I am not a vba expert and do no understand what you mean by comment out the relevant lines.
Can you provide me an example?

[June7]

You did not write the code in this db?

Find every line like: Me.Send_Ltr =

But not the lines that begin with If.

Put an apostrophe in front. That makes it a comment and will not execute.

[DCV0204]

Yes, sorry I misunderstood what you were stating in your previous post.

Ok, I must be doing something wrong because I can not get it to go to the next record.

If I remove the status table from my query my code will not work.

will you send me the copy of the database where you got it to work so I can compare and see what I am doing wrong?
Thanks.

[June7]

I prefer you follow the same steps and accomplish yourself. Here is what I did with your file. Modified the PrescriberReview form as follows:

1. comment the Me.Send_Ltr = lines so they won't execute

2. set PrescriberReview form RecordSource to Non-Registered_Prescribers table

3. comment 2 more lines
'Me.Status = "Blocked"
'Me.StatusDate = Date

4. open form from the MainMenu form button and navigate records

[DCV0204]

Thanks June7 I can now navigate to the next record, but since my form is linked to the table instead of my query how can I filter the form after each form (record) is updated where the ReviewedBy is null?
Thanks.

[June7]

Options:

1. use intrinsic filter tools - right click on the ReviewedBy textbox to open the shortcut menu

2. use code to set the form Filter and FilterOn properties

3. if you want the form to open only to records where ReviewedBy is null, use a query with static filter criteria - but don't include the Status table
or use the WHERE CONDITION argument of DoCmd.OpenForm