Access I need help with a data entry form in Microsoft Access 97

[vlannaman]

Hi. This is my first time ever using MS Access, and I'm a little stuck on something. I'm creating a database in MS Access 97 which keeps track of rail car incidents. The main menu of my database is a form consisting of combo boxes and a command button. The command button takes you to a data entry form bound to a table called tbl_incidents. This form allows the user to add or edit data in the Incidents table.

I also want the user to be able to search the incidents based on user-specified criteria. This is where the combo boxes come to use. I have 2 combo boxes, one each for a specific field in the Incidents table: Date and System. Instead of binding the combo boxes to their respective fields in tbl_incidents however, I created seperate tables called tbl_date and tbl_system for the combo boxes. These tables are indexed with no duplicates, this way each value is only displayed ONCE in the combo box. I cannot index the fields in tbl_incidents with no duplicates because tbl_incidents is used as a log for all problems relating to the rail cars and each incident needs to be recorded with its corresponding date and system. So for instance, I may have one entry:

[Date] 6/2/11; [System] Brake; [Incident] Brake fault on car 1555

Then I may have a following entry with duplicate dates and systems, but with a different incident:

[Date] 6/2/11; [System] Brake; [Incident] Brake pipe on car 1234 frozen

So, I need to allow duplicates in tbl_incidents. For this reason, I cannot bind the combo boxes to that table. Incidents with the rail cars happen daily and need to be recorded. The problem is the data entered into the text boxes on the data entry form get saved in tbl_incidents but not in tbl_date or tbl_system. I need the new values to also be added to those two tables in order to display them in the combo boxes. For example, if the user enters

[Date] 6/5/11; [System] Car; [Incident] xxxxxxxxxxxxxxx

I want "6/5/11" to be added to tbl_date and "Car" to be added to tbl_system. But if, for instance, the user enters a value into a field that already exists in the table, I don't want a duplicate of that value to be added to the table.

So my question is how do I store data entered into a text box in a table not bound to the form, and how can I do it so that there are no duplicates displayed in the combo box?

 

[apr pillai]

Your main issue is to bring Unique values of Date field (don't use the field name as Date because it is a builtin function, use something like incDate for incidents date) and System Fields of tbl_incidents into the Combobox Source. For that you don't have to maintain two different tables, one for date and the other for system.

You can create two Select Queries from the tbl_incidents itself and use them as Row Source for the combo boxes. This way you don't have to worry about adding the same record information on three different tables at the same time.

Copy the following SQL into a new Query's SQL Editing window and save it with the name incDateQ:

SELECT tbl_incidents.incDate
FROM tbl_incidents
GROUP BY tbl_incidents.incDate;

Create another Query with the following SQL for System combobox source, name the Query as SystemQ:

SELECT tbl_incidents.system
FROM tbl_incidents
GROUP BY tbl_incidents.system;

Use above Queries as Row source for Date as well as System Combo Boxes replacing the Date and System tables you are using now.

This way you don't have to bother about updating all the three tables at the same time. When you add a new record with different values they will automatically appear in both Combo Boxes.

NB: The current new record values, if different, may not immediately appear in the Combo Boxes, but it will update when you move out to another record.

 

[vlannaman]

Thank you so much! It worked and does exactly what I want it to do! Thanks again