.value

[Ankarn]

I have a Query1 with records that may or may not have a value in the property "OrdreID". The most sensible would be to open the report if all of them had the same OrdreID. If they dont have the same or if some of them doesnt have a value or if none of them have a value, i would like to make them choose whether or not to continue.

The thing i'm wondering about is the OrdreID.Value. I'm not sure what ordre really count here. Is OrdreID.Value the first element, and it counts how many records that have the same value in OrdreID as the first one?

Is there any "first" in a access-table? Does it take the first OrdreID.Value it find? I have tried to read about this in help but i dont understand it.

        total = DCount("*", "Query1")
        If Not IsNull(Query1.OrdreID.Value) Then
            ordre = DCount("*", "Query1", "OrdreID = " & OrdreID.Value)
            If ordre = total Then
                DoCmd.OpenReport "FakturaRapport", acPreview
                DoCmd.RunCommand acCmdZoom100
                DoCmd.MoveSize 1000, 500, 13500, 8000
            ElseIf ordre > 0 Then
                strMsg = "Alle de valgte elementene har ikke samme ordrenr./Ikke alle elementene har ordrenr." & vbCrLf & vbCrLf & "Fortsette?"
                If MsgBox(strMsg, vbQuestion + vbYesNo, "Vis Rapport?") = vbYes Then
                        DoCmd.OpenReport "FakturaRapport", acPreview
                        DoCmd.RunCommand acCmdZoom100
                        DoCmd.MoveSize 1000, 500, 13500, 8000
                End If
            End If
        Else
            strMsg = "Ingen av elementene har ordrenr." & vbCrLf & vbCrLf & "Fortsette?"
        End If

 

[namliam]

The thing i'm wondering about is the OrdreID.Value. I'm not sure what ordre really count here. Is OrdreID.Value the first element, and it counts how many records that have the same value in OrdreID as the first one?

Yes it is counting just that.

But unless you are opening this from some recordset, form or report called Query1, your "IsNull(Query1.OrdreID.Value)" will not work.

 

[Ankarn]

I discovered that. I am not opening this from a recordset. How do i count it from the Query1?

I just want to find out whether
1. All the records have same OrdreID
2. All the records dont have the same OrdreID
3. None of the records have OrdreID

Can i get this information from the query?

 

[namliam]

I dont know what the query does... but what you describe is best done with a "group by"-query.

Dim RS as dao.recordset
Set rs = currentdb.openrecordset("Select Ordre_ID, Count(*) from Query1 group by Ordre_ID")
Rs.movelast
If Rs.Recordcount = 1 then ' only 1 Ordre Number or all blank
else ' multiple Ordre Numbers
end if
rs.close
set rs = nothing

Hope that helps

 

[Ankarn]

I found it easy to add OrdreID to the recordsource.

The thing is that Query1 is displayd in a subform from the form where i have this code.

But when i have OrdreID in the recordsource of a subform, shouldnt this work?
ordre = DCount("*", "Query1", "[OrdreID = " & Me!Results.Form.OrdreID.Value)

I want to count how many of the record displayd that have the same ordreID as the first one. I use something like this someplace in my code, but there it's from a controll so i use Me.ControllName.Value instead of Me!Results.Form.OrdreID.Value

Results is the subform.

ordre turns out empty. What is wrong?

 

[namliam]

Me!Results.Form.OrdreID.Value or slightly better to use . instead of !: Me.Results.Form.OrdreID.Value

This will fetch the current selected record's value of the OrdreID, by default that would be the first record... or any record selected by the user.

Note:
You are missing a ] here:
ordre = DCount("*", "Query1", "[OrdreID] = " & Me!Results.Form.OrdreID.Value)

By your own explenation there are Ordre numbers that are Null (empty) and you cannot search for Null like this... You have to do
ordre = DCount("*", "Query1", "[OrdreID] is null")
So you have to create a "IIF" structure to either to one count or the other.
Or use the RS method I described before.

 

[Ankarn]

I know about the ].

ordre = DCount("*", "Query1", "[OrdreID] = " & Me!Results.Form.OrdreID.Value) Doesnt work either.

Is the reason that DCount doesnt work here that Me!Results.Form.OrdreID.Value might be Null?

 

[namliam]

This is what I posted in my previous post... If you want to actually search for a blank value you have to use "Is null" instead of = <blank>

 

[Ankarn]

Ah..

I understand.

The problem is that Me!Results.Form.OrdreID.Value can be Null, so i have to rewrite my code.

Thanks.