20. marts 2006 - 15:57Der er
13 kommentarer og 1 løsning
lave en hvis ikke gyldig regel
Jeg skal have en regel ind i den nedestående kode, så hvis den ikke finder et resultat, så skal den give besked og afslutte koden.
Her er kode:
Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '" & InputBox("Indtast kundens registerings nummer") & "'")
og i denne kode (hvis "LAST_SERVICE_TXT" er tom) :
du kan tilfeoeje en DCount- handling. DCount er ikke genial hvis du arbejder med virkeligt store databaser, men for de fleste loesninger i Access er det ok.
Har lige siddet og leget lidt med det men kan ikke rigtig få det til at virke.
Kan man ikke gøre det på denne måde, dog med lidt tilretninger?
Set db = CurrentDb() Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '" & UCase(InputBox("Indtast kundens registerings nummer")) & "'")
If DCount(MailList(MyEmailAddresses)) < 0 Then
MsgBox "Registreringsnummer ikke fundet" Exit Function End If
Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '" & UCase(InputBox("Indtast kundens registerings nummer")) & "'")
If DCount(REGISTER_NUMBER, MyEmailAddresses, "*") = 0 Then
MsgBox "Registreringsnummer ikke fundet" Exit Function End If
hvor følgende: MyEmailAddresses er min query dbo_VEHICLE er min tabel REGISTER_NUMBER er feltet
Jeg ville nok goere det omvedt. Istedet for at oprette et recordset baseret paa din SQL-saetning ville jeg lavet din DCount foerst. Paa den maade kan du spare lidt processor-cycles. Hvis Dcount retunerer en nul-vaerdi behoever du ikke at oprette recordsettet.
saa noget i retning af:
dim Regnr as string Regnr = InputBox("Indtast kundens registerings nummer")
if DCount(REGISTER_NUMBER, MyEmailAdresses, "REGISTER_NUMBER Like '" & Ucase(Regnr) & "'") = 0 Then
MsgBox "Registreringsnummer ikke fundet" Exit Function
Else
Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '" & UCase(Regnr) & "'")
her kan du saa udofere anden kode med recordsettet indeni If-then-else loekken.
ok det er nmuligvis pga underscore i navnet, proev med [MyEmailAdresses].[REGISTER_NUMBER] Hvis det er i den foerste felt, saa saet ogsaa klammer omkring dette.
Nu starter vi helt forfra.... Point værdi er steget til 100 som går til den der kan give mig en komplet løsning.
Håber i kan hjælpe mig. Hvis i mangler flere info så sig til.
Vigtigt: MyEmailAddress er en forespørgsel over tabelen dbo_VEHICLE som har Feltet REGISTER_NUMBER som her skal søges på.
Skal bruge en "IF "regnr ikke eksistere i"...Then...msgbox"mangler bla bla"...exit function, Else" kode i 2 punkter af min kode.
Første sted (har forsøgt med hjælp herfra men virker ikke): ------------------------------------------------------------------- Regnr = InputBox("Indtast kundens registerings nummer")
If DCount(dbo_VEHICLE.REGISTER_NUMBER, MyEmailAdresses, "REGISTER_NUMBER Like '" & UCase(Regnr) & "'") = 0 Then
MsgBox "Registreringsnummer ikke fundet" Exit Function
Det andet sted (hvor "LAST_SERVICE_TXT" skal være større end nul): ----------------------------------------------------------- Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it MyMail.To = MailList("LAST_SERVICE_TXT") ----------------------------------------------------------
Den komplete kode: -------------------------------------------------------------
Option Compare Database Option Explicit ' You need to declare a reference to the Outlook library, and the filesystemobject. ' this is not as hard as it sounds. ' ' Look in the menu above, and click Tools, then select References ' ' Scroll down the list until you see ' Microsoft Scripting Runtime -- and put a check next to it (if one is not there already) ' ' Microsoft Outlook Object Library -- check that. ' There will be some version number there as well; it doesn't matter. ' This will work with Outlook98 and Outlook2000 and OutlookXP. It hasn't been tested on Outlook 2003 yet.
Public Function SendEMail()
Dim db As DAO.Database Dim MailList As DAO.Recordset Dim MyOutlook As Outlook.Application Dim MyMail As Outlook.MailItem Dim Subjectline As String Dim BodyFile As String Dim fso As FileSystemObject Dim MyBody As TextStream Dim MyBodyText As String Dim Regnr As String
Set fso = New FileSystemObject
' Now, we open Outlook for our own device.. Set MyOutlook = New Outlook.Application
If DCount(dbo_VEHICLE.REGISTER_NUMBER, MyEmailAdresses, "REGISTER_NUMBER Like '" & UCase(Regnr) & "'") = 0 Then
MsgBox "Registreringsnummer ikke fundet" Exit Function
Else
Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '" & UCase(Regnr) & "'")
' now, this is the meat and potatoes. ' this is where we loop through our list of addresses, ' adding them to e-mails and sending them.
Do Until MailList.EOF
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it MyMail.To = MailList("LAST_SERVICE_TXT")
'This gives it a subject MyMail.Subject = Subjectline$
'This gives it the body MyMail.Body = MyBodyText
' To briefly describe: ' "c:\myfile.txt" = the file you want to attach ' ' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut. ' the shortcut only works if the file is available locally (via mapped or local drive) ' ' 1 = the position in the outlook message where to attachment goes. This is ignored by most ' other mailers, so you might want to ignore it too. Using 1 puts the attachment ' first in line. ' ' "My Displayname" = If you don't want the attachment's icon string to be "c:\myfile.txt" you ' can use this property to change it to something useful, i.e. "4th Qtr Report"
'This sends it! 'MyMail.Send'
'Some people have asked how to see the e-mail 'instead of automaticially sending it. 'Uncomment the next line 'And comment the "MyMail.Send" line above this.
MyMail.Display
'And on to the next one... MailList.MoveNext
End If
Loop
'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done. 'Otherwise, it will stay running.
'MyOutlook.Quit Set MyOutlook = Nothing
MailList.Close Set MailList = Nothing db.Close Set db = Nothing
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
