R
Rich1234
Hi
I am having difficulties in accomplishing the things listed below. I am a
relative newcomer to Access and have tried everything I can think of after
spending much time putting the database together. If you are able to help
with any or all of the below points, your expertise would be hugely
apprecciated.
I have a main form (Vehicles) containing 3 subforms:
OtherT (One to Many with many at OtherT end)
Previous Stops (One to Many with many at Previous Stops end)
Known Drivers (Many to Many with Vehicles, using DriverVehicles as a
junction)
I need to have two versions of the form: one which is read-only and one
which allows edits, deletes and new records. I have created a form with
subforms that displays all of the above information correctly (after I have
input the data myself directly into related tables) and am now trying to
create a way for a user to edit, delete and add records using a version of
the form. The table and relationship structure is working (although some
relationships don't show up in the relationships window - see point [4]
below) and I now need to find a way for users to edit/delete/add new records.
1) My first question is about the switchboard "open form in add mode." By
definition, I guess this must open the same form just in a different way.
Does it simply override any settings in the form / control property box
(allow edit, allow delete etc.?) And if the main form has subforms, are
these subforms similarly overridden in order to accept edits/deletes/new
records?
2) When using a command button to add a new record on a form, it only works
when "Allow Additions" for the form is set to yes in the property box. This
means that the blank "extra" record is permanently shown onscreen at the end
of any existing records. Is there any way around this, so that the blank
record is not shown, and only appears when the command button is pressed?
3) I am using a query to display the records in the Previous Stops form. I
am using a query to draw from an underlying table (related to the main form
by VRM field) because it enables me to sort the records in chronological date
order which I can't do by showing the straightforward table (right?) How do
I add new records into the underlying table using this query? If I use the
"open form in add mode", will this change the query automatically to be able
to edit/delete/add new records? Is this possible?
Or do I have to use a form based directly on a table (not query) to do
this?
(I have selected "allow additions" in the OtherT subform which allows me to
enter new records here, related to the main form by one to many. the
Previous Stops is also a one to many but uses a query... this is what has
thrown me.)
4) I am having problems with Access refusing to show the relationships after
I have created them in the Relationships window (ie when I close the window
then re-open it.) This is making things more convoluted! Is there any way
to fix this? I'm using Access 2003 and it's driving me crazy.
5) The Known Drivers subform is using a query which draws fields from two
tables (many to many junction table, VehiclesDrivers and Drivers.) How am I
able to select which drivers are related to which vehicle using this query?
Or is this not possible? I guess I need to write into the Vehicles Drivers
junction "directly." And how can I add new drivers to a vehicle (drivers who
do not yet exist in the database?)
Am I being too ambitious in trying to accomplish all of this using one form
(the same one that is used to display the info?) I am trying to make it user
friendly - but it is frying my brain!
Thank you for your help in advance.
Rich
I am having difficulties in accomplishing the things listed below. I am a
relative newcomer to Access and have tried everything I can think of after
spending much time putting the database together. If you are able to help
with any or all of the below points, your expertise would be hugely
apprecciated.
I have a main form (Vehicles) containing 3 subforms:
OtherT (One to Many with many at OtherT end)
Previous Stops (One to Many with many at Previous Stops end)
Known Drivers (Many to Many with Vehicles, using DriverVehicles as a
junction)
I need to have two versions of the form: one which is read-only and one
which allows edits, deletes and new records. I have created a form with
subforms that displays all of the above information correctly (after I have
input the data myself directly into related tables) and am now trying to
create a way for a user to edit, delete and add records using a version of
the form. The table and relationship structure is working (although some
relationships don't show up in the relationships window - see point [4]
below) and I now need to find a way for users to edit/delete/add new records.
1) My first question is about the switchboard "open form in add mode." By
definition, I guess this must open the same form just in a different way.
Does it simply override any settings in the form / control property box
(allow edit, allow delete etc.?) And if the main form has subforms, are
these subforms similarly overridden in order to accept edits/deletes/new
records?
2) When using a command button to add a new record on a form, it only works
when "Allow Additions" for the form is set to yes in the property box. This
means that the blank "extra" record is permanently shown onscreen at the end
of any existing records. Is there any way around this, so that the blank
record is not shown, and only appears when the command button is pressed?
3) I am using a query to display the records in the Previous Stops form. I
am using a query to draw from an underlying table (related to the main form
by VRM field) because it enables me to sort the records in chronological date
order which I can't do by showing the straightforward table (right?) How do
I add new records into the underlying table using this query? If I use the
"open form in add mode", will this change the query automatically to be able
to edit/delete/add new records? Is this possible?
Or do I have to use a form based directly on a table (not query) to do
this?
(I have selected "allow additions" in the OtherT subform which allows me to
enter new records here, related to the main form by one to many. the
Previous Stops is also a one to many but uses a query... this is what has
thrown me.)
4) I am having problems with Access refusing to show the relationships after
I have created them in the Relationships window (ie when I close the window
then re-open it.) This is making things more convoluted! Is there any way
to fix this? I'm using Access 2003 and it's driving me crazy.
5) The Known Drivers subform is using a query which draws fields from two
tables (many to many junction table, VehiclesDrivers and Drivers.) How am I
able to select which drivers are related to which vehicle using this query?
Or is this not possible? I guess I need to write into the Vehicles Drivers
junction "directly." And how can I add new drivers to a vehicle (drivers who
do not yet exist in the database?)
Am I being too ambitious in trying to accomplish all of this using one form
(the same one that is used to display the info?) I am trying to make it user
friendly - but it is frying my brain!
Thank you for your help in advance.
Rich