Replies

Hans,
I put the deselect as the last statement of the code

Drew,
I can’t do it today. Since its part of a large db, I’ll have to slice that part off and put some dummy data into it.
I’ll see if I can do that tomorrow.
Thanks

Drew,
As stated above, the listbox is already set that way.
Thanks anyway

Thanks, that gives me a starting point

FWIW here is an example of printing to a PDF file using Adobe Acrobat and Access.
It now works with all versions of Windows.

Thanks Patt

Charlotte,
Here is my logic flawed as it may be
I’m setting the Tag to a 1 when a button is pressed so when the code loops through the buttons it will only see the buttons that are pressed and build the state string from those. Then it sets the ForeColor of the buttons pressed to region color and the Tags to 11 12 13 14 or 15 depending on what region you are creating.
Say the user selected the whole top row for region1. The Tags for all of those are now 11 (1 for being pressed and 1 for region1) and the ForeColor is Red.
Now say the user selects the whole bottom row and Clicks Create Region 2. ForeColor turn green and the Tags for the bottom row are 12. Again 1 for being pressed and 2 for region 2.
Ok they print the report and find that that alignment of regions is not exactly what they want because of the data in the report. They want to swap 2 of the states between regions. GA to region 2 and WY to region 1
They now un toggle the two buttons they want to change(GA & WY) ForeColor changes to Black and tags to a 0.
Next they select GA. The Tag is set to 1 and then click on the Modify Region 2 button. The state string is now built from the existing buttons with Tags 12 and the newly pressed (GA) button who’s Tag is 1. GA Forecolor is now changed to green and the Tag changed to 12.
When they select WY and click on Modify Region 1 The state string is now built from the existing buttons with Tags 11 and the newly pressed (WY) button who’s Tag is 1 ForeColor is set to red and The tag is now 11.
This lets them create and modify up to 5 Regions, visually see what is selected and a report of stats in the selected regions.
Let me know if you see a better way.
Thanks for the input.

John,
I’m using the tag property for something else so the caption text is used to build the where clause for the SQL string.
What the user needed is to be able to create from 1 to 5 regions using different combinations of states. After selecting a report will print out showing stats for the combinations that they selected.
If they need to modify a region they could move states from 1 region to another by for lack of a better term un-toggle two buttons and reassign them to a different region.
The report would then show the changed combination.
Here is some code for anyone interested

Here is the code behind each togglebutton:
Private Sub Toggle1_Click()
If Me.Toggle1.VALUE = True Then
Me.Toggle1.Tag = 1
Else
Me.Toggle1.Tag = 0
Me.Toggle1.ForeColor = RGB(0, 0, 0)
End If
End Sub

Here is the code for the create region buttons:
Private Sub cmdREGION1_Click()
Dim i As Integer
Dim strWHERE As String
For i = 0 To 49
If Me(“Toggle” & i).Tag = “1” Then
strWHERE = strWHERE & “(tblSTATE_SPECS.STATE) LIKE ” & “‘” & Me(“Toggle” & i).Caption & “‘” & ” or ”
Me(“Toggle” & i).Tag = 11 ‘the extra 1 in the tag is for region 1
Me(“Toggle” & i).ForeColor = RGB(255, 0, 0)
End If
Next i
strWHERE = “WHERE ((” & Left(strWHERE, Len(strWHERE) – 4) & “))”
Me.cmdREGION1.ForeColor = RGB(255, 0, 0) ‘turns the text of the button red
Debug.Print strWHERE
End Sub

Here is the code for the modify regions button:
Private Sub cmdMODIFY1_Click()
Dim i As Integer
Dim strWHERE As String

For i = 0 To 49
If Me(“Toggle” & i).Tag = “1” Or Me(“Toggle” & i).Tag = “11” Then
strWHERE = strWHERE & “(tblSTATE_SPECS.STATE) LIKE ” & “‘” & Me(“Toggle” & i).Caption & “‘” & ” or ”
Me(“Toggle” & i).Tag = 11
Me(“Toggle” & i).ForeColor = RGB(255, 0, 0)
End If
Next i
strWHERE = “WHERE ((” & Left(strWHERE, Len(strWHERE) – 4) & “))”
Debug.Print strWHERE
End Sub

And finally the code to reset all buttons:
Private Sub cmdRESET_Click()
For i = 0 To 49
Me(“Toggle” & i).DefaultValue = False
Me(“Toggle” & i).Tag = 0
Me(“Toggle” & i).ForeColor = RGB(0, 0, 0)
Next i
Me.cmdREGION1.ForeColor = RGB(0, 0, 0)
Me.cmdREGION2.ForeColor = RGB(0, 0, 0)
Me.cmdREGION3.ForeColor = RGB(0, 0, 0)
Me.cmdREGION4.ForeColor = RGB(0, 0, 0)
Me.cmdREGION5.ForeColor = RGB(0, 0, 0)

End Sub

Patt,
They are all individual toggle buttons not an option group so you can select as many as you want

Thanks Shane.
What I did was set the tag property to 1 if the button is depressed. Then loop through all the controls that have a tag of 1 and built the where clause.
The buttons aren’t very big 1/4 in. So 50 takes up little room. I did it this way so you could visually see all the selections that were made. In the list box
you have to scroll up/down too much to see all the selections made.

Thanks, That worked Great
Happy Holidays

My appologies to the original author of this:
Here are some date calculations
Put this in a column of a query or in a control.

AgeInYears: DateDiff(“yyyy”,[dob],date())

Turns out, this returns answers that are sometimes off by a year. If you try this:

AgeInYears: Int((DateDiff(“m”,[dob],date())/12))

Which seems to work better, however, it can be off by as much as a month. A
better solution is:

AgeInYears: Int((DateDiff(“n”,[dob],date())/525960))

Which calculates the number of minutes between the dates and divides it by
the number of minutes in the year. However, this does not calculate
correctly on birth dates (i.e. 5/5/1976 – 5/5/2001) except on leap years.

Now, the following works correctly based on the accepted standard of
365.2425 days in the year:

AgeInYears: Int((DateDiff(“s”,[dob],date())/31556952))

This does not take the leap second into account (which is added every 500
days to bring standard time up to atomic clock time), but I’m not going to
bother, because I don’t know if this effects computer clock time.

Both libraries are in the references but neither is checked off.
I’m not sure which one it is using.
I got most of my info from this site:
http://www-10.lotus.com/ldd/46dom.nsf?OpenDatabase

Horst,
Here is the code I use to send an email with an attachment:
Function SendLotus(Attachment) ‘sends an email with an attachment
Dim S As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String

‘ start up Lotus Notes and get object handle
Set S = CreateObject(“Notes.NotesSession”)
Server = S.GETENVIRONMENTSTRING(“MailServer”, True)
Database = S.GETENVIRONMENTSTRING(“MailFile”, True)
Set db = S.GETDATABASE(Server, Database)

On Error GoTo ErrorLogon
‘ See if user is logged on yet;
‘ if not, the error handler will kick in!
Set doc = db.CREATEDOCUMENT
On Error GoTo 0

doc.Form = “Memo”
doc.Importance = “1” ‘(WHERE 1=URGENT, 2= NORMAL, 3=FYI)

‘Send an EMail to
doc.SENDTO = (“firstaddress@somewhere.com”,”secondaddress@somewhere.com”)
‘SENDS A RETURN RECIEPT
‘doc.RETURNRECIEPT = “1”
doc.Subject = “Place your subject text here”

‘ this will build the text part of your mail message

Set rtItem = doc.CREATERICHTEXTITEM(“Body”)
Call rtItem.APPENDTEXT(GetBody)
Call rtItem.ADDNEWLINE(1)

‘Attach a document!
Call rtItem.EMBEDOBJECT(1454, “”, Attachment)

Call doc.send(False) ‘Make sure this parameter stays false

‘ set all object handles to nothing to release memory
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing

MsgBox “Mail has been sent!”, vbInformation

Exit Function

ErrorLogon:
If ERR.NUMBER = 7063 Then
MsgBox “Please login to Lotus Notes first!”, vbCritical
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing

Exit Function
Else
strError = “An Error has occurred on your system:” & vbCrLf
strError = strError & “Err. Number: ” & ERR.NUMBER & vbCrLf
strError = strError & “Description: ” & ERR.Description
MsgBox strError, vbCritical
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing

Exit Function
End If

End Function
Usage SendLotus “C:yourdocument.doc”

Try this:
iif([txtHOLD]=TRUE,”Hold”,iif([txtRENT]=true,”Rent”,””))
The falsepart can be another if statement. The falsepart of your last if statement would contain the “”
Scott