Combo 'All' records

Hi zubin6220,

No records apear because "(All)" does not match any record for the subform.

Regards,

Patrick

I understand that.  The subform child link is PastureID and master link cboPasture.  What needs to be changed/added to produce all records?
If you need more information, please let me know.

You need a value representing All Records you can't just say Null.

Make the Row Source equal to:

0; "(All)"; & SELECT tkpPasture.PastureID, tkpPasture.PastureName FROM tkpPasture

When loading the subform make sure the code allows for "If 0 Then All records shown" (Pseudo Code)

I just read your last comment.  If your subform is tied to the Main form with Parent/Child relationship you can only view records relating to that relationship.

Your combo box rowsource:  If you need ID field, let me know to add it

SELECT PastureName FROM tkpPasture UNION Select "<All>" as PastureName FROM tkpPasture

In the SELECT statement of your recorsource SQL add following WHERE statement

Select PastureName , .... From MyTable WHERE PastureName = IIF(fnPastureName()="<All>", [PastureName], fnPastureName())

In a module have:

Function fnPastureName() As String
On Error GoTo 10
  msgbox nz(Forms!MyForm!cmoPastueName,"<All>") & " <-- remove after a sucessful test"
  fnPastureName=nz(Forms!MyForm!cmoPastueName,"<All>")
Exit Function
10:
fnPastureName="<All>"
End Function

In the after update of your combo box add:

On Error Resume Next
me.requery

Please make sure form, table, and field names are correct.

Mike

Add sort:

SELECT PastureName FROM tkpPasture UNION Select "<All>" as PastureName FROM tkpPasture Order By PastureName

eghtebas,
Thanks for your help.
Is subform  recordsource SQL correct?

SELECT tblLivestock.LivestockID, tblLivestock.AnimalTypeID, tblLivestock.TagNumber, tblLivestock.LivestockTypeID, tblLivestock.BreedID, tblLivestock.Description, tblLivestock.Color, tblLivestock.ChBxFlag, tkpPasture.PastureID, tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription, tkpLivestockType.LivestockTypeDescription, tlkpBreed.BreedDescription
FROM tlkpBreed INNER JOIN (tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID) ON tlkpBreed.BreedID = tblLivestock.BreedID
WHERE (((tkpPasture.PastureName)=IIf(fnPastureName()="<All>",[PastureName],fnPastureName())));

and I'm not clear on <-- remove after a sucessful test" part.  Could you explain further.

re:> Is subform  recordsource SQL correct?

It look okay.  Some of the records may not show if you have null or empty values in PastureName field.  Only if you do, change it to:

SELECT tblLivestock.LivestockID, tblLivestock.AnimalTypeID, tblLivestock.TagNumber, tblLivestock.LivestockTypeID, tblLivestock.BreedID, tblLivestock.Description, tblLivestock.Color, tblLivestock.ChBxFlag, tkpPasture.PastureID, Nz(tkpPasture.PastureName,"tbd") As Pasture_Name tkpLivestockType.LivestockTypeDescription, tkpLivestockType.LivestockTypeDescription, tlkpBreed.BreedDescription
FROM tlkpBreed INNER JOIN (tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID) ON tlkpBreed.BreedID = tblLivestock.BreedID
WHERE Nz(tkpPasture.PastureName,"tbd")=IIf(fnPastureName()="<All>",[PastureName],fnPastureName());
 
And make sure the control source of txt PastureName changes to  Pasture_Name from current  PastureName
=========
Re:> Is subform...

Where the combo box is?  If on the main form, then you have to have

Me!SubformContainerControlName.Form.Requery

in place of me.requery

SubformContainerControlName is the control where your subform inserted in.  You will see the name of your subform in its Sorce Object propery.

Normally, SubformContainerControlName is the same as your subform name, so most likey, you will have:

Me!SubformName.Form.Requery
        ^-- change to whatever you have

======
re:> and I'm not clear on <-- remove after a sucessful test" part.  Could you explain further.

fnPastureName() used in

WHERE (((tkpPasture.PastureName)=IIf(fnPastureName()="<All>",[PastureName],fnPastureName())));

is supposed to return a value.  It is supposed to read the information from Forms!MyForm!cmoPastueName, right?

To isolate any potential problem, normally what I do is to include a MsgBox ... to make sure that our function is able to read the intended value from Forms!MyForm!cmoPastueName.  That is all.  If your salection at Forms!MyForm!cmoPastueName is "ABC" and the

msgbox nz(Forms!MyForm!cmoPastueName,"<All>")  also pages ABC, they you are certain your function is working okay.  This is the time to delete this line of code.  You will delete the following line:

msgbox nz(Forms!MyForm!cmoPastueName,"<All>")

eghtebas,
As soon as the main form opens, the msg appears.  I have to click ok 4 times to clear it then cboPastureName does not display any values to select from.  Shouldn't the msg box appear after I click a selection from cboPastureName?

Also Re> And make sure the control source of txt PastureName changes to  Pasture_Name from current  PastureName
I'm not clear on this.  
I used your select statement above and received error: The Select statement includes a reserve word or arguement that is misspelled or missing....               and the >tkpLivestockType< was highlighted.

Sorry for this extra trouble.  I really appreciate your helping me with this.    

re:> As soon as the main form opens, the msg appears.

Does massage includes correct reading from your conmbo box?  If it does, just delete Msgbox .... line.  This way you no longer would be bothered by it.  If it doesn't, then do whatever correction necessary to make it report correct response.

re:> and the >tkpLivestockType< was highlighted.

Remove Where statement and try the query without it.  If it doesn't run then start a new query and replace the old one.  Then add Where statement back to it.

WHERE tkpPasture.PastureName=IIf(fnPastureName()="<All>",[PastureName],fnPastureName())

mike

Re:>Nz(tkpPasture.PastureName,"tbd") As Pasture_Name
     What is "tbd"   and also still not clear about changing the control source of txt PastureName to Pasture_Name.  Where does this get changed?

tbd is to be determined.  As I mentioned before, you need to use Nz(tkpPasture.PastureName,"tbd") if there are some blank fields for PastureName.  If not, don't use it at all.

You could upload a simplified version of your database using the instructions at http://www.ee-stuff.com/
for one of us to take a look at it.

Mike

eghtebas,

I've uploaded the db.
https://filedb.experts-exchange.com/incoming/ee-stuff/754-10-7-Ranch-xx.zip 

frmWorkLivestock is the main form.  The combo was working prior to the fnPastureName() Function.  
There are only two records, each for a different pasture.  The combo was bringing up the selected pasture.  In trying to use the 'All' is where the problem is.  

thanks again

Also

Function fnPastureName() As String
On Error GoTo 10
'***** remove msgbox
'  MsgBox Nz(Forms!frmWorkLivestock!cboPasture, "<All>") & " <-- remove after a sucessful test"
  fnPastureName = Nz(Forms!frmWorkLivestock!cboPasture, "<All>")
Exit Function
10:
fnPastureName = "<All>"
End Function

eghtebas,

Worked great.  Thanks for your help and patience.