Best method for situation??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a set of error codes (21 of them) that I need to keep track of for a
given name and date combo. I have created a form that works well if I simply
add records - when the "add" button is clicked on, my code loops through all
the checkboxes (one for each error code) and creates a new record detail for
each one. My problem is that I would like to make this form 'editable'. I
have already set it up so that I can see if a record exists for a name/date
combo and if so I display a message to this effect, otherwise, I am free to
add a new record. Does anyone know of a good method that would allow me to
display the record information (error code details) when a name/date combo is
found? I am not sure how to pull the information from the underlying tables
(5 of them) to display on my form...

Any guidance would be greatly appreciated - I have reached my limits with
Access and do not know where to go from here.

Thanks,
Carrie
 
Carrie

Based on your description, I don't have a clear picture of your underlying
data. You mention "5 tables", but didn't describe how they are related.
How you bind table fields to form controls depends on the data structure.

I guess I don't understand why you need five tables to hold error code
fields, and also don't understand why you are using yes/no (checkboxes)
controls to represent the data. I'll make the assumption that your
underlying data is structured something like "ErrorCode1", "ErrorCode2", ...

If so, that describes ... a spreadsheet! Access is a relational database
that has great functions and tools that work best with relational data.
 
Table 1: Dept code (PK), manager
Table 2: EmplID(PK), Name, manager
Table 3: Eval ID(PK), EmplID(PK), Eval Date (PK), Eval comments
Table 4: Detail ID(PK), Eval ID(PK), Error Code(PK)
Table 5: Error Code(PK), Description

The form I created is able to save the appropriate info to the correct
table, I am just unsure of how to retrieve the info from so many tables to
display on my form. Maybe there is a better way of capturing and retrieving
all this info at once?

Thanks,
Carrie
 
Thanks for the additional clarification, Carrie.

From your table structure, it appears you have a one-to-many relationship
between an Evaluation and Error Codes. Since an Evaluation appears tied to
an employee and a specific date, I can see how you'd have zero-to-many Error
Codes per Employee/Date/Evaluation.

A common method for handling one (or zero)-to-many data relationships in
forms is to use a main form and subform. In your situation, the main form
would be the Evaluation (and a combobox to lookup/select the Employee), and
the subform would be any/all related Error Codes.

Or am I still fuzzy on what you're trying to do?
 
Thanks for taking the time to help out on this.

I have a one to many relationship between Tbl 1 and 2, Tbl 2 and 3, Tbl 3
and 4 and nothing linking to table 5. My form is set up such that the main
form is linked to Table 3 and the subform is linked to Table 4 but I used
check boxes and some code to capture the error codes that were set to True,
using a loop (if you know of a simpler way to capture the error codes, I
would greatly appreciate it). By luck, when I save a record on the main
form, the data on the subform will be save to the appropriate tables - I
suppose by the relationships I set. However, as I am in unfamiliar
territory, I am unsure of how to pull that same info (record) and display it
on the form. I know that Access is set up to do this, but I am a self-taught
Access user and knowledge on this aspect is as clear as mud.

Hopefully this helps.

Thanks so much!!
 
Carrie

Your Table4 appears to contain zero-to-many ErrorCodes for an Evaluation.
Table5 appears to be a "lookup" table that gives a human-readable face to
the ErrorCodeIDs.

If you already have a subform with Table4 records on it, you can use a combo
box on that form, bound to your Table4 ErrorCodeID field, and looking up
ErrorCodeID, ErrorCodeDescription. If you set the subform to Continuous
Forms, you can add/view multiple error codes related to the evaluation.
 
Back
Top