Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box Problem (adding to list) (Access 2000)

    I'm having a little trouble with my combo box. I have a database for keeping track of student special needs transportation. I have two tables: Student table, and Payments table. The Student table lists all info about the student: First and Last Name, Birthdate, Special Needs Code, Student Number, Driver Name and Address, what kind of contract they have, etc. The Payments table lists all info about payments made to the drivers. I have a Student form that shows all the student info with Payments table as the subform.

    Well, I made a combo box for the Student Last Name so that I could easily find the right record for that particular student. I have many students in there several times because I have several different drivers for the same student. I have a primary key set to an autonumber. Anyway, I made a combo box showing the Last and First Name with the primary key hidden (EntryNo). Well my problem is...I want to be able to add new students. But when I try to change the LimitToList property to no it says that it can't because the bound column is set to the hidden primary key field. I tried setting it to column 2, but then it gives me a run time error 13. I don't know what I'm doing wrong. Does anyone have any idea???

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Hi Jennifer

    Check <post#=189173>post 189173</post#> by Charlotte.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    <<I have many students in there several times because I have several different drivers for the same student.

    This isn't really your question, but the line above indicates that your db structure is not really correct. You need a strudents table, a drivers table and studentdrivers table to keep track of which drivers are assigned to which students.

    Back to your question. Pat has pointed you to Charlotte's post that explains why you can't set to Limit to List property to No. But setting the LimitttoList property to No doesn't add things to tables. Instead it allows to use a value in a combo box without adding it to the table. The problem here is that the table being used for the values for the combo box is the same table as the form itself is trying to add values to.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    So what you're saying is to make the student name list a separate table...then I should be able to change the LimitToList to No?

    I didn't make a separate driver table because most of the time there is no duplication of drivers. I don't quite understand what you mean by having a Student table, a Drivers table, AND a StudentDrivers table. What is the point to this?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    If a student is always driven by the same driver, and a driver only drives one student, you have a one-to-one relation between students and drivers. In that case, student info and driver info can be put into one table.

    If a driver can drive several students, while a particular student is always driven by the same driver, you have a one-to-many relation. You must put student info into one table, and driver info into another table.

    If a driver can drive several students, and a student can be driven by several drivers, you have a many-to-many relation between students and drivers. The way to implement a many-to-many relation is to create separate student and driver tables, plus an intermediate table with just two fields (StudentID and DriverID). The intermediate table contains a record for each student-driver combination that occurs.

    In itself, this has nothing to do with the LimitToList problem, as John Hutchison remarked. The row source of the combo box must have the StudentID field as first column; you can set the width of this column to 0 in the ColumnWidths property. See attached picture. If the primary key is not the first column, you will run into problems.
    Attached Images Attached Images
    • File Type: gif x.gif (2.4 KB, 0 views)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Okay, I did have the StudentID field set to 1 and the column width to 0, but it said that I can't switch the LimitToList to No because "the first visible column, which is determined by the ColumnWidths property, isn' t equal to the bound column". So then if I make separate tables like you both suggested, will I then be able to change the LimitToList to No? Or is this not going to affect the error message problem?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    I took a look at Charlotte's posting and I've already done what she said. I have EntryNo as the hidden field, with a zero column width. StudentLastName, and StudentFirstName is visible. The column count is set to 3. The bound column is 1. But it will not let you set the LimitToList to No...that's the problem. It just gives me a message saying "the first visible column, which is determined by the ColumnWidths property, isn' t equal to the bound column". How can I correct this???

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Post your DB and I'll take a look, I cannot promise anything but I'll take a look.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Hi Jen,
    what is the order of your fields in the Combo box, is it EntryNo, StudentLastname, StudentFirstname?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Yep, that is the order. I don't know if I should post the DB because I have a lot of personal confidential info about the students. Should I maybe change the StudentLastName field in the Students table to Combo Box on the Lookup tab? Would that make a difference? I just want to be able to click on the combo box and see all the students names in my database by last name, then first name--it does that okay. But I also need to be able to add more students to that list. And I can't get that part to work. I don't know what I'm doing wrong. I have everything in one table (and on one form) except for the Payments list (subform).

    I just tried to change the StudentLastName field in the Students table to Combo Box on the Lookup tab. It didn't work either. It just kept giving an error, and it changed the LimitToList back to Yes. Arrghhh!!!

    Another thought...should I maybe get the combo box data from a query that has the EntryNo, StudentLastName, and StudentFirstName fields, instead of straight from the Student table (because I have the Student table and the Payments table on one form)???

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Jen
    Some questions.
    Is the ControlSource of your ComboBox the Primary key of the table that is the RecordSource of the form?
    Is the form bound to the same table as the ComboBox?
    Is this the form called frmInvoices by Students that I have looked at previously?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Some answers:
    I don't have anything in the ControlSource property of the Combo Box. Should I???
    The RecordSource property of the Form says Students table which is the same table the Combo Box is referring to.
    Nope, this is a different database. I have to keep track of Special Needs Students and their Drivers. (do up contracts, keep track of payments made to them, etc.)

    Hope these answers help. Thanks, Pat!

    I just tried to put EntryNo in the ControlSource property of the combo box and when I tried to change the LimitToList to No, it just gave me the same error message as noted in post #189638 above.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    I have just bought up the message myself. What it is saying is that the first visible column (StudentLastName, because you have made the first column invisible, ie. EntryID) is not equal to the Bound column (EntryID).

    I think you may have to do this another way, eg. have an unbound ComboBox (with it's Limit to List set to yes) and a text boxes for the Lastname and Firstname, which you probably have now. Let the user decide if the name is not in the Combobox and if not just tell them to hit the Add new record field and add this record normally.
    Maybe someone else has a better idea of how to do this.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    Thank you so much! It worked! Now guess what I'll be working on all weekend...I have a bunch of students to enter. You're a life-saver, Pat.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Problem (adding to list) (Access 2000)

    That's <img src=/S/cool.gif border=0 alt=cool width=15 height=15>. If I had have known that I wouldn't have replied till Monday.
    What are you, a workaholic?
    I'm glad you got it working.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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