comboboxes in MSACCESS

[nhtraven]

I am creating a database and would like to use combo boxes on a coupl of fields, but i need to be able to add to the value list. I am not sure if Onnotonlist is what i should use, and if i use that where do i use it? In the lookup tab or properties? any help is appreciated! = ) Thanks

 

[Telsa]

I would instead create a table with the values listed and then have the Table/Query property of the field point to that table. Easier to maintain.

 

[nhtraven]

I have made instead a form. I was trying to do it all at table level, instead of doing it inside a form. The biggest problem for this database is it is replacing operations done COMPLETELY on paper. The people who are to use the new database, instead of writing it down on paper, are not comfortable with a computer. My Boss wants something completely user friendly, and that will not require alot of typing. oh, this is for recieving dept. To accomplish this in most fields is not too difficult, but in the shipping company's name field, they have hundreds of names. They want to type that name in once and not have to type it again. I thought a combo box would be good for this, since they have limited computer skills. I think they should just invest ( the company i work for) in the computer literacy of the shipping personnel, personally. To put a find query in a form to find the name of a shipping company requires them typing it in anyways. they dont need to have shipper info, just record that it was recieved. SO now that i have wrote a book, any suggestions would be appreciated. This one is frustrating me. lol = ) thanks
raven

 

[RickSpr]

I'm glad you realized you need a form. You can't do the NotInList thing from a table.

First you'll need to create a table to hold the shipping company names. The list inside a combo box doesn't persist; as soon as you close the form or table, the list is gone, and along with it any new names that were added. So you need to save them in a table. Let's call the table Shippers, and the field name CompanyName.

Set your combo box's Row Source Type to Table/Query (the default). Set the Row Source to "SELECT CompanyName FROM Shippers ORDER BY CompanyName;". Set the Limit To List property to Yes. (This causes the NotInList event to fire. You'll use the NotInList event to add each new name to the Shippers table.)

Set the On Not in List property to "[Event Procedure]", and click the "..." button to create the event procedure.

Before continuing, if you're using Access 2000, choose Tools>References from the menu. In the References dialog, uncheck "Microsoft ActiveX Data Objects x.x Library". Then scroll down until you find Microsoft DAO 3.6 Object Library and check it. Click OK to close the dialog.

Add the following code to the module:

Private Sub cboShipper_NotInList(NewData As String, Response As Integer)
    Dim db As Database
    
    Beep
    If MsgBox("Shipping company is not in the list. Add it?", _
        vbYesNo Or vbQuestion, "Unknown shipping company") = vbYes Then
        Set db = CurrentDb()
        db.Execute "INSERT INTO Shippers (CompanyName) Values ('" & NewData & "');"
        Set db = Nothing
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

The MsgBox warns the user that the shipping company name wasn't found, giving them a chance to check for a misspelling. If you're starting out with an empty Shippers table, your users might get annoyed at getting this MsgBox almost all the time. If they nag you to remove it, you can just keep the 4 statements before the "Else" and get rid of the rest of the If statement, but it's their fault if they get the same company in there spelled 5 different ways.
Rick Sprague

 

[nhtraven]

Hi Rick,
I used you instructions and code and it works!!!!!!!! Thanks a million!
raven

 

[nhtraven]

I am wondering if you could help me further with the same database, same feild. Now they would like the Shipper name from the shipper combo box(in the form) added to the original table (table a). I put this code in to do this, and somewhere i am messing it up.

dim db as database
dim rst1 as database

set db = currentdb
set rst1 = db.openrecordset("tblBookStore&quot

rst1.addnew
rst1!fieldname=cboshipper.value
rst1.update
db.close
rst1.close


This doesnt work. I have to demo this on monday with the new idea working. I thought that maybe i could add the shipper value when clicking on the addrecord command button and wrote this:
frm_BookStore.cboshipper.value = fieldname.value
thinking that this would add the value of combo box to the current recordset(table a), well it adds the field value in it, but then my value in shippers doesnt work.
I think i am confusing visual basic with vba. I have visited the other forums in tektips and checked FAQ, and Keyword searched this. So if anyone has any suggestions, I would greatly appreciate them.
thanks Raven

 

[jfhewitt]

Here's a copy of a memo I sent my daughter when she wanted to add names to a table on the fly from a combo box. It might help you.

This routine will let you add data to a table on which a combo box is based, on the fly. For example, the "bates" table contains numbers and a name. To add a new name to the table while entering data on the form, this routine is attached to a command button (command5) which opens a dialog box for entry of the new name, then adds the new name to the existing table "bates" in the field "name". The combo box must be based upon the table "bates", and "name" must be selected as the listbox field. The same routine can be used to add data to any table in the selected database and recordset.



Private Sub Command5_Click()

Dim varName As Variant 'variable must be declared
varName = InputBox("Enter New Name&quot

Dim db As Database 'The "AddNew" function works with a recordset, so these
Dim rst As Recordset 'settings are required to make it work.
Set db = CurrentDb
Set rst = db.OpenRecordset("bates", dbOpenDynaset)
With rst
.AddNew
!name = varName
.Update
Me.cboList.Requery 'may not be essential sine this is a table not a query
End With 'probably not a bad idea to include just in case to use
End Sub 'a query rather than a table

 

[nhtraven]

JFH,
I am going to try this, but i dont want it in a command button, maybe lostfocus instead. when the user enters info in the combo box, then tabs to next field, lostfocus, will add. I will try and let you know if it works. This database is being used by people who do not type well, and are not computer literate. So it has to be as easy and straight forward as possible. I appreciate your help! thanks
Raven

 

[Dreamboat]

Raven: You must work where I do? Heh heh! Just to let you all know, I thoroughly intend to steal all the stuff in this post when you're done!
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[sierraonsite]

Dreamboat, I've been stealing from you since I discovered your first post lol

You've posted some very good advice. I liked your system memory check that you posted on one of your responses. Have you tried the Windows Logo Key + Pause/Break ? I use that because I can do it with one hand. That will bring up System Properties and I'm one click away from Performance.

Sorry, didn't mean to stray from the thread.

Jim
sierraonsite