Results 1 to 9 of 9
  1. #1
    Join Date
    May 2013
    Posts
    29

    Unanswered: Linking Information from Table to Form

    Hi I created a table with fields: ID Number, Member Name, Phone Number, Address, Contact Person, Reason for Referral, Referral Source, Date, Phone:, and Fax:. All the fields are blank. except for the Phone: and Fax: fields, those have numbers in them. How do I get those two fields to go onto every new form I create? And I want to add a combobox to the form, so every new record created they have that option. But I want every record created to be unique. For example, if i choose Sally for the combobox in the first record. The second record I want to pick Jim. and I want that information to be saved on each record. And for it to record the information back to my table. I hope I explained this enough.

    -Mark

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You've posted what is effectively the same question twice.

    I get the impression from what you've said here and there that this is your first time using Access - welcome!

    Your table structure look a little ropy. While Access will allow you to use non-alphanumeric values in table and field names, it's not a good idea as it makes it harder to write queries and code later on. There are other ways to have meaningful, readable names:
    • names_with_underscores
    • camelCase
    • MixedCase
    My preference is the latter, but it makes little difference. Pick one and stick to it, and you'll reap the benefits down the line.
    You've also used a reserved word for a field name - something else that Access really ought to prevent at table design. "Date" is a function in Jet and VBA, and using it as a field name can again cause problems later on. From the context, I would suggest ReferralDate as an alternative.

    except for the Phone: and Fax: fields, those have numbers in them.
    This indicates a slight misunderstanding of data types. Access by default populates numeric fields of new records with zeroes. However, phone and fax numbers are not (from the point of view of a database) numeric data. They are text, composed of numeric characters. The test to apply is this:
    Am I going to use the contents of this field in mathematical operations?
    If not, you might be better served with another data type. In this case, Text works better.

    But I want every record created to be unique.
    That's a good aim. How are you defining "unique"? By this I mean, how much similarity between two records is permitted before the second one is deemed to be a match to the first? Once you have worked that out, select the column (or columns) in the design of the table and click the Primary Key button. This will create an index on the selected field(s) that will only permit one record to have a given value or combination or values for that field or group of field. Please note that Access will not permit null values in fields that form a primary key, so you will need to take this into account when inputting data.

    And I want to add a combobox to the form, so every new record created they have that option.
    What option? Combo boxes can be useful, but they take a little more work than text boxes. As well as linking them to a field in the underlying table that will hold its value, you also need to define the options that appear in the box when it's activated. This can be a query statement that returns fields from another table, or a static list of values. If you use a query statement, you will also need to define which of the fields returned is the one that goes into the form's underlying table, and the widths of the columns within the combo box.

    If you get stuck, you can always zip up your file and attach it here.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    29
    I changed all my field names so they all have underscores.

    -For the phone and fax fields they are text data types. And in the form properties under the default value for both those fields I put the number I want it to display every record. But it comes out to be the wrong number..

    -And for the combo box, the values I want to put in it are: Physician, Home Care, Concurrent, Marketing, Med Director, CSO, PreCert, and Other. And i would like a text box to link the data back to the table as well. But the combo box is linked to the Referral Source field. Do I need to write a query to make this work?

    -And I tried to upload my file here but it says it won't work. It's a zipx file.

  4. #4
    Join Date
    May 2013
    Posts
    29
    kkkkkkkkkkkkkkkkkkkkk
    Got it
    Last edited by MTSPEER; 06-05-13 at 14:14.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Wow, okay. A few problems there:
    • ALL of your field types were text. This is generally not a good idea, and can lead to problems if you want to try performing date comparisons, for example.
    • There is still a non-alphanumeric character in a field name.
    • Default values for fields have to be in the format of field - text fields require text default values, numeric fields require numbers, etc. As you had included hyphens between sections of the default contact numbers, and not enclosed the whole thing in quote marks, Access interpreted the default as a mathematical operation, which is why the values that appeared for new records were wrong.

    I've added a couple of new tables and a new form. Pick them apart and see how close it is to what you're looking for
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    May 2013
    Posts
    29
    It says I cannot open file because it isnt a valid archive?

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That's odd. It works for me...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    May 2013
    Posts
    29
    Maybe it's this computer at work. I may have to try it when I get home. Maybe try sending some screenshots?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try using an old version of Winzip or WinRar to open the archive.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •