Can I populate a control with the record number?

I have a number control on a form. I want this to be automatically populated
with the record number and save the value in the field that is the source of
the control. How do I do this?
Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

hi Tony,

On 19.02.2010 14:11, TonyWilliams via AccessMonster.com wrote:
I have a number control on a form. I want this to be automatically populated
with the record number and save the value in the field that is the source of
the control. How do I do this?
There is no such thing like a record number. You may use a DCount() in
the record source:

SELECT DCount("*", "yourTable", "ID =" & [ID]) AS RecNo, *
FROM yourTable

Where ID is your primary key field or any other unique, not null field.


mfG
-- stefan --

Hi Stefan. I had thought of DCount but it wont work in my scenario I don't
think.
I have a form that has a control for a company name (txtcompany) It has a
subform where I input data on a number of deals which are identified by a
control deal number (txtdealnbr) In any month any company can have many deals
so the first deal for the first company is 1, then 2, then 3 and so on. The
next company can also have a number of deals and they will be 1 then 2 then 3
etc. But the ID for the deals as they are held in the table will run as 1,2,3,
4,5 and 6 in my example. So each month each companies deel number should
start as 1 not consecutive from the previous month. That's why I thought that
it would be good to populate the txtdealnbr from the number that is shown in
the record navigation buttons at the bottom of the screen.
Hope I've explained that?
Thanks
Tony

Stefan Hoffmann wrote:
hi Tony,

I have a number control on a form. I want this to be automatically populated
with the record number and save the value in the field that is the source of
the control. How do I do this?
There is no such thing like a record number. You may use a DCount() in
the record source:

SELECT DCount("*", "yourTable", "ID =" & [ID]) AS RecNo, *
FROM yourTable

Where ID is your primary key field or any other unique, not null field.

mfG
-- stefan --

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Just to add that the control I want to populate is a combo box where the user
chooses a value for the first record and the I want that value to appear in
the next records until they want to choose another value.
Hope I've explained that?
Thanks
Tony

TonyWilliams wrote:
I have a number control on a form. I want this to be automatically populated
with the record number and save the value in the field that is the source of
the control. How do I do this?
Thanks
Tony


--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Sorry forget my last message wrong post!!!!
Tony

TonyWilliams wrote:
Just to add that the control I want to populate is a combo box where the user
chooses a value for the first record and the I want that value to appear in
the next records until they want to choose another value.
Hope I've explained that?
Thanks
Tony

I have a number control on a form. I want this to be automatically populated
with the record number and save the value in the field that is the source of
the control. How do I do this?
Thanks
Tony


--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

hi Tony,

On 19.02.2010 15:02, TonyWilliams via AccessMonster.com wrote:
Hi Stefan. I had thought of DCount but it wont work in my scenario I don't
think.
It can work. Try adding the company as criteria:

SELECT DCount("*",
"yourTable",
"ID=" & [ID] & " AND CompanyID = " & [CompanyID]) AS RecNo,
*
FROM yourTable

So each month each companies deel number should
start as 1 not consecutive from the previous month.
Also add the month to the criteria

"ID=" & [ID] &
" AND CompanyID = " & [CompanyID] &
" AND Year([SalesDate]) = " & Year([SalesDate]) &
" AND Month([SalesDate]) = " & Month([SalesDate])


mfG
-- stefan --

Hi Stefan
This is what I've got in the Control source of my field
SELECT DCount("*","tblhvdealspt1","ID=" & [ID] & " AND txtcompany = " &
[txtcompany] & " AND Year([txtmonth]) = " & Year([txtmonth]) & " AND Month(
[txtmonth]) = " & Month([txtmonth]) AS txtdealnbr,*

But I get an error message that says "Syntax of subquery is incorrect check
and enclose subquery in parenthesis"
Any ideas?

Stefan Hoffmann wrote:
hi Tony,

Hi Stefan. I had thought of DCount but it wont work in my scenario I don't
think.
It can work. Try adding the company as criteria:

SELECT DCount("*",
"yourTable",
"ID=" & [ID] & " AND CompanyID = " & [CompanyID]) AS RecNo,
*
FROM yourTable

So each month each companies deel number should
start as 1 not consecutive from the previous month.
Also add the month to the criteria

"ID=" & [ID] &
" AND CompanyID = " & [CompanyID] &
" AND Year([SalesDate]) = " & Year([SalesDate]) &
" AND Month([SalesDate]) = " & Month([SalesDate])

mfG
-- stefan --

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

hi Tony,

On 20.02.2010 16:44, TonyWilliams via AccessMonster.com wrote:
Hi Stefan
This is what I've got in the Control source of my field
SELECT DCount("*","tblhvdealspt1","ID="& [ID]& " AND txtcompany = "&
[txtcompany]& " AND Year([txtmonth]) = "& Year([txtmonth])& " AND Month(
[txtmonth]) = "& Month([txtmonth]) AS txtdealnbr,*

But I get an error message that says "Syntax of subquery is incorrect check
and enclose subquery in parenthesis"
Any ideas?
You have to calculate this in your Record Source of your form. If you
put it into the Control Source you only need the DCount(), e.g.

Control Source: =DCount("*", "yourTable", "ID=" & [ID]...)

The better solution is to put into the Record Source.


mfG
-- stefan --

Sorry Stefan I'm a bit of a newbie. Where would I find the Record Source
Property?
Thanks
Tony

Stefan Hoffmann wrote:
hi Tony,

Hi Stefan
This is what I've got in the Control source of my field
[quoted text clipped - 5 lines]
and enclose subquery in parenthesis"
Any ideas?
You have to calculate this in your Record Source of your form. If you
put it into the Control Source you only need the DCount(), e.g.

Control Source: =DCount("*", "yourTable", "ID=" & [ID]...)

The better solution is to put into the Record Source.

mfG
-- stefan --

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

Sorry Stefan I've just realised where you mean. But, if I put it in the
Record Source of the form how will my form get access to all the other fields
in the table on which the form is based and which is currently the Record
Source of the form?
Tony

Stefan Hoffmann wrote:
hi Tony,

Hi Stefan
This is what I've got in the Control source of my field
[quoted text clipped - 5 lines]
and enclose subquery in parenthesis"
Any ideas?
You have to calculate this in your Record Source of your form. If you
put it into the Control Source you only need the DCount(), e.g.

Control Source: =DCount("*", "yourTable", "ID=" & [ID]...)

The better solution is to put into the Record Source.

mfG
-- stefan --

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via http://www.accessmonster.com