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.