Can I populate a control with the record number?

hi Tony,

On 21.02.2010 12:26, TonyWilliams via AccessMonster.com wrote:
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 --

hi Tony,

On 21.02.2010 12:40, TonyWilliams via AccessMonster.com wrote:
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 --

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

Stefan Hoffmann wrote:
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 --

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

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

Stefan Hoffmann wrote:
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 --

--
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 21.02.2010 14:38, TonyWilliams via AccessMonster.com wrote:
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
structu

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

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

Stefan Hoffmann wrote:
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
structu

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

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

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

Stefan Hoffmann wrote:
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
structu

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

--
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 22.02.2010 12:04, TonyWilliams via AccessMonster.com wrote:
What have I done wrong? Should I add the tablhvdealspt1 in the query design
screen?
Mail me a sample...


mfG
-- stefan --

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 wrote:
hi Tony,

What have I done wrong? Should I add the tablhvdealspt1 in the query design
screen?
Mail me a sample...

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 24.02.2010 12:10, TonyWilliams via AccessMonster.com wrote:
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 --