LISA GEORGE
asked on
rownumber to auto populate in subform
Hi, I have main form work order which contains continuous subform workorderpayitem...this means each workorder will have many workorderpayitemsID
Now i added new field called rownum in workoderpayitem table
this row num should display row numbers in the subform but it should display rownumbers as per workorder not numbers as per workorderpayitem form
to be more precise,, say i have workorder1 which has 5xworkorderpayitems so now that row number should display 1,2,3,4,5 in that bound textbox rownum only for that workorderpayitems which are there in workorder
for this i have created function Public Function ShowRecordNumber() As Integer
Me.RecordsetClone.Bookmark
ShowRecordNumber = (Me.RecordsetClone.Absolut
End Function
and i am calling that function in rownum before update event
me.rownum.value = ShowRecordNumber()
but it's not displaying any value in the text box
so i tried to call that function in sql query ---ShowRecordNumber() as rownum
but it's throwing up error undefined function
FYI i place this function in subform vb code at the top
apart from this i also used DCount("*","WorkOrderPayIt
also in subform user may delete some records so that means it must auto change that value(say if record 3 is selected then rownum 3 must point to record4 now..i don't want that rownum3 to be empty or null)
this rownum must update to existing forms as well for new forms
please let me know how to write code fo this and which event i need to use
Now i added new field called rownum in workoderpayitem table
this row num should display row numbers in the subform but it should display rownumbers as per workorder not numbers as per workorderpayitem form
to be more precise,, say i have workorder1 which has 5xworkorderpayitems so now that row number should display 1,2,3,4,5 in that bound textbox rownum only for that workorderpayitems which are there in workorder
for this i have created function Public Function ShowRecordNumber() As Integer
Me.RecordsetClone.Bookmark
ShowRecordNumber = (Me.RecordsetClone.Absolut
End Function
and i am calling that function in rownum before update event
me.rownum.value = ShowRecordNumber()
but it's not displaying any value in the text box
so i tried to call that function in sql query ---ShowRecordNumber() as rownum
but it's throwing up error undefined function
FYI i place this function in subform vb code at the top
apart from this i also used DCount("*","WorkOrderPayIt
also in subform user may delete some records so that means it must auto change that value(say if record 3 is selected then rownum 3 must point to record4 now..i don't want that rownum3 to be empty or null)
this rownum must update to existing forms as well for new forms
please let me know how to write code fo this and which event i need to use
1. What is the purpose of the rownumber?
2. Should the row number for row x remain constant regardless of how the recordset is sorted or should the row number be constantly recalculated?
3. Is the row number ever printed on anything? Will it be confusing if it changes from one view of the form to another?
2. Should the row number for row x remain constant regardless of how the recordset is sorted or should the row number be constantly recalculated?
3. Is the row number ever printed on anything? Will it be confusing if it changes from one view of the form to another?
ASKER
@parhartman
i want this rownumber field to re-oder rows if user clicks on button
i have vba code that will rearrange record set based on row number so that's why i want to have that field in form though it's not visible to user
i want this rownumber field to re-oder rows if user clicks on button
i have vba code that will rearrange record set based on row number so that's why i want to have that field in form though it's not visible to user
You didn't quite answer my questions.
Do you want the rownumber for any given row to be static and NEVER change? If that is the case, the rownumber MUST be assigned when the row is added to the table rather than on the fly in a query.
If you are using an autonumber for the unique ID of this data, that represents the sequence in which the data was added so even if it isn't 1,2,3, etc, it is STILL the data entry order i.e. 789, 3776, 4456, etc and sorting on it will reorder the data into data entry sequence. Which sounds like what you are asking for.
Do you want the rownumber for any given row to be static and NEVER change? If that is the case, the rownumber MUST be assigned when the row is added to the table rather than on the fly in a query.
If you are using an autonumber for the unique ID of this data, that represents the sequence in which the data was added so even if it isn't 1,2,3, etc, it is STILL the data entry order i.e. 789, 3776, 4456, etc and sorting on it will reorder the data into data entry sequence. Which sounds like what you are asking for.
ASKER
@pathartman
yes i want rownumber to be assigned on the fly like when user enters a row then row number should be auto incremented for that particular form(1,2,3) not based on unique ID
i want this way
intially rownum data
1 aa
2 bb
3 cc
say if rownum 2 is deleted then it has to be like 1 aa
2 cc
yes i want rownumber to be assigned on the fly like when user enters a row then row number should be auto incremented for that particular form(1,2,3) not based on unique ID
i want this way
intially rownum data
1 aa
2 bb
3 cc
say if rownum 2 is deleted then it has to be like 1 aa
2 cc
My RecordNumber function will do exactly that. You can read the full story in my article here:
Sequential Rows in Microsoft Access
It also has a demo for you to download and play with.
Sequential Rows in Microsoft Access
It also has a demo for you to download and play with.
Check my post here that contains a running sample
Lisa,
You said YES that you want the number to be static and then proceeded to explain that it should change.
You also said that you did not want to display the information, you merely wanted it to be available to sort back into original order. I explained how to do that by using the autonumber and NOTHING ELSE. If you want to incur the overhead of calculating a value that you are not going to display then either Gus or John's solutions will work. If you prefer a more efficient method, simply sort on the autonumber to return to original sequence.
at
You said YES that you want the number to be static and then proceeded to explain that it should change.
You also said that you did not want to display the information, you merely wanted it to be available to sort back into original order. I explained how to do that by using the autonumber and NOTHING ELSE. If you want to incur the overhead of calculating a value that you are not going to display then either Gus or John's solutions will work. If you prefer a more efficient method, simply sort on the autonumber to return to original sequence.
at
ASKER
i ihave used this code in after update of control in the form to get row numbers
If IsNull(Me.RowNo) = True Then
Me.RowNo.Value = Nz([RowNo].[Value], Me.CurrentRecord)
End If
If IsNull(Me.RowNo) = True Then
Me.RowNo.Value = Nz([RowNo].[Value], Me.CurrentRecord)
End If
That could be reduced to:
If IsNull(Me!RowNo.Value) = True Then
Me!RowNo.Value = Me.CurrentRecord
End If This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
ASKER
i created unbound text box(num) in subform and control source for that is =ShowRecordNumber()
now this num is showing row numbers of the form
but when i tried to assign this value to my field rownum it's just showing 1 in 1st row for rest its just showing blank
in sub-form load event(i tried in before update event as well)
Me.RowNum.Value = Me.num.Value
is something wrong in my code?