Query - Multiple rows but can only select 1

[JoCraig]

Hi there

This is my first question on this Forum, so not actually sure how to title it.

I have an Access 2003 recruitment database.

I have 3 rows (Field Name) in question:

1. Current (Yes/No);
2. Archive (Yes/No); and
3. Recruitment in Progress (Yes/No).

I have created a Form so recruiters can add new entries and they are to tick either of these three.

My questions are:

1. Is there a prompt that won't let the form be saved unless one of these are ticked?
2. Is there a prompt that will only allow one of these entries to be ticked, eg. not Current and Recruitment in Progress?

I had thought of changing the 3 rows to just one and name it something else. But with 719 entries that would be a lot of work and I could make errors.

So I had thought perhaps there could be a Query modified to reflect these two questions, eg. cannot save Form until Current, Archive or Recruitment in Progress has been ticked.

I have Googled and gone to MS website, but not sure what to search on.

Any ideas would be greatly appreciated.

Many thanks,

Jo Craig.

 

[jdraw]

I have no idea of what you are trying to do. Perhaps you could describe what you are trying to do, and someone will help you with options.

What version of Access? What tables are involved?...

 

[dnlbrky]

It sounds like you have three fields where you should really only have one. I'm guessing a potential recruit can only be in one of the three states ("Current", "Archive", or "Recruitment in Progress") at any given time, right? If so, it would be better to create a table with a single field that contains the possible status values. In the form, use a combo box control that has a data source of the new status table/field.

 

[vbaInet]

1. Is there a prompt that won't let the form be saved unless one of these are ticked?
2. Is there a prompt that will only allow one of these entries to be ticked, eg. not Current and Recruitment in Progress?

There are no such prompts but you can write code to do perform those validations. Put something like this in the BEFORE UPDATE event of the FORM:

If (Nz(Me.[COLOR=Red]txtCurrent[/COLOR], 0) + Nz(Me.[COLOR=Red]Archive,[/COLOR] 0) + Nz(Me.[COLOR=Red]RecruitProgress[/COLOR], 0)) = 0 Then
    Cancel = True
    Msgbox "You must tick one of the three options before saving"
Else If (Nz(Me.[COLOR=Red]txtCurrent[/COLOR], 0) + Nz(Me.[COLOR=Red]Archive,[/COLOR] 0) + Nz(Me.[COLOR=Red]RecruitProgress[/COLOR], 0)) < -1 Then
    Cancel = True
     Msgbox "Only one selection is allowed" 
End If

The first block of code is for 1 and the second is for 2. All you need to change is what is in red. Notice that I've hinted that you rename the Current textbox to txtCurrent.

 

[JoCraig]

Hi all

I gave up with the 3 rows, as you say probably isn't the best way it should be done.

So I now have 1 row in which I select Current, Recruitment in Progress or Archive. Took a little bit of work, but problem solved and looks much better.

Now I just have to work on the Code. Not many training courses around for Advanced Access, and I haven't done Codes before. But I will give it a shot.

I appreciate everyone's responses and will advise how I go.

Cheers, Jo.

 

[vbaInet]

Good job Jo! Having one field is better.

You can even further normalize it (if you haven't already done so) by using a Number data type and setting the Field Size to Byte. The representations of the Numbers could be as follows:

0 - Default, nothing has been selected
1 - Current
2 - Archive
3 - Recruitment in Progress

Then you create a new table with two fields, ID (Number) and Status (Text) which would contain the values displayed above. Finally, in your Relationships link your original table to this new table via the Number fields. Is that clear?

To give you some heads up on the new code you would use, just check if the value of the combo box is Not 0. Anything other than 0 means nothing has been selected.