Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Form creation w/autonumbers

Discussion in 'Information Technology' started by Nick, Jul 28, 2006.

  1. Nick

    Nick
    Expand Collapse
    Guest

    Hi All-

    A little background first. My tables:

    ENGINEER
    EngID (PK/Autonumber)
    EngName (text)
    EngDept (text)

    CERTIFICATION
    CertID (PK/Autonumber)
    CertName (text)
    CertVend (text)

    ENGINEERCERTIFICATION
    EngCertID (PK/Autonumber)
    EngID (Number- long integer)
    CertID (Number- long integer)
    ExpDate (Date/Time)

    What I'm looking to do is to create a form. I need to be able to select the
    name of the engineer from a drop down menu, then select a certificatoin from
    a drop down menu. I will then input the expiration date manually. The
    probelm is, however, that in the EngineerCertification table, it wants me to
    input numeric values (since it thinks I want EngID and CertID, when in
    reality, I want to select the names of both of those).

    My newbie DB instincts tell me that I need to keep the EngID and CertID to
    keep the relationships in the table, but I have no idea how to proceed. Any
    help would be appreciated, as the boss is getting antsy.

    Thanks!
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Can you use a combo box to enter the EngID and CertID?

    For example, you could set the RowSource of the EngID combo to this query:
    SELECT EngID, EngName
    FROM Engineer
    ORDER BY EngName
    and then set these properties as well:
    Bound Column 1
    Column Count 2
    Column Widths 0
    Since the bound column is zero-width, Access displays the EngName. The user
    can enter by EngName, even though the value of the combo is EngID (i.e.
    that's the bound column.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Nick" <Nick@discussions.microsoft.com> wrote in message
    news:86DD6C25-F21E-4B0C-81FA-98046CABB417@microsoft.com...
    > Hi All-
    >
    > A little background first. My tables:
    >
    > ENGINEER
    > EngID (PK/Autonumber)
    > EngName (text)
    > EngDept (text)
    >
    > CERTIFICATION
    > CertID (PK/Autonumber)
    > CertName (text)
    > CertVend (text)
    >
    > ENGINEERCERTIFICATION
    > EngCertID (PK/Autonumber)
    > EngID (Number- long integer)
    > CertID (Number- long integer)
    > ExpDate (Date/Time)
    >
    > What I'm looking to do is to create a form. I need to be able to select
    > the
    > name of the engineer from a drop down menu, then select a certificatoin
    > from
    > a drop down menu. I will then input the expiration date manually. The
    > probelm is, however, that in the EngineerCertification table, it wants me
    > to
    > input numeric values (since it thinks I want EngID and CertID, when in
    > reality, I want to select the names of both of those).
    >
    > My newbie DB instincts tell me that I need to keep the EngID and CertID to
    > keep the relationships in the table, but I have no idea how to proceed.
    > Any
    > help would be appreciated, as the boss is getting antsy.
    >
    > Thanks!
     
  4. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi Nick -- You're right in that you should store the numeric ID for the
    relationships but display the text in the drop-down. In design view, right
    click on the combo box and select properties.

    On the Data tab, in the Row Source field check to make sure you are
    selecting the ID and the Name. If you need to add a field, click the elipse
    (...) and select the field from the table . Also, the Bound field should be
    set to 1 and and the Control Source field to your ID field.

    On the Format tab, the Column Count should be 2. Change the Column Widths
    to 0"; 2.5", where the latter can be adjusted to the desired size.

    This is can also be accomplished by using the Control Wizard.

    "Nick" wrote:

    > Hi All-
    >
    > A little background first. My tables:
    >
    > ENGINEER
    > EngID (PK/Autonumber)
    > EngName (text)
    > EngDept (text)
    >
    > CERTIFICATION
    > CertID (PK/Autonumber)
    > CertName (text)
    > CertVend (text)
    >
    > ENGINEERCERTIFICATION
    > EngCertID (PK/Autonumber)
    > EngID (Number- long integer)
    > CertID (Number- long integer)
    > ExpDate (Date/Time)
    >
    > What I'm looking to do is to create a form. I need to be able to select the
    > name of the engineer from a drop down menu, then select a certificatoin from
    > a drop down menu. I will then input the expiration date manually. The
    > probelm is, however, that in the EngineerCertification table, it wants me to
    > input numeric values (since it thinks I want EngID and CertID, when in
    > reality, I want to select the names of both of those).
    >
    > My newbie DB instincts tell me that I need to keep the EngID and CertID to
    > keep the relationships in the table, but I have no idea how to proceed. Any
    > help would be appreciated, as the boss is getting antsy.
    >
    > Thanks!
     
  5. Nick

    Nick
    Expand Collapse
    Guest

    Brilliant! This seems to be working. Thanks a million!

    "Allen Browne" wrote:

    > Can you use a combo box to enter the EngID and CertID?
    >
    > For example, you could set the RowSource of the EngID combo to this query:
    > SELECT EngID, EngName
    > FROM Engineer
    > ORDER BY EngName
    > and then set these properties as well:
    > Bound Column 1
    > Column Count 2
    > Column Widths 0
    > Since the bound column is zero-width, Access displays the EngName. The user
    > can enter by EngName, even though the value of the combo is EngID (i.e.
    > that's the bound column.)
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Nick" <Nick@discussions.microsoft.com> wrote in message
    > news:86DD6C25-F21E-4B0C-81FA-98046CABB417@microsoft.com...
    > > Hi All-
    > >
    > > A little background first. My tables:
    > >
    > > ENGINEER
    > > EngID (PK/Autonumber)
    > > EngName (text)
    > > EngDept (text)
    > >
    > > CERTIFICATION
    > > CertID (PK/Autonumber)
    > > CertName (text)
    > > CertVend (text)
    > >
    > > ENGINEERCERTIFICATION
    > > EngCertID (PK/Autonumber)
    > > EngID (Number- long integer)
    > > CertID (Number- long integer)
    > > ExpDate (Date/Time)
    > >
    > > What I'm looking to do is to create a form. I need to be able to select
    > > the
    > > name of the engineer from a drop down menu, then select a certificatoin
    > > from
    > > a drop down menu. I will then input the expiration date manually. The
    > > probelm is, however, that in the EngineerCertification table, it wants me
    > > to
    > > input numeric values (since it thinks I want EngID and CertID, when in
    > > reality, I want to select the names of both of those).
    > >
    > > My newbie DB instincts tell me that I need to keep the EngID and CertID to
    > > keep the relationships in the table, but I have no idea how to proceed.
    > > Any
    > > help would be appreciated, as the boss is getting antsy.
    > >
    > > Thanks!

    >
    >
    >
     
  6. Nick

    Nick
    Expand Collapse
    Guest

    OK the steps above/below work, however, when I select a certification from
    the drop down, I get an invalid data type because Access is expecting a
    numerical value, not a textual input. How would I work around this?

    Much obliged!

    "xRoachx" wrote:

    > Hi Nick -- You're right in that you should store the numeric ID for the
    > relationships but display the text in the drop-down. In design view, right
    > click on the combo box and select properties.
    >
    > On the Data tab, in the Row Source field check to make sure you are
    > selecting the ID and the Name. If you need to add a field, click the elipse
    > (...) and select the field from the table . Also, the Bound field should be
    > set to 1 and and the Control Source field to your ID field.
    >
    > On the Format tab, the Column Count should be 2. Change the Column Widths
    > to 0"; 2.5", where the latter can be adjusted to the desired size.
    >
    > This is can also be accomplished by using the Control Wizard.
    >
    > "Nick" wrote:
    >
    > > Hi All-
    > >
    > > A little background first. My tables:
    > >
    > > ENGINEER
    > > EngID (PK/Autonumber)
    > > EngName (text)
    > > EngDept (text)
    > >
    > > CERTIFICATION
    > > CertID (PK/Autonumber)
    > > CertName (text)
    > > CertVend (text)
    > >
    > > ENGINEERCERTIFICATION
    > > EngCertID (PK/Autonumber)
    > > EngID (Number- long integer)
    > > CertID (Number- long integer)
    > > ExpDate (Date/Time)
    > >
    > > What I'm looking to do is to create a form. I need to be able to select the
    > > name of the engineer from a drop down menu, then select a certificatoin from
    > > a drop down menu. I will then input the expiration date manually. The
    > > probelm is, however, that in the EngineerCertification table, it wants me to
    > > input numeric values (since it thinks I want EngID and CertID, when in
    > > reality, I want to select the names of both of those).
    > >
    > > My newbie DB instincts tell me that I need to keep the EngID and CertID to
    > > keep the relationships in the table, but I have no idea how to proceed. Any
    > > help would be appreciated, as the boss is getting antsy.
    > >
    > > Thanks!
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page