How to get at sub-form record number in VBA (or an expresion)?

[usenet]

I want to be able to use the record numbers of a sub-form, are they
available anyhow in VB (Access 2003)?

I want to use the sub-form record number as *part* of the primary key
for the table whose data is displayed on the sub-form. The other part
of the primary key is the column used to join to the master table.

E.g. I have TableA (the master table) with a primary key TableA.Column1.
TableB which is shown in the sub-form has a primary key made up of its
Column1 which joins to TableA.Column1 and a number TableB.Column2 which
I always want to start at 1 and be numbered sequentially.

If I can get at the sub-form row number I can simply set
TableB.Column2 to that value.
Any other way of achieving the same result would be welcome!

--
Chris Green

[David W. Fenton]

us****@isbd.co.uk wrote in news:442ef99d.0@entanet:

I want to be able to use the record numbers of a sub-form, are
they available anyhow in VB (Access 2003)?

I want to use the sub-form record number as *part* of the primary
key for the table whose data is displayed on the sub-form. The
other part of the primary key is the column used to join to the
master table.

E.g. I have TableA (the master table) with a primary key
TableA.Column1. TableB which is shown in the sub-form has a
primary key made up of its Column1 which joins to TableA.Column1
and a number TableB.Column2 which I always want to start at 1 and
be numbered sequentially.

If I can get at the sub-form row number I can simply set
TableB.Column2 to that value.

Any other way of achieving the same result would be welcome!

There is no such thing as a fixed record number because the order of
the records depends on the SORT ORDER of the recordsource of the
form.

Sounds like what you want is a line number (like you might have in
an invoice) so that you can maintain the order of the lines as they
were originally entered. In that case, you would just populate your
line number field by using DCount() + 1 on the set of records that
are shown in the subform. Of course, if a line gets deleted, that
could cause duplicates, so you might instead choose DMax() + 1.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[usenet]

David W. Fenton <XX*******@dfenton.com.invalid> wrote:

us****@isbd.co.uk wrote in news:442ef99d.0@entanet:

I want to be able to use the record numbers of a sub-form, are
they available anyhow in VB (Access 2003)?

I want to use the sub-form record number as *part* of the primary
key for the table whose data is displayed on the sub-form. The
other part of the primary key is the column used to join to the
master table.

E.g. I have TableA (the master table) with a primary key
TableA.Column1. TableB which is shown in the sub-form has a
primary key made up of its Column1 which joins to TableA.Column1
and a number TableB.Column2 which I always want to start at 1 and
be numbered sequentially.

If I can get at the sub-form row number I can simply set
TableB.Column2 to that value.

Any other way of achieving the same result would be welcome!
There is no such thing as a fixed record number because the order of
the records depends on the SORT ORDER of the recordsource of the
form.

True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)
Sounds like what you want is a line number (like you might have in
an invoice) so that you can maintain the order of the lines as they
were originally entered. In that case, you would just populate your
line number field by using DCount() + 1 on the set of records that
are shown in the subform. Of course, if a line gets deleted, that
could cause duplicates, so you might instead choose DMax() + 1.

Yes, OK, that's exactly what I want, the problem I have is how to
generate the value as you enter lines into the sub-form. This isn't
(usually) an already populated sub-form, when it first appears it just
has the one 'new record' line in it. I want to set the value of the
'line number' column in the sub-form to the line numbers as I add new
lines. I don't think setting it to DCount() + 1 will do what I want
as I enter data.

I'll try it though.

--
Chris Green

[usenet]

us****@isbd.co.uk wrote:

David W. Fenton <XX*******@dfenton.com.invalid> wrote:

us****@isbd.co.uk wrote in news:442ef99d.0@entanet:

I want to be able to use the record numbers of a sub-form, are
they available anyhow in VB (Access 2003)?

I want to use the sub-form record number as *part* of the primary
key for the table whose data is displayed on the sub-form. The
other part of the primary key is the column used to join to the
master table.

E.g. I have TableA (the master table) with a primary key
TableA.Column1. TableB which is shown in the sub-form has a
primary key made up of its Column1 which joins to TableA.Column1
and a number TableB.Column2 which I always want to start at 1 and
be numbered sequentially.

If I can get at the sub-form row number I can simply set
TableB.Column2 to that value.

Any other way of achieving the same result would be welcome!

There is no such thing as a fixed record number because the order of
the records depends on the SORT ORDER of the recordsource of the
form.

True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)

Sounds like what you want is a line number (like you might have in
an invoice) so that you can maintain the order of the lines as they
were originally entered. In that case, you would just populate your
line number field by using DCount() + 1 on the set of records that
are shown in the subform. Of course, if a line gets deleted, that
could cause duplicates, so you might instead choose DMax() + 1.

Yes, OK, that's exactly what I want, the problem I have is how to
generate the value as you enter lines into the sub-form. This isn't
(usually) an already populated sub-form, when it first appears it just
has the one 'new record' line in it. I want to set the value of the
'line number' column in the sub-form to the line numbers as I add new
lines. I don't think setting it to DCount() + 1 will do what I want
as I enter data.

I'll try it though.

No, well I can't make it work anyway.

I can't see how to write the DCoount() expression to get what I want.

If I put something like:-

=DCount("[Column1]", "[TableB]")

I simply get the total number of rows in TableB which is not what I
want at all.

I can't see how to write the third parameter for DCount so that it
only counts the records so far in the sub-form. It needs to be
something like:-

=DCount("[Column1]", "[TableB]", "[Column1]=Forms![FormA]![Column1]")

.... but this just gives me #Error.

--
Chris Green

[polite person]

I think what you need is the AbosolutePosition property (that's what it was in Access 97)

[polite person]

On Sun, 02 Apr 2006 12:14:04 GMT, polite person <si*****@ease.com> wrote:

I think what you need is the AbosolutePosition property (that's what it was in Access 97)

or maybe the AbsolutePosition property!

[rkc]

us****@isbd.co.uk wrote:

David W. Fenton <XX*******@dfenton.com.invalid> wrote: True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)

Perhaps the CurrentRecord property of the Form object is what you're
looking for.

[Bob Quintal]

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.com:

On Sun, 02 Apr 2006 12:14:04 GMT, polite person
<si*****@ease.com> wrote:

I think what you need is the AbosolutePosition property
(that's what it was in Access 97)

or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset, when
creating a new record, it's simpler to use the .recordcount
property, as the value of the (sub)form control's .defaultvalue
property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1

--
Bob Quintal

PA is y I've altered my email address.

[usenet]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

us****@isbd.co.uk wrote:

David W. Fenton <XX*******@dfenton.com.invalid> wrote:

True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)

Perhaps the CurrentRecord property of the Form object is what you're
looking for.

Sounds perfect, but if I put =[FormB].[CurrentRecord] as the
expression for the ControlSource all I get displayed is #Name?

I'm not very clued up on Access/VB syntax though, being a C/C++/Java
programmer at heart and mostly Unix/Linux based at that. Therefore
I'm probably making lots of silly mistakes as well as wanting to do
something a little out of the way.

--
Chris Green

[usenet]

Bob Quintal <rq******@sympatico.ca> wrote:

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.com:

On Sun, 02 Apr 2006 12:14:04 GMT, polite person
<si*****@ease.com> wrote:

I think what you need is the AbosolutePosition property
(that's what it was in Access 97)

or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset, when
creating a new record, it's simpler to use the .recordcount
property, as the value of the (sub)form control's .defaultvalue
property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1

Could you expand a little for me as I'm definitely *not* an Access of
VB specialist. The actual column in question is called Text10, if I
put the following:-

me.[Text10].defaultvalue = nz(me.recordcount)+1

I just get told that Access can't find the macro "me". Is "me"
accepted shorthand for something - I seem to be somewhat confused.

Just to be clear this is Access 2003. (... and yes, I know Text10
isn't a good name, but I'm only trying to see how to do what I want at
the moment)

--
Chris Green

[rkc]

us****@isbd.co.uk wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

us****@isbd.co.uk wrote:

David W. Fenton <XX*******@dfenton.com.invalid> wrote:

True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)

Perhaps the CurrentRecord property of the Form object is what you're
looking for.

Sounds perfect, but if I put =[FormB].[CurrentRecord] as the
expression for the ControlSource all I get displayed is #Name?

I'm not very clued up on Access/VB syntax though, being a C/C++/Java
programmer at heart and mostly Unix/Linux based at that. Therefore
I'm probably making lots of silly mistakes as well as wanting to do
something a little out of the way.

Where is your code? If it is in the Form module that you are
referencing then Me.CurrentRecord should work. Me is just a
reference to the object the code is part of. Same as This in Java
I believe.

If it is in a stand alone module then Forms("FormB").CurrentRecord.
Me has no meaning in a stand alone module.

[tina]

sounds like you're putting the expression directly on the event "line" in
the Properties box. the code, that you've been getting in several posts in
this thread, goes in the form's VBA module. for instructions on creating an
event procedure in a form, go to
http://home.att.net/~california.db/instructions.html and click te
CreateEventProcedure link.

hth
<us****@isbd.co.uk> wrote in message news:442fed22.0@entanet...

Bob Quintal <rq******@sympatico.ca> wrote:

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.com:

On Sun, 02 Apr 2006 12:14:04 GMT, polite person
<si*****@ease.com> wrote:

>
>I think what you need is the AbosolutePosition property
>(that's what it was in Access 97)
or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset, when
creating a new record, it's simpler to use the .recordcount
property, as the value of the (sub)form control's .defaultvalue
property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1

Could you expand a little for me as I'm definitely *not* an Access of
VB specialist. The actual column in question is called Text10, if I
put the following:-

me.[Text10].defaultvalue = nz(me.recordcount)+1

I just get told that Access can't find the macro "me". Is "me"
accepted shorthand for something - I seem to be somewhat confused.

Just to be clear this is Access 2003. (... and yes, I know Text10
isn't a good name, but I'm only trying to see how to do what I want at
the moment)

--
Chris Green

[usenet]

tina <no****@address.com> wrote:

hth
<us****@isbd.co.uk> wrote in message news:442fed22.0@entanet...

Bob Quintal <rq******@sympatico.ca> wrote:

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.com:

> On Sun, 02 Apr 2006 12:14:04 GMT, polite person
> <si*****@ease.com> wrote:
>
>>
>>I think what you need is the AbosolutePosition property
>>(that's what it was in Access 97)
> or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset, when
creating a new record, it's simpler to use the .recordcount
property, as the value of the (sub)form control's .defaultvalue
property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1

Could you expand a little for me as I'm definitely *not* an Access of
VB specialist. The actual column in question is called Text10, if I
put the following:-

me.[Text10].defaultvalue = nz(me.recordcount)+1

I just get told that Access can't find the macro "me". Is "me"
accepted shorthand for something - I seem to be somewhat confused.

Just to be clear this is Access 2003. (... and yes, I know Text10
isn't a good name, but I'm only trying to see how to do what I want at
the moment)

sounds like you're putting the expression directly on the event "line" in
the Properties box. the code, that you've been getting in several posts in
this thread, goes in the form's VBA module. for instructions on creating an
event procedure in a form, go to
http://home.att.net/~california.db/instructions.html and click te
CreateEventProcedure link.

I tried that as well and couldn't get it to work either, I got the
same complaint from VB that "me" doesn't exist.

However I've got almost exactly what I want by simply putting
=[CurrentRecord]+1 as the default value for my 'line number' column.
It just needs some manipulation of the first record value and I'll
have what I want.

--
Chris Green

[usenet]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

us****@isbd.co.uk wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

us****@isbd.co.uk wrote:

David W. Fenton <XX*******@dfenton.com.invalid> wrote:

True in a way, but what I want is the number that appears in the "Record
Selector" at the bottom of the sub-form. That has exactly the
relationship with the records in the sub-form that I want so Access
does have these 'record numbers' inside it somewhere. (Or at least it
can generate them)

Perhaps the CurrentRecord property of the Form object is what you're
looking for.

Sounds perfect, but if I put =[FormB].[CurrentRecord] as the
expression for the ControlSource all I get displayed is #Name?

I'm not very clued up on Access/VB syntax though, being a C/C++/Java
programmer at heart and mostly Unix/Linux based at that. Therefore
I'm probably making lots of silly mistakes as well as wanting to do
something a little out of the way.

Where is your code? If it is in the Form module that you are
referencing then Me.CurrentRecord should work. Me is just a
reference to the object the code is part of. Same as This in Java
I believe.

If it is in a stand alone module then Forms("FormB").CurrentRecord.
Me has no meaning in a stand alone module.

Getting even simpler an expression of =CurrentValue+1 as the default
value of the column gives me almost exactly what I want. I think I
can now get to what I want.

--
Chris Green

[Bob Quintal]

us****@isbd.co.uk wrote in news:442fed22.0@entanet:

Bob Quintal <rq******@sympatico.ca> wrote:

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.com:

> On Sun, 02 Apr 2006 12:14:04 GMT, polite person
> <si*****@ease.com> wrote:
>
>>
>>I think what you need is the AbosolutePosition property
>>(that's what it was in Access 97)
> or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset,
when creating a new record, it's simpler to use the
.recordcount property, as the value of the (sub)form
control's .defaultvalue property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1

Could you expand a little for me as I'm definitely *not* an
Access of VB specialist. The actual column in question is
called Text10, if I put the following:-

me.[Text10].defaultvalue = nz(me.recordcount)+1

I just get told that Access can't find the macro "me". Is
"me" accepted shorthand for something - I seem to be somewhat
confused.

Just to be clear this is Access 2003. (... and yes, I know
Text10 isn't a good name, but I'm only trying to see how to do
what I want at the moment)

What Tina said.

me. is a standard notation for the current form, in visual
basic. it is defined in VBA as Forms![currentForm].

Open the form in design mode. Open the properties popup. click
the EVENTS tab and find the OnCurrent Event. It should show
Event procedure if you've set it right, If it is empty or says
something else, click in the control, set it to event procedure
from the dropdown, and enter the one line inbetween the two
lines that Access would have pre-loaded:
Private sub [formname]_OnCurrent
End sub

As to your use of the word column, that may also be the issue.
Access tables have columns called fields.Access forms, even when
in datasheet view, have controls. Controls may be bound to
fields, but they don't have to be bound.
--
Bob Quintal

PA is y I've altered my email address.

[salad]

us****@isbd.co.uk wrote:

tina <no****@address.com> wrote:

hth
<us****@isbd.co.uk> wrote in message news:442fed22.0@entanet...

Bob Quintal <rq******@sympatico.ca> wrote:

polite person <si*****@ease.com> wrote in
news:i1********************************@4ax.co m:
>On Sun, 02 Apr 2006 12:14:04 GMT, polite person
><si*****@ease.com> wrote:
>
>
>>I think what you need is the AbosolutePosition property
>>(that's what it was in Access 97)
>
>or maybe the AbsolutePosition property!

Since the OP only needs the count of the subform's recordset, when
creating a new record, it's simpler to use the .recordcount
property, as the value of the (sub)form control's .defaultvalue
property.

in the subform's OnCurrent event, put
me.[column1].defaultvalue =nz(me.recordcount)+1
Could you expand a little for me as I'm definitely *not* an Access of
VB specialist. The actual column in question is called Text10, if I
put the following:-

me.[Text10].defaultvalue = nz(me.recordcount)+1

I just get told that Access can't find the macro "me". Is "me"
accepted shorthand for something - I seem to be somewhat confused.

Just to be clear this is Access 2003. (... and yes, I know Text10
isn't a good name, but I'm only trying to see how to do what I want at
the moment)

sounds like you're putting the expression directly on the event "line" in
the Properties box. the code, that you've been getting in several posts in
this thread, goes in the form's VBA module. for instructions on creating an
event procedure in a form, go to
http://home.att.net/~california.db/instructions.html and click te
CreateEventProcedure link.

I tried that as well and couldn't get it to work either, I got the
same complaint from VB that "me" doesn't exist.

However I've got almost exactly what I want by simply putting
=[CurrentRecord]+1 as the default value for my 'line number' column.
It just needs some manipulation of the first record value and I'll
have what I want.

That's strange. Is you code module for the form or is it in a module in
the Modules tab? If its in your code module for the form, open it up,
find some routine, and enter Me. in it. Does the intellysis present a
dropdown of methods/properties? If not, that would indicate that
somewhere there's something that isn't compiling. If you don't/can't
use Me. then you have a problem that should be corrected.

[rkc]

us****@isbd.co.uk wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

us****@isbd.co.uk wrote:
Where is your code? If it is in the Form module that you are
referencing then Me.CurrentRecord should work. Me is just a
reference to the object the code is part of. Same as This in Java
I believe.

If it is in a stand alone module then Forms("FormB").CurrentRecord.
Me has no meaning in a stand alone module.

Getting even simpler an expression of =CurrentValue+1 as the default
value of the column gives me almost exactly what I want. I think I
can now get to what I want.

Sure. You can use CurrentValue without referencing Me since the form's
properties and methods are simply available when called from within the
form's own methods. But then someone like you comes along, sees it and
has no clue where it came from or why it works. Qualifying it with Me
clears that up. Sort of.