Limit Combo Box records in Table/form

  • Thread starter Thread starter Thorson
  • Start date Start date
T

Thorson

I currently have a table set up ('tblBirthInformation'), when an animal is
born the Dam (mother) and Sire (father) are put in the table. The sire field
currently has a combo box, but I would like to limit the options in that box
based on other tables in the database. Two other tables
('tblArtificialInsemination' and 'tblCleanUpBulls') List possible sires for
the calf depending on the Dam.

I would like it so that when the user enters the Dam's EarTag
(identification) into the table the combo box in the Sire field will look up
the possible Sires based on the tables 'tblArtificialInsemination' and
'tblCleanUpBulls'.

How do I do this? Let me know if you need clarification.
 
I can't give you a specific answer without a lot more info, but here is
generally what I would do:
1. Build a query that links Sires to Dams via your tblArtif... and
tblClean... tables. The only fields you need to in the query are the SireID
(ear tag?), the Sire's Name, and the Dam's ID (ear tag) - whatever your
unique identifier is (this field does not need to be shown).
2. In the criteria line of the Dam's ID enter
=[forms]![frmBirthInformation]![DamID] using your actual form/field names of
course.
3. In your form, make a select sire drop down box, based on the query you
just made. You can use the wizard and select the SireID and SireName from the
query, hide the ID column...save the value in your SireID field of your birth
info table.
4. In your form, add an after update event procedure to the Dam drop down
box to requery the Sire drop down box data source.

Jill
 
Thank you, I think I followed everything and it looks good, but I"m having
a problem with step 4.

I haven't written code for a "requery" yet. I used the following:

DoCmd.Requery([ControlName])

I'm not sure what to put for 'controlname', I assumed the combo box name of
the Sire control so I put:

DoCmd.Requery([cboSire])

But that didn't work, What am I suppose to put here? Thanks for your help!


--
Thorson


Mrs. Ugh said:
I can't give you a specific answer without a lot more info, but here is
generally what I would do:
1. Build a query that links Sires to Dams via your tblArtif... and
tblClean... tables. The only fields you need to in the query are the SireID
(ear tag?), the Sire's Name, and the Dam's ID (ear tag) - whatever your
unique identifier is (this field does not need to be shown).
2. In the criteria line of the Dam's ID enter
=[forms]![frmBirthInformation]![DamID] using your actual form/field names of
course.
3. In your form, make a select sire drop down box, based on the query you
just made. You can use the wizard and select the SireID and SireName from the
query, hide the ID column...save the value in your SireID field of your birth
info table.
4. In your form, add an after update event procedure to the Dam drop down
box to requery the Sire drop down box data source.

Jill

Thorson said:
I currently have a table set up ('tblBirthInformation'), when an animal is
born the Dam (mother) and Sire (father) are put in the table. The sire field
currently has a combo box, but I would like to limit the options in that box
based on other tables in the database. Two other tables
('tblArtificialInsemination' and 'tblCleanUpBulls') List possible sires for
the calf depending on the Dam.

I would like it so that when the user enters the Dam's EarTag
(identification) into the table the combo box in the Sire field will look up
the possible Sires based on the tables 'tblArtificialInsemination' and
'tblCleanUpBulls'.

How do I do this? Let me know if you need clarification.
Click to expand...
Click to expand...
 
Sorry, I thought I used requery, but I couldn't get it to work either...
Me.Refresh did work though.
Jill

Thorson said:
Thank you, I think I followed everything and it looks good, but I"m having
a problem with step 4.

I haven't written code for a "requery" yet. I used the following:

DoCmd.Requery([ControlName])

I'm not sure what to put for 'controlname', I assumed the combo box name of
the Sire control so I put:

DoCmd.Requery([cboSire])

But that didn't work, What am I suppose to put here? Thanks for your help!


--
Thorson


Mrs. Ugh said:
I can't give you a specific answer without a lot more info, but here is
generally what I would do:
1. Build a query that links Sires to Dams via your tblArtif... and
tblClean... tables. The only fields you need to in the query are the SireID
(ear tag?), the Sire's Name, and the Dam's ID (ear tag) - whatever your
unique identifier is (this field does not need to be shown).
2. In the criteria line of the Dam's ID enter
=[forms]![frmBirthInformation]![DamID] using your actual form/field names of
course.
3. In your form, make a select sire drop down box, based on the query you
just made. You can use the wizard and select the SireID and SireName from the
query, hide the ID column...save the value in your SireID field of your birth
info table.
4. In your form, add an after update event procedure to the Dam drop down
box to requery the Sire drop down box data source.

Jill

Thorson said:
I currently have a table set up ('tblBirthInformation'), when an animal is
born the Dam (mother) and Sire (father) are put in the table. The sire field
currently has a combo box, but I would like to limit the options in that box
based on other tables in the database. Two other tables
('tblArtificialInsemination' and 'tblCleanUpBulls') List possible sires for
the calf depending on the Dam.

I would like it so that when the user enters the Dam's EarTag
(identification) into the table the combo box in the Sire field will look up
the possible Sires based on the tables 'tblArtificialInsemination' and
'tblCleanUpBulls'.

How do I do this? Let me know if you need clarification.
Click to expand...
Click to expand...
Click to expand...
 
Do you put anything besides me.refresh, like the name of the combo box for
the Sire ID?? Do you still do it on the after update? It isn't working for
me. This is my code:

Private Sub DamTatto_AfterUpdate()

Me.Refresh

End Sub


--
Thorson


Mrs. Ugh said:
Sorry, I thought I used requery, but I couldn't get it to work either...
Me.Refresh did work though.
Jill

Thorson said:
Thank you, I think I followed everything and it looks good, but I"m having
a problem with step 4.

I haven't written code for a "requery" yet. I used the following:

DoCmd.Requery([ControlName])

I'm not sure what to put for 'controlname', I assumed the combo box name of
the Sire control so I put:

DoCmd.Requery([cboSire])

But that didn't work, What am I suppose to put here? Thanks for your help!


--
Thorson


Mrs. Ugh said:
I can't give you a specific answer without a lot more info, but here is
generally what I would do:
1. Build a query that links Sires to Dams via your tblArtif... and
tblClean... tables. The only fields you need to in the query are the SireID
(ear tag?), the Sire's Name, and the Dam's ID (ear tag) - whatever your
unique identifier is (this field does not need to be shown).
2. In the criteria line of the Dam's ID enter
=[forms]![frmBirthInformation]![DamID] using your actual form/field names of
course.
3. In your form, make a select sire drop down box, based on the query you
just made. You can use the wizard and select the SireID and SireName from the
query, hide the ID column...save the value in your SireID field of your birth
info table.
4. In your form, add an after update event procedure to the Dam drop down
box to requery the Sire drop down box data source.

Jill

:

I currently have a table set up ('tblBirthInformation'), when an animal is
born the Dam (mother) and Sire (father) are put in the table. The sire field
currently has a combo box, but I would like to limit the options in that box
based on other tables in the database. Two other tables
('tblArtificialInsemination' and 'tblCleanUpBulls') List possible sires for
the calf depending on the Dam.

I would like it so that when the user enters the Dam's EarTag
(identification) into the table the combo box in the Sire field will look up
the possible Sires based on the tables 'tblArtificialInsemination' and
'tblCleanUpBulls'.

How do I do this? Let me know if you need clarification.
Click to expand...
Click to expand...
Click to expand...
 
Use the requery method of the control
[cboSire].Requery

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Works Perfectly, Thanks!
--
Thorson


John Spencer (MVP) said:
Use the requery method of the control
[cboSire].Requery

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thank you, I think I followed everything and it looks good, but I"m having
a problem with step 4.

I haven't written code for a "requery" yet. I used the following:

DoCmd.Requery([ControlName])

I'm not sure what to put for 'controlname', I assumed the combo box name of
the Sire control so I put:

DoCmd.Requery([cboSire])

But that didn't work, What am I suppose to put here? Thanks for your help!
Click to expand...
Click to expand...
 
Back
Top