Can I populate a control with the record number?

[TonyWilliams via AccessMonster.com]

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

 

[Stefan Hoffmann]

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 <--

 

[TonyWilliams via AccessMonster.com]

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

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 <--

 

[TonyWilliams via AccessMonster.com]

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 via AccessMonster.com]

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

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

 

[Stefan Hoffmann]

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 <--

 

[TonyWilliams via AccessMonster.com]

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?

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 <--

 

[Stefan Hoffmann]

hi Tony,

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 <--

 

[TonyWilliams via AccessMonster.com]

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

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 <--

 

[TonyWilliams via AccessMonster.com]

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

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 <--

 

[Stefan Hoffmann]

hi Tony,

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

The Record Source property is a property of the Form. Open your form in
the design view, press F4 to open the property editor. Select your form
and navigate to the Data page.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tony,

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?

Built a sample table:

myTable:
ID, AutoNumber, Primary Key
Payload, Text

Fill in some records. Delete one or three.

Create a sample query on it, use this SQL:

SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID<=" & [ID])
AS RecNo
FROM myTable;

Create a new query, don't select a table in the dialog. Switch to the
SQL view and paste it into it.

Now you can use this query as Record Source in your form. Instead of
using a query you may copy this SQL string into the Record Source of a
new form.


mfG
--> stefan <--

 

[TonyWilliams via AccessMonster.com]

Thanks Stefan I think I get the idea (?) I'll have a go over the next day or
so and come back if I have a problem. Duty (my wife!) calls now!
Thanks for sticking with me
Tony

hi Tony,

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?

Built a sample table:

myTable:
ID, AutoNumber, Primary Key
Payload, Text

Fill in some records. Delete one or three.

Create a sample query on it, use this SQL:

SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID<=" & [ID])
AS RecNo
FROM myTable;

Create a new query, don't select a table in the dialog. Switch to the
SQL view and paste it into it.

Now you can use this query as Record Source in your form. Instead of
using a query you may copy this SQL string into the Record Source of a
new form.

mfG
--> stefan <--

 

[TonyWilliams via AccessMonster.com]

Ok so I think I followed your instructions and have an sql statement like
this
SELECT tblhvdealspt1.ID, tblhvdealspt1.txtdn, DCount("*","tblhvdealspt1",
"ID<=" & [ID]) AS RecNo
FROM tblhvdealspt1;
I used this as the Record source of my form and all the controls are now
showing as having no control source.

I'm sorry to be so pedantic but could I just return to what I'm trying to do.
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 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. I'm still not sure how using the ID of the table creates these for each
of the deals as they are held in the table and the ID will run as 1,2,3,4,5
and 6 in my example. Each month I want each companies deal number to start as
1 not consecutive from the previous month. This why I can't see how using the
ID will do this. But I am not an experienced Access programmer so maybe I'm
missing something fundamental here?

Thanks again Stefan
Tony

hi Tony,

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?

Built a sample table:

myTable:
ID, AutoNumber, Primary Key
Payload, Text

Fill in some records. Delete one or three.

Create a sample query on it, use this SQL:

SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID<=" & [ID])
AS RecNo
FROM myTable;

Create a new query, don't select a table in the dialog. Switch to the
SQL view and paste it into it.

Now you can use this query as Record Source in your form. Instead of
using a query you may copy this SQL string into the Record Source of a
new form.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tony,

I'm sorry to be so pedantic but could I just return to what I'm trying to do.
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 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. I'm still not sure how using the ID of the table creates these for each
of the deals as they are held in the table and the ID will run as 1,2,3,4,5
and 6 in my example. Each month I want each companies deal number to start as
1 not consecutive from the previous month. This why I can't see how using the
ID will do this.

But I am not an experienced Access programmer so maybe I'm missing something fundamental here?

No, I don't think so. Take a closer look at the logic in the simple
example. The record number is built on an (artifical) order given
through the criteria in the DCount() statement. You have now to build a
criteria which expresses exactly your needs. Using a simplified table
structure:

Company:
[ID], AutoNumber, Primary Key
[Name] Text(255) Not Null

Deal:
[ID], AutoNumber, Primary Key
[idCompany], Number Not Null, Foreign Key to table Company
[Date] DateTime Not Null
[Comment] Memo

Then you need for your sub-form this SQL as record source:

SELECT
*,
DCount(
"*",
"[Deal]",
"[idCompany] = " & [idCompany] &
" AND [Date] <= " & [Date] &
" AND Year([Date]) = " & Year([Date]) &
" AND Month([Date]) = " & Month([Date])
) AS [RecNo]
FROM [Deal]


mfG
--> stefan <--

 

[TonyWilliams via AccessMonster.com]

Thanks Stefan, forgive me but I've been at this all day now and my 65 year
old brain (which doesn't work as quick as it used to) is getting a bit weary.
I'm going to study your reply closer tomorrow when the fog's lifted from my
eyes!
Thanks agian really appreciate your efforts.
Tony

hi Tony,

I'm sorry to be so pedantic but could I just return to what I'm trying to do.
I have a form that has a control for a company name (txtcompany) It has a

[quoted text clipped - 7 lines]

1 not consecutive from the previous month. This why I can't see how using the
ID will do this.

But I am not an experienced Access programmer so maybe I'm missing something fundamental here?

No, I don't think so. Take a closer look at the logic in the simple
example. The record number is built on an (artifical) order given
through the criteria in the DCount() statement. You have now to build a
criteria which expresses exactly your needs. Using a simplified table
structure:

Company:
[ID], AutoNumber, Primary Key
[Name] Text(255) Not Null

Deal:
[ID], AutoNumber, Primary Key
[idCompany], Number Not Null, Foreign Key to table Company
[Date] DateTime Not Null
[Comment] Memo

Then you need for your sub-form this SQL as record source:

SELECT
*,
DCount(
"*",
"[Deal]",
"[idCompany] = " & [idCompany] &
" AND [Date] <= " & [Date] &
" AND Year([Date]) = " & Year([Date]) &
" AND Month([Date]) = " & Month([Date])
) AS [RecNo]
FROM [Deal]

mfG
--> stefan <--

 

[TonyWilliams via AccessMonster.com]

Hi Stefan.
So, this is what I have as the Record Source for my form:
SELECT DCount("*","[tblhvdealspt1]","[IDcompany] = " & [IDcompany] & " AND
[txtmonth] <= " & [txtmonth] & " AND Year([txtmonth]) = " & Year([txtmonth])
& " AND Month([txtmonth]) = " & Month([txtmonth])) AS RecNo, *;

And I created an Unbound Control on my form with the name RecNo. Should I
have done this or was RecNo supposed to have been the field that I'm storing
which is Txtdealnbr, which I can't make it that as it is available as a
Control Source.

AND all the other controls on my form have the signal that they have no
Control Source and when I click on "Add Existing Fields" only RecNo is shown.

What have I done wrong? Should I add the tablhvdealspt1 in the query design
screen?
Thanks again for your continued help.
Tony

hi Tony,

I'm sorry to be so pedantic but could I just return to what I'm trying to do.
I have a form that has a control for a company name (txtcompany) It has a

[quoted text clipped - 7 lines]

1 not consecutive from the previous month. This why I can't see how using the
ID will do this.

But I am not an experienced Access programmer so maybe I'm missing something fundamental here?

No, I don't think so. Take a closer look at the logic in the simple
example. The record number is built on an (artifical) order given
through the criteria in the DCount() statement. You have now to build a
criteria which expresses exactly your needs. Using a simplified table
structure:

Company:
[ID], AutoNumber, Primary Key
[Name] Text(255) Not Null

Deal:
[ID], AutoNumber, Primary Key
[idCompany], Number Not Null, Foreign Key to table Company
[Date] DateTime Not Null
[Comment] Memo

Then you need for your sub-form this SQL as record source:

SELECT
*,
DCount(
"*",
"[Deal]",
"[idCompany] = " & [idCompany] &
" AND [Date] <= " & [Date] &
" AND Year([Date]) = " & Year([Date]) &
" AND Month([Date]) = " & Month([Date])
) AS [RecNo]
FROM [Deal]

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tony,

What have I done wrong? Should I add the tablhvdealspt1 in the query design
screen?

Mail me a sample...


mfG
--> stefan <--

 

[TonyWilliams via AccessMonster.com]

Was this a sample of the database or the data that's going into it? Also how
do I email it to you?
Thanks
Tony

 

[Stefan Hoffmann]

hi Tony,

Was this a sample of the database or the data that's going into it? Also how
do I email it to you?

ahh, I see you're not using NNTP... can you publish it on some free
space in the web?



mfG
--> stefan <--