Form Validation Rule from Another Query

[CodenameAter]

I am trying to ensure that users can only select bands from a drop down menu from females who are present in the location record.

In other words, users first enter the location of the egg, then they have to select the female that laid the egg. Currently, the field where they enter the female's band is linked to a table with all the female's information including their band. However, I just want the bands to appear based on their current location. I have a query that lists all their current locations.

I attempted to use this: DLookUp("[Band]","[Qry_BirdCurrentLocID]","[Nest] =" & "[Qry_BirdCurrentLocID]![Loc_ID]")... i.e. return the bands from the qry with all their current locations if the form location = the current location in the Qry.



For example, if I were to select Location 1, I want a list of all females who could potentially be at location 1 according to the Qry_BirdCurrentLocID

I hope that makes sense. Thank you.

[Micron]

DLookup can only return data from one record.
Create a select query that does what you want, then either pick that query as the combo row source, or copy/paste the sql into the row source field of the property sheet for the combo. You might want to start with actual values that return correct information, then substitute those with your form references. When testing a query this way, the form has to be open, and the referenced controls must hold valid criteria for the query to still work while you're testing it.

[CodenameAter]

Micron,

I am a bit confused. I already have a query that lists all the bands and all the possible locations the bands could be at...

Band	Location
FNP	Loc1
FNP	Loc2
FGB	Loc2
FGB	Loc3
FGB	Loc5
FGB	Loc7

In the form I want it to show the bands available at that location. For example, if I select Loc2, both FNP and FGB should be available, but if I only select Loc7, then FGB would show up in the list.

My problem is I do not know how to select make the bands available dependent on if the form Location matches the Table Location. I hope that makes more sense.

Thank you

[aytee111]

Combobox 1 is for the locations, the row source will list only the list of locations. The user will select one from the list.

Combobox 2 is the list of bands, the row source will list all bands where (criteria) the location is =Forms!FormName!Combobox1.

In the AfterUpdate of Combobox 1 reset Combobox 2 : Me!Combobox2.Requery

[CodenameAter]

Combobox 1 is for the locations, the row source will list only the list of locations. The user will select one from the list.

Yes, I already have this

Combobox 2 is the list of bands, the row source will list all bands where (criteria) the location is =Forms!FormName!Combobox1.

In the AfterUpdate of Combobox 1 reset Combobox 2 : Me!Combobox2.Requery

Okay, I will give this a try and see if it works. Thank you

[Micron]

FYI - this is usually referred to as 'cascading' combo boxes though I don't think I would have coined that phrase for it. To me cascading is what happens when something spills out or over into something else, like a waterfall. The sub-combo boxes don't usually contain anything that comes from the one before it. Oh well...

[CodenameAter]

Combobox 2 is the list of bands, the row source will list all bands where (criteria) the location is =Forms!FormName!Combobox1.

In the AfterUpdate of Combobox 1 reset Combobox 2 : Me!Combobox2.Requery

Okay, so it appears it is looking up the query however, the query is returning no values. Here is what I did:

In the NestLaidComboBox, it refers to the list of all possible nests the bird could lay in from the table SELECT Var_LocationIDs.[Loc_ID] FROM Var_LocationIDs ORDER BY [Loc_ID]; .

After Update in this combo box triggers Reset Mother : Me!Mother.Requery

In the MotherComboBox, I wrote the following SQL code: SELECT Qry_BirdCurrentLocID.[Band] FROM Qry_BirdCurrentLocID WHERE (Qry_BirdCurrentLocID.[LocID] = Forms!Form_EggInput!NestLaidComboBox);

Logically, I do not complete understand your recommendation. If the query is based on the current location, but that location changes record to record, how does it know to query the brand new record I am inputting?

[CodenameAter]

Just something else to note. Whenever I attempt to use the expression builder, although I cannot stand using it, I noticed the none of the fields in the form are not showing. I just put the name of the combo fox in regards, but I do not know if this would impact anything.

[Micron]

what is this data type? Qry_BirdCurrentLocID.[LocID]
if text, you have to wrap it in quotes.
(Qry_BirdCurrentLocID.[LocID] = "' & Forms!Form_EggInput!NestLaidComboBox & "');"

I don't understand your last statement. What sort of form view are you presenting - single, single with navigation controls, continuous, datasheet?
Is this data entry or view only?

I just want the bands to appear based on their current location.

Sounds like you might want more than this. If so, best to elaborate clearly and concisely on what the desired outcome is from the data you have. Explaining what you did that isn't working doesn't always lead us down the right path.

In the simplest terms, the initial posts read like you want the second combo row list to be filtered based on what was chosen in the first. I for one have no idea on how achieving that will fit into your process.

[CodenameAter]

If I understand correctly, it is a 'single with navigation controls' form that allows users to both add new records as well as edit previous records.

Background on what I am trying to achieve: Everyday my research assistants collect eggs and document them into the database. Females are located in different aviaries. When a student is filling out the form and selects the location they got the rest from, they must then select the female who laid the egg. I want the list of females available to them to be limited to just the females who had access to the location they retrieved the nest. That way, a student cannot enter a female into the form who was not located in the location the egg was listed.

Currently, the script I have below (that I just created from what I found online) allows me to do that. However, after the first selection, the list of females is tied to that selection even in new records. Therefore, I am wondering if there is a way that when students edit or create new records, this script will execute each time. I hope that helps

Event --> After Update:

Me.cboMother.RowSource = "SELECT Band , LocID " & _
                           "FROM Qry_BirdCurrentLocID " & _
                           "WHERE LocID = " & Nz(Me.NestLaidComboBox) & _
                           "ORDER BY Band"

[Micron]

Don't know if you've bound the combos to their related fields or not (i.e. there is or isn't a source control set on the property sheet). So when you go to a new record, the first combo becomes blank/empty but the second does not? If so, then requery the second in the OnCurrent event.

I noticed the none of the fields in the form are not showing. That would mean they are there, yet I doubt that's what you meant.
Also, you have not addressed the question of data type of LocID and I don't see any quotes in the latest code posted so it's either not a problem, or you're ignoring the question - or it is and you are.

[aytee111]

In post #7 you have this code:Me!Mother.Requery
Is "mother" the name of your combobox, or is it supposed to be MotherCombobox? Make sure that you are requerying the right field.