Can I populate a control with the record number?

No I use IE8 to connect to the Access Monster site. I don't have access to
any free space except our own website. I will have a look at whether I can
load a file onto there for you to access.
Cheers
tony

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

--
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 Stefan, you can download a cutdown version of the database he
http://www.thecapitalpartnership.co.uk/test.html

If you open the forms you will see there is a control called Deal Nbr. I've
set the default value to 1. if you add more records in the subform for the
same company I want this to increase by 1. If you input a new company data it
should start from 1 again. If you start a new month it should start at 1
again for all companies.

Thanks for the help so far, really appreciate it.
Regards
tony

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

--
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 26.02.2010 13:36, TonyWilliams via AccessMonster.com wrote:
If you open the forms you will see there is a control called Deal Nbr. I've
set the default value to 1. if you add more records in the subform for the
same company I want this to increase by 1. If you input a new company data it
should start from 1 again. If you start a new month it should start at 1
again for all companies.
As far as I understand it, you need the deal number for [tblhvdealspt1].

Normally you would use a Date/Time field to order it correctly:

DealNo:
DCount(
"*";
"tblhvdealspt1";
"txtcompany='" & [txtcompany] &
"' AND Format(txtmonth,'yyyy-mm') = '" &
Format([txtmonth];"yyyy-mm") & "' AND [txtmonth] =" &
Format([txtmonth];"\#yyyy-mm-dd hh:nn:ss\#")
)

Unfortunately you do not store the complete time in [txtmonth]. So we
need to use the [ID] as order criteria

DealNo:
DCount(
"*";
"tblhvdealspt1";
"txtcompany='" & [txtcompany] &
"' AND Format(txtmonth,'yyyy-mm') = '" &
Format([txtmonth];"yyyy-mm") & "' AND [ID] =" & [ID]
)

So built a query based on [tblhvdealspt1] and add one of the above fields.

In your case it may be sufficient to calculated the number before
inserting a new record:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim maxDealNbr As Variant

maxDealNbr = DMax( _
"txtdealnbr", _
"tblhvdealspt1", _
"txtcompany = '" & Me![txtcompany] & "' AND " & _
"Format(txtmonth,'yyyy-mm') = '" & Format([txtmonth], "yyyy-mm") _
)

Me![txtdealnbr] = Nz(maxDealNbr, 0) + 1

End Sub

btw, prefixing table names may be necessary, but doing the same with
field names in your case 'txt' makes no sense. It adds unnecessary noise
to the code.


mfG
-- stefan --

Thanks Stefan. I now get asynatx error and it highlights this line:
"txtcompany = '" & Me![txtcompany] & "' AND " & _
"Format(txtmonth,'yyyy-mm') = '" & Format([txtmonth], "yyyy-mm") _
)
I've tried it with the - between yyyy and mm because I thought the format
function didn't require that bit I still get the error. is it something to do
with the code being on seperate lines?

Did it work in the tes database I sent?
Again really appreciate you help
Tony

Stefan Hoffmann wrote:
hi Tony,

If you open the forms you will see there is a control called Deal Nbr. I've
set the default value to 1. if you add more records in the subform for the
same company I want this to increase by 1. If you input a new company data it
should start from 1 again. If you start a new month it should start at 1
again for all companies.
As far as I understand it, you need the deal number for [tblhvdealspt1].

Normally you would use a Date/Time field to order it correctly:

DealNo:
DCount(
"*";
"tblhvdealspt1";
"txtcompany='" & [txtcompany] &
"' AND Format(txtmonth,'yyyy-mm') = '" &
Format([txtmonth];"yyyy-mm") & "' AND [txtmonth] =" &
Format([txtmonth];"\#yyyy-mm-dd hh:nn:ss\#")
)

Unfortunately you do not store the complete time in [txtmonth]. So we
need to use the [ID] as order criteria

DealNo:
DCount(
"*";
"tblhvdealspt1";
"txtcompany='" & [txtcompany] &
"' AND Format(txtmonth,'yyyy-mm') = '" &
Format([txtmonth];"yyyy-mm") & "' AND [ID] =" & [ID]
)

So built a query based on [tblhvdealspt1] and add one of the above fields.

In your case it may be sufficient to calculated the number before
inserting a new record:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim maxDealNbr As Variant

maxDealNbr = DMax( _
"txtdealnbr", _
"tblhvdealspt1", _
"txtcompany = '" & Me![txtcompany] & "' AND " & _
"Format(txtmonth,'yyyy-mm') = '" & Format([txtmonth], "yyyy-mm") _
)

Me![txtdealnbr] = Nz(maxDealNbr, 0) + 1

End Sub

btw, prefixing table names may be necessary, but doing the same with
field names in your case 'txt' makes no sense. It adds unnecessary noise
to the code.

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 meant WITHOUT the -
Tony

TonyWilliams wrote:
Thanks Stefan. I now get asynatx error and it highlights this line:
"txtcompany = '" & Me![txtcompany] & "' AND " & _
"Format(txtmonth,'yyyy-mm') = '" & Format([txtmonth], "yyyy-mm") _
)
I've tried it with the - between yyyy and mm because I thought the format
function didn't require that bit I still get the error. is it something to do
with the code being on seperate lines?

Did it work in the tes database I sent?
Again really appreciate you help
Tony

hi Tony,

[quoted text clipped - 55 lines]
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 Tony,

On 26.02.2010 16:40, TonyWilliams via AccessMonster.com wrote:
Thanks Stefan. I now get asynatx error and it highlights this line:
"txtcompany = '"& Me![txtcompany]& "' AND "& _
"Format(txtmonth,'yyyy-mm') = '"& Format([txtmonth], "yyyy-mm") _
)
I've tried it with the - between yyyy and mm because I thought the format
function didn't require that bit I still get the error. is it something to do
with the code being on seperate lines?

Did it work in the tes database I sent?
Yes, I copied it from it. It should work.

But in your quote above there are spaces missing around the ampersands (&).

mfG
-- stefan --

I think it must be something to do with the way the AccessMonster posts the
text. Could you email me the code to tony[dot]williams[at]
thecapitalpartnership[dot]co[dot]uk? Either that or tell me where the line
endings and spaces should be?
Thanks Stefan
Tony

Stefan Hoffmann wrote:
hi Tony,

Thanks Stefan. I now get asynatx error and it highlights this line:
"txtcompany = '"& Me![txtcompany]& "' AND "& _
[quoted text clipped - 5 lines]

Did it work in the tes database I sent?
Yes, I copied it from it. It should work.

But in your quote above there are spaces missing around the ampersands (&).

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

Stefan could you check this line:
"Format(txtmonth,'yyyy-mm') = '" & Format([txtmonth], "yyyy-mm") _
)
there are ' around the first txtmonth but " around the second?
also there seem to be a ' before the "& but not after?

Just a couple of things I noticed. I'm clearly not an expert on this so I
don't reall know what I'm looking for.
Regards
Tony

Stefan Hoffmann wrote:
hi Tony,

Thanks Stefan. I now get asynatx error and it highlights this line:
"txtcompany = '"& Me![txtcompany]& "' AND "& _
[quoted text clipped - 5 lines]

Did it work in the tes database I sent?
Yes, I copied it from it. It should work.

But in your quote above there are spaces missing around the ampersands (&).

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

"TonyWilliams via AccessMonster.com" u56994@uwe wrote in message
news:a3ddf4cf8f883@uwe...
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