How to assign Current Record value to textbox in form

[JHite]

I'm using MS Access 2003. I have a form named frmClient which has a subform named frmGrantLoan. [frmGrantLoan's underlying recordset is a query which pulls from tblClient and tblGrantLoan as linked by a ClientID.] On frmGrantLoan I have an unbound text box named CRNum. I have the VBA code (shown below) executed by the On Current property of frmGrantLoan. I'm trying to show the user the current record number by displaying it in the text box named CRNum on frmGrantLoan.

Private Sub Form_Current()
Me!CRNum.Value = Me.CurrentRecord
End Sub

I am getting the following error (attached jpg):

I've tried various things and this is the closest I've come to thinking I've done everything right.

Can someone help me troubleshoot this?
Thanks so much. JHite
[imgnothumb]http://bytes.com/attachments/attachment/6898d1360862555/errormsg.jpg[/imgnothumb]

[Rabbit]

Rabbit - Removed after change of mind:
Me in this context refers to the Form. A form has no current record property, the form's recordset does however.

Check the name of the CRNum control. The code works fine for me.

[Seth Schrock]

Can you please type out the error message? I can't open attachments at work and I don't normally open attachments from people that I don't know as a security precaution. Many of the other experts don't as well.

[JHite]

error msg reads:
"The Microsoft.Jet database engine could not find the object". Make sure the object exists and that you spell its name and the pathname correctly."

The name of the unbound textbox on frmGrantLoan is definitely CRNum

[Seth Schrock]

And you are for sure in frmGrantLoan's OnCurrent event? When you start typing Me.CR does Intelisense find the control name?

[NeoPa]

When dubugging (Debugging in VBA) what value do you get for Me.CurrentRecor d?

[zmbd]

My apologies for the length.
You'll find I don't post a lot, but the few I do post have a lot of what I hope is useful information.

In two parts:

@Seth
Actually, the form object does have a current record property: Form.CurrentRec ord Property (Access). And the recordset object doesn't list Current Record as being one of its properties.

Seth what are you trying to state here in post #3?
In the first part you tell us something that is already known and is what OP is trying to use. Then in the second part you go off on a tangent with recordsets which OP doesn't mention.

Also in post #5, I want to point out that you are using the me-dot-ctrlname... instead of the me-bang-ctrlname. Not that there's anything wrong between the two conventions in as we're trouble shooting and is a good way to double check a control name. However, an alternative that most people are not aware of when using the me-bang-ctrlname method is that when you type the bang "!" , the very next thing to press is, <ctrl><spacebar > - You will get a context type popup (usually starting with A_ADD), this works just like a combo/listbox if you keep typing the control's name it will be eventually selected enough to press the [Tab] key or arrow/mouse to the correct control. Ah, this is why all of my controls start with "z_" so that all of my controls are grouped.... for example for a control on the current form I would type "M""E""!""<ctrl ><SpaceBar"[the popup opens]"z""_" as I type the last two, the first control name in the form's control collection in the list is selected.

<ctrl><spacebar > is also useful in alot of other situations as it pulls up the constants (like when opening a dynaset start you "SET" start typing, when you get to the record type <ctrl><SpaceBar >dbopen... ) My spelling is, well, very bad, and my memory slips so having this available is a great asset for me.

-x-

JHite:
(...)I'm trying to show the user the current record number by displaying it in the text box named CRNum on frmGrantLoan.

Be careful here... consider what information you are really needing to provide the user - do they need the absolute record number for the current recordset or the primary key from the current record within the recordset?

I know that makes no sense what so ever, so let me try via an example:
Consider a table where in you have an autonumber as the primary key. In a normal database, you'll often end up with the primary key and the record number not being the same (i.e.)

[AccessRecord_#][autonumber_pk][someinfo]....
      (1)           (1)          (d)
      (2)           (2)          (c)
      (3)           (4)          (e)
      (4)           (5)          (f)
      (5)           (8)          (a)
      (6)           (11)          (b)
      (7)           (20)          (g)
      (8)           (25)          (h)
(...)

Notice that the first two record numbers match the primary key; however, starting at record 4, things are out of sync. Thus, if you provide the [AccessRecord_#]=8 to the user and you serch on [autonumber_pk] for (8) to find the the information in [someinfo] then you would return [AccessRecord_#]=5 and get (a) when you were expecting (h)
AND this WILL get worse if you are using a query for the form... say, WHERE [autonumber_pk]>=5 the [AccessRecord_#]=1 is related to [autonumber_pk]=5 and your search based on this set of record number will be even more interesting. Oh my... and if the user does a sort on one of the data columns like [someinfo] the first record will be for primary key 8. Welcome to the nightmare.

The only time I pass an absolute record number to the user is when I am pulling directly from the data table, sorted in a VERY specific order for the purpose of troubleshooting . If I need to keep referring to a specific record in VBA, I'll set a bookmark as the CurrentRecord recordid is not always reliable given that I often allow the user to sort on what ever field they want and so forth.

As for why your control is not working:
As Seth and others have pointed out:
You either have the control name wrong or you are entering your VBA in the Parent form and not the Child form.
Want to test that theory?
In your event copy and paste: msgbox parent.name
If you are in the parent form's code, without an error trap, you should get a debug dialog:

[Microsoft Visual Basic]
[Run-time '2452':]
[The expression you entered has an invalid reference to the Parent property.]

Or you might get the name of a form you're not expecting

Just a few thoughts.

I've got to go school the kids on how to share their toys by removing the offending object from play. I don't punish one, I make them all unhappy.... it takes two (or more) to fight. I find that they tend to try to solve the issue themselves without my micromanagement . I also answer "NO" to most questions asked when I'm on the telephone or in conversation with another adult. Even if it would be something I'd normally allow... I don't get interrupted much either...

ttfn

[Seth Schrock]

@Z In post 3, the first paragraph was in response to Rabbit. However, he has edited that part out, so I will edit my part out as well since it clearly doesn't make much sense now. Thanks for pointing this out.

[NeoPa]

I've re-edited Rabbit's post #2 to reflect the start of that part of the conversation. I doubt Rabbit realised when he removed it that it was already integral to the thread.

@Z Thanks for the info on using Ctrl-Space with the bang. I wouldn't personally use it for objects that are a direct part of the class (such as its controls), but knowing about it is certainly useful for all the areas where one might want to use bangs (!). Well worth posting.

Z:
You'll find I don't post a lot, but the few I do post ...

I had to laugh. Do you really not realise you're one of the most regular and frequent posters on the whole site? I expect you wish you had time for more, but from everyone else's perspective you qualify for post-miles! I hate to think what it'll be like if/when you get time for more.

[JHite]

I haven't marked any of your replies as the best answer but I really appreciate your help and learned some other good stuff from your replies.

I finally got the result I wanted by simply putting the following in the Control Source of my unbound text box (CRNum) and deleting the VBA code.

=IIf([CurrentRecord]>Count([GrantLoanID]),0,[CurrentRecord])

I used the IIf so that the current record for a new record would show as 0 instead of the total number of records + 1.

Once again I thank everyone. I'm sure I'll be asking for more help before long.
JHite

[zmbd]

Why are you giving the end user the record id... which will change, even on something as simple as sort order, instead of the value contained in [GrantLoanID]?

The "CurrentRec ord" number is USELESS for anything outside of current record set with a given sort order.

Did you get to the Second Part of my post in #7?

[Rabbit]

Sorry about that. I did more research right after I posted and did not realize someone else had already responded.