Can I populate a control with the record number?

[TonyWilliams via AccessMonster.com]

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

 

[TonyWilliams via AccessMonster.com]

Hi Stefan, you can download a cutdown version of the database here:
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]

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

 

[TonyWilliams via AccessMonster.com]

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,

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

 

[TonyWilliams via AccessMonster.com]

sorry meant WITHOUT the -
Tony

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

 

[Stefan Hoffmann]

hi Tony,

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

 

[TonyWilliams via AccessMonster.com]

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

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

 

[TonyWilliams via AccessMonster.com]

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

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