Help needed for a non-programmer (1 Viewer)

Be gentle with me people - I am a first time forum user and I'm not a programmer but can follow instructions really well.
I'm trying to create a db using a form for data input. What i need is for a drop down box that when a specific item is selected, this will then go to another list, currently a table, that is to be selected from and that entry to be entered into the db/form. I have no idea how to do this. I know it's possible from reading other threads but I must be stupid because I can't work out how to achieve it. Help please!!!

What you are trying to do is very basic and easy but needs to be setup to work in the way you intend.

The Drop Down Box is called a Combo Box.

Please explain what your end result is and the starting point you have eg
End Result - create a new Customer record
Starting Point - blank form, form with all current customer records displayed in alpha sort order ...

End result is to create a new record containing surgery details. The combo boxes Consist of 3 main categories. Each of these categories has a sub category which is the one that will end up in the record. For example If I select 'Hernia' this would then bring up a list of 7 to select from. If it's at all possible, I would like to have both selections recorded in the record but I haven't set the table up for this. This database will then be used for auditing purposes - but that I can handle. I'm using Access 2007.
I am starting with a blank form as there are no records in the database at the moment. i have created all the combo boxes as tables for the main categories and sub categories.

If the result is to create a unique record for an Operation on a Hernia then on your Main Form, you have two combo boxes - left to right.
the left combox will select Option One - Hernia.
The next combo box will select the sub category and refer to the result of combo box 1 (left hand side).
Left right of course is only a visual issue.

You would add code to the 2nd combo box to confirm there is data in the first combo box.

I would have the Row Source of each combo box to be an sql/query.

The first would refer to the main complaint and the 2nd would refer to the sub complaint where it matches the first main complaint.

How have you set up your tables ??

You should have two eg tables of complaints. tblComplaintsMain and tblComplaintsSub
Or one table but include a field MainComplaintID.
This would hold the Primary Key of the main Complaint.

I imagine two tables would suit where a sub complaint is never a complaint on it's own and One table would suit where sub complaints could be a complaint on there own.
This is where industry knowledge plays a part.

You also need a table tblOperations which will hold the record you are creating.

Trust the above is how you want to proceed or, if not, does provide some ideas.

You've lost me PNGBill. somehow I have managed to get my main category table to off a drop down arrow. I just don't know how to link the sub category tables to the individual record in the main category table. Getting closer.......I can feel it

No primary keys created for anything

Be mindful of Normanlising your database. This refers to how you set up your tables.

Some points I can think of that should be considered in new databases.

Don't put spaces in table and field names (any names)

Don't use table level lookup fields. Use a Form for this.

Name all your form and report controls properly. Do not rely on the default name. eg field name LastName control name txtLastName

Try and use double or even triple names for field, tanle and control names eg LastName, tblPatientComplaints, frmPatientVisits txtLastName
Apart from being easier to know what you are dealing with (later) you help to avoid stumbling on Reserved words. There are hundred of them.

Agree on a standard Form layout ie Close button in same place etc. This makes it easier later on.

confused1 said:

You've lost me PNGBill. somehow I have managed to get my main category table to off a drop down arrow. I just don't know how to link the sub category tables to the individual record in the main category table. Getting closer.......I can feel it

Click to expand...

You need to set your Table Relationships.

Use a Form to hold your Drop Down Arrow (combo box), not a table.

confused1 said:

No primary keys created for anything

Click to expand...

How can you create a table without a primary key ??

Primary key can be a text field (complaint name) but I would think you are better to have it as a Number field and the 2nd field is the complaint name.
You can still set the text field to not allow duplicates ie avoid two records for "sore finger"
The database is faster with numeric primary keys and Operators do not need to see the primary key.

Every Table should have a Primary Key.

I use AutoNumber.

The Primary Key is a Field that uniquely identifies a Record.

E.G. 'Hernia' may be Record 28. 'Hernia' would be unique. Only appears in Record # 28. This is in reference to using the table as a look up not patient records.

In Patient Records you may have 100s of Patients with 'Hernia' but you would store 28 not 'Hernia'

Link to Naming conventions.

http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Bill,

Hope you don't mind my jumping in here.

Thanks RainLover, watching Chevy Chase movie