Hi Eric,
"I can't allow the various waypoint lists to fall out of order"
Since the lists must have an order, give the users an Ordr ('Order' is a
reserved word) field that is automatically assigned by Access when
records are created, and that they have the ability to change -- or use
another method*.
"In short the whole thing has to be done manually and meothodically"
I agree that what you need to do is something methodical; ideally, you
would have a 2-dimensional way of keeping things in order.
* Another option is to fill Latitude/Longitude for each road and
intersection. If you do everything manually, believe me, that will lead
to more headaches down the road than you have now!
After more thought, I see that perhaps, ideally, you could use something
like this (and this is just a start, probably requires more thought):
Intersections
IntersectID, autonumber
- Lat, double
- Lng, double
Intersect_Waypoints
- IntersectID, autonumber
- IntSectID, long, FK to Intersections
- Run_waypoint_ID, long, FK to tbl_Waypoints_Master_List
- DirectionID, long, FK to DirectionIDs
DirectionIDs 'so it is not confused with more generic use of term
- DirectionID, autonumber
- Direction, text, 10 ' E-W, N-S, etc
store Latitude and Longitude as numbers in separate fields so that you
can calculate distances and use them for ordering records. Currently
you are storing BOTH Lat and Long in a text field... if you have this
information, you can put it to much better use!
Ideally, roads would be straight; but they are not -- this leads to more
complexity. You are probably under the gun, so to speak, by your boss
to get this done. Please understand that what you are attempting to do
is complicated.
On the form to identify Road Restrictions, you could store the beginning
intersection, the ending intersection, make sure that both are in the
Intersections and Intersect_Waypoints table and, if not, put them there.
then if necessary, make sure that the intermediate intersections are
also stored. This will make it possible to store the data once, keep it
ordered, and reuse it (the true goal of a database).
What you are doing it not easy, but certainly can be done. I am
offering ideas, not a complete solution; you are the best one to see
what you have to work with and, given possibnle methods, determine what
will work best for your situation. You must spend time studying if you
really want to get this right. The way you see it, you are on your last
legs of development; the way I see it, you are just beginning to grasp
the complexity of the problem. If you are looking for what I refer to
as duct-tape, sorry to have wasted your time.
~~~
I apologize that I have not been able to give you the exact answers you
seek. When I help someone, I first try to help them get onto the right
path with their data structure -- if the foundation isn't right, then
anything you do will be a temporary patch to make something work -- and,
once done, you may have more patches than cloth!
"not the answer I was expecting to the main question that began this thread"
It seems to me that what you have been unable to do is to see your
database from a different perspective -- I understand this; it is hard
to see the trees when you are knee-deep in alligators.
You keep coming back to the same question and I fault myself for not
being better with explanations; I just don't know any other way to put it.
I will be unavailable for the next 1-1/2 weeks. I was hoping to wrap up
my responses to you before my trip so you are not left hanging. Maybe
someone else can help.
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:
The reason I hadn't changed them (yet) was because I am still dealing with
the problem of how to deal with various suplicate Addresses that are in the
waypoint table that will constitute the eventual address master list, the
table that you call roadnames. The problem is that the full database is a
live document (unlike the cutdown version I send you) and I can't allow the
various waypoint lists to fall out of order, at the same time, If I
consolidate the dupe addresses into single-entity representations of those
addresses, I will end up with gaps in my waypoiint lists. In short the whole
thing has to be done manually and meothodically, I can't see how a query
would achieve the same result. So for now, I am trying to do just that, make
the addresses from the waypoint table spawn a tbl_Waypoints_Master_List, so
pleae bear with me while I get this done and I will come back to you.
I jiust wanted to know how the process of creating copies of the related
'tbl_Road_Restrictions' and its sub-table 'tbl_Road_Restrictions_Detail'
would work as much out of curiosity as it is out of need.
Though to be honest, I am confused by your answer
"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."
which just sounds like a description of a combo box using autoexpand and not
the answer I was expecting to the main question that began this thread.
regards
Eric
"strive4peace" wrote:
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