Replies

Take a look at the SQL of your query.
Make sure that it looks like:

SELECT DISTINCT field1, field2 etc.

Using DISTINT will filter all duplicate rows out of your results.

Bart

Hi,

Access does not have any tracking possibilities. If you want to track the changes made by users there is only one way to do it right.
Define an extra table and store all information you want to track in that table. This means a lot of work (you have to program additional lines at every point you want to track changes) and a performance loss of your application.
Do not forget to include functionality to delete the extra data in the tracking table once in a while and compress your db regularly.
If you want to thank anyone, thank mister Gates for omitting an essential thing like this in his wonderfull MS Access!

Bart
Software Developer

I agree completely with Charlotte.

Program a public function in a public module. You can call that function directly from the afterupdate event in the properties window of any control you want. You call it like this: =fnMyFunctionName()
Note the ‘=’ sign!

Use a query like this:

SELECT tblTest.ID, tblTest.fldDate
FROM tblTest
WHERE (((DateDiff(“y”,CDate(#11/1/00#),[tblTest]![fldDate]))<-184));

Bart
Software Designer

If you can trap the error in your code you can relink the table(s) dynamically.

I use code like this for the relink (sorry comments are dutch):

Set dbs = CurrentDb()
For Each doc In dbs.Containers!tables.Documents
If Not blnGevonden Then
If doc.Name = mstrTabel Then blnGevonden = True Else blnGevonden = False
End If
Next doc

If blnGevonden Then
Set tdf = dbs.TableDefs(mstrTabel)
If Len(tdf.Connect) > 0 Then
tdf.Connect = “;DATABASE=” & strExternGegevensBestand
Err = 0
On Error Resume Next
tdf.RefreshLink
‘******************************************************************
‘Als opnieuw connecten niet lukt, stoppen met de handel.
‘******************************************************************
If Err > 0 Then
fnRefreshLink = False
Exit Function
End If
End If
fnRefreshLink = True
Else
‘******************************************************************
‘Tabel niet in database, reconnect onmogelijk.
‘******************************************************************
fnRefreshLink = False
End If

Bart
Software Designer

If the link is established in the front end database, it is stored in the front end database.
If you want to establish referential integrity to your database you should set up the link in the back end database and not in the front end database.

Bart Stam
Software designer

Use a recordset (type dynaset) and populate the recordset with the records that have no PIN yet.

SELECT * FROM tblYourTable WHERE PIN Null;

Loop through this recordset and update the records with the new PIN.
Generate the PIN using a counter and check before updating the record whether the new generated PIN already existst. If it exists generate a new PIN

In pseudocode

counter = 1
open recordset
while records in recordset
newpin = “PIN-” & cstr(counter)
while exists new pin in database
counter = counter + 1
newpin = “PIN-” & cstr(counter)
loop newpin
update current record with new pin code
counter = counter + 1
next record in recordset
loop recordset

I hope this helps

Bart
Software designer

dim db as database
dim strSQL as string
set db = currentdb()
strSQL = “UPDATE tblYourTable SET fldRownumber = fldRownumber * 10

db.execute strSQL, dbFailOnError

set db = Nothing

Hi

I developed a class module to send mails. Comments are in dutch, but the coding speaks for itself:

Option Compare Database
Option Explicit
‘*******************************************************
‘Class clsMAPI
‘*******************************************************
‘Algemene declaraties voor een email object.
‘*******************************************************
Dim objMySes As Object ‘Session object voor de MAPI sessie

Public Function SendMail(txtMailTo As String, Optional txtText As String, Optional txtSubject As String) As String
‘*******************************************************
‘Functie voor het versturen van een mailtje.
‘De returnwaarde is een string.
‘Als de string leeg is is de toevoeging succesvol geweest.
‘Als de string niet leeg is, dan bevat de string een
‘foutboodschap.
‘
‘LAATSTE WIJZIGING:
‘ Versie 1.0 augustus 2000 B. Stam
‘ Initiele versie van de functie
‘*******************************************************
If gTrace Then
Debug.Print “clsMAPI SendMail”
Else
On Error GoTo ErrorHandler
End If

Dim strMessage As String
Dim objRecip As Object
Dim objMes As Object

‘*******************************************************
‘Ontvanger is verplicht.
‘*******************************************************
If txtMailTo = “” Then
strMessage = “The recipient of the mail is not valid or not given!”
GoTo ExitFunction
End If

‘*******************************************************
‘Niet ingevulde parameters afhandelen.
‘*******************************************************
If IsMissing(txtText) Then
txtText = “”
End If
If IsMissing(txtSubject) Then
txtSubject = “”
End If

‘*******************************************************
‘De boodschap zelf invullen.
‘*******************************************************
Set objMes = objMySes.Inbox.Messages.Add
With objMes
.Text = txtText
.Subject = txtSubject
End With

‘*******************************************************
‘De ontvanger invullen
‘*******************************************************
Set objRecip = objMes.Recipients.Add
With objRecip
.Name = txtMailTo
.Resolve
‘.Send
End With

‘*******************************************************
‘En nog even versturen.
‘*******************************************************
objMes.Update
objMes.Send ShowDialog:=False

ExitFunction:
If strMessage “” Then
SendMail = strMessage
Else
SendMail = “”
End If
Exit Function

ErrorHandler:
SendMail = Chr(10) & Chr(13) & “Errornumber: ” & CStr(Err.Number) & ” ”
SendMail = SendMail & “Errormessage: ” & Err.Description
Exit Function
End Function

Private Sub Class_Initialize()
‘*******************************************************
‘Bij het instantieren van het object een MAPI sessie
‘openen.
‘Let op de profilename is afhankelijk van het mail
‘programma dat gebruikt wordt.
‘Als profilename weggelaten wordt, dan wordt er, indien
‘noodzakelijk, automatisch om gevraagd.
‘*******************************************************
If gTrace Then Debug.Print “clsMAPI Class_Initialize”
Set objMySes = CreateObject(“MAPI.Session”)
If Not objMySes Is Nothing Then
objMySes.Logon ‘Als profilename niet gegeven hoeft te worden.
‘objMySes.Logon profileName:=”Microsoft Exchange”
End If

End Sub

Private Sub Class_Terminate()
‘*******************************************************
‘Als het object verwijderd wordt ook de MAPI sessie
‘verwijderd.
‘*******************************************************
If gTrace Then Debug.Print “clsMAPI Class_Terminate”
objMySes.Logoff
End Sub

Bart
Software designer

That was also my solution, but I did not like it. I hoped for a more elegant way to get the function name. Pity….
Thanks for the effort.

Bart Stam
Software developer
PGGM Zeist