Enable/Disable a field based value of another field in the same table

[Deecrypt]

Hi all,
Access noob here. I have two fields (availability, borrower) in the
table dvd. The availability field has a lookup as the data type so
user can select either "Borrowed" or "Available" vaules. Is it
possible to make the "borrower" field uneditable if the value in the
"availability" field is "Available" and editable if "Borrowed" is
selected?

Thank you in advance
Deecrypt

 

[Guest]

Hi all,
Access noob here. I have two fields (availability, borrower) in the
table dvd. The availability field has a lookup as the data type so
user can select either "Borrowed" or "Available" vaules. Is it
possible to make the "borrower" field uneditable if the value in the
"availability" field is "Available" and editable if "Borrowed" is
selected?

Thank you in advance
Deecrypt

You can do this but more information is required first. You say fields,
these are table items, does this mean you are working directly in the table
and not in a form? If so, dont, its very bad practice.

Secondly you say that the availability field is a lookup, again, this could
mean you are working directly in tables, again don't, and certainly don't use
the lookup option in the table design as this causes numerous problems in
Access.

The right way forward is to design a simple form based either on your table
or on a query based on your table. Your should then have "Controls" which
appear on the form not fields.

Once you have done that, it is possible to put code in the on change event
of the one control which limits edits in the other control.

This can be advised once you state the controls that appear on your form.
Bear in mind that you can still use Lookups in forms. I hope I have not
mis-understood your question?????

HTH Mike B
--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Guest]

Firstly I'd advise that you don't use the lookup wizard when selecting a data
type for a field in table design. It has several unfortunate side effects
and its inclusion in Access is generally regarded by Access developers as
having been something of a blunder. I've assumed below that when running the
wizard you opted to type in the values as a value list rather than have them
stored in another table. If so you'll have avoided the side effects of the
wizard fortunately, which kick in when the values are in a separate table,
but you could have set up the field in exactly the same way very simply
without the wizard, so its best to steer clear of it.

You can control the input in this way provided data entry is only via a
form, which is how it should be in any database application worth the name in
any case. You'll need to write a little bit of simple VBA code.

Firstly you need to prevent data being entered in Borrower before a value is
selected for Availability. This is done in the form's Current event
procedure like so:

If Me.Newrecord Then
' for new record disable Borrower control
' until Availability selected
Me.Borrower.Enabled = False
Else
' for existing record disable Borrower control
' if Availability is not "Borrowed"
Me.Borrower.Enabled = (Me.Availability = "Borrowed")
End If

Next you need to control what happens when a user selects an Availability
value in the combo box. If the user selects Borrowed then you enable
Borrower; if a user selects Available the you not only disable Borrower but
remove any value already in it by making it Null. The code in this case goes
in the AfterUpdate event procedure of the Availability control:

If Me.Availability = "Borrowed" Then
' enable Borrower control if Availability = "Borrowed"
Me.Borrower.Enabled = True
Else
' disable Borrower control if Availability = "Available"
' and remove any existing data
Me.Borrower = Null
Me.Borrower.Enabled = False
End If

Ken Sheridan
Stafford, England

 

[Deecrypt]

Ken and Mike,
As you guys can see, I am fairly new to Access.

Mike: Yes I was directly inputting data into tables and using the
Lookup dropdown menus that are available in table design. I will
adhere to the advice about using forms and inform you once this has
been achieved.

Ken: Your approach looks like it will work for me. I will first
change the way I am entering data into my tables and then try and use
the example code you have provided.

My thanks to you both and I will be posting in the next day or two
once design changes have been made.

Cheers
Khurram

 

[Deecrypt]

Hi all,
As agreed, I now have forms to enter data in my Dvd and borrower
tables. The aim of this database is to be able to track who has
borrowed a particular dvd. Currently I have the two tables with their
respective forms for data entry. Each table has a unique id assigned
as an autonumber. No relationships currently exist. I need to know
how I can proceed save borrowed dvd information in this database. No
need to be detailed. I just need to know the basic concept and I'll
find the nitty gritty details. I have listed the field names of the
two tables below

dvd:

dvd_no Autonumber
dvd_name Text
region Text


borrower:

borrower_no AutoNumber
borrower_last_name Text
borrower_first_name Text
phone Text


Thank you kindly
Khurram

 

[Guest]

Khurram:

What you now need is a third table to represent the many-to-many
relationship between DVD and Borrower; lets call it Borrowings for the moment
but you can call it whatever name you wish of course. This table will have
two foreign key columns, dvd_no and borrower_no which reference the primary
keys of DVDS and Borrower in separate many-to-one relationships. The table
will also have columns for other attributes of each 'borrowing' such as
date_borrowed and possibly date_returned. You might want other columns to
record other facts about each 'borrowing'.

For data entry the usual approach would be to have a DVD form based on a
query such as:

SELECT *
FROM DVD
ORDER BY dvd_name;

which would order the form by the DVD name, and embedded within it a subform
in continuous form view based on a query such as:

SELECT *
FROM Borrowings
ORDER BY date_borrowed;

The LinkMasterFields and LinkChildFields properties of the subform control
would be dvd_no. In the subform you'd have a combo box bound to the
borrower_no field with a RowSource of:

SELECT borrower_no,
(borrower_first_name + " ") & borrower_last_name
AS full_name
FROM Borrower
ORDER BY borrower_last_name, borrower_first_name;

Its properties would be as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm

For the last you can of course use the rough equivalent in inches, but the
first dimension must be zero to hide the first column.

You don't need any control bound to the dvd_no on the subform, but you would
add controls for date-borrowed, date-returned etc. Whenever a dvd is borrowed
you simply go to its record in the main form and add a row in the subform,
selecting the borrower from the combo box's list and entering the date
borrowed.

You can also reverse the set-up of course having a form based on the
Borrower table and a subform on the Borrowings table but this time with the
combo box bound to the dvd_no field and with its RowSource based on the DVD
table. This form would show you all the DVD's borrowed by each Borrower.

Ken Sheridan
Stafford, England

 

[Deecrypt]

Hi Ken,
First of all thank you for the excellent set of notes. Really
detailed which I appreciate. I believe I have followed your advice to
the tee however I seem to be having some problems with it. Below are
the details.

When I want to make a dvd booking, I go to the main form (called
dvdsByDvdname) which contains the subform (borrowingsByDateBorrowed).
The subform contains the combobox (borrower_no).

When I click on borrower_no combobox, it asks me for the
borrower_last_name. Whatever I type in this, the combobox returns all
the first names and what I had typed in as the last name for all the
names.

If I select any of the names and then enter a date in the date column,
Access gives me the following error "You cannot add or change a record
because a related record is required in the table 'Borrower'". I am
unable to save this record?

Please let me know where I could be making a mistake. Thank you again
for this.

Cheers
Khurram

 

[Deecrypt]

Hi all,
An update below as I managed to solve one of my queries but I still
need help regrading the borrower surname.

When I want to make a dvd booking, I go to the main form (called
dvdsByDvdname) which contains the subform (borrowingsByDateBorrowed).
The subform contains the combobox (borrower_no).

When I click on borrower_no combobox, it asks me for the
borrower_last_name. Whatever I type in this, the combobox returns all
the first names and what I had typed in as the last name for all the
names.

I still have this problem. Am I supposed to be prompted to enter the
borrower's surname everytime I open the main form? I'd rather I didnt
get this prompt until I click on the combobox.

If I select any of the names and then enter a date in the date column,
Access gives me the following error "You cannot add or change a record
because a related record is required in the table 'Borrower'". I am
unable to save this record?

I've managed to fix this and the data is now storing in the borrowings
table.


Cheers
Khurram

 

[Guest]

Khurram:

The fact that you are being prompted for the surname means that the combo
box's RowSource does not recognize borrower_last_name as the name of a column
in the Borrower table, and therefore treats it as a parameter. Check the
RowSource property carefully to make sure that the column name is spelt
absolutely correctly.

What you should get when you pull down the combo box's list is a list of all
the names form the Borrower table in the format 'Ken Sheridan' sorted
alphabetically by surname. You then simply select one from the list as the
borrower of the DVD currently shown in the parent form. You should not be
prompted for the name at any stage.

Ken Sheridan
Stafford, England

 

[Deecrypt]

Perfect, Thats exactly what the problem was.

Cheers Ken

Khurram