create a loop

[COMP]

hi guys im trying to create a loop where it goes through the authority fields without entering in each field. If it is the same then it should come up with a message. At the moment it works as the following:

Dim row1 As String
Dim row2 As String
Dim row3 As String
Dim row4 As String
row1 = Form_Authorities.Authority_1
row2 = Form_Authorities.Authority_2
row3 = Form_Authorities.Authority_3
row4 = Form_Authorities.Authority_4
If Form_Authorities.Authority_5 = row1 Or row2 Or row3 Or row4 Then
MsgBox ("The Authority has already been chosen")
End If


is there a simpler way??

thanks

 

[DCrake]

Yes, have a multi select list box on your form that is linked to a child table of the parent table. This way you are not limited to 5 you can have a few or as many as you wish.

David

 

[COMP]

I dont understand that?? What i mean is the code that i have written works fine but instead of entering in

row1 = Form_Authorities.Authority_1
row2 = Form_Authorities.Authority_2
row3 = Form_Authorities.Authority_3
row4 = Form_Authorities.Authority_4

isnt there a loop i can do where it goes through all these fields and if it is the same then it displays a message?? i thought it would be something like this:

row1 = Form_Authorities.Authority_""
row1 = row1 +1

but it does not seem to work

thanks

 

[NigelShaw]

Hi

is the authorities.authority_5 likely to change as in, is the the latest record at the minute whereas it could be replaced with a newer higher number?

I have a loop to do this. I made it to run through email appointments. The essence was-

if the appointment existed, skip it otherwise add the appointment
it looped through every month as it was recurring so you would need something simple. I'll do this in a bit when I've got my kiddie to sleep. Who knows when that'll be!


Regs

Nigel

 

[boblarson]

I dont understand that??

In other words, he's telling you that you should not have repeating fields and that you should have another table to store the Authorities and then you add them as records and not fields and therefore it is much easier to determine if there would be a duplicate, and also it follows the rules of normalization.

 

[COMP]

Hi NigelShaw yes thats right if one of the authorities are displayed in another field then show a message. if not allow it to be entered in. so for example

Authority 1: x
Authority 2: y
Authority 3 z
Authority 4: p
Authority:5 p

Message appears and doesnt allow the user to enter in p.

So i want a loop to go through each to ensure that the same authority is not entered in more than once. Hope this makes sense. i have allready got a code for this to work but i want to learn how the loop function would work and if there is a way where it will go through each authority without having to enter each in, in the code.

Thanks

 

[NigelShaw]

Hi

not entirely sure about looping through fields as you usually loop a recordset.

If these fields are only 1 -5 and your code works ok, why not stick with it?

I'd loop through if I was collecting or comparing data through tables but if I was comparing from a form, then a straight forward match not match would suffice.

Nigel

 

[COMP]

hi its not just 5 theres 20 all togeather. the values that are in the fields come from the table authorities. how would you write this in code "straight forward match not match would suffice."

thanks

 

[DCrake]

So you have one table with 20 fields, namely, Auth1, Auth2, Auth3....Auth20. Anf the user can fill in these feilds on a form and the coressponding combo is bound to each specific field in the table.

And the object of the exercise is not to allow duplicates.

If the above is correct then this is a perfect example of denormalisation. Can you confirm/deny the above statement before I make my suggestions.

David

 

[COMP]

Hi DCrake that is correct.

Thanks

 

[DCrake]

Well, where to begin?

For a start why arfe you doing it this way? A 1-many relationship between the parent and child table is the normal approach.

How are you envisiaging analysing the data. For example show me all records that are attached to Auth15. You are going to have to test each of the 20 fields for every single record.

You are going to have to ensure that gaps do not appear in the fields. What happens if fields 1,2 & 3 are populated and then the user places the next one in 6. You don't have 6 links you only have 4.

If you are storing the Authority Id in each field then any queries will have the main Authority table aliased 20 times in order to get the description for each field.

What is the logic behind this approach? and who came up with the concept?

Getting back to your original question, the testing for duplicates is going to be a big exercise. Each field is going to have to be checked against each other. The only method I can think of that may be quicker is to have an additional field on your form that is the concatenation of all the other selections delimited with say a pipe (|) symbol. such as 01|02|04|05 Then on the selection of another authority get it to check the string for the existance of the selected code using the InStr() command. The reason for using a delimiter is to maintain uniqueness amongst the id's as searchin for 1 and 11 would not be unique.

David

 

[COMP]

Dim row1 As String
Dim row2 As String
row1 = Form_Authorities.Authority_1
row2 = Form_Authorities.Authority_2
Name of dropdown lists
If Form_Authorities.Authority_3 = row1 Then
Form_Authorities.Authority_3 = False
MsgBox ("The Authority has already been chosen")
End If
If Form_Authorities.Authority_3 = row2 Then
Form_Authorities.Authority_3 = False
MsgBox ("The Authority has already been chosen")
End If
If Form_Authorities.Authority_3 = row3 Then
Form_Authorities.Authority_3 = False
MsgBox ("The Authority has already been chosen")
End If

 

[COMP]

Sorry the first bit did go on. If i had the follwing table

AuthorityID AuthorityName
1 a
2 b
3 c
etc.....

And then i created a form with 3 dropdownlists looking up the values in the table is there an easier way than writing the code above to ensure that the same authority does not occur twice within the 3 dropdownlists. (this is just an example)

Thanks

 

[COMP]

Apologies again, the letters are meant to be under AuthorityName