Replies

Hi Brian

Thanks for response.

I tried following code:

addr_no_p: Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & ” ”

Works fine except:

There are 6410 records in the append query.

I Get Error Message:

Insert set 785 field(s) to Null due to a type conversion error.

There are 785 blank strLocStreetNo fields

Is there a way to test and eliminate this error message?

Thanks, John Graves

Could you point me to:

discussion on building public functions

Can you do this in a2k

Hi Charlotte

Sorry you had to see that table. It is a transition table from a legacy to access database. Was able to drop 10 fields. It then exports OK, and can be maintained/saved in its new location.

Thanks for your help.

John Graves

Hi All

Link correction is

http://www.LewistonQueenstonRotary.org

John Graves

Hi Leif

Sorry!

You have the correct link.

John Graves

Jim

For A2K, do you know where I can find code to

1. Hold open a table in the backend MDB for the life of the app.

I’ve seem this code posted somewhere.

John Graves

You Said

As a newbie I was taught this and using it extensivily through my tables.

Do you remove table lookups after you build your forms with wizzard?

Or do you build your form combo boxs from scratch?

John Graves

Hi Mark

Your right, DAO 3.6 was not checked.

Your code worked perfectly.

Do you know of any front-end code that would allow the developer to open a unbound form combo box and display a user relationship table as follows:

tblRelationshipsBP
strTable
strOneFeild
strForeignTable
strManyFeild
ysnEnforceRefInt
ysnCascadeUpdate
ysnCascadeDelete
strJoinType 1, 2, or 3
ysnSetRelationship Include this relationship in the batch
(don’t know if I’m missing something)

The developer would Ctrl Click all the relationship records that would be included in the batch.

The code would then perform all the relationships that were selected.

I’m not quite sure of the code required to cycle through the record selected and feed them to your CreateRelationship function.

Thanks, John Graves

NBS7335

Hi Mark

I get the following error when running CreateRelationship code. I’m very new to access & it’s probably somthing missing on my end.

Dim db As DAO.Database (Compile error – User-defined type not defined)

Thanks, John Graves

————————
‘ button on form
Private Sub Set_Relationships_Click()
On Error GoTo Err_Set_Relationships_Click

Dim strTable As String, strOneField As String
Dim strForeignTable As String, strManyField As String

strTable = “tblBPermit”
strOneField = “lngBPermitID”
strForeignTable = “tblBPFees”
strManyField = “ingBPermitID”

Call CreateRelationship(strTable, strOneField, strForeignTable, strManyField)
DoCmd.Close

Exit_Set_Relationships_Click:
Exit Sub

Err_Set_Relationships_Click:
MsgBox Err.Description
Resume Exit_Set_Relationships_Click

End Sub

————————-
‘ your code
Option Compare Database

‘MarkJ replied to your post on the Access board at the Woody’s Lounge site:
‘Re: VBA CODE TO EST RELATIONSHIPS – NBS’.
‘http://www.wopr.com/cgi-bin/w3t/showthread…cc&Number=44019
‘Sorry for the delay. This should work for you:

Function CreateRelationship(strTable As String, _
strOneField As String, _
strForeignTable As String, _
strManyField As String)

‘=================================================
‘Created by Mark S. Johnston
‘MarkjSC@yahoo.com
‘ http://www.geocities.com/MarkjSC
‘
‘Adapted from MSDN Library article:
‘ http://msdn.microsoft.com/library/default.asp ? _
‘URL=/library/books/dnjet/c3_body_37.htm
‘=================================================

Dim strRelationName As String
Dim db As DAO.Database
Dim rln As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()

‘Destroy any currently existing relationships between
‘the two tables
For Each rln In db.Relations
If rln.Table = strTable And rln.ForeignTable = strForeignTable Then
db.Relations.Delete rln.Name
End If
Next ‘rln

strRelationName = strTable & “_” & strForeignTable

‘Create the Relationship
Set rln = db.CreateRelation(strRelationName)

‘Set the properties of the Relationship
With rln
.Table = strTable
.ForeignTable = strForeignTable

‘Relationship will be Cascade Update and Cascade Delete
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
End With

‘Set the Field of the Relation object
Set fld = rln.CreateField(strOneField)
fld.ForeignName = strManyField

‘Add the Field to the Relation object
rln.Fields.Append fld

‘Add the Relation to the Databse
db.Relations.Append rln

‘destroy the evidence ;^)
Set fld = Nothing
Set rln = Nothing
Set db = Nothing

End Function

‘Post back if there are any problems.
‘MarkJ – http://www.geocities.com/MarkjSC
‘http://www.wopr.com/w3tuserpics/MarkJ_sig.gif

Interesting

I Imported tblBPermit as you suggested, at just about completion of building indexes, received “The operation failed. There are too many indexes on table tblBPermit OK” message

I had to click OK 5 times to clear dialog box.

There are 8 legitimate indexes in the source tblBPermit table, after viewing destination tblBPermit table Indexes, it revealed 32 indexes (the max). It automatically created an index for any non-indexed field that has ID or Code in the field name.

Is there a way to turn off this feature? DUH, yes there is!

Tools, Options, Tables/Queries, AutoIndex on Import/Create (I cleared this)

This (index creation) was happening on 42 other tables.

Needless to say my performance is better, and my database when from 53 MB to 39 MB

After all this, including Detect & Repair, I get the same “PROPERTY VALUE IS TOO LARGE” MESSAGE – Error 3309 as described above.

I did notice when I get “Property Value is too Large OK” message. I Click OK

I get “Errors encountered during the save operation OK” message. I Click OK, exit Design View again, say No to Save Change message, go back into design view and my changes are there.

What next?

PS: I attached tbpBPermit for your review.

Hi Charlotte

When I open Build_BE.mdb tblBPermit in Design View, make change (just press the space bar) in Description field (property), and save I get “Property Value is too Large” message.

When I go back and open BuildCvt.mdb tblBPermit in Design View, make change to Description field or add additional fields, and save, it saves OK

John Graves

Thanks, the link worked.

John Graves

How about

Private Sub Command11_Click()

Me.Filter = “”

End Sub

John Graves

Hi Charlotte

Thanks for all the access refreence info, still can’t find orders9.mdb as described in Q248674, says site cannot be found/loaded.

Just subscribed to Access-VB-SQL Advisor.

Is there another site I can find and download orders9.mdb?

John Graves