Adding item to a listbox from a text box

[Hudson]

Hi

I've a list box of 4 items. How do I allow the user to type into a separate text box an item to add to the list box and update the list box immediately?

Cheers

 

[jzwp22]

You would have to execute an append query to add the new item to the table on which the list box is based and then requery the list box. This can be done via Visual Basic for Application (VBA) code.

Alternatively, if you use a combo box, you can use some VBA code in the Not in List Event of the combo box to add a new item to the list.

 

[boblarson]

Which version of Access are you using? If using 2007 or above then you have some additional options which require no coding at all.

 

[Hudson]

Hi Bob

I'm using Access 2007 and the list is filled via a table. I've a job interview tomorrow and I've been told that theres a good chance something like this will come up.

cheers

Hudson

 

[boblarson]

For the list box then jzwp22's suggestion of an append query is in order. If you use a combo box you can use the built-in Not In List stuff that Access 2007 provides. If you want to know how to use that, let me know.

 

[Hudson]

Thanks chaps - really appreciate your help here but how do you use the append query? I have a text box on form ("frmEmployees") called txtEmp. The table tblState (which the list box 'lies' on) has 2 fields a primary key called StateID which is autonumbered by access and a field called State. In the append query (Which I am obviously doing wrong) I have on the criteria [=forms]![tblEmployee]![txtEmp]. How do I pass this parameter into the table and how do I handle the autonumbering?

Cheers

Hudson

 

[jzwp22]

The autonumber will be handled automatically so you do not have to worry about it.

The most basic form of the append query is as follows (assuming only 1 field is to be included and it is a text field)

INSERT INTO tablename (fieldname)
VALUES ("textvaluetobeadded")

Now, are you trying to append a state or an employee?
Is the form frmEmployees bound to the employee table?

If you want to append a new state name, the append query would look like this:

INSERT INTO tblState (state)
VALUES (forms!frmEmployees!NameOfControlThatHoldsTheNewStateToBeAdded)

 

[Hudson]

Hallaluja - it worked. I decided to start from scratch and it worked first time. Thanks very much - it was driving me nuts. Similarly I've been trying to delete a state from the list box but having troubloe with SQL syntax:

DELETE FROM tblState (State) WHERE
VALUES (Forms!frmEmployee!txtDState);

Can you see where I'm going wrong

Thanks again

 

[jzwp22]

The syntax of the DELETE query is different; this should work

DELETE *
FROM tblState
WHERE state=Forms!frmEmployee!txtDState