find if selected combo value matches any text value on a continues form

[gint32]

Hi everyone



Is there a way to check if selected combo-box.value matches any textbox.value on a continues form within any other field on a continues form, if a use me.textbox.value it will not check all textboxs, it'll just check that specific record on a continues form, how do you do this to check all values (apart from the combobox of course)

Should I be using

 if Dcount ("*" qry_someQuery)  >0 then

...if so how do I build my query, as I have tried this with no success

At the moment I have

if me.combobox.value = me.someOtherTextBox.value then 

msgbox "Cannot assign this, try another value!!"
me.combobox.value = ""

end if

Many thanks for reading

[Gicu]

I think you need to do a loop:

Dim ctrl as Control

For each ctrl in Me.Controls
if ctrl.ControlType=acTextBox then
if me.combobox.value = ctrl.value then
msgbox "Cannot assign this, try another value!!"
me.combobox.value = ""
exit loop
end if
end if
Next ctrl

Cheers,
Vlad

[ArviLaanemets]

Having same values possible in different fields of table, and having to check for such cases, shouts loudly about problems with database structure!

[gint32]

Having same values possible in different fields of table, and having to check for such cases, shouts loudly about problems with database structure!

thanks for your input, I hear you, but not in this case as there is no issue with the structure with regards to current issue, but I do need to prevent a user selecting a like for like value in this instance!

[gint32]

I think you need to do a loop:

Dim ctrl as Control

For each ctrl in Me.Controls
if ctrl.ControlType=acTextBox then
if me.combobox.value = ctrl.value then
msgbox "Cannot assign this, try another value!!"
me.combobox.value = ""
exit loop
end if
end if
Next ctrl

Cheers,
Vlad

Thanks, this sounds like what I need I'll give it a try

[Micron]

Don't think it will work if it's a multi-column combo and the 1st column isn't visible.

[gint32]

Don't think it will work if it's a multi-column combo and the 1st column isn't visible.

Correct, the suggested vba loops through all the controls, but unfortunately only gets one instance of any control within the continues form part, so it'll check the current record but not any other within the form.

Its really just a bit of error checking ...I have created a form that show all absent staff in a continues form via a query (textbox), then I also have a combo so as admin can select from the combo (shows all staff for replacement), so obviously admin may choose (which is what I wish to prevent ) from the combo the same person as whats in the textboxs(hope that makes sense). In essence they be assigning-calling in the same person that has booked off in on Over-time for that given date which maybe days ahead,
vba wise, I already have it check one value against the selected for match and if so make them try again, but if that staff member that's booked off is in another row, then my vba checking does not work so I need a way to prevent selection even if they are in another row within the continues form.

So that we don't call the same person in on Over-time that just booked off sick.

So I either need to exclude these staff members(in the textboxs) from the combo or check that they don't match some way

[gint32]

Thanks, this sounds like what I need I'll give it a try

Sorry this idea doesnt work, any other suggestions ...thanks

[gint32]

I think you need to do a loop:

Dim ctrl as Control

For each ctrl in Me.Controls
if ctrl.ControlType=acTextBox then
if me.combobox.value = ctrl.value then
msgbox "Cannot assign this, try another value!!"
me.combobox.value = ""
exit loop
end if
end if
Next ctrl

Cheers,
Vlad

Tried it, the code doesn't work with continues forms have you any other suggestion for me?..thanks

[Gicu]

Hi there,

The code should work for checking for the current record, I didn't realize you wanted to span records. In your case the easiest way would probably be to modify the combo box record source to eliminate the employees that are off. Can you post a picture of your query so we could see how it looks. How many textboxes holding employee ids are there in each record? You could create a number of queries listing the employees that are off in each field (textbox on your form) then use a union query to get them in a list of unique ids that you then use in your combo box record source query to eliminate them.

Cheers,
Vlad

[Micron]

suggest you research FindFirst and use the RecordsetClone of the form.
If you're saying that the target word could be in ANY textbox in ANY record then that is bizarre to me and that method won't work. You'd have to outer loop the records and inner loop the fields. You should explain how this is the situation you face, seeing as how the value you're looking for is being picked from a combo, which normally is based on only one field from some record source. I'm confused as to why the situation and solutions are being explained on the basis that the sought value could be anywhere.

If it could only be in a particular field of your continuous form but be in any record, then it should work.