2 Me!Field, is this possible?

[CompGirl]

hi everyone... im new at this but can someone tell me how i am supposed to write this code. It complains on this row:
Me!Risker = Me!hiddenEnvi_Risk_Class And Me!hiddenPro_Risk_Class


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Re As DAO.Recordset
Dim ReSum As DAO.Recordset
Dim ReFreq As DAO.Recordset
Dim Envi_Risk_Class, Pro_Risk_Class, Risk, tot


Set Re = CurrentDb.OpenRecordset("SELECT Envi_Risk_Class, Pro_Risk_Class, HazardID FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "' AND Pro_Risk_Class ='" & Me!hiddenPro_Risk_Class & "' ORDER BY HazardID&quot
Risk = ""
Set ReSum = CurrentDb.OpenRecordset("SELECT COUNT(HazardID) As HazardTotal FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "' AND Pro_Risk_Class ='" & Me!hiddenPro_Risk_Class & "'&quot
txtSumTotal = ReSum("HazardTotal&quot
Set ReFreq = CurrentDb.OpenRecordset("SELECT SUM(Envi_Freq) As Total FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "'&quot
txtTotal = ReFreq("Total&quot

Me!Risker = Me!hiddenEnvi_Risk_Class And Me!hiddenPro_Risk_Class
Do While Not Re.EOF
Risk = Risk & Re!HazardID & ", "
Re.MoveNext
Loop
Me!ID = Left(Risk, Len(Risk) - 2)

End Sub

 

[MichaelRed]

The "bang" operator is used with recordsets and FORMS (and Reports) uus the "Dot" operator with "ME", as the Forms (or reports) predicate is not involved / required.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CasperTFG]

Change Me to Re... Or better yet change all your Me's and Re's to Rs. That is the more common usage.

Craig

"I feel sorry for people who don't drink. When they wake up in the morning, that's as good as they're going to feel all day."
~Frank Sinatra

 

[chiph]

By the two different answers you've received it seems we're a little confused over what "Me" refers to. Is it the VB reserved word that indicates the current Form or Object? Or is it that you mis-typed "Re"?

Chip H.

 

[CompGirl]

ive tried both of yer examples but it still doesnt work... it complains on this row...

Set Rs = CurrentDb.OpenRecordset("SELECT Pro_Risk_Class, Envi_Risk_Class, HazardID FROM tblHazard Where Pro_Risk_Class ='" & Rs!hiddenPro_Risk_Class & "' AND Envi_Risk_Class ='" & Rs!hidden_Envi_Risk_Class & "' ORDER BY HazardID&quot

 

[johnwm]

You appear to be missing spaces after each of the equals signs


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CompGirl]

okej.. i will try to explain it better... if i choose only one field like this
Set Re = CurrentDb.OpenRecordset("SELECT Pro_Risk_Class, HazardID FROM tblHazard Where Pro_Risk_Class ='" & Me!hiddenPro_Risk_Class & "' ORDER BY HazardID&quot
Risk = ""

it works perfect.... everything is fine... but i want it to pick two fields and do the same that the code does... that means that i want it to "add" those two fields together and list the result ...

im thankful for the answers.. but none of them works so far

 

[CompGirl]

all of this is shown in a report that in the end looks something like this


Risk class ID TOTAL Frequency
a 14,18 2 1
b 1,2,3,4 4 256
c 7,8,10 3 2
d 12,13,14 3 54

now.. like i said.. it works fine is the risk class belongs to property_risk_class and only that... what i want to do is.. i have another field.. called envi_risk_class.. i want them to show everything on the same table...

 

[MichaelRed]

I'm SURE there are 'issues' here which I'm not seeing. I "Re-Arranged" the procedure for my own 'readbility' quotiient. It "looks like" some minor punctuation issues were present, bbut without testing on a 'real' group of record sets, it is not clear that these are actually problems. What DOES appear to be a RUN TIME problem is the attempt to 'calculate' the control "Me!Riskier", as commented in the code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim Re As DAO.Recordset
    Dim ReSum As DAO.Recordset
    Dim ReFreq As DAO.Recordset
    Dim Envi_Risk_Class
    Dim Pro_Risk_Class
    Dim Risk
    Dim Tot
    Dim Quo As String
    Dim ReSql As String
    Dim ReSunSql As String
    Dim ReFreqSql As String

    Quo = Chr(34)

    ReSql = "SELECT Envi_Risk_Class, Pro_Risk_Class, HazardID " & _
            "FROM tblHazard " & _
            "Where Envi_Risk_Class = " & Quo & Me!hiddenEnvi_Risk_Class & Quo & _
            "AND Pro_Risk_Class = " & Quo & Me!hiddenPro_Risk_Class & Quo & _
            "ORDER BY HazardID" & ";"
    Set Re = CurrentDb.OpenRecordset(ReSql)

    Risk = ""
    Do While Not Re.EOF
        Risk = Risk & Re!HazardID & ", "
        Re.MoveNext
    Loop
    Me!ID = Left(Risk, Len(Risk) - 2)

    ReSumSql = "SELECT COUNT(HazardID) As HazardTotal " & _
               "FROM tblHazard " & _
               "Where Envi_Risk_Class = " & Quo & Me!hiddenEnvi_Risk_Class & Quo & " AND " & _
               "Pro_Risk_Class = " & Quo & Me!hiddenPro_Risk_Class & Quo & ";"


    Set ReSum = CurrentDb.OpenRecordset(ReSumSql)
    txtSumTotal = ReSum("HazardTotal")

    ReFreqSql = "SELECT SUM(Envi_Freq) As Total " & _
                "FROM tblHazard " & _
                "Where Envi_Risk_Class = " & Quo & Me!hiddenEnvi_Risk_Class & Quo & ";"

    Set ReFreq = CurrentDb.OpenRecordset(ReFreqSql)
    txtTotal = ReFreq("Total")

    Me!Risker = Me!hiddenEnvi_Risk_Class And Me!hiddenPro_Risk_Class    'Probable error - "aaa" AND "'bbb" or 123 AND 456 = 72?


End Sub

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CompGirl]

hmmm i havent looked anything at the code .. but the error right now is type mismatch...

Me!Risker = Me!hiddenEnvi_Risk_Class And Me!hiddenPro_Risk_Class 'Probable error - "aaa" AND "'bbb" or 123 AND 456 = 72?

 

[MichaelRed]

hmmm back at you!

I'd have thought the comment added (and "QUOTED&quot would be clue enough! the "AND" in the statement is 'inappropiate' it is NOT a string concatenation operator, which it what I THINK you want (and use properly in other srtatements).


Assuming -of course- you are refering to my previous post (to this thread)?





MichaelRed
m.red@att.net

Searching for employment in all the wrong places