Auto fill and drop down lists

[pompeyboyUSA]

The fields are Rank and Rank Sort (I cant do this alpabetically as a the
ranks (Military) don't run in alphabetical order.

The idea is this:

The form (Pers Details Form) contains personal details Name, Number etc from
the Pers Details Table. There will be box where I can select the rank from a
dropdown list. The Rank details are contained in a seperate table (Rank
Table) containing a Rank and Rank Sort column. In the Pers Details Form I
can create a record, enter details but I want to have a drop down box to
choose the rank from. Once chosen I would like the rank to show (but not the
sort details) but I will need to use the sort later when compiling queries
and reports.

I can produce the combo box easily enough, but I seem to be having problems
with maybe the relationships or the query? I seem to be chasing my tail
again and getting confused!!

I suppose I can just have the Rank and Rank Sort in the Pers Details table
but I believe this is bad database design as lots of people will have the
same rank.

Please let me know if you need more details if I haven't explained enough

Thank you

Charlie

 

[Tom van Stiphout]

On Fri, 14 Dec 2007 02:59:02 -0800, pompeyboyUSA


If the Ranks table ONLY has Rank and RankSort (get rid of spaces in
field names - they require more complicated syntax), then I would have
a Rank field in the PersDetails table, and a 1:M relationship between
those two fields.
If the Ranks table has several other fields, I would create an
additional RankID field in that table, and also have a RankID in the
PersDetails table, and a 1:M relationship between those two fields.

The RankSort field is only for sorting the dropdown; there is no need
to store it in the PersDetils table.

-Tom.

 

[Pat Hartman]

In the query for the combo's RowSource, you can include RankSort so that you
can sort by it but uncheck the "show" box so that it will not show in the
combo.

In the properties of the combo, is one named Column Widths. Any column, you
want to hide would be set to zero. So, if you have three columns but you
want to hide the middle one, the column widths might be:

2";0";3.5"

Make sure that the number of columns property accurately reflects the actual
number of columns returned by the RowSource.

 

[pompeyboyUSA]

On Fri, 14 Dec 2007 02:59:02 -0800, pompeyboyUSA


If the Ranks table ONLY has Rank and RankSort (get rid of spaces in
field names - they require more complicated syntax), then I would have
a Rank field in the PersDetails table, and a 1:M relationship between
those two fields.
If the Ranks table has several other fields, I would create an
additional RankID field in that table, and also have a RankID in the
PersDetails table, and a 1:M relationship between those two fields.

The RankSort field is only for sorting the dropdown; there is no need
to store it in the PersDetils table.

-Tom.

Tom,

Thank you for this, I will try it - however my apologies for any confusion,
the RankSort field is not for sorting the drop down. It will be used for
sorting the query. I cannot sort the ranks in order unless I have a sort
field. A senior Officer is a Lt Col, then a Major, then Capt - as you can
see they will not order correctly unless I have a sort field.

Charlie

 

[pompeyboyUSA]

In the query for the combo's RowSource, you can include RankSort so that you
can sort by it but uncheck the "show" box so that it will not show in the
combo.

In the properties of the combo, is one named Column Widths. Any column, you
want to hide would be set to zero. So, if you have three columns but you
want to hide the middle one, the column widths might be:

2";0";3.5"

Make sure that the number of columns property accurately reflects the actual
number of columns returned by the RowSource.

Pat,

Thank you very much for your time. Will this allow me to sort the ranks
after I have entered them into the form by running a query? Understand about
hiding them - I will give your idea a try

Thanks - Charlie

 

[pompeyboyUSA]

Tom,

Thank you for this, I will try it - however my apologies for any confusion,
the RankSort field is not for sorting the drop down. It will be used for
sorting the query. I cannot sort the ranks in order unless I have a sort
field. A senior Officer is a Lt Col, then a Major, then Capt - as you can
see they will not order correctly unless I have a sort field.

Charlie

Q About the 1:M relationship. I have in the RankTable 2 fields - RankHeld
and RankSorted (no spaces ) when I try to form the relatinship I can only
get a Indeterminate relationship ???? I can't have the RankHeld as a primary
field as lots of people will have the same rank. Am I correct in saying you
can only have a 1:M if you use the primary field?

Thanks

Charlie

 

[pompeyboyUSA]

Pat,

Thank you very much for your time. Will this allow me to sort the ranks
after I have entered them into the form by running a query? Understand about
hiding them - I will give your idea a try

Thanks - Charlie

I am also having problems with the query - I can't seem to get it to display
any details I get a type mismatch message - I have the feeling there is a
very simple explanation for all this, but I am just missing that one simple
key

Charlie

 

[Tom van Stiphout]

On Fri, 14 Dec 2007 16:03:01 -0800, pompeyboyUSA

Yes, that's what I meant about sorting the dropdown. You are correct
that what's being sorted is the query that's the RowSource for the
dropdown.

RankHeld should be the PK in the Ranks table. No index needed on the
corresponding foreign-key field in the PersDetails table.
Be sure to enforce your relationship.

-Tom.

 

[pompeyboyUSA]

On Fri, 14 Dec 2007 16:03:01 -0800, pompeyboyUSA

Yes, that's what I meant about sorting the dropdown. You are correct
that what's being sorted is the query that's the RowSource for the
dropdown.

RankHeld should be the PK in the Ranks table. No index needed on the
corresponding foreign-key field in the PersDetails table.
Be sure to enforce your relationship.

-Tom.

Tom,

Please excuse my ignorance - I thought the PK cannot be duplicated? Can you
please explain "No index needed on the
corresponding foreign-key field in the PersDetails table"

Thank you

Charlie

 

[pompeyboyUSA]

Tom,

Please excuse my ignorance - I thought the PK cannot be duplicated? Can you
please explain "No index needed on the
corresponding foreign-key field in the PersDetails table"

Thank you

Charlie

Tom,

I have got it working - the penny finally dropped!! Thank you so much for
your help and guidance. I now have to work on the reports and queries.

Regards

Charlie