As far as I can see this is a classic 'missing link' problem. I think I've
given you the following 'regions' example before, but even so I'll reiterate
it for clarity here:
Its commonly encountered in international databases of geographical data (I
came upon it a while ago in an OECD database for instance) where you have a
table Cities referencing a table Regions referencing a table Countries. Not
all countries have a regional structure, however, so the solution is to have
a row in Regions for each country with a Region value of N/A. It follows
from this that the key of Regions must be a surrogate RegionID of course.
In your case Contacts is analogous to Regions as you say "there are yet to be
any Contacts for these customers", so in Contacts you'd first need a row for
each CLEC with a value of N/A. You can then insert the relevant ContactID
and CLECID values into each row in TProject to reference the relevant N/A
row in Contacts for the CLEC. in question. Once you’ve done this for all
existing TProject rows you can enforce its relationship with Contacts. As
ContactID is the key of Contacts you don't need a CLECID column in TProject
in fact, nor the relationship from TProject to CLECS2.
So the query would now be a simple one of CLECS2 inner joined to Contacts on
CLECCID, Contacts inner joined to TProject on ContactID. CLECS2 does not
need to be joined to TProject as each row in the former maps to rows in the
latter via Contacts.
The relationships mirror the joins in the query:
CLECS2---<Contacts---<TProject
A form based on this query will be updatable, but you should make any
controls bound to columns from Contacts or CLECS2 read only by setting their
Locked property to True (Yes) and their Enabled property to False (No); this
prevents values from the referenced tables being edited, allowing only those
from TProject to be edited. Changing a 'N/A' contact to an existing 'real'
contact simply requires editing the ContactID in TProject, which you'd do via
a bound combo box on the form. To add a new contact for a customer, however,
means first inserting a new row in Contacts and a value in that row for the
CLECID of that contact in this instance. You can do this via the combo box's
NotInList event procedure. Just how you do this depends on how you are
storing the contact names in contacts, whether (a) as a single Contact column.
e.g. Ken Sheridan, or (b) as FirstName and LastName columns, Ken in the
former Sheridan in the latter. If (a) then the code would be like this:
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmContacts", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmContacts closed
DoCmd.Close acForm, "frmContacts"
' ensure contact has been added
If Not IsNull(DLookup("ContactID", "Contacts", "Contact = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Contacts table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
This opens frmContacts, which is a form bound to the Contacts table, in add
mode so that a CLEC can then be selected for the contact. In this form's
Open event procedure you'd put:
If Not IsNull(Me.OpenArgs) Then
Me.Contact.DefaultValue = """" & Me.OpenArgs & """"
End If
If (b), which is the preferred way of storing names of course, the combo box
would be set up like this:
ControlSource: ContactID
RowSource: SELECT ContactID, FirstName & " " & LastName FROM Contacts
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
So to insert a row into Contacts you have to parse the value entered into
separate first and last names. Here's an example of the NotInList event
procedure for a combo box of SalesPersons in one of my files set up in this
way:
Const conMESSAGE = "Salesperson's name must be entered " & _
"in format 'FirstName <space> LastName'"
Dim args As String
Dim strFirstName As String
Dim strLastName As String
Dim intSpacePos As Integer
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
intSpacePos = InStr(NewData, " ")
If intSpacePos = 0 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
Else
strFirstName = Left(NewData, intSpacePos - 1)
strLastName = Mid(NewData, intSpacePos + 1)
' add named arguments
AddArg args, "argFirstName", strFirstName
AddArg args, "argLastName", strLastName
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmSalesPersons", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=args
' ensure frmSalesPersons closed
DoCmd.Close acForm, "frmSalesPersons"
' ensure salesperson has been added
If Not IsNull(DLookup("SalesPersonID", "SalesPersons", _
"FirstName = """ & strFirstName & """ And " & _
"LastName = """ & strLastName & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Salespersons table.
"
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
End If
The code for the Open event of the frmSalesPersons form in this case is:
Dim args As String
If Not IsNull(Me.OpenArgs) Then
args = Me.OpenArgs
' get named named arguments
Me.FirstName.DefaultValue = """" & Arg(args, "argFirstName") & """"
Me.LastName.DefaultValue = """" & Arg(args, "argLastName") & """"
End If
One caveat: the above does not allow for a first name with a space in it, e.g.
'Mary Lou'. Names such as 'Victoria de los Angeles' would treat 'de los
Angeles' as the last name.
You'll see that the code in the above two event procedures calls AddArg and
Args functions, so you'd also need to add the following module, which allows
various ways of passing multiple arguments between forms or reports, to your
database:
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="
Function Arg(buffer, idx) As Variant
If IsNumeric(idx) Then
I& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
I& = InStr(I&, buffer, ASSIGNOP) + 2
Else
I& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, I&, InStr(I&, buffer, token$) - I&)
End Function
Function Argname(buffer, idx) As String
I& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, I& + 1, InStr(I&, buffer, ASSIGNOP) - (I& + 1))
End Function
Function ArgCount(buffer) As Long
ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET
End Function
Sub AddArg(buffer, Argname, argval)
If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer, 1))
+ 1)
End Sub
Sub AddArgList(buffer, ParamArray Tokens())
For I& = 0 To UBound(Tokens)
AddArg buffer, I& + 1, Tokens(I&)
Next
End Sub
For a demo of how this module works see:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
It worked! Thank you! I'm daring to update the regions and states in the
same fashion, which I made into a CLECs/Region/States which is a main
form/subform/subform design.
On another design note I have a design problem similar to what you helped
with a while back on "Update Records in Current Table and Another Table" that
I really needed some advice on if at all possible, which is the following:
CLECS2<Contacts(joined on CLECID-auto number) CLECS2<TProject (joined on
CLECID) Contacts<TProject (Tried joining TProject on ContactID-auto number
also)
I have a new table called TProject. This had existing system data of
customers. I assigned each customer the appropriate CLECID which matches the
CLECS Table. I then gave it a ContactID field to coincide with the Contacts
Table, which has ContactsID.
I tried to simply join TProject with Contacts( There are yet to be any
Contacts for these customers, so I joined them to show all of the records on
Tproject and only ones that match on Contacts-outerjoin I think).
I built a form including the fields in TProject with name, email, telephone
from Contacts. It will not let me update/add any contact information in
these fields. I assume this is because it's looking for the auto number
ContactID. I've joined CLECS Contacts and TProject together many different
ways.
Is there not a way to join these together and be able to type the contact
info? I thought about joining CLECS and TProject and then doing a subform
of Contacts. But I really wanted a continuous tabular form and found that I
could not insert a subform on this type form. Or is this the only way to
accomplish?
Just noticed I didn't update some of the comment lines to the new names.
[quoted text clipped - 159 lines]
