hi all

i have created a form to enter details into a 'job' table.

here is the part of the form my question deals with.

FIELD- Airport DATATYPE- Yes/No
FIELD- Terminal DATATYPE - Lookup wizard (the values 1-5 stored in a separate terminal table)

basically the system involves entering minicab jobs into the database.
the job may or may not be at the airport.

THE RESTRICTION I WANT TO PLACE ON THE JOB FORM/TABLE

if the airport checkbox is ticked (Yes), then the terminal value can be set (1-5) from the form.
if the airport checkbox is unticked (No), then the terminal value cannot be changed from the form.

if this can be done, how can it be done? anyone..??

First I do NOT recommend using Lookup field on the table level. For more details onthat check out this article: http://www.mvps.org/access/lookupfields.htm

In the After Update event of the checkbox AND the On Current event of the form (assuming you use the same form for entering and editing data) put code like:

If Me!chkAirport then
Me!cboTerminal.Enabled = True
Else
Me!cboTerminal.Enabled = False
End if

im not exactly sure where to place that code

on the actual form i choose build event, and have this code

Option Compare Database

Private Sub Detail_Click()

End Sub


do i place the code you gave me above within this section, and on the checkbox?

Don't use Build Event. Open the Properties dialog and go to the Events tab. Select the On Current event and click on the elipsses and use Code Builder. next select the Checkbox and use the After Update event.

http://k.1asphost.com/forum66/chop.jpg

this is an image of my table design.
i know that it is not the best way it could be done, but this database really does not need anything else apart from this. i know i have named the fields with spaces in between them, which is not right. i would just like to know the code for my field names,

if you look at the table, lets say i just want to put in my restriction on the fields
Pickup Airport AND Pickup Terminal

what code would i place in the form event

what code would i place in the checkbox?

http://k.1asphost.com/forum66/chop.jpg

this is an image of my table design.
i know that it is not the best way it could be done, but this database really does not need anything else apart from this. i know i have named the fields with spaces in between them, which is not right. i would just like to know the code for my field names,

if you look at the table, lets say i just want to put in my restriction on the fields
Pickup Airport AND Pickup Terminal

what code would i place in the form event

what code would i place in the checkbox?

You're right this is not the best way. You should have tables for Customers, Companies, Addresses, and Trips at the very least. You can significantly speed up your data entry if you design this properly. As well as do a better job reporting.

But I've already answered your question. I've given you an example of the code. And told you it needs to go in the After Update event of the checkbox (for new or changed entries) and the On Current event of the form (for existing entries).

Again, I strongly urge you to normalize your database. If you don't you will be back here again and again trying to do things that would be easy in a normalized structure.

yes, i sometimes envision how the tables should look based on how they are gonna be entered into the database from the form.
I shall not do this, i will normalise the database like i should have done.
I wasnt actually sure on how to enter the code into the checkbox with my long spaced field names, but that is not an issue now, i will redesign the whole thing.

thanks for making me realise that ALREADY, even at the earliest possible stage of the simplest possible database, normalisation of some sort is necessary.

Database design starts with a process like:

1) Determine what info the database needs to capture
2) Organize the data into logical entities.
3) Normalize the data into related tables
4) Design forms to enter the data
5) Design queries and reports to extract the data

Normalization is key. Properly normalizing at the begining pays dividends at the end.

ok i have the fields

pairport - checkbox
pterminal - selection box with values 1-5

have used the code builder method on both the form and the checkbox.

this is what the final code in the form looked like

Option Compare Database

Private Sub Form_Current()
If Me!chkpairport Then
Me!cbopterminal.Enabled = True
Else
Me!cbopterminal.Enabled = False
End If
End Sub

Private Sub pairport_AfterUpdate()
If Me!chkpairport Then
Me!cbopterminal.Enabled = True
Else
Me!cbopterminal.Enabled = False
End If
End Sub


i get this error

runtime error 2465... cant find the field chkairport, what am i doing wrong, what do i have to do??

That's because the control is named pairport not chkpairport. You have to use the correct names for the controls.

Option Compare Database

Private Sub Form_Current()
If Me!pairport Then
Me!cbopterminal.Enabled = True
Else
Me!cbopterminal.Enabled = False
End If
End Sub

Private Sub pairport_AfterUpdate()
If Me!pairport Then
Me!cbopterminal.Enabled = True
Else
Me!cbopterminal.Enabled = False
End If
End Sub



using this code, i named the actual check box and menu with the names used in the code

now when the form opens, no errors are found

the checkbox is unticked and the terminal drop down is disabled like i want it

when i tick the checkbox however the terminal does not become active

and i have just deleted the records from the job table, and shut the database, now when i try to open the job form i get this error

runtime error 94
invalid use of null

If you try testing a checkbox that is Null you will get that error. Either set a default value for your checkboxes or trap the Nulls with IsNull()

How do you know the terminal doesn't become active? If its an empty value, you might not see any physical change on the form.

ok what do i place in the table design default value of the pairport Yes/No field

to make its default value unchecked when it is opened.

ok firget that, i have made the default value No and it is unchecked when the form loads.
i get no errors when the form opens. when i tick the checkbox on the form however, the terminal drop down table, with my lookup values of 1-5 does not become active in a sense that the actual drop down does not work and the values cannot be chosen from the form

ok wait dont reply yet i think i have been editin the wrong database, just watch this space

yeah i got it workin, cheers for that

Should work. Try making it

If Me!Pairport = True Then

See if that makes a difference.