subforms with 1-many from main form - why is this so hard to make it work correctly?

[misscrf]

I have this normalized database, and I don't understand why setting up the forms is so hard for me. I have the main candidate entry form. This is to enter in candidates who apply for a job. I have a tab control with a page that shows if the candidate type is 1 of a few types ( in code). Otherwise 2 pages show. 1 is for contact information. This has an address subform and a phone subform. I can't figure out how to show all address, while maintaining the combo box changes per record, which won't seem to hold on a continuous or single form. I have tried both.

Also, I have been trying to lock down code for only one of a candidate's address to be allowed to ahve a primary checkbox checked. Also that 1 must be checked. No luck there.

On the 2nd page, I have applications. I also have a sub-sub form for activities, which go by application.

These 2 subforms have some conditional combos. If the combo choice chosen is 1 answer a text box may come up - ie if an interview scheduled is the activity, a date box comes up for the date of the interview to be entered. But if the activity chosen is materials forwarded, then the date box is hidden and the office combo is shown, so that the office the materials were forwarded to can be chosen. If I enter one record, the change stays for other records. I can scroll through the records, and even though one might say offer extended was the activity, its offer result combo is not shown, the interview date text box is visible, because that was the last choice for an activity. This form is set to single.

I have screen shots to show what I am saying:

http://www.geocities.com/misscrf/images/1activityandshowtextbox.jpg
shows 1 activity entered with the interview date showing because interview scheduled is the activity chosen from the drop down.

http://www.geocities.com/misscrf/images/2shouldshowcombo.jpg
What activity 2 should look like if materials forwarded is chosen ( with the office combo showing)

http://www.geocities.com/misscrf/images/2activityandshowwrongbox.jpg
what is happening.

This is happening other places for me, so I am trying to find a fix that I can everywhere.

Thanks for any help!

 

[aleb]

It is hard to tell what is going on there without the db itself but I think you have a multiple events here.
your code is not executed when you scroll the records but it does what you need when you change the value in a combo box, is it true?
Sound like your combo box values come from the separate table.
Changes in a combo box trigger the excution of the code. But when you scroll the records the combo box values are not changed.

So you would need to put the same code on "on load" event of the subform.

To test the example run form1.

 

[misscrf]

Thank you, Pat and Alexei. I don't know if the onload is not needed, but the example you attached works perfectly I will try to implement this and see if I have any problems on my db. If need be, I will attach what I have.

Since you were so great at this problem, maybe you will understand another issue.

Pretend that that subform has a checkbox for if that record is the primary record ( if it were an address for a candidate that has multiple addresses). How would I make code that would evaluate the check and the records? I want to control it so taht if it is the first record, it becomes checked, but if more than one record is added for the candidate, I have a problem. How do I make sure that one and only one sub record for that candidate has the primary checkbox checked? I ahve played around with what the rules should be and how to enforce them, but I have not come up with a solid solution. I am almost ready to give up on caring about it, but I know if that if I don't take care of this issue, it will be a problem.

Thanks!

 

[aleb]

Well if you want to restrict the number of addresses in the table for the same person make fkey indexed and set to "no duplicates" that will allow only one record per person.
If you just need to know that there is a number of records then dcount in the invisible unbound field and conditional formating for the check box would be a good solution.

 

[misscrf]

1) I found that the onload and current do cause problems. When there is a dcount in there, something funny happens. If I click next on the navigation bar, the records go up and up and up. Like there is no end. Even though a record ( candidate record) had no address records to it. Now I just have this code in the afterupdate and change and it works good.

2) I need a candidate to be able to have multiple addresses. Sorry if I was unclear. My issue is that 1 candidate may have a residential address, a work address, and a school address. Only one of them should be the primary address. The one we should use for mailings. I need to control that only 1 address is the primary. Here is the code that I am troubleshooting.

My idea is that I want to control the primary checkbox. I want to ensure that for every candidate there will be 1 primary address, and there will be only 1 primary.

To accomplish this, I have set out to define how the rules will work. When a user begins to add a first address, that records primary checkbox will be checked. Then when more than one is added, a label will show which will say "click to change primary address" In the onclick event of the label, a message will come up "would you like to change this candidate's primary address from (record with checkbox) to (current record)?

vb yes, the current checkbox is unchecked, and the current record.checkbox is checked. After any of these the (1st record added or any change to primary) the checkbox field is locked.

Make sense?

Here is the code:

Private Sub cmbAddressTypeID_AfterUpdate()
On Error GoTo Err_cmbAddressTypeID_AfterUpdate
If DCount("*", "tblAddress", "CandID = anCandID") <= 1 Then
Me.lblclick2changeadd.Visible = False
Me.chkPrimary.Locked = False
Me.chkPrimary = True
ElseIf DCount("*", "tblAddress", "CandID = anCandID") > 1 Then
Me.lblclick2changeadd.Visible = True
Me.chkPrimary.Locked = True
End If
Exit_cmbAddressTypeID_AfterUpdate:
Exit Sub

Err_cmbAddressTypeID_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmbAddressTypeID_AfterUpdate
End Sub

-----

I put this in the after update of the address type because that is the first field of the form. I might add some stuff later to the other fields that moves them back to that field to make sure that they go there first.

It does the first part correct. It checks the first record. When you go to the next, it looks nice and null. Problem is that when you start the second record, it checks that too.

Can't seem to figure that part out.

Any suggestions are welcome.

 

[misscrf]

I got that code working :

Private Sub cmbAddressTypeID_AfterUpdate()
On Error GoTo Err_cmbAddressTypeID_AfterUpdate

If DCount("*", "tblAddress", "CandID = anCandID") = 0 Then
    Me.lblclick2changeadd.Visible = False
    Me.chkPrimary.Locked = False
    Me.chkPrimary = True
ElseIf DCount("*", "tblAddress", "CandID = anCandID") >= 1 Then
    Me.lblclick2changeadd.Visible = True
    Me.chkPrimary.Locked = True
End If

Exit_cmbAddressTypeID_AfterUpdate:
    Exit Sub

Err_cmbAddressTypeID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cmbAddressTypeID_AfterUpdate
End Sub

now I am working on the yes/no responses to clicking the label to change the primary check to the current record. I made another post for this but I will put the code here so you can see where I am troubleshooting:

Private Sub lblclick2changeadd_Click()
On Error GoTo Err_lblclick2changeadd_Click
Dim Msg, Style, Title
    Msg = "Would you like to change the primary address, for this candidate, to the current address?"
    Style = vbYesNo
    Title = "A candidate may only have one primary address."
    Response = MsgBox(Msg, Style, Title)


    If Response = vbYes Then
    '(uncheck chkPrimary for all records where CandID = anCandID)
    '(check chkPrimary for the current address record on this subform)
    '(lock chkPrimary)
    '(me.lblclick2changeadd.visible = true)
    'else me.undo? / setfocus me.zipcode?
    
    

End If

Exit_lblclick2changeadd_Click:
    Exit Sub

Err_lblclick2changeadd_Click:
    MsgBox Err.Description
    Resume Exit_lblclick2changeadd_Click
End Sub

 

[misscrf]

I am so close!
It works great at checking the current box and all, it
is just not deleting the other check that was checked
( on the other address record for that candidate)

Private Sub lblclick2changeadd_Click()
On Error GoTo Err_lblclick2changeadd_Click
Dim Msg, Style, Title
    Msg = "Would you like to change the primary
address, for this candidate, to the current address?"
    Style = vbYesNo
    Title = "A candidate may only have one primary
address."
    Response = MsgBox(Msg, Style, Title)


    If Response = vbYes Then
    sSQL = "DELETE Primary FROM tblAddress WHERE
CandID = " & anCandID & ";"
    Me.chkPrimary = True
    Me.lblclick2changeadd.Visible = True
    Me.chkPrimary.Locked = True
    Else
    Me.lblclick2changeadd.Visible = True
    Me.chkPrimary.Locked = True
    DoCmd.GoToControl txtZip
End If

Exit_lblclick2changeadd_Click:
    Exit Sub

Err_lblclick2changeadd_Click:
    MsgBox Err.Description
    Resume Exit_lblclick2changeadd_Click
End Sub

let me know what you think.

 

[aleb]

You were lucky that your code didn't work
It would delete the records instead of updating the field.
Anyway slightly modified code:
If Response = vbYes Then
sSQL = "Update tblAddress set chkPrimary = false WHERE anCandID = " & anCandID & ";"
Docmd.runSql sSQL
Me.chkPrimary = True
Me.lblclick2changeadd.Visible = True
'Me.chkPrimary.Locked = True '<=Just curious why do want to lock the checkbox.
me.Requery
Else
Me.lblclick2changeadd.Visible = True
'Me.chkPrimary.Locked = True
DoCmd.GoToControl txtZip
End If

Exit_lblclick2changeadd_Click:
Exit Sub

Err_lblclick2changeadd_Click:
MsgBox Err.Description
Resume Exit_lblclick2changeadd_Click
End Sub

 

[misscrf]

Thanks. I actually got some code to work. I just have to test the heck out of it now. I want to lock the primary checkbox, because I don't want the user to be able to change it. They have to consciously make the decision that they are changing the primary address used in mailings and such.

Here is the code that I have working:

Private Sub lblclick2changeadd_Click()
On Error GoTo Err_lblclick2changeadd_Click

Select Case MsgBox("Would you like to make the current address this candidate's primary address?" _
                   & vbCrLf & "" _
                   , vbOKCancel Or vbExclamation Or vbDefaultButton1, "Change Primary Address")
    Case vbOK
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryDeletePrimaryAddress"
        Me.chkPrimary.Locked = False
        Me.chkPrimary = True
        Me.chkPrimary.Locked = True
        DoCmd.SetWarnings True
    Case vbCancel
End Select

Exit_lblclick2changeadd_Click:
    Exit Sub

Err_lblclick2changeadd_Click:
    MsgBox Err.Description
    Resume Exit_lblclick2changeadd_Click
End Sub

The query has the following sql:
UPDATE tblAddress SET tblAddress.[Primary] = False
WHERE (((tblAddress.Primary)=True) And ((tblAddress.CandID)=Forms!frmCandidateEntry!CandID));


pretty much what you have, just changed to an ok cancel.

Thanks for the help.

 

[misscrf]

ps, you said I was lucky... I am using test data lol. I know better than to go live when troubleshooting the creation of an app.