Welcome to SPN

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

Sign Up Now!

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!
     

Share This Page