Ideas please?

[June7]

Review http://access.mvps.org/Access/lookupfields.htm

QA_Log should relate to QA_ScoreLog. It is the second link of QA_Log_1 to QA_ScoreLog that is anomalous. I haven't found why Access insists on creating that link. Something in one of the queries or lookups probably. It is preventing setting Cascade Delete on the valid link between the two tables.

The macro wants to open a query ActiveCriteria which doesn't exist. Another thing I have to work around.



The INSERT query errors. Records are created but the CriteriaID won't populate. Change the insert query to reference CriteriaID instead of Description.

You are seeing those funky characters when you view the insert query in Datasheet. Don't do that or just ignore them. The query runs fine with the above correction.

I have never run a dynamic INSERT query object so never noticed this before. I have always constructed these SQL actions in VBA.

[Jake0254]

Thanks both! I was so focused on the dodgy symbol I didn't even get to start looking at other fields, but thats resolved that issue. Have sorted those relationship problems too - thanks again.

I now have a new issue - sorry to be a pest!

So now that I'm appending the criteria to the QAScoreLog, I need to have a continuous form that displays the appropriate criteria - this is working fine. I now need to create comboboxes that display the options for that criteria, i.e. either a combobox with options 'Yes;No;N/A' or a combobox with options 'pass;fail;n/a' etc. How would I best get this data into a combobox?

Thanks!

[isladogs]

Use a value list with those values for your combo

[Jake0254]

Sorry whats a value list

For clarity, this option would have to work on a continuous form where the first 'form' might have a different combobox list to the next..

[isladogs]

Its one of the three choices you get when using the combo wizard.
Choose 'i will type the values I want' then add what you want to appear.
Do that for each combo.
Another method is to grab the values from a table or query. That might work for you as well.

[June7]

I think you have a table that defines the response options for each question. Set combobox RowSource with an SQL statement that has a WHERE clause. Then will likely require code in combobox GotFocus event to Requery the combobox.

Conditional combobox is a very common topic.

Keep in mind that conditional (or cascading/dependent) comboboxes with lookup alias don't work nicely on Continuous or Datasheet form. As long as you save actual descriptive value and not a key, should be fine.

[Jake0254]

I think you have a table that defines the response options for each question. Set combobox RowSource with an SQL statement that has a WHERE clause. Then will likely require code in combobox GotFocus event to Requery the combobox.

Conditional combobox is a very common topic.

Keep in mind that conditional (or cascading/dependent) comboboxes with lookup alias don't work nicely on Continuous or Datasheet form. As long as you save actual descriptive value and not a key, should be fine.

Thanks June that's exactly what I'm looking for - if I re-query though won't it change those that have already been selected and therefore remove their value?

[June7]

Requery of combobox list does not impact what is already saved into record. However, as noted, if combobox uses alias then the alias will not display when the list does not have that item. The saved key is still in table, but alias text is not available for display.