Query with Combo Boxes (1 Viewer)

[Bladerunner]

I have a navigation form where I use combo boxes to pick an AnimalID and then fill in the subform based for that animals information.

The AnimalID ComboBox (cboAnimalID) has a row source like this:

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType FROM [tblAnimal Setup], [tblAnimal Type] WHERE ((([tblAnimal Setup].AnimalType)=[Forms]![NavigationForm]![cboanimaltype])) ORDER BY (animalID);


p.s. these cbo(s) are in the detail area of the navigation form above the subform(s) area.

The AnimalType ComboBox (cboanimaltype) has a row source like this:

SELECT [tblAnimal Type].AnimalTypeID, [tblAnimal Type].AnimalType FROM [tblAnimal Type] ORDER BY [AnimalType];

When I run the navigation form, It pulls up the animal types and allows me to pick one. This part is ok.

However when I pulldown the AnimalID list (cboanimalID), It gives me a box asking for [forms!NavigationForms!cboAnimaltype]. If I put in an animalID lets say the #5 ( this number corresponds to the animal type I picked in the cboanimaltype), it pulls up the data correctly. It just will not do it itself without asking for the data I have already put in. The cboanimalID does not seem to recognize cboAnimaltype!

anyone got any ideas. I have tried everything, or at least according to my limited abilities with Access.

 

[CJ_London]

Assuming the AnimalID ComboBox and AnimalType combobox are both on your navigation form, the rowsource for AnimalID should be

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType FROM [tblAnimal Setup], [tblAnimal Type] WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) ORDER BY (animalID)

Note this should be typed directly into the rowsource and not a query which is then set as the rowsource.

 

[Bladerunner]

It works however it gives 5 of every animal ID. I inserted the ; at the end of the row source you gave me but it still pulls up 5x of each AnimalID.

I inserted the normal row source for this field and it only gives one of each like it should. Got any ideas on what is causing this.? It is way out of my league.

 

[Bladerunner]

When I try to add a 'Group' to the mix, it gives me the right information however, there are about 15x each of them

SELECT [tblAnimal Setup].AnimalSetupID, [tblAnimal Setup].AnimalID, [tblAnimal Setup].AnimalType, [tblAnimal Setup].Group FROM [tblAnimal Setup], [tblAnimal Type], [tblGroup] WHERE ((([tblAnimal Setup].AnimalType)=[cboanimaltype])) and ((([tblAnimal Setup].Group)=[cbogroup])) ORDER BY (animalID)

 

[CJ_London]

hmm,

Why do you have [tblAnimal Type] table in your rowsource? you are not using any of the values or linking to it, so I suspect it is because you have 5 records in this table.

Suggest you remove it from the query since you are not using it.

If it is still a problem, try putting the word DISTINCT after the SELECT

The ; makes no difference - you don't need to include it, but Access will add it anyway

 

[Bladerunner]

A bunch of thanks, that was the problem. Now since this is not considered a query, how can I get cboanimalID to?(requery)? if I decide to change the Animal Type.

Under the other method, I think a coded requery would have worked??????? I am not sure about this way either.

Also since these CBOs are in the detail section of a navigation Form and not in one of the subforms, do I use [forms]![NavigaitonForm]![cboanimalID] to enable me to call up this record on the subform?

 

[Bladerunner]

One other problem I am experiencing with the navigation form. To get the above to work I pulled up a new navigation form and placed the CBOs in the detail area. There were no subforms on this nav form. Everything worked.

However, on a nav form where there is a subform, the last CBO in the detail area does not recognize any of the previous CBOs. Is this because the subform has the spotlight when the nav form is opened? If this is the case, Can I get the CBOs in the detail area to recognize each other but specifying where they are at?

 

[CJ_London]

Now since this is not considered a query, how can I get cboanimalID to?(requery)?

It is a query, just within the form.

To requery it, in the cboanimaltype afterupdate event put

cboAnimalID.requery

Also since these CBOs are in the detail section of a navigation Form and not in one of the subforms, do I use [forms]![NavigaitonForm]![cboanimalID]

No, you use the parent property - i.e.
Parent.cboanimalID

Which again, like the rowsource, the recordsource for your form needs to be the sql and not a query name

However, on a nav form where there is a subform, the last CBO in the detail area does not recognize any of the previous CBOs.

Sorry, I don't understand

 

[Bladerunner]

Thanks a bunch. It is good to know about the parent.cbo(x) part.

You said something about the rowsource of the form. Is this because the CBOs are in the parent form otherwise the subforms can use a query for a rowsource?

I had an error in my original Nav. Form which kept it from working properly. The new one works even when I add a subform to it.

Again thanks for you help. Have been mulling this over for the better part of the three days.

 

[CJ_London]

You said something about the rowsource of the form. Is this because the CBOs are in the parent form otherwise the subforms can use a query for a rowsource?

Yes. Providing any rowsource or recordsource of any control or subform is written as a sql statement (rather than in a separate query) it can refer directly to the objects within that form.

If you have a subform in a subform then to refer to the main parent you would use

parent.parent.cbo...

 

[Bladerunner]

I am confused.... I have a subform [frmAnimal Setup] that has a cboanimalID. I have used parent.subform.controlname or me.subform.controlname with no results. Should not the Parent (or Me).[frmanimal setup].cboanimalID = Parent (or Me).cboanimalID in the row source of the cboanimalID on the subform work?

Would like the value of the subform cboanimalID be the same as the form (parent) cboanimalID.

 

[Bladerunner]

Mr. London.. While I am use to using VBA and recordsets, etc. or one record at a time, Access from what I am finding out is pretty cool. What I have been trying to do is to pull one record up and fill a form. However, after a little research, I believe this is the wrong way. What I would like to have is a nav. form with three filters, animaltype, group and location which would filter all datasheet view(s) on the Nav.form. From the datasheet view, I would be able to highlight a record and call up another single record form for editing or I could call up a form for adding a new record if needed. I would need it to apply the results of this filter to all the datasheet (tbl) views on the nav.form. I need to turn off editing/adding for the datasheet views.

On the farm, I can keep up with where my animals are and what group (category) they are in (i.e. nursery, sick, active, etc.). 'A sick animal should be in the Treatment Bonding field' (i.e. animaltype, group ='sick', location ='T/B field') and the tblanimalsetup may show 5-6 animalIDs while the tblHealthrecord may only show 2 or 3.

I simply want to know what your opinion is about my direction for this project as laid out above.

Please do consider that the tables have been normalized as much as my Access skills allow. Please be advised that reports (paper or otherwise) are not needed nearly as much as data on a mobile tablet, laptop. (i.e work in the fields)

Please don't pull any punches. I put my big boy breeches on this morning.lol

Oh, and thanks again for all your help before this.

 

[CJ_London]

I am confused.... I have a subform cboanimalID that has a cboanimalID. I have used parent.subform.controlname or me.subform.controlname with no results.

One thing to be aware of is that subform data is loaded before the main form -can't remember why now but it means when it loads, the rowsource for your cboanimalID control on your cboanimalID will not find anything in parent object.

In you main form current event, put the code
[frmAnimal Setup].requery

Would like the value of the subform cboanimalID be the same as the form (parent) cboanimalID.

In the main form cboanimaltype control afterupdate event put

[frmAnimal Setup].form.cboanimalID.defaultvalue=cboanimaltype

 

[CJ_London]

I simply want to know what your opinion is about my direction for this project as laid out above.

Will respond later, have a lot of work to complete

 

[Bladerunner]

Navigation form with one sub form

main form = NavForm
Subform = FrmAnimalSetup (tied to tblAnimalsetup)
Combo on Parent = defaultcboanimalID (value from tblAnimalsetup)
Combo on Subform = cboanimalID

I have tried just about everything but here are the last tries:
Subform cboanimalID to Data - default:

[Forms]![NavForm]![NavigationSubf].[Form]![frmAnimalSetup].[Form].[cboAnimalID]=[Forms]![NavForm].[Form].[defaultcboanimalID]

Parent defaultcboanimalID to event change code :

Forms!NavForm!NavigationSubf.Form!FrmAnimalSetup.Requery

I used change because the afterupdate event does not automatically requery/refresh until it loses focus. The change event does it immediately.

Once I get the value that is in 'defaultcboanimalID', I need to pull up the entire record for that ID value.

my Access 2010 does not recognize 'me. or parent.' Getting closer everyday but still no cigar! lol


Hope someone can help.