Ideas please?

[Jake0254]

Hello



I'm trying to create a database in as dynamic a way as possible so that any changes can be managed by people that aren't me basically, i.e. people who have little to no experience of access.

So for the purposes of explaining my quandary, we have a table of Criteria, a table of MeasurementTypes, a table of MeasurementTypeValues, a QALog and a QAScoreLog.

What I want to do is create an entry in the QA Form that provides basic information of the person being QAd and the channel the QA is being conducted on.
I then want a subform to open up that will display a list of the appropriate criteria - the criteria should be looked up against the 'ActiveCriteria' query (this could be five, ten, fifteen results etc.), and only return results where Channel matches the QA Form. This is the first problem - how do I get a Form to display a dynamic number of fields and list the results of a query? I tried a continuous form, that isn't possible due to the other issues I have.

I then want the user to able to assign a score to each of the criteria. This is dependent on the MeasurementType of that Criteria. This is then looked up against the MeasurementTypeValue table to find out which values should be listed in the combo box, such as 'Yes';'No';'N/A' or 'pass';'fail';'N/A' or '1;2;3;4;5' etc.

I then intended to have a query append both the criteria used and the score given to the 'QAScoreLog', with the main details being appended to the QALog - both obviously linked together by an ID from the QALog.

I'm a little stumped and would appreciate any advice you could provide!

Thanks

[ranman256]

you can build an easy 'point and click' frontend.
the app opens with a menu: put buttons on the form to open the various input forms.
the form allows user to add/edit data.

build queries , reports users can just click.
I have an Admin button for the administrator to setup : table values, folder defaults, etc.

[Micron]

how do I get a Form to display a dynamic number of fields

Impossible? No. Would anyone do this? No.
Getting table design correct right from the start is paramount. If you're saying you can't do something because of "other issues" that raises a red flag. Seems like you're off on the wrong foot already. Maybe research normalization to see if you're on the right track? For me, not much clue because of all the jargon in your post.

[June7]

Sounds similar to a db I had to build. Materials are tested for quality and results are output in report. Sample is logged in and desired tests selected, records in appropriate tables are created. When tests are completed, results are input to the records, report is printed. In your case the 'material' is a person to evaluate. Will the same people be evaluated repeatedly? If so, have a table of people with their detail info. You select the individual when creating entry into QALog.

Then you probably also need a table that establishes standard sets of QA 'channel' criteria to be evaluated. Select a channel and code batch creates a set of criteria records in QAScore associated with new record in QALog.

The more 'user friendly', the more code.

[Micron]

person being QAd and the channel the QA is being conducted

I took it as a question and answer thing. Channel is television?
It's anybody's guess.

[Jake0254]

Sounds similar to a db I had to build. Materials are tested for quality and results are output in report. Sample is logged in and desired tests selected, records in appropriate tables are created. When tests are completed, results are input to the records, report is printed. In your case the 'material' is a person to evaluate. Will the same people be evaluated repeatedly? If so, have a table of people with their detail info. You select the individual when creating entry into QALog.

Then you probably also need a table that establishes standard sets of QA 'channel' criteria to be evaluated. Select a channel and code batch creates a set of criteria records in QAScore associated with new record in QALog.

The more 'user friendly', the more code.

This is pretty much exact, and is how I thought to do this in the end last night. I've got a query I'm trying to use to append the relevant criteria into the QAScoreLog, however I'm obtaining the 'QAID' (that links with the QALog data) from the form - the problem I have is that I'm getting a symbol?! I've tried compact and repair as I know this is a common issue with Memo fields (this field is an autonumber) and I've also tried the simplest method of deleting the field from the form and re-adding it, both to no avail. Image below - every time I try something new the symbol changes - I've had ⇵, ‼, ◄, as well as the below. If I query the table directly, it is fine.

Anyone seen this before?

[isladogs]

Assuming that's not the actual data, either you are using a font like Wingdings or you have corruption in your memo field.
Have you checked the data in the table itself?
Do you have a 'strange' table called MSysCompactError or another called MSysRecoveryErrors?
NOTE you may need to tick Show Hidden Objects /Show System Object in Access options to see these if they exist.

[Jake0254]

These are the only hidden objects



It is an autonumber field, not a memo. In both the form and the source table, the data is fine.




Data source:

[Jake0254]

If it helps, in the query the field is set to Expr1: [Forms]![QAform]![QAID]

[June7]

In what query?

[isladogs]

OK I'm baffled. Are you able to upload the relevant section of your database?

[Jake0254]

See attached!

[June7]

First thing I did was look at Relationships window. Why is QA_Log (as QA_Log_1) linking back to QAScoreLog? This makes no sense.

Then there appears to be circular relationship between 4 tables which might cause confusion.

Lookups in table. Most of us here would recommend not doing that. Just build comboboxes on forms.

There are 2 forms but not set up as form/subform, in spite of one being named QASubform.

Using embedded macros which are harder to debug than VBA (in my opinion, but then I never use macros).
https://sourcedaddy.com/ms-access/te...ing-macro.html
https://www.techonthenet.com/access/...10/debug01.php

Some data would have been helpful. The only contact type with any criteria established is Chat.

There is no CurrentAgents table so of course this combobox errors. A little annoying I have to work around this.

[Jake0254]

First thing I did was look at Relationships window. Why is QA_Log (as QA_Log_1) linking back to QAScoreLog? This makes no sense.

Then there appears to be circular relationship between 4 tables which might cause confusion.

Lookups in table. Most of us here would recommend not doing that. Just build comboboxes on forms.

There are 2 forms but not set up as form/subform, in spite of one being named QASubform.

Using embedded macros which are harder to debug than VBA (in my opinion, but then I never use macros).
https://sourcedaddy.com/ms-access/te...ing-macro.html
https://www.techonthenet.com/access/...10/debug01.php

Some data would have been helpful. The only contact type with any criteria established is Chat.

There is no CurrentAgents table so of course this combobox errors. A little annoying I have to work around this.

Thanks June - appreciate the feedback.

Sorry - had to remove CurrentAgents due to the PD in there.

Why would QALog relationship to QAScoreLog be an issue?

Lookups in table - that's interesting - thanks for the tip! Why is that for my curiosity?

Forms / Subforms - yes you're right - I was trying to get everything else to work before I finish any aesthetics!

[isladogs]

I've simplified your relationships removing two duplicate of same tables

Your append query fails as you are trying to append a text field Description into a number field Criteria.

Unable to test more fully as there are referenced items missing.
I've not seen your strange symbols but perhaps I'm not looking in the right place