Solved Weird case of passing values from parent to subform (1 Viewer)

[dev_omi]

Here is a dummy version of my situation:

Any combination of first and last names is valid. I want to build a form for assigning tasks (and setting one as default) for each combination. It should look roughly like this:

I can't seem to get this to work. If I have a "nameQ" query that just selects * from fname and lname, use this as the data source for the parent form, and then link the Parent-Child fnameID and lnameID fields, then I can't use the parent combo boxes to select anything--the error in the status bar says, "Control can't be edited; it's bound to AutoNumber field 'fnameID'". If I unbind the parent combo boxes, I can use them as filters for the subform, but then they don't feed their values into any new records (so I get a missing values error).

Would appreciate any help.

 

[plog]

You've nailed it--you can't do what you want exactly like what you want. So, what do you want that is possible?

The closest I think you can get is to unbind the main form, add unbound inputs for Task and Default and a button to the main form. User inputs data into all those unbound inputs, clicks a button and that adds data to nameXtask and refreshes the subform to show that new record. The fname and lname dropdowns would have OnChange events that changes the filtering of the subform to show just those records that match the current fname and lname in the main form.

In all cases, both your queries are unnecessary to achieving anything with your form. Ditch them.

 

[MajP]

If I unbind the parent combo boxes, I can use them as filters for the subform, but then they don't feed their values into any new records (so I get a missing values error).

Not sure what you are talking about. I made the main form unbound. I can then pick any combination of F and L, and in the below image I can add Dust to Alice Johnson no problem.

 

[The_Doc_Man]

Just an observation that in the long run might not count for much, but splitting first and last names could lead to combinations that don't actually represent someone's name. Left up to me, I might make a table that had every person list with first and last name in the same table, thus reducing the number of selections to be made and simultaneously limiting the combinations of names to people who actually exist.

 

[dev_omi]

Not sure what you are talking about. I made the main form unbound. I can then pick any combination of F and L, and in the below image I can add Dust to Alice Johnson no problem.
View attachment 104614

I'm gonna get back to this thread when I'm at work tomorrow, but were you also able to change the first and last names? That was the other problem.

 

[dev_omi]

Just an observation that in the long run might not count for much, but splitting first and last names could lead to combinations that don't actually represent someone's name. Left up to me, I might make a table that had every person list with first and last name in the same table, thus reducing the number of selections to be made and simultaneously limiting the combinations of names to people who actually exist.

Oh, if these were actually first and last names in the real database, I would agree. In the actual use case, it makes way more sense, and though there will still be a few "dead combinations", I don't think it is worth designing a form for "full names". Could be wrong, but that's why I'm approaching it this way.

Another angle: I want pretty much every combination to be "available", and if one becomes relevant in the future, I don't want the user to have to remember to "activate" it, so to speak.

 

[MajP]

I'm gonna get back to this thread when I'm at work tomorrow, but were you also able to change the first and last names? That was the other problem.

If the combos are unbounded, then you can select any values in the combo. This will filter the subform and any new records in the subform will receive the proper lname and fname foreign keys.

 

[dev_omi]

Wow, nice! The reason it wasn't working for me before was that, if the parent form is unbound, when you click the three-dotted button for linking master-child fields, it gives the error "Can't build a link between unbound forms". But it looks like just typing in the values for these properties without clicking the button works.

Unfortunately, it's not working in the main database I'm working on, but I'm sure I can figure it out from here. Thanks so much!

Edit: Rebuilt the form from scratch and it works. Thank you!!

 

[MajP]

That is correct, the wizard will not help. Must do it manually.

 

[dev_omi]

Let me ask a mostly unrelated follow-up. If I don't get responses here, I'll pop it into a new thread.

I want the user to be able to select at most one default. The way I tried to do this was to use the subform's BeforeUpdate event. Here is the code, more or less (I'm editing it quickly from my main database to roughly match the nameXtask example we've been working on):

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim res As Long

    If Me.is_default = -1 Then
        If DCount("*", "nameXtaskQ", "[is_default] = -1") > 0 Then
            res = MsgBox("There is already a default task for this " & _
                "name. Change default to new assignment?", vbYesNoCancel)
           
            If res = vbYes Then
                DoCmd.RunSQL "UPDATE nameXtask " & _
                "SET nameXtask.is_default = 0 WHERE [fnameID] = " & Forms!taskF!FNameCombo & _
                " AND [lnameID] = " & Forms!taskF!LNameCombo & " AND [taskID] <> " & Me.TaskCombo
            End If
       
            If res = vbNo Then
                Me!is_default = 0
            End If
       
            If res = vbCancel Then
                Cancel = True
            End If
       
        End If
    End If

End Sub

This works fine when I'm adding a new record. It breaks down when I'm updating an existing record. Suppose I have two tasks assigned to Alice Johnson: sweeping and mopping. Suppose sweeping is her default task. Suppose I then try to set the is_default field on the mopping record to TRUE. I get an error because the UPDATE SQL statement can't run with the active record locked.

Any thoughts on how to fix this? I tried Me.Dirty = False before running the SQL, but that didn't work either.

Also, if there are much better ways to achieve this "at most one default" functionality, I'm happy to do something different. This is just what first came to mind.

Edit: I guess I can put the SQL statement in the AfterUpdate event and just make it conditional on another DCount function. The "No" and "Cancel" responses would still work as intended, and "Yes" would just allow the record to be saved, triggering the AfterUpdate event. Is that the best solution?

Edit 2: Well, I think that worked, and I think I managed to account for all of the weird cases.

 

[Pat Hartman]

Your dCount() is incorrect. You need to have a where that includes THREE fields. fnameID, lnameID, and is_default.

I'm with Doc, I would never separate first and last names this way. You need ONE ID for what is a person. I'm going to assume that you dumbed this down for us and choose a bad example.

 

[dev_omi]

Your dCount() is incorrect. You need to have a where that includes THREE fields. fnameID, lnameID, and is_default.

I'm with Doc, I would never separate first and last names this way. You need ONE ID for what is a person. I'm going to assume that you dumbed this down for us and choose a bad example.

Whoops! Typo. It actually counts nameXtaskQ which is already filtered by the controls in the parent form.

And yes, as I explained, I substituted names for what I am really working on for sake of privacy. I don't think the example is bad--all that mattered for this question was form, not content. MajP was easily able to demonstrate a solution with the dummy data. But yes, if I was asking about basic table design and these were the real tables I was working with, I would agree with you guys 100%.