2003/2000: 3021/2585 Event Error-Trap Puzzle 1

[marcus101]

Hello:

I have a form that is designed to add new records to a table in my Access 2003/2000 data compatible database.

My problem is that my OnCurrent event is triggering an Error #3021, "No Current Record" and while my error trap is catching it, I'd like to be able to handle it.

The problem stems from the fact that there are NO records in the table at this point. This will not be a problem normally, but I am adding this from the point of view of there being empty tables (a "clean project" as it were).

I'd like to be able to allow the user to choose what to do when this issue occurs, ie: If they want to add new records, then just not open this form and be taken to the other form to add them, or if they don't want to create new records, just exit, possibly with some sort of info. message box.

Unfortunately, I just can't solve this one by sticking in "Cancel=True" in the OnOpen event because that is NOT where the 3021 error is firing.

Different variations of code I am trying always force me to exit the sub and allow the form to open with no record, and that is not what I want. I'd prefer to not open it at all if I can.

Here is a sample of the code I am trying to use in the OnCurrent event:

-- code block begins here --

Private Sub Form_Current()

On Error GoTo Err_Form_Current

Dim FiltCount As Integer, FiltCurrent As Integer
Dim Lab1 As String
Dim Lab2 As String

Me.Refresh

Me.RecordsetClone.MoveLast

FiltCount = Me.RecordsetClone.RecordCount
FiltCurrent = Me.CurrentRecord

Lab1 = FiltCurrent
Lab2 = FiltCount

Me.xofLbl.Caption = Lab1
Me.ofxLbl.Caption = Lab2

Exit_Form_Current:
Exit Sub

Err_Form_Current:

Select Case (Err.Number)

Case 3021

' Code to create new records here - handle no records status..

Dim AX As Variant

AX = MsgBox("No values present. Do you wish to ADD new records now?", vbYesNo, "No Records Available to Edit")

If AX = vbYes Then

' go to AddForm with parameter to close this form..

DoCmd.OpenForm "AddForm", acNormal, , , acFormPropertySettings, acWindowNormal, "AF"
Resume Exit_Form_Current

Else

' NOTE: Generates an error 2585, can't exit or close?

' This would work in an OnOpen, but here?

Cancel = True
DoCmd.Close

End If

Case Else

MsgBox Err.Number & ": " & Err.Description, vbInformation
Resume Exit_Form_Current

End Select

End Sub

-- code block ends here --

Suggestions? There is only an OnOpen event and this OnCurrent event, and the OnOpen event is merely checking for args passed through from other forms - nothing fancy.

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[RoyVidar]

The code triggering the error is the .refresh or gotorecord thingie then?

[tt]if me.recordsetclone.recordcount > 0 then
' do all your code
else
' ouch no records - what to do now ... I don't think you're
' allowed to close the form during this event ... so perhaps
' try the on open event again, perhaps using the above test?
' and cancel=true?
end if[/tt]

BTW - shouldn't be a need to refresh in the on current.

Roy-Vidar

 

[marcus101]

Roy:

Thanks for the suggestions. I have a couple of ideas that have occurred to me as a result of your message, will try them out later today and will report my results back soon.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[marcus101]

Hi Roy:

Tried your suggestions, and they didn't really work well because the problem still remains in the OnCurrent event.

And then I had a flash of insight: Why not try testing for the FiltCount value in the OnOpen event FIRST, BEFORE the OnCurrent event triggers?

Sure enough, that SEEMS to work.

I haven't had the chance to try it with actual records yet, but I'm hoping that WILL work, should do in theory, but I'm not sure if the OnOpen event is merely firing off a blank zero with no meaning or connectivity to the actual record count amount.

So to summarize, I inserted your IF test for FiltCount > 0 at the top of my OnOpen event block.

If the value is greater than 0, run the normal OnOpen code that processes the Args, if NOT, then include a Message and set Cancel equal to true.

It will be interesting to see if I can control things (via my Yes/NO Input box) at this point. I see no reason why that shouldn't work, but I will need to test that to be 100% sure I'm not just using a fictional value.

Thanks for the post.

If this does indeed work with actual records and processes as expected under those conditions, I'll fire off a star and let you know how things went.

If not, well, I'm pretty much back to square one, I'm afraid..;-)

If I do get stuck at that point, the only other thing I can think of is to use some other event in the chain that comes before onCurrent but allows for me to cancel as an abort mechanism, but isn't OnOpen.

Cheers,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[RoyVidar]

Just so I get this straight

- when I suggested that you move the code to the on open event of the form, it didn't work

- when you had the great idea ("a flash of insight") of moving the code to the on open event of the form, it did work

interesting ... would you care to elaborate on the difference?

Roy-Vidar

 

[marcus101]

Roy:

Sorry - you're right. Somehow I didn't 'see' your suggestion of moving the code to OnOpen, just the idea of running the recordcount test so I got hung up on that and tried kicking the idea around in my head later, so it seemed otherwise.

My bad, and my apologies.

Will let you know how things go.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[marcus101]

Hi Roy:

Your suggestion worked great for forms that EDIT existing records. This is very valuable, so my apologies for the (now delivered) delayed star.

However, I'm still in a bit of a jam when it comes to the same situation (this one) but when using forms that ADD NEW records, because the only way I can check to see if I have the correct data to populate the form to ADD new records is to use recordsets, and that means using OnCurrent again, not OnOpen, which would allow me to Cancel as you have stated.

Any ideas on how to do a recordset lookup in an OnOpen event so I could do something like this?

Thanks,


marcus101
Access/SQL/XML Developer
Ottawa, Canada