Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    self store on combo (A2k)

    Hi

    Could somebody tell me how to make a combo box on a form that retains a value that is typed into it for future use on other forms, i.e. self learning and not linked to another table.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: self store on combo (A2k)

    Hi Steve,
    Can you elaborate on what exactly you're trying to achieve by this? There may be an easier way of getting what you want.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: self store on combo (A2k)

    Hi Rory,

    I have a job sheet form that people need to enter information into all day, a number of the fields contain 1 line entries, I originally considered setting up tables such as employee's names, carton type etc. etc. but in other posts I have seen here it is encouraged to have many tables but discouraged to have them with just 1 field.

    I then thought that if I had a combo box that automatically stored new unique records it would do the trick. I know how to leave form 1, make a new reocrd in a form 2, return and use the information in form 1 but this would be cumbersome with the amount I am talking about.

    Somebody with heaps more knowledge than me just said "Recursive Join" but would not elaborate at all.

    Thanks for any help

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: self store on combo (A2k)

    It's nice when people are helpful like that! To be honest I'm not sure how a recursive join would help here, but then I'm still a little confused as to what you're trying to do.
    I suspect that you do indeed want tables such as Employees set up. Such tables can have as few as 2 fields (eg employeeID, employeename) - there's not a lot of point in having a 1 field table since in order to link it to any other table you're including all the data from the table anyway!
    I think what you need to look at is probably the NotInList event. If your combobox retrieves data from a table or query (eg employees, carton type etc) and its LimitToList property is set to true, the NotInList event occurs if a user types in a new value. You can use code in the event procedure to add the new item to the underlying table so that it will be available in the combobox in future.
    Does that sound like what you're after?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: self store on combo (A2k)

    Maybe I have confused you, put simply all I want to do is have a combo box remember a value once it has been entered. If a new form is opened the combo will still have thats value among its stored entries so the user just has to select it from the drop down.

    Initially I made tables such as CartonID, CartonType but this would not let me change the property from Yes to No because it says the first field property cannot be 0 (when I set up the combo I ask the lookup to hide the Primary Key so I get 0";1"). I then deleted all the Primary Keys and made the actual item (for example CartonType) as the primary key, because these values would always be unique I did not see a problem with that. I can now set the property to No and I can type in any value I want but it does not save it.

    Hope this explains better, thanks so far.

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: self store on combo (A2k)

    Hi Steve,
    I'm attaching a zipped database which I hope is along the lines you're talking about - there's one form which has 2 comboboxes on it (employees and cartons) which lookup their values in 2 tables. If you type a new item in either of them, it will ask if you want to add the new item and then insert that item into the table without leaving the form. Is that what you're after?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: self store on combo (A2k)

    As an addendum to my last post, you could make the add new item process into a generic function by passing it the names of the table and field to add to if you have a lot of comboboxes to do this to. It's not necessary but it might make for easier maintenance!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: self store on combo (A2k)

    Thanks Rory that is perfect as is.

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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