Replies

Hi,

You use the brackets on anything you define yourself.
Me![TableName].SetFocus is the correct notation.
Me is a preserved word in VBA, so it should not be between brackets.
TableName is the name you gave to the table, this should be between brackets.
SetFocus is a method of the table object and should not be between brackets.
Using the brackets enables you to use spaces in naming your objects. I never do that, becaus I think it makes your code unreadable. I normally use capitals (for instance in tblThisIsMyTable).

The + sign in front of your records indicates there is a one to many relation ship between the two tables. It is not an indication of a query!
If you click the + it simply opens the many branch of the relationship.

I agree with grugeon.
Can you give more details on what you want to do?
Do you want to append records to your table or do you want to update existing records in your table?

If you get error messages please post the exact error message and the SQL that causes the error.

It’s the VBA.
Looks like Excel 2000 has protected every piece of code I wrote previously.
Can it be some sort of tremendous safety feature (probably called bug by a lot of people) the guys in Redmond has put in their product?

Hi,

To secure my databases I allways present them as a .MDE to my users. That prevents a lot of playing around in the code.
Also I make sure that in the startup procedure of my application the following code is executed to restrict users to access the application in design mode:

If Command = “Debug” Then
fnChangeProperty “StartupShowDBWindow”, dbBoolean, True
fnChangeProperty “AllowBuiltinToolbars”, dbBoolean, True
fnChangeProperty “AllowToolbarChanges”, dbBoolean, True
fnChangeProperty “AllowShortcutMenus”, dbBoolean, True
fnChangeProperty “AllowFullMenus”, dbBoolean, True
fnChangeProperty “AllowBreakIntoCode”, dbBoolean, True
fnChangeProperty “AllowSpecialKeys”, dbBoolean, True
fnChangeProperty “AllowBypassKey”, dbBoolean, True
Else
fnChangeProperty “StartupShowDBWindow”, dbBoolean, False
fnChangeProperty “AllowBuiltinToolbars”, dbBoolean, False
fnChangeProperty “AllowToolbarChanges”, dbBoolean, False
fnChangeProperty “AllowShortcutMenus”, dbBoolean, False
fnChangeProperty “AllowFullMenus”, dbBoolean, False
fnChangeProperty “AllowBreakIntoCode”, dbBoolean, False
fnChangeProperty “AllowSpecialKeys”, dbBoolean, False
fnChangeProperty “AllowBypassKey”, dbBoolean, False
End If

—————-
fnChangeProperty looks like this:

Function fnChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
‘******************************************************************
‘SUB: fnChangeProperty
‘
‘Wijzigen van de startup properties.
‘Als de aangeroepen property nog niet bestaat, dan wordt deze aangemaakt.
‘
‘AANROEP: fnChangeProperty par1, par2, par3
‘
‘RETOUR: True (-1) indien aanroep ok
‘ False (0) indien fout
‘
‘LAATSTE WIJZIGING:
‘ Versie 1.0 februari 1999 B. Stam
‘ Initiele versie van de functie
‘******************************************************************
If gTrace Then Debug.Print “baszsfrmSplash fnChangeProperty”
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo fnChangeProperty_Err
dbs.Properties(strPropName) = varPropValue
fnChangeProperty = True

fnChangeProperty_Exit:
Set dbs = Nothing
Exit Function

fnChangeProperty_Err:
‘******************************************************************
‘Als propertie niet gevonden, dan aanmaken.
‘******************************************************************
If Err = conPropNotFoundError Then ‘ Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
‘******************************************************************
‘Als er een andere fout is, dan maar negeren.
‘******************************************************************
Else
fnChangeProperty = False
Resume fnChangeProperty_Exit
End If
End Function

————————-
Most users will not be able to hack this and change the design of an application.
If a user manage to change the design, you should consider to invide him or her to be a member of your team!

Thanks Rory, you gave me the solution.
I didn’t read the invaluable information under the F1 button completely and overlooked the second parameter on the Column property.

Hi,

Did you check if your references all still correct?
Do you use a function from a dll that is not present?
Can you post the exact error message you get, that can give a clue to the solution of your problem.

Yes, that should work.
Although, if you apply a naming convention, you would call it txtContactName (that is the name of the control on your form or report) and put in the caption of the label (with the name lblContactName) &Name, ensuring the hotkey for the field is ALT+N.

You can use the content of a textbox in a query with a LIKE operator.

Check Woody’s Access Watch #3.02 (yes the latest one). Here you will find a description and some examples of the LIKE operator.

Like Charlotte says, name is a preserved word in VBA.
You will net get problems like this if you apply a naming convention like LNC. Check Woody’s Access Watch #3.02 (yes the latest one). Here you will find a description of naming conventions.

Bart

Like this:

DoCmd.OpenReport “R1”, acPreview, , sizing(Gebinde)

Public Function sizing(Gebinde) as string
If Gebinde = 1 Then
sizing = StrDrums
else
sizing = “”
End If
End Function

Bart

OK I want to suggest a couple of changes to your query.
First replace the DISTINCTROW by DISTINCT.

Show only the fields you want to see, and no more. Your query looks like this:

SELECT DISTINCT field1, field2, field3 FROM ….
After FROM put only those tables you need for the fields you want to see and the ID’s from your subselects (see further).

Now the WHERE clause, use subqueries here.
A subquery looks like this:
SELECT DISTINCT field1, field2, field3 FROM ….
WHERE ID IN (SELECT ID FROM table1 etc WHERE field1 LIKE etc.)

You can store subqueries in seperate queries.

I think if you do all this, your query looks a lot different and it will work.

Success.

Bart

There must be a unique field in the query (before the FROM).
Do all fields in two different rows have the same content?

Bart

There is an advantage putting the code in a module.
If you don’t have any code at all in the module attached to a form, you can set the HasModule property of that form to False. The form then does not have a module anymore resulting in a quicker load of the form.
If you load a form it’s module is loaded too, if the form doesn’t have a module that part isn’t carried out anymore.

Bart

Yes, it is possible.
I developed a class module that’ll do the job.
It uses de default mail client to send the mail.
Sorry, comments are dutch but the code is pretty self explaining.

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
‘*******************************************************
On Error GoTo ErrorHandler

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.
‘*******************************************************
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.
‘*******************************************************
objMySes.Logoff
End Sub

Bart
Software Designer

Take a look at arrays!
These will probably well suited for what you want to do.

Bart