If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DCount
I’ve been lost for days on this one. Any help is very much appreciated...
On the first form, I double click on a list box value. This action causes the second form to open with OpenArgs: DoCmd.OpenForm "frmFloorProgAudit", _ acNormal, , , acFormEdit, acWindowNormal, _ Me.FloorProgCriteriaID The second form has these open events: DoCmd.GoToRecord , , acNewRec _ Me.FloorProgCriteriaID = Me.OpenArgs On this second form’s current event, I’m using a DCount function to count the number of records that match the criteria of three fields: Me.txtCountOfObservations = DCount("*", _ "tblFloorProgAudit", "[AuditID] = " & _ Me.AuditID.Value & " And " & _ "[FloorProgCriteriaID] = " & _ Me.FloorProgCriteriaID.Value & " And " & _ "[FloorProgObservationID] = " & _ Me.FloorProgObservationID.Value & " And " & _ "[AuditorID] = '" & Me.AuditorID.Value & "'") Two of the fields are populated with default values in the form’s property sheet. These two field’s values are recognized by the DCount function, AuditID and AuditorID. Why does the FloorProgCriteriaID field return Null? And more importantly, how can I code this form properly so that DCount will correctly count the number of records that match the criteria of all three fields on the form’s current event? Michael -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
#2
|
|||
|
|||
DCount
"T5925MS via AccessMonster.com" u58316@uwe wrote in message
news:a4dbc4d86170b@uwe... I’ve been lost for days on this one. Any help is very much appreciated... On the first form, I double click on a list box value. This action causes the second form to open with OpenArgs: DoCmd.OpenForm "frmFloorProgAudit", _ acNormal, , , acFormEdit, acWindowNormal, _ Me.FloorProgCriteriaID The second form has these open events: DoCmd.GoToRecord , , acNewRec _ Me.FloorProgCriteriaID = Me.OpenArgs On this second form’s current event, I’m using a DCount function to count the number of records that match the criteria of three fields: Me.txtCountOfObservations = DCount("*", _ "tblFloorProgAudit", "[AuditID] = " & _ Me.AuditID.Value & " And " & _ "[FloorProgCriteriaID] = " & _ Me.FloorProgCriteriaID.Value & " And " & _ "[FloorProgObservationID] = " & _ Me.FloorProgObservationID.Value & " And " & _ "[AuditorID] = '" & Me.AuditorID.Value & "'") Two of the fields are populated with default values in the form’s property sheet. These two field’s values are recognized by the DCount function, AuditID and AuditorID. Why does the FloorProgCriteriaID field return Null? And more importantly, how can I code this form properly so that DCount will correctly count the number of records that match the criteria of all three fields on the form’s current event? Michael I imagine that, when your code in the Open event executes DoCmd.GoToRecord, the form's Current event fires before the next line that sets the value of Me.FloorProgCriteriaID. So when the DCount is evealuated in the Current event, Me.FloorProgCriteriaID is still Null. One possibility is to set the value in the Current event, if you're adding a new record. Like this: '------ start of example code ------ Private Sub Form_Current() If Me.NewRecord And Not IsNull(Me.OpenArgs) Then Me.FloorProgCriteriaID = Me.OpenArgs End If Me.txtCountOfObservations = DCount("*", _ "tblFloorProgAudit", "[AuditID] = " & _ Me.AuditID & " And " & _ "[FloorProgCriteriaID] = " & _ Me.FloorProgCriteriaID & " And " & _ "[FloorProgObservationID] = " & _ Me.FloorProgObservationID & " And " & _ "[AuditorID] = '" & Me.AuditorID & "'") End Sub '------ end of example code ------ If you do it that way, you don't need the line in the Open event setting Me.FloorProgCriteriaID. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
DCount
Thanks for your reply. I put the code in exactly the way you recommended, and
now DCount does see a value. Thanks again! Dirk Goldgar wrote: I’ve been lost for days on this one. Any help is very much appreciated... [quoted text clipped - 33 lines] Michael I imagine that, when your code in the Open event executes DoCmd.GoToRecord, the form's Current event fires before the next line that sets the value of Me.FloorProgCriteriaID. So when the DCount is evealuated in the Current event, Me.FloorProgCriteriaID is still Null. One possibility is to set the value in the Current event, if you're adding a new record. Like this: '------ start of example code ------ Private Sub Form_Current() If Me.NewRecord And Not IsNull(Me.OpenArgs) Then Me.FloorProgCriteriaID = Me.OpenArgs End If Me.txtCountOfObservations = DCount("*", _ "tblFloorProgAudit", "[AuditID] = " & _ Me.AuditID & " And " & _ "[FloorProgCriteriaID] = " & _ Me.FloorProgCriteriaID & " And " & _ "[FloorProgObservationID] = " & _ Me.FloorProgObservationID & " And " & _ "[AuditorID] = '" & Me.AuditorID & "'") End Sub '------ end of example code ------ If you do it that way, you don't need the line in the Open event setting Me.FloorProgCriteriaID. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
Thread Tools | |
Display Modes | |
|
|