Hi Eric,

I would have covered that ... but you did not replace the text fields in
'tbl_Road_Restrictions' and 'tbl_Road_Restrictions_Detail' with numeric
ID fields ... so it did not look like you were ready for that information.

once you do this, as the user types a waypoint name, the combobox will
move to that selection -- they do not have to "trawl through the master
records" with the arrows... and, if they end up typing a completely new
name, you can use a NotInList event to add those new waypoint names to
your master list.


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



efandango wrote:
Ok, I will try that. But going back to my original question; how would I make
things so that the user can avoid having to trawl through the master records
of waypoints and create sub records for any previous (dupe) records
elsewhere; Instead, I want to add new records from 'tbl_Road_Restrictions'
and its sub-table
'tbl_Road_Restrictions_Detail' to duplicate Waypoints (without an
intersection subrecord) in other corresponding records of 'tbl_Waypoints'
using [Run_No] as the reference/link.

regards

Eric

"strive4peace" wrote:

Hi Eric,

got it, thanks

in tbl_Waypoints

sort on Run_waypoint_ID
delete the 0's you have

then make a relationship from tlb_Waypoints_Master_List to tbl_Waypoints
on Run_waypoint_ID and Enforce Referential Integrity

If you want to be able to type a Waypoint that is not in the list and
automatically create a record in the master list, on frm_Waypoints, put
this code on the NotInList [event procedure] for Run_waypoint

'~~~~~~~~~~~~~~~~~~~~
Private Sub Run_waypoint_NotInList( _
NewData As String, _
Response As Integer)

Dim s As String _
, mRecordID As Long _
, mText As String


'if you want to convert to ProperCase
mText = StrConv(NewData, vbProperCase)

s = "INSERT INTO tbl_Waypoints_Master_List(Run_waypoint) " _
& " SELECT '" & mText & "';"

'comment or remove next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("Run_waypoint_ID", "tbl_Waypoints_Master_List"))

If mRecordID 0 Then
Response = acDataErrAdded

Me.ActiveControl = mRecordID
Else
Response = acDataErrContinue
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~


in
Private Sub Run_waypoint_AfterUpdate()
add this line to the top --
If Len(Trim(Nz(Run_waypoint.Column(2), ""))) = 0 Then Exit Sub



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



efandango wrote:
Hello Cystal,

I have sent you a file in Access 2003.

regards

Eric


"strive4peace" wrote:

Hi Eric,

ps, I will look at your file faster if you convert it to Access 2003
before you send it to me ... that is the version I am using most of the
time and, with what I am helping you with, it (probably) doesn't matter...

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



efandango wrote:
Hello Crystal,

I have sent you a new file, with an email message. I forgot to mention in my
last email, that I have not actually recieved any emails from yourself, i
checked to see if any were blocked, and nothing seems to be wrong at this end.

regards

Eric


"strive4peace" wrote:

Hi Eric,

"how far into the life of the db I am; the answer is all the way, to the
end"

I was referring to its use, not development

While I can see you have put quite a bit of time into developing it,
there is only so far you can go with duct tape and bailing wire smile

Please understand that designing tables is an iterative process; even
the best of us have to redo work; it is better to make your structure
strong now than face problems later

~~

both of the examples you posted are the same ... can you post the
alternative?

I notice that the Waypoints table has duplicate entries, which is why I
suggested a Roads table that would have distinct names.

It would not take as much time as you think to convert to using IDs
instead of text. If, for instance, you have a combobox where the first
column (ID) is hidden and the text shows, it is easy to add that new
text entry, for instance, to the Roads table and pull the new ID

on your form, here is an example with the properties you need to set for
a combobox that stores the ID and displays the text

combobox control

Name -- RoadID
ControlSource -- RoadID
RowSource --
SELECT
RoadID,
RoadName
FROM Roads
ORDER BY RoadName

BoundColumn -- 1
ColumnCount -- 2

columnWidths -- 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth -- 2
(should add up to the sum of the column widths)

RoadID will be stored in the form RecordSource while showing you
information from another table...

for the NotInList event of the combobox, here is code behind the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub RoadID_NotInList( _
NewData As String, _
Response As Integer)

'assumption:
'and its first column (hidden)
'is the Autonumber record ID for the source table

Dim s As String _
, mRecordID As Long _
, mText As String

'~~~~~~~~~~~~~~~~~~~~~~~~
'Choose ONE of these code blocks

'--------------------------------------------------------

'if you want to convert to ProperCase
'mText = StrConv(NewData, vbProperCase)

's = "INSERT INTO Roads (RoadName) " _
& " SELECT '" & mText & "';"

'---------------

'or, if you wish to leave it as the user entered...

s = "INSERT INTO Roads (RoadName) " _
& " SELECT '" & NewData & "';"

'--------------------------------------------------------

'comment or remove next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("RoadID", "Roads"))

If mRecordID 0 Then
Response = acDataErrAdded

'assuming the first column of the listbox
'is the RecordID, RecordID and is a Long Integer

me.RoadID = mRecordID
Else
Response = acDataErrContinue
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


WHERE
- control Name for RoadID is also RoadID

~~~~~~~~~~~~~~`

ok, so we got that ... right? and now you are probably wondering how
you are going to convert your data ... easy!

Make a copy of your database so you have no fear about messing it up (if
that is what happens)

First, make the Roads table from your names:

SELECT DISTINCT tbl_Waypoints.Run_waypoint AS RoadName
INTO tbl_Roads
FROM tbl_Waypoints
WHERE (((tbl_Waypoints.Run_waypoint) Is Not Null));

now, we will modify the Roads table to add:
- RoadID, autonumber

and we will also add a unique index:
field -- RoadName
Indexed -- Yes (No Duplicates)
Descirption -- Name of Road

~~~

now, with the unique index, we can add the road names used from other
tables without fear of duplicating what is already there...

INSERT INTO tbl_Roads ( RoadName )
SELECT DISTINCT tbl_Road_Restrictions.Road_Name_To
FROM tbl_Road_Restrictions
WHERE (((tbl_Road_Restrictions.Road_Name_To) Is Not Null));

INSERT INTO tbl_Roads ( RoadName )
SELECT DISTINCT tbl_Road_Restrictions.Road_Name_From
FROM tbl_Road_Restrictions
WHERE (((tbl_Road_Restrictions.Road_Name_From) Is Not Null));

when you run each of these queries, Access will probably tell you it
cannot append all records -- and that is fine, the problem is that some
of them are already there -- your unique index on RoadName is protecting
you smile

Now, add the following fields to tbl_Road_Restrictions

- RoadID_to, Number, Field Size -- Long, Default Value -- null,
Description -- Road Name To
- RoadID_from, number, Field Size -- Long, Default Value -- null,
Description -- Road Name From

ok, now we just have to update the ID fields...

UPDATE tbl_Road_Restrictions
INNER JOIN tbl_Roads
ON tbl_Road_Restrictions.Road_Name_To = tbl_Roads.RoadName
SET tbl_Road_Restrictions.RoadID_To = [tbl_Roads].[RoadID];

UPDATE tbl_Road_Restrictions
INNER JOIN tbl_Roads
ON tbl_Road_Restrictions.Road_Name_From = tbl_Roads.RoadName
SET tbl_Road_Restrictions.RoadID_From = [tbl_Roads].[RoadID];

verify that IDs are filled wherever you have road names in
tbl_Road_Restrictions ... then you can delete the following fields: