Arrange the sequence increment number of subform

G

Guest

I have a Form ("JobFormA") and a Subform ("JobFormB")
JobFormA is based on Table "JobTabA" and JobFormB is based on Table "JobTabB".

In the subform I have a field "Ino", which is numeric type, in that the user
enters the RowNumber manually for every record.
Now the problem arises when about 80 to 90 records has been entered in the
Subform and then a time comes later where the user has to delete some the
records from inbetween. This disturbs the row number sequence and the user
has to go to every record and set the "Ino" number field again from the place
it was deleted, to arrange the sequence number properly. This takes a too
much time to handle.

I am looking for a solution through VB Code to do this job inbackground on
ONEXIT EVENT of Subform :

Select the records of the Table "JobTabB" where the
"JobNo"=Forms!JobFormA!JobNo
Go the first record set the Ino = 1, then to next record INo=2 and keep
increment till end of the records:

I tried by the method, but was not sucessfully as it is not complete, Just
for REFERENCE purpose I am writing below to understand the reader well :


Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("JobTabB")
Vdate = 1
rst.MoveFirst
rst.Fields(0) = Vdate
rst.Update
rst.Close
Set rst = Nothing

But I dont know what to add so that It will sort the data only where
JobNo=Forms!JobFormA!JobNo and how to move next and add +1 etc.

I want the job done at back ground as I will Requery my Subform Control. I
cannot do on the form, as there are some codes written OnGotFocus event of
Subform fields.

I have seen one code example on Microsoft Help sample, how to get the auto
increment number, but that does not suit my requirement.
Please advise, this will help me a lot to complete the job.
 
J

John Nurick

Hi Irshad,

Why not just use a calculated field in the underlying query to display a
sequential number? There seems no point in storing Ino because it's not
only liable to change at any time but also depends entirely on other
data.

Doing it that way, way you avoid all the problems of adjusting and
storing new numbers as the data changes. One way of doing this is with
DCount, e.g.
Ino: DCount("JobNo","JobTabB","[XXX]<=" & [XXX])
where XXX is the name of the field that determines the order in which
the records are displayed in the subform. (If XXX is a text field, you
need to use
"[XXX]<='" & [XXX} & "'"
 
G

Guest

Thanks Mr. John for your reply.
After an hours, I was fiddling with codes. Later got an idea ...why not
remove the event of the suform which is disturbing the code to run, get my
job done and again put the code back. At the start I dont why it does not
work. But after repairing the database, I found that it is working normal. My
try here is On exit of subform, it will starting going to each field and set
the number. It worked. But still I want you please advise me that "There will
be any problem later, when I will make an mde of this database, Any error can
be arised, OR it will work fine later also.


Private Sub TotFormB_Exit(Cancel As Integer)
Dim EndTills As Integer
Dim LookTill As Integer
'Me.Text13 is the field which counts the number of records on the subform.
LookTill = Me.Text13 + 1
EndTills = 1
If Me.Text13 > 0 Then
DoCmd.GoToControl "TotFormB"
[Forms]![TotFormA]![TotFormB]![Ino].OnGotFocus = ""
[Forms]![TotFormA]![TotFormB]![Qty].OnGotFocus = ""
DoCmd.RunCommand acCmdRecordsGoToFirst
Do
[Forms]![TotFormA]![TotFormB]![Ino] = EndTills
DoCmd.RunCommand acCmdRecordsGoToNext
EndTills = EndTills + 1
Loop Until EndTills = LookTill
[Forms]![TotFormA]![TotFormB]![Ino].OnGotFocus = "[Event Procedure]"
[Forms]![TotFormA]![TotFormB]![Qty].OnGotFocus = "[Event Procedure]"
DoCmd.GoToControl "Rem"
End If
End Sub

Actually, my mdb is about to complete and due to this I was not satisfied
neither in the position to make a big changes as it took a lot of time to
design the Main and subform. Therefore i was looking for an alternate
solution. Please advise.



John Nurick said:
Hi Irshad,

Why not just use a calculated field in the underlying query to display a
sequential number? There seems no point in storing Ino because it's not
only liable to change at any time but also depends entirely on other
data.

Doing it that way, way you avoid all the problems of adjusting and
storing new numbers as the data changes. One way of doing this is with
DCount, e.g.
Ino: DCount("JobNo","JobTabB","[XXX]<=" & [XXX])
where XXX is the name of the field that determines the order in which
the records are displayed in the subform. (If XXX is a text field, you
need to use
"[XXX]<='" & [XXX} & "'"
 
J

John Nurick

I'm honestly not sure whether your code will work in an MDE. The best
thing would be to create an MDE and test it on a variety of computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item numbers or
line numbers for a form or report (because the numbers will change any
time a record is added or deleted). Normally it is neither necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.

Thanks Mr. John for your reply.
After an hours, I was fiddling with codes. Later got an idea ...why not
remove the event of the suform which is disturbing the code to run, get my
job done and again put the code back. At the start I dont why it does not
work. But after repairing the database, I found that it is working normal. My
try here is On exit of subform, it will starting going to each field and set
the number. It worked. But still I want you please advise me that "There will
be any problem later, when I will make an mde of this database, Any error can
be arised, OR it will work fine later also.


Private Sub TotFormB_Exit(Cancel As Integer)
Dim EndTills As Integer
Dim LookTill As Integer
'Me.Text13 is the field which counts the number of records on the subform.
LookTill = Me.Text13 + 1
EndTills = 1
If Me.Text13 > 0 Then
DoCmd.GoToControl "TotFormB"
[Forms]![TotFormA]![TotFormB]![Ino].OnGotFocus = ""
[Forms]![TotFormA]![TotFormB]![Qty].OnGotFocus = ""
DoCmd.RunCommand acCmdRecordsGoToFirst
Do
[Forms]![TotFormA]![TotFormB]![Ino] = EndTills
DoCmd.RunCommand acCmdRecordsGoToNext
EndTills = EndTills + 1
Loop Until EndTills = LookTill
[Forms]![TotFormA]![TotFormB]![Ino].OnGotFocus = "[Event Procedure]"
[Forms]![TotFormA]![TotFormB]![Qty].OnGotFocus = "[Event Procedure]"
DoCmd.GoToControl "Rem"
End If
End Sub

Actually, my mdb is about to complete and due to this I was not satisfied
neither in the position to make a big changes as it took a lot of time to
design the Main and subform. Therefore i was looking for an alternate
solution. Please advise.



John Nurick said:
Hi Irshad,

Why not just use a calculated field in the underlying query to display a
sequential number? There seems no point in storing Ino because it's not
only liable to change at any time but also depends entirely on other
data.

Doing it that way, way you avoid all the problems of adjusting and
storing new numbers as the data changes. One way of doing this is with
DCount, e.g.
Ino: DCount("JobNo","JobTabB","[XXX]<=" & [XXX])
where XXX is the name of the field that determines the order in which
the records are displayed in the subform. (If XXX is a text field, you
need to use
"[XXX]<='" & [XXX} & "'"
 
G

Guest

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in your
advise, but I could not understand. If you could be more clear about, then I
can have a try to do that. Let take a example below and advise accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I started
feeding the data.

Now I want to get advised at where I should add this field, I tried to add a
field as mentioned in your reply to the query mentioend above, but the query
fails to open and error.

Please advise.
 
J

John Nurick

I'm sorry I haven't been clear. Let's start by eliminating one possible
source of confusion.

First, I need to be quite sure that you are using a form and subform to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential numbers.
 
G

Guest

Thanks again Sir.
I tried your method, it works fine. But the major problems remains unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean the
main form every record. While it counts for all the records based in the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards, as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from 1.

B.
Sometimes I also noticed that you can understand the JobDes can be same.
Like for example "Machine Service Done", again next month the same JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



John Nurick said:
I'm sorry I haven't been clear. Let's start by eliminating one possible
source of confusion.

First, I need to be quite sure that you are using a form and subform to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential numbers.


Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in your
advise, but I could not understand. If you could be more clear about, then I
can have a try to do that. Let take a example below and advise accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I started
feeding the data.

Now I want to get advised at where I should add this field, I tried to add a
field as mentioned in your reply to the query mentioend above, but the query
fails to open and error.

Please advise.
 
J

John Nurick

We're getting there. I think I've misunderstood the exact situation with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument of
the DCount statement.



Thanks again Sir.
I tried your method, it works fine. But the major problems remains unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean the
main form every record. While it counts for all the records based in the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards, as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from 1.

B.
Sometimes I also noticed that you can understand the JobDes can be same.
Like for example "Machine Service Done", again next month the same JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



John Nurick said:
I'm sorry I haven't been clear. Let's start by eliminating one possible
source of confusion.

First, I need to be quite sure that you are using a form and subform to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential numbers.


Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in your
advise, but I could not understand. If you could be more clear about, then I
can have a try to do that. Let take a example below and advise accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I started
feeding the data.

Now I want to get advised at where I should add this field, I tried to add a
field as mentioned in your reply to the query mentioend above, but the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE. The best
thing would be to create an MDE and test it on a variety of computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item numbers or
line numbers for a form or report (because the numbers will change any
time a record is added or deleted). Normally it is neither necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.
 
G

Guest

Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table fields.

Regards

..


John Nurick said:
We're getting there. I think I've misunderstood the exact situation with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument of
the DCount statement.



Thanks again Sir.
I tried your method, it works fine. But the major problems remains unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean the
main form every record. While it counts for all the records based in the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards, as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from 1.

B.
Sometimes I also noticed that you can understand the JobDes can be same.
Like for example "Machine Service Done", again next month the same JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



John Nurick said:
I'm sorry I haven't been clear. Let's start by eliminating one possible
source of confusion.

First, I need to be quite sure that you are using a form and subform to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in your
advise, but I could not understand. If you could be more clear about, then I
can have a try to do that. Let take a example below and advise accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I started
feeding the data.

Now I want to get advised at where I should add this field, I tried to add a
field as mentioned in your reply to the query mentioend above, but the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE. The best
thing would be to create an MDE and test it on a variety of computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item numbers or
line numbers for a form or report (because the numbers will change any
time a record is added or deleted). Normally it is neither necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.
 
K

Ken Snell [MVP]

Irshad -

John got called away on some business and asked me to step in for him. He
and I have discussed your information. Let me see if we're understanding
correctly.

You want the subform to display the records from DataTabB table. This table
has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table (except
you want JobNo to be a sequence of numbers), meaning that you have no
primary key in this table. And it appears that no combination of JobDes and
JobChgs would be unique -- the values in these fields can be the same in
many records.

JobNo contains a sequence (no gaps) of numbers that serve to "number" the
records. Each set of records for a specific FormNo value start at 1 and
increment from there. So there is a record where JobNo = 1 for each FormNo
value.

Currently, your users must type a number into JobNo to provide it with its
value for each record. But, when a record is deleted from DataTabB table,
the users then must go back and renumber the JobNo values so that they again
are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo instead of
storing it in the table. He had suggested this based on an assumption (which
I would have made as well) that the record in DataTabB had other uniqueness
to it. It now appears that the only "unique" value for these records is the
combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query whenever a
user deletes a record from the subform. This could be done by using the
AfterDelConfirm event of the form that is the subform; and would be done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

Irshad Alam said:
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table fields.

Regards

.


John Nurick said:
We're getting there. I think I've misunderstood the exact situation with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument of
the DCount statement.



Thanks again Sir.
I tried your method, it works fine. But the major problems remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean the
main form every record. While it counts for all the records based in the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from
1.

B.
Sometimes I also noticed that you can understand the JobDes can be same.
Like for example "Machine Service Done", again next month the same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and subform
to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in
your
advise, but I could not understand. If you could be more clear about,
then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I
started
feeding the data.

Now I want to get advised at where I should add this field, I tried
to add a
field as mentioned in your reply to the query mentioend above, but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE. The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item
numbers or
line numbers for a form or report (because the numbers will change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.
 
K

Ken Snell [MVP]

Sorry.. slight error in the SQL query. What I posted is not updatable. Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Irshad -

John got called away on some business and asked me to step in for him. He
and I have discussed your information. Let me see if we're understanding
correctly.

You want the subform to display the records from DataTabB table. This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table (except
you want JobNo to be a sequence of numbers), meaning that you have no
primary key in this table. And it appears that no combination of JobDes
and JobChgs would be unique -- the values in these fields can be the same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to "number" the
records. Each set of records for a specific FormNo value start at 1 and
increment from there. So there is a record where JobNo = 1 for each FormNo
value.

Currently, your users must type a number into JobNo to provide it with its
value for each record. But, when a record is deleted from DataTabB table,
the users then must go back and renumber the JobNo values so that they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo instead of
storing it in the table. He had suggested this based on an assumption
(which I would have made as well) that the record in DataTabB had other
uniqueness to it. It now appears that the only "unique" value for these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query whenever a
user deletes a record from the subform. This could be done by using the
AfterDelConfirm event of the form that is the subform; and would be done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

Irshad Alam said:
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table fields.

Regards

.


John Nurick said:
We're getting there. I think I've misunderstood the exact situation with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean
the
main form every record. While it counts for all the records based in
the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from
1.

B.
Sometimes I also noticed that you can understand the JobDes can be
same.
Like for example "Machine Service Done", again next month the same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and subform
to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I
started
feeding the data.

Now I want to get advised at where I should add this field, I tried
to add a
field as mentioned in your reply to the query mentioend above, but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE. The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item
numbers or
line numbers for a form or report (because the numbers will change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.
 
G

Guest

Sorry Sir, I am late in reply as was not in touch with internet due to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event. But it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables, queries
and form, with the code written to your Email Id, so that you just have a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query as
advised to me earlier, simply selected the JobNo field in the Query. Then
opened the Opened the Form onDelete Event pasted the code. but it does not
work, neither it produces error.

Please advise. Regards.



Ken Snell said:
Sorry.. slight error in the SQL query. What I posted is not updatable. Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Irshad -

John got called away on some business and asked me to step in for him. He
and I have discussed your information. Let me see if we're understanding
correctly.

You want the subform to display the records from DataTabB table. This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table (except
you want JobNo to be a sequence of numbers), meaning that you have no
primary key in this table. And it appears that no combination of JobDes
and JobChgs would be unique -- the values in these fields can be the same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to "number" the
records. Each set of records for a specific FormNo value start at 1 and
increment from there. So there is a record where JobNo = 1 for each FormNo
value.

Currently, your users must type a number into JobNo to provide it with its
value for each record. But, when a record is deleted from DataTabB table,
the users then must go back and renumber the JobNo values so that they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo instead of
storing it in the table. He had suggested this based on an assumption
(which I would have made as well) that the record in DataTabB had other
uniqueness to it. It now appears that the only "unique" value for these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query whenever a
user deletes a record from the subform. This could be done by using the
AfterDelConfirm event of the form that is the subform; and would be done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

Irshad Alam said:
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact situation with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean
the
main form every record. While it counts for all the records based in
the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged the
sequence number in order. But When I moved to next record of Main form,
Posted 2 records and found that Its is bring the number from 4 onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start from
1.

B.
Sometimes I also noticed that you can understand the JobDes can be
same.
Like for example "Machine Service Done", again next month the same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and subform
to
view the data, and not a datasheet with subdatasheets which drop down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the tables
individually. Select one or more fields to sort on (we have to ensure
that the records will always appear in the same order); for now I'll
assume you're sorting on [JobDes] and that it's a text field. Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use it in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and I
started
feeding the data.

Now I want to get advised at where I should add this field, I tried
to add a
field as mentioned in your reply to the query mentioend above, but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE. The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems that the
purpose of these numbers is simply to provide sequential item
numbers or
line numbers for a form or report (because the numbers will change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them whenever
needed, in a query, using a technique such as the one in my last
message.
 
K

Ken Snell [MVP]

The Delete event is not where I would run this code. I would run it in the
form's AfterDelConfirm event, where you would need to test that a delete was
actually done by checking the value of the Status integer that is provided
by ACCESS to this procedure.

The Delete event occurs before the record is deleted. From the Help File:
"Occurs when the user performs some action, such as pressing the DEL key, to
delete a record, but before the record is actually deleted." So, running
this query in the Delete event means that it does the update before the
deletion, so you won't see the result.

Put the code in the AfterDelConfirm event and then see if it works as
desired. Post back to let us know "yes" or "no". If "no", tell us what the
data looked like before the deletion and after the deletion so that we can
identify what may need to be changed with the query.
--

Ken Snell
<MS ACCESS MVP>



Irshad Alam said:
Sorry Sir, I am late in reply as was not in touch with internet due to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event. But
it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables, queries
and form, with the code written to your Email Id, so that you just have a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query as
advised to me earlier, simply selected the JobNo field in the Query. Then
opened the Opened the Form onDelete Event pasted the code. but it does not
work, neither it produces error.

Please advise. Regards.



Ken Snell said:
Sorry.. slight error in the SQL query. What I posted is not updatable.
Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Irshad -

John got called away on some business and asked me to step in for him.
He
and I have discussed your information. Let me see if we're
understanding
correctly.

You want the subform to display the records from DataTabB table. This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table
(except
you want JobNo to be a sequence of numbers), meaning that you have no
primary key in this table. And it appears that no combination of JobDes
and JobChgs would be unique -- the values in these fields can be the
same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to "number"
the
records. Each set of records for a specific FormNo value start at 1 and
increment from there. So there is a record where JobNo = 1 for each
FormNo
value.

Currently, your users must type a number into JobNo to provide it with
its
value for each record. But, when a record is deleted from DataTabB
table,
the users then must go back and renumber the JobNo values so that they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo instead
of
storing it in the table. He had suggested this based on an assumption
(which I would have made as well) that the record in DataTabB had other
uniqueness to it. It now appears that the only "unique" value for these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query
whenever a
user deletes a record from the subform. This could be done by using the
AfterDelConfirm event of the form that is the subform; and would be
done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post
back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes
the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table
fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact situation
with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument
of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean
the
main form every record. While it counts for all the records based in
the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged
the
sequence number in order. But When I moved to next record of Main
form,
Posted 2 records and found that Its is bring the number from 4
onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start
from
1.

B.
Sometimes I also noticed that you can understand the JobDes can be
same.
Like for example "Machine Service Done", again next month the same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and
subform
to
view the data, and not a datasheet with subdatasheets which drop
down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the
tables
individually. Select one or more fields to sort on (we have to
ensure
that the records will always appear in the same order); for now
I'll
assume you're sorting on [JobDes] and that it's a text field. Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS
JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in
stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should
show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use it
in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before
in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the
field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and
I
started
feeding the data.

Now I want to get advised at where I should add this field, I
tried
to add a
field as mentioned in your reply to the query mentioend above,
but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE.
The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems that
the
purpose of these numbers is simply to provide sequential item
numbers or
line numbers for a form or report (because the numbers will
change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them
whenever
needed, in a query, using a technique such as the one in my
last
message.
 
G

Guest

I made mistake in hurry while writing to you, Actually I posted the Code in
the AFterDelConfirm Event. I rechecked, It produces no result. It just
deletes. When I move to next record and come to this record again, It shows
the changes only.

The major problem remains unsolved. The number of the subform increment
field is not starting from 1, Please note. I did every step which I was
advised before. Its calculates all the record of the subform and produces
number, if the field value is same then it gives the same number to both.

Will wait for your further advise. Regards.



Ken Snell said:
The Delete event is not where I would run this code. I would run it in the
form's AfterDelConfirm event, where you would need to test that a delete was
actually done by checking the value of the Status integer that is provided
by ACCESS to this procedure.

The Delete event occurs before the record is deleted. From the Help File:
"Occurs when the user performs some action, such as pressing the DEL key, to
delete a record, but before the record is actually deleted." So, running
this query in the Delete event means that it does the update before the
deletion, so you won't see the result.

Put the code in the AfterDelConfirm event and then see if it works as
desired. Post back to let us know "yes" or "no". If "no", tell us what the
data looked like before the deletion and after the deletion so that we can
identify what may need to be changed with the query.
--

Ken Snell
<MS ACCESS MVP>



Irshad Alam said:
Sorry Sir, I am late in reply as was not in touch with internet due to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event. But
it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables, queries
and form, with the code written to your Email Id, so that you just have a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query as
advised to me earlier, simply selected the JobNo field in the Query. Then
opened the Opened the Form onDelete Event pasted the code. but it does not
work, neither it produces error.

Please advise. Regards.



Ken Snell said:
Sorry.. slight error in the SQL query. What I posted is not updatable.
Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Irshad -

John got called away on some business and asked me to step in for him.
He
and I have discussed your information. Let me see if we're
understanding
correctly.

You want the subform to display the records from DataTabB table. This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table
(except
you want JobNo to be a sequence of numbers), meaning that you have no
primary key in this table. And it appears that no combination of JobDes
and JobChgs would be unique -- the values in these fields can be the
same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to "number"
the
records. Each set of records for a specific FormNo value start at 1 and
increment from there. So there is a record where JobNo = 1 for each
FormNo
value.

Currently, your users must type a number into JobNo to provide it with
its
value for each record. But, when a record is deleted from DataTabB
table,
the users then must go back and renumber the JobNo values so that they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo instead
of
storing it in the table. He had suggested this based on an assumption
(which I would have made as well) that the record in DataTabB had other
uniqueness to it. It now appears that the only "unique" value for these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query
whenever a
user deletes a record from the subform. This could be done by using the
AfterDelConfirm event of the form that is the subform; and would be
done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post
back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that makes
the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table
fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact situation
with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where" argument
of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I mean
the
main form every record. While it counts for all the records based in
the
DataTabB.
On first record of Main form, I posted three records. Its showed the
sequence number. Then I deleted one. reopened the form. It arranged
the
sequence number in order. But When I moved to next record of Main
form,
Posted 2 records and found that Its is bring the number from 4
onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start
from
1.

B.
Sometimes I also noticed that you can understand the JobDes can be
same.
Like for example "Machine Service Done", again next month the same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and
subform
to
view the data, and not a datasheet with subdatasheets which drop
down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the
tables
individually. Select one or more fields to sort on (we have to
ensure
that the records will always appear in the same order); for now
I'll
assume you're sorting on [JobDes] and that it's a text field. Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS
JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in
stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field should
show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use it
in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave before
in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the
field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type. and
I
started
feeding the data.

Now I want to get advised at where I should add this field, I
tried
to add a
field as mentioned in your reply to the query mentioend above,
but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE.
The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems that
the
purpose of these numbers is simply to provide sequential item
numbers or
line numbers for a form or report (because the numbers will
change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them
whenever
needed, in a query, using a technique such as the one in my
last
message.
 
K

Ken Snell [MVP]

Then there must be some aspect of the data that we're not understanding
here.

Please post examples of the data before and after the deletion, including
what the data look like after the AfterDelConfirm code runs.
--

Ken Snell
<MS ACCESS MVP>


Irshad Alam said:
I made mistake in hurry while writing to you, Actually I posted the Code in
the AFterDelConfirm Event. I rechecked, It produces no result. It just
deletes. When I move to next record and come to this record again, It
shows
the changes only.

The major problem remains unsolved. The number of the subform increment
field is not starting from 1, Please note. I did every step which I was
advised before. Its calculates all the record of the subform and produces
number, if the field value is same then it gives the same number to both.

Will wait for your further advise. Regards.



Ken Snell said:
The Delete event is not where I would run this code. I would run it in
the
form's AfterDelConfirm event, where you would need to test that a delete
was
actually done by checking the value of the Status integer that is
provided
by ACCESS to this procedure.

The Delete event occurs before the record is deleted. From the Help File:
"Occurs when the user performs some action, such as pressing the DEL key,
to
delete a record, but before the record is actually deleted." So, running
this query in the Delete event means that it does the update before the
deletion, so you won't see the result.

Put the code in the AfterDelConfirm event and then see if it works as
desired. Post back to let us know "yes" or "no". If "no", tell us what
the
data looked like before the deletion and after the deletion so that we
can
identify what may need to be changed with the query.
--

Ken Snell
<MS ACCESS MVP>



Irshad Alam said:
Sorry Sir, I am late in reply as was not in touch with internet due to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event.
But
it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables,
queries
and form, with the code written to your Email Id, so that you just have
a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query as
advised to me earlier, simply selected the JobNo field in the Query.
Then
opened the Opened the Form onDelete Event pasted the code. but it does
not
work, neither it produces error.

Please advise. Regards.



:

Sorry.. slight error in the SQL query. What I posted is not updatable.
Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Irshad -

John got called away on some business and asked me to step in for
him.
He
and I have discussed your information. Let me see if we're
understanding
correctly.

You want the subform to display the records from DataTabB table.
This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table
(except
you want JobNo to be a sequence of numbers), meaning that you have
no
primary key in this table. And it appears that no combination of
JobDes
and JobChgs would be unique -- the values in these fields can be the
same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to
"number"
the
records. Each set of records for a specific FormNo value start at 1
and
increment from there. So there is a record where JobNo = 1 for each
FormNo
value.

Currently, your users must type a number into JobNo to provide it
with
its
value for each record. But, when a record is deleted from DataTabB
table,
the users then must go back and renumber the JobNo values so that
they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo
instead
of
storing it in the table. He had suggested this based on an
assumption
(which I would have made as well) that the record in DataTabB had
other
uniqueness to it. It now appears that the only "unique" value for
these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query
whenever a
user deletes a record from the subform. This could be done by using
the
AfterDelConfirm event of the form that is the subform; and would be
done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post
back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that
makes
the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table
fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact
situation
with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where"
argument
of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems
remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I
mean
the
main form every record. While it counts for all the records based
in
the
DataTabB.
On first record of Main form, I posted three records. Its showed
the
sequence number. Then I deleted one. reopened the form. It
arranged
the
sequence number in order. But When I moved to next record of Main
form,
Posted 2 records and found that Its is bring the number from 4
onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start
from
1.

B.
Sometimes I also noticed that you can understand the JobDes can
be
same.
Like for example "Machine Service Done", again next month the
same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my
need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and
subform
to
view the data, and not a datasheet with subdatasheets which
drop
down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the
tables
individually. Select one or more fields to sort on (we have to
ensure
that the records will always appear in the same order); for now
I'll
assume you're sorting on [JobDes] and that it's a text field.
Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something
like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to
copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS
JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in
stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field
should
show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use
it
in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave
before
in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the
field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type.
and
I
started
feeding the data.

Now I want to get advised at where I should add this field, I
tried
to add a
field as mentioned in your reply to the query mentioend above,
but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE.
The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems
that
the
purpose of these numbers is simply to provide sequential
item
numbers or
line numbers for a form or report (because the numbers will
change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them
whenever
needed, in a query, using a technique such as the one in my
last
message.
 
G

Guest

Hi Sir,
As per instruction and code, after delete the JobNo field numbering should
be arranged in a sequence from 1 onwards. But it does not.
Secondly I cannot find any changes when I delete a record from Subform.
The code is working perfect or not, it is also been not checked neither the
changes occurs neither it produces error.
regards.


Ken Snell said:
Then there must be some aspect of the data that we're not understanding
here.

Please post examples of the data before and after the deletion, including
what the data look like after the AfterDelConfirm code runs.
--

Ken Snell
<MS ACCESS MVP>


Irshad Alam said:
I made mistake in hurry while writing to you, Actually I posted the Code in
the AFterDelConfirm Event. I rechecked, It produces no result. It just
deletes. When I move to next record and come to this record again, It
shows
the changes only.

The major problem remains unsolved. The number of the subform increment
field is not starting from 1, Please note. I did every step which I was
advised before. Its calculates all the record of the subform and produces
number, if the field value is same then it gives the same number to both.

Will wait for your further advise. Regards.



Ken Snell said:
The Delete event is not where I would run this code. I would run it in
the
form's AfterDelConfirm event, where you would need to test that a delete
was
actually done by checking the value of the Status integer that is
provided
by ACCESS to this procedure.

The Delete event occurs before the record is deleted. From the Help File:
"Occurs when the user performs some action, such as pressing the DEL key,
to
delete a record, but before the record is actually deleted." So, running
this query in the Delete event means that it does the update before the
deletion, so you won't see the result.

Put the code in the AfterDelConfirm event and then see if it works as
desired. Post back to let us know "yes" or "no". If "no", tell us what
the
data looked like before the deletion and after the deletion so that we
can
identify what may need to be changed with the query.
--

Ken Snell
<MS ACCESS MVP>



Sorry Sir, I am late in reply as was not in touch with internet due to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event.
But
it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables,
queries
and form, with the code written to your Email Id, so that you just have
a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query as
advised to me earlier, simply selected the JobNo field in the Query.
Then
opened the Opened the Form onDelete Event pasted the code. but it does
not
work, neither it produces error.

Please advise. Regards.



:

Sorry.. slight error in the SQL query. What I posted is not updatable.
Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

Irshad -

John got called away on some business and asked me to step in for
him.
He
and I have discussed your information. Let me see if we're
understanding
correctly.

You want the subform to display the records from DataTabB table.
This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this table
(except
you want JobNo to be a sequence of numbers), meaning that you have
no
primary key in this table. And it appears that no combination of
JobDes
and JobChgs would be unique -- the values in these fields can be the
same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to
"number"
the
records. Each set of records for a specific FormNo value start at 1
and
increment from there. So there is a record where JobNo = 1 for each
FormNo
value.

Currently, your users must type a number into JobNo to provide it
with
its
value for each record. But, when a record is deleted from DataTabB
table,
the users then must go back and renumber the JobNo values so that
they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo
instead
of
storing it in the table. He had suggested this based on an
assumption
(which I would have made as well) that the record in DataTabB had
other
uniqueness to it. It now appears that the only "unique" value for
these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query
whenever a
user deletes a record from the subform. This could be done by using
the
AfterDelConfirm event of the form that is the subform; and would be
done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database. Post
back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that
makes
the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table
fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact
situation
with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record unique?

We need to include both or all these fields in the "where"
argument
of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems
remains
unsolved.
A.
The subform JobNo should start always from 1 on every record, I
mean
the
main form every record. While it counts for all the records based
in
the
DataTabB.
On first record of Main form, I posted three records. Its showed
the
sequence number. Then I deleted one. reopened the form. It
arranged
the
sequence number in order. But When I moved to next record of Main
form,
Posted 2 records and found that Its is bring the number from 4
onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should start
from
1.

B.
Sometimes I also noticed that you can understand the JobDes can
be
same.
Like for example "Machine Service Done", again next month the
same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my
need.



:

I'm sorry I haven't been clear. Let's start by eliminating one
possible
source of confusion.

First, I need to be quite sure that you are using a form and
subform
to
view the data, and not a datasheet with subdatasheets which
drop
down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all the
tables
individually. Select one or more fields to sort on (we have to
ensure
that the records will always appear in the same order); for now
I'll
assume you're sorting on [JobDes] and that it's a text field.
Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something
like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself, as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just to
copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS
JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name in
stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field
should
show
sequential numbers.

5) Now use this query as the RecordSource for your subform (at
present
the RecordSource is probably just DataTabB). You can also use
it
in
subreports or other places you need to generate the sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave
before
in
your
advise, but I could not understand. If you could be more clear
about, then I
can have a try to do that. Let take a example below and advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all the
field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform type.
and
I
started
feeding the data.

Now I want to get advised at where I should add this field, I
tried
to add a
field as mentioned in your reply to the query mentioend above,
but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an MDE.
The
best
thing would be to create an MDE and test it on a variety of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems
that
the
purpose of these numbers is simply to provide sequential
item
numbers or
line numbers for a form or report (because the numbers will
change
any
time a record is added or deleted). Normally it is neither
necessary nor
desirable to store such numbers: one just generates them
whenever
needed, in a query, using a technique such as the one in my
last
message.
 
K

Ken Snell [MVP]

Would you please post examples of the records' data before any deletion is
done, and then after the deletion is done? It seems clear that there is some
aspect of the data that we're not understanding, and seeing examples of the
data may help identify what is missing from our suggested solutions.

--

Ken Snell
<MS ACCESS MVP>

Irshad Alam said:
Hi Sir,
As per instruction and code, after delete the JobNo field numbering should
be arranged in a sequence from 1 onwards. But it does not.
Secondly I cannot find any changes when I delete a record from Subform.
The code is working perfect or not, it is also been not checked neither
the
changes occurs neither it produces error.
regards.


Ken Snell said:
Then there must be some aspect of the data that we're not understanding
here.

Please post examples of the data before and after the deletion, including
what the data look like after the AfterDelConfirm code runs.
--

Ken Snell
<MS ACCESS MVP>


Irshad Alam said:
I made mistake in hurry while writing to you, Actually I posted the Code
in
the AFterDelConfirm Event. I rechecked, It produces no result. It just
deletes. When I move to next record and come to this record again, It
shows
the changes only.

The major problem remains unsolved. The number of the subform increment
field is not starting from 1, Please note. I did every step which I was
advised before. Its calculates all the record of the subform and
produces
number, if the field value is same then it gives the same number to
both.

Will wait for your further advise. Regards.



:

The Delete event is not where I would run this code. I would run it in
the
form's AfterDelConfirm event, where you would need to test that a
delete
was
actually done by checking the value of the Status integer that is
provided
by ACCESS to this procedure.

The Delete event occurs before the record is deleted. From the Help
File:
"Occurs when the user performs some action, such as pressing the DEL
key,
to
delete a record, but before the record is actually deleted." So,
running
this query in the Delete event means that it does the update before
the
deletion, so you won't see the result.

Put the code in the AfterDelConfirm event and then see if it works as
desired. Post back to let us know "yes" or "no". If "no", tell us what
the
data looked like before the deletion and after the deletion so that we
can
identify what may need to be changed with the query.
--

Ken Snell
<MS ACCESS MVP>



Sorry Sir, I am late in reply as was not in touch with internet due
to
telephone connections problem.

I have tried the method you showed to apply the code onDelete event.
But
it
does not work.

But you understood my question/query perfectly.

Is is possible, that I make a small mdb just with required tables,
queries
and form, with the code written to your Email Id, so that you just
have
a
look that actual mistake i am making.

Based on your suggestion, I removed the extra codes written in Query
as
advised to me earlier, simply selected the JobNo field in the Query.
Then
opened the Opened the Form onDelete Event pasted the code. but it
does
not
work, neither it produces error.

Please advise. Regards.



:

Sorry.. slight error in the SQL query. What I posted is not
updatable.
Try
this instead:


strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=DCount(""JobNo"", ""DataTabB"", " & _
"""FormNo=" & Me.FormNo & _
" AND JobNo<="" & A.JobNo & "");"



--

Ken Snell
<MS ACCESS MVP>

message
Irshad -

John got called away on some business and asked me to step in for
him.
He
and I have discussed your information. Let me see if we're
understanding
correctly.

You want the subform to display the records from DataTabB table.
This
table has four fields:
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should come)
JobDes
JobChgs

You also have stated that there is no "unique" field in this
table
(except
you want JobNo to be a sequence of numbers), meaning that you
have
no
primary key in this table. And it appears that no combination of
JobDes
and JobChgs would be unique -- the values in these fields can be
the
same
in many records.

JobNo contains a sequence (no gaps) of numbers that serve to
"number"
the
records. Each set of records for a specific FormNo value start at
1
and
increment from there. So there is a record where JobNo = 1 for
each
FormNo
value.

Currently, your users must type a number into JobNo to provide it
with
its
value for each record. But, when a record is deleted from
DataTabB
table,
the users then must go back and renumber the JobNo values so that
they
again are sequential without any gaps.

Is this correct?

John and I have discussed the use of a display field for JobNo
instead
of
storing it in the table. He had suggested this based on an
assumption
(which I would have made as well) that the record in DataTabB had
other
uniqueness to it. It now appears that the only "unique" value for
these
records is the combination of the FormNo and JobNo values.

Thus, I think you'll need to have your form run an update query
whenever a
user deletes a record from the subform. This could be done by
using
the
AfterDelConfirm event of the form that is the subform; and would
be
done
only if a record was deleted.


Private Sub Form_AfterDelConfirm(Status As Integer)
Dim dbs As DAO.Database
Dim strSQL As String
Select Case Status
Case acDeleteOK
Set dbs = CurrentDb
strSQL = "UPDATE DataTabB AS A " & _
"SET A.JobNo=(SELECT COUNT(H.[JobNo]) " & _
"FROM DataTabB AS H " & _
"WHERE H.FormNo=" & Me.FormNo & _
" AND H.JobNo<=A.JobNo);"
dbs.Execute strSQL, dbFailOnError
DoEvents
Me.Requery
dbs.Close
Set dbs = Nothing

Case acDeleteCancel, acDeleteUserCancel
' do nothing
End Select
End Sub

This has not been tested, so try it on a copy of your database.
Post
back
with comments/results.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks.
Please find below :

"FormNo" is the field on the form that links with Subform that
makes
the
relationship.

There is no such field which is unique in the subform.

Please refer to my previous post to get more details of my Table
fields.

Regards

.


:

We're getting there. I think I've misunderstood the exact
situation
with
the form and subform. Can you tell me:

- what are the fields that link the main form to the subform?

- what field or fields in qryTabBSubform make each record
unique?

We need to include both or all these fields in the "where"
argument
of
the DCount statement.



On Wed, 23 Feb 2005 22:35:04 -0800, "Irshad Alam"

Thanks again Sir.
I tried your method, it works fine. But the major problems
remains
unsolved.
A.
The subform JobNo should start always from 1 on every record,
I
mean
the
main form every record. While it counts for all the records
based
in
the
DataTabB.
On first record of Main form, I posted three records. Its
showed
the
sequence number. Then I deleted one. reopened the form. It
arranged
the
sequence number in order. But When I moved to next record of
Main
form,
Posted 2 records and found that Its is bring the number from 4
onwards,
as in
the first record it was 3 records in subform.
I wanted on every main Form record the subform JobNo should
start
from
1.

B.
Sometimes I also noticed that you can understand the JobDes
can
be
same.
Like for example "Machine Service Done", again next month the
same
JobDes can
come. At this situation is brings the same sequence number.

Please advise, what to do next to bring it perfect as per my
need.



:

I'm sorry I haven't been clear. Let's start by eliminating
one
possible
source of confusion.

First, I need to be quite sure that you are using a form and
subform
to
view the data, and not a datasheet with subdatasheets which
drop
down
when you click the + sign next to the records.

Assuming you are using a form and subform:

1) Create a new Select query based on DataTabB. Select all
the
tables
individually. Select one or more fields to sort on (we have
to
ensure
that the records will always appear in the same order); for
now
I'll
assume you're sorting on [JobDes] and that it's a text
field.
Save
the
query (let's call it qryTabBSubform).

2) Switch the query into SQL view. It should look something
like
this,
although the line breaks will be different.

SELECT DataTabB.FormNo,
DataTabB.JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

Now edit it so that "DataTab.JobNo," is on a line by itself,
as
above.

3) Replace "DataTabB.JobNo," with this. It's simplest just
to
copy
and
paste the whole line.

DCount("FormNo","DataTabB","JobDes<='" & [JobDes] &"'") AS
JobNo,

The query should now look like this (which I've tested on my
computer):

SELECT DataTabB.FormNo,
DCount("FormNo","DataTabB","JobDes<=" & [JobDes]) AS
JobNo,
DataTabB.JobDes, DataTabB.JobChgs
FROM DataTabB
ORDER BY DataTabB.JobDes;

If you are sorting on some other field, use that field name
in
stead
of
JobDes in the DCount() expression.

4) Save and preview the query. The calculated JobNo field
should
show
sequential numbers.

5) Now use this query as the RecordSource for your subform
(at
present
the RecordSource is probably just DataTabB). You can also
use
it
in
subreports or other places you need to generate the
sequential
numbers.


On Wed, 23 Feb 2005 00:23:02 -0800, "Irshad Alam"

Sir, Thank you for your valuable advise.
Actually I read again and again your tips which you gave
before
in
your
advise, but I could not understand. If you could be more
clear
about, then I
can have a try to do that. Let take a example below and
advise
accordingly :

DataTabA (Table Name)
FormNo (primary & numeric Type)
MachSerNo
MachBrand
JRemarks


DataTabB
FormNo (numeric & have relationship )
JobNo (this is the field where number increment should
come)
JobDes
JobChgs

I have made a query "JobQryA", in whcih i have taken all
the
field
of
DataTabA and all the field of DataTabB except FormNo.
So when I make form, It makes automatically the subform
type.
and
I
started
feeding the data.

Now I want to get advised at where I should add this field,
I
tried
to add a
field as mentioned in your reply to the query mentioend
above,
but
the query
fails to open and error.

Please advise.




:

I'm honestly not sure whether your code will work in an
MDE.
The
best
thing would be to create an MDE and test it on a variety
of
computers to
see what happens.

But basically I feel uneasy about your approach. It seems
that
the
purpose of these numbers is simply to provide sequential
item
numbers or
line numbers for a form or report (because the numbers
will
change
any
time a record is added or deleted). Normally it is
neither
necessary nor
desirable to store such numbers: one just generates them
whenever
needed, in a query, using a technique such as the one in
my
last
message.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top