Continuous Form bound to Table A with Unbound Combo Box to Append Records to Table B

[padlocked17]

All,

I have a table called tbl_TrendAreas that is the recordsource for a continuous form displaying all of the "Options" a user can select to grade an individual on.

I would like to have an unbound combo box on each record that associates a score for the option.

I already have a MemberID and ExamID that is selected on the form via a combo box.

When the unbound combo box is updated, I would like a record appended/updated/deleted from the table tbl_Trends with the fields (MemberID, ExamID, TrendAreaID, ScoreID).

Bottom line. I just want a list of all the Trend Areas listed out with the option to grade just 1 or all of them and have another table appended/updated/deleted based on the selections.

Am I over complicating this? I feel like I am, so any suggestions would be very welcomed.

 

[vbaInet]

Are you saying that your combo box will have these three options appended/updated/deleted? And it should perform the action selected?

 

[padlocked17]

Are you saying that your combo box will have these three options appended/updated/deleted? And it should perform the action selected?

No, the combo box would be used to select the score and it would then update if the value was different, add the record if none exists or delete the record if a score was deselected.

 

[vbaInet]

Alright. What value will indicate that the record should be deleted?

Anyway, before you go any further, you can't have an unbound combo in a Continuous Form or in Datasheet view. The closest you can get is by having a look at the Continuous Form Combo example in the link below:

http://www.databasedevelopment.co.uk/examples.htm

 

[padlocked17]

Alright. What value will indicate that the record should be deleted?

It would essentially be the act of removing a score that would cause a record to be deleted in the separate table I've been mentioning.

Anyway, before you go any further, you can't have an unbound combo in a Continuous Form or in Datasheet view. The closest you can get is by having a look at the Continuous Form Combo example in the link below:

http://www.databasedevelopment.co.uk/examples.htm

I just realized that I can't have an unbound control on a continuous form. I'm not stuck on using a continuous form, but don't have a better solution that will display all of the criteria and allow for the grading of each without first clicking the criteria and then grading it.

I'm thinking the only other way to handle it would be to use a listbox and have it display all of the possible criteria with a column dedicated to showing what the score was. I could then allow the user to double click a record in the listbox with a popup form to change/edit the score. With this solution, and even with the continuous form, I'm having trouble figuring out how to actually append/update/delete records to a separate table *ONLY* when a specific criteria record is graded.

Does that help clarify what I'm looking to do any?

 

[vbaInet]

To be honest that sounds like a lot of unecessary work when a value is deleted from the combo box. Think about how many users you will have using the db and how many times that will execute.

So it looks like you've given it some good thought and came up with an alternative solution. What you need to do is fire off an Append Query, Update Query or Delete query depending on what value is selected. The query will be linked to this other table you describe and the to fire it off (for starters) would be DoCmd.RunSQL.

Use a SELECT CASE or IF..ELSE IF statement to determine what action query to run.