Check if value exists

[primagic]

Hi

I have a table called tblContacts. Each contact is linked to a school and one school can have many contacts.

However, there can only be 1 primary contact and the rest are secondary contacts. On a contact form I have a radio box so you can select whether to make them primary or not. However, I can make them all primary. How do I make the current record primary and check if there is a primary record and if not then allocate the current record the primary status.

Whether they are primary or not is held in a seperate field which simply is yes or no.

Help please

Thank you

 

[Andrzejek]

If your tblContacts looks something like:[tt]

SchoolID PersonName PrimContact
1 Joe Yes
2 Bill Yes
2 Susie No
2 Willie No
3 Bob Yes
3 Janet No[/tt]

You can display the contact information on your form based on the SQL:

[tt]SELECT * FROM tblContacts
WHERE SchoolID = 2
ORDER BY PrimContact DESC, PersonName[/tt]

This way you will get:
[tt]

SchoolID PersonName PrimContact
2 Bill Yes
2 Susie No
2 Willie No
[/tt]

And display it with option buttons ("On a contact form I have a radio box" - what's the 'radio box'?)
[tt]
o Bill <selected person>
o Susie
o Willie[/tt]

where Bill will be selected as primary contact (based on info from PrimContact from your table). This way you can choose Willie to be primary and that's what you save to your DB. One person as primary in your DB per school.

Have fun.

---- Andy

 

[primagic]

Thanks for your reply

How do I display it as options button. At the moment I have a parent form which is the school.

Then on that form I have a listbox which displays all the contacts for that school like your select query. when the user clicks on a contact from the list it autopopulates a subform with their contact details. its on this contact details form that I currently have the option to make them primary or not. so at the moment its done per record. I dont understand your part about how I would create an option group and list only the contacts for that school.

Thanks again

 

[Andrzejek]

I assume you have at least one contact person per school. Even if that’s not the case, you can always display some information like “No contact people available for this school at this time” So in addition of displaying contact people in the list box (which allows you to see their details) you may also display all contact people in a group of option buttons on the 'school' form and not have the option to choose the primary contact person on the other Form.

The way you have it now it is easy to mark the contact person as 'not the primary contact' and never choose another person to be a primary one.

If you have all of them in option buttons, one of them HAS to be chosen, which that's what you want.

You may want to research of 'how to add an option buttons at rune time' because you may have 1 person for school 1, and 25 people for school 2.


Have fun.

---- Andy

 

[Andrzejek]

how I would create an option group and list only the contacts for that school.

You may want to investigate adding controls (option buttons) dynamically (at run time):

Option Explicit
Dim X As Integer

Private Sub CommandButton1_Click()
Dim NewOption As MSForms.OptionButton

    'Create 5 option Buttons
    For X = 0 To 5
        Set NewOption = Controls.Add("Forms.OptionButton.1")
        With NewOption
            .Name = "FieldLabel" & X + 1
            .Caption = "My Label " & X + 1
            .Top = 20 + (12 * X)
            .Left = 6
            .Width = 90
            .Height = 12[green]
            '.Font.Size = 7
            '.Font.Name = "Tahoma"
            '.BackColor = &H80FFFF[/green]
            If .Name = "FieldLabel1" Then
                .Value = True
            End If
        End With
    Next

End Sub

Have fun.

---- Andy

 

[primagic]

Hi

Thank you for your help. Would this run on say a 'Select Primary' button?

But then how does it know which names to pull through?

 

[Andrzejek]

If I would do it - I would put the option buttons on 'school' form along with the list box with all contacts. List box allows you to look at the details of selected contact. Option buttons with the names of contact people will allow you to always have one chosen as a primary person.

You *could* have a separate Form where you would display all people per school with radio buttons, but if you have enough room on your ‘school’ form, I would have it right there.

But then how does it know which names to pull through?

You already have it – the same way you go after them in your list box.


Have fun.

---- Andy

 

[TheAceMan1]

How are ya primagic . . .

I'd make [blue]PrimContact[/blue] a [purple]checkbox[/purple] in the table. In the form, SQL will set all other PrimContact to false, save the one in the record you selected. This is all triggered by the PrimContact [blue]AfterUpdate[/blue] event. A requery and repositioning to the record changed polishes this off.

[blue]Your Thoughts? , , ,[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[primagic]

Hi Aceman,

Thanks for your reply. I understand adding a checkbox to the table but how would I set it so it makes the rest of the contacts non primary and the current primary. Keep in mind the parent form is a school form with a listbox displaying the contacts and when they choose a contact from the list it shows their details in a sundown of the parent form

 

[TheAceMan1]

[blue] ... how would I set it so it makes the rest of the contacts non primary and the current primary.[/blue]

On the form, if you physically change PrimContact from false to true and save:
This takes care of tagging the primary.
An SQL sets all other contacts to false

[blue]UPDATE tblContacts 
Set PrimContact = False 
WHERE (SchoolID = 10) and (ContactID <> 50)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Andrzejek]

On the form, if you physically change PrimContact from false to true and save:

What about if you change from True to False? All contact people are marked as False, no Primary Contact person for that school will be selected.

That's whay I would repeat all contact people (from the list) in a group of option (radio) buttons on the School Form and I would not allow to delete a contact person if it is a primary contact for the school.

Have fun.

---- Andy

 

[TheAceMan1]

Andrzejek . . .

For True to False the record simply saves normally as the query only runs for the opposite condition. However, you make a good point as I've asked myself: what if the user accidentally selects the wrong contact!

For either condition above I would prompt the user for confirmation of action with approrpriate warnings!

Your method is a good one, as long as there's enough screen realastate or not an over abundance of contacts.

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Andrzejek]

there's enough screen realastate or not an over abundance of contacts

I agree.
I don't know how many contact people can a school have, this info was never provided. If just a few (4-6) it should be OK, but if you have a lot (20-50) then you have a problem and it may require another Form just for choosing the Primary Contact person..

Have fun.

---- Andy

 

[primagic]

Guys I really appreciate your replies, however I agree radio buttons on the school form would be best, but how can I make the labels show the names of the contacts for that school. I have searched online and cant see anything about assigning a rowsource to an option group.

 

[Andrzejek]

Well, I don't think you can "assign a rowsource to an option group", but that's why we have jobs (computer programmers).

Try this:
You already have a list of all the people to contact in a listbox, right?

On a new UserForm place a ListBox1 and a CommandButton1 and try this code:

Option Explicit

Private Sub UserForm_Initialize()

With ListBox1
    .AddItem "John Brown"
    .AddItem "Susie Que"
    .AddItem "Duffy Duck"
    .AddItem "Bugs Bunny"
    .AddItem "Elvis Presley"
End With

End Sub

Private Sub CommandButton1_Click()
Dim NewOption As MSForms.OptionButton
Dim X As Integer

'Create 5 option Buttons
For X = 0 To ListBox1.ListCount - 1
    Set NewOption = Controls.Add("Forms.OptionButton.1")
    With NewOption
        .Name = "FieldLabel" & X + 1
        .Caption = ListBox1.List(X)
        .Top = 20 + (12 * X)
        .Left = 6
        .Width = 100
        .Height = 18
        If .Name = "FieldLabel1" Then
            .Value = True
        End If
    End With
Next

End Sub

Now you have all the people in option (radio) buttons.

Have fun.

---- Andy

 

[primagic]

Thanks for all your help. I managed to sort it by running two update queries from the listbox. when a user double clicks a contact it runs 1 query by setting all contacts to 'no' then runs another update query and sets the selected contact to 'yes'

again thank you for your assistance.