Initializing unique values for fields (Datasheet Form)

J

jemartin80

New member
Local time
Today, 12:20
Joined
Jan 8, 2009
Messages
7
The issue I am having seems like it should be simple, but they often do and seldom are. When I load a datasheet form I want to update one field (CompTime) for each record with a different value (sTotalTime). I am using the Load event for the form to run a code that is meant to generate the correct value for each record and store set sTotalTime to that value. Then it executes

CompTime.value = sTotalTime

This is, of course, updating the CompTime field for every record in the form. How can I proceed.
 
Use the On current event instead...

Load is on form load, while current is on every active record

ALso you will want to check if you are not overwriting an existing value (isnull)
 
I just gave that a try and it is still setting the value for all the fields to the same value. When I debug, it is only running through the code one time, instead of once for each record.
 
I'm sorry, that was very unclear. It is still setting the value for the CompTime field the same for every record on the datasheet form.
 
Namlian understood you at least - I myself do not.
 
I will try to be more clear. When I open my datasheet form the code currently sets the value of the unbound CompTime field to the same value for every record. I would like it to set a different value in the CompTime field for each record.
The following is the code I am using for the Current event of the form.

Private Sub Form_Current()

'Begin: Totals the Time for each event
Dim dbsTrax As dao.Database
Dim rstTimeInfo As dao.Recordset
Dim strSearch As String
Dim sTotalTime As Single

Set dbsTrax = CurrentDb
Set rstTimeInfo = dbsTrax.OpenRecordset("TimeTable", dbOpenDynaset)
strSearch = "[WOID] = " & Me![WO]
sTotalTime = 0

With rstTimeInfo
.FindFirst (strSearch)
sTotalTime = !DailyTime
Do
.FindNext (strSearch)
sTotalTime = nTotalTime + !DailyTime
Loop Until .NoMatch = True
End With

rstTimeInfo.Close
dbsTrax.Close

CompTime.Value = sTotalTime
'End: Totals time for each event

End Sub

Thank you for your time.
 
I can make no sense of this. I'm guessing CompTime is an unbound textbox. Your code calculates a total and then sets

CompTime.Value = total

You say you instead want it to show the value for each record instead of the total? But you do this in a loop - so you want the textbox to flash to each value for each record, too fast for the human eye to see?
 
jemartin80 said:
the datasheet form.
Click to expand...
On form calculations only work if you have a form view, not if you have tabular or datasheet.
The problem is the code only uses "Me![WO]" which means that it only works on the currently selected record = all records have the same value, as there always is just one record selected.

If you want to have a line by line total, you have to put together a query that you use for your input of your form. Down side for this you would need a "group by" query, this would make your recordset read only.... doubt that is what you want...

Please use [ code] and [ /code] (without the spaces) around code when you post it on the forum
Code: 
Private Sub Form_Current()
    
    'Begin: Totals the Time for each event
    Dim dbsTrax As dao.Database
    Dim rstTimeInfo As dao.Recordset
    Dim strSearch As String
    Dim sTotalTime As Single
        
    Set dbsTrax = CurrentDb
    Set rstTimeInfo = dbsTrax.OpenRecordset("TimeTable", dbOpenDynaset)
    strSearch = "[WOID] = " & Me![WO]
    sTotalTime = 0
    
    With rstTimeInfo
        .FindFirst (strSearch)
        sTotalTime = !DailyTime
        Do
            .FindNext (strSearch)
            sTotalTime = nTotalTime + !DailyTime
        Loop Until .NoMatch = True
    End With
    
    rstTimeInfo.Close
    dbsTrax.Close
    
    CompTime.Value = sTotalTime
    'End: Totals time for each event
    
End Sub

Doing a loop like this is awfull slow, instead just use a query if you want to do this at all...
Code: 
Set rstTimeInfo = dbsTrax.OpenRecordset(" Select sum([dailyTime]) as TotalTime " & vbnewline & _
" from  TimeTable " & vbnewline & _
" where [WOID] = " & Me![WO]
(this needs some layout work, but this is just quick code)

will do the same as your loop except be faster.
 
Jal, if you look closely, the value is not assigned to the unbound textbox until after the do loop is complete, so the values are not being overwritten before they can be seen. But thank you for your suggestion.

namliam, thank you for working with me on this. I am not very familiar with query commands and sql. When you say "group query" are you talking about the code you have shown me or is that something else I need to research? The CompTime field will be locked and disabled, so it will not matter if it is read only. Thanks again!
 
the group by query is 'basicaly' what I showed you, but one step further...

Code: 
Select WOID, sum([dailyTime]) as TotalTime 
from  TimeTable 
Group by WOID
That group by query makes the total for all your IDs that you can then join to the query feeding your form.

Note! Only only your total time but your whole record becomes read only!
 
Oh, well I can't have that. The rest of the record needs to be open to editing. So there is no way you know of to update that field with a different value for each record?
 
In any case, thank you for your help. I've learned something I can apply now. It may not do just what I wanted but its a start.
 
You will have to use a DSum or your function kindof thing on form to get your results.... Possibly use the DSum function in your query, this is (much) slower, but leaves your recordset editable.
 
You must log in or register to reply here.

Similar threads

D
Solved Highlighting Next Record After Current Record Filtered Out of Datasheet
Replies
17
Views
2,085
cheekybuddha
cheekybuddha
mloucel
Solved Continuous Form and Color Code
Replies
6
Views
398
mloucel
mloucel
I
Solved Error about assigning Null value when clicking away from deleted form field
Replies
15
Views
1,065
The_Doc_Man
The_Doc_Man
S
Solved Datasheet: Different back colors for columns and alternate row colors
Replies
16
Views
2,282
AHeyne
A
N
Solved (.Value) doesn't show textbox Value on Change() event
Replies
16
Views
1,772
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom