list with inputfield

[SeanD]

Hi all,

I have a table called Customers.
In a form I have a list with inputfield linked to this table.
If you type in a word in this list, and the word is not already in the table, is there a way to put it automaticly in the table?

If tried with the next code when 'If not in list' :

Private Sub Customer_NotInList(NewData As String, Response As Integer)

Dim dbs As Database, StrSQL As String
Set dbs = CurrentDb
DoCmd.RunSQL ("INSERT INTO Customer (Customer) VALUES (" & Customer.Value & ")")


End Sub

But this won't work

 

[SeanD]

No, it doesn't work.

 

[JANR]

If NewData is Sting try this if you want to add without user confirmation.

Private Sub Customer_NotInList(NewData As String, Response As Integer)
 
Currentdb.Execute "Insert Into Customer (Customer) Values ('" & NewData & "'), dbFailOnError
Response = acDataErrAdded
 
End Sub

JR

 

[SeanD]

This query works when I link it to a button

DoCmd.RunSQL ("Insert Into Customer (Customer) Values ('" & Customer.Value & "');")

But when I put the code in the NotInlist-sub, it does nothing

Private Sub CustomerNotInList(NewData As String, Response As Integer)

DoCmd.RunSQL ("Insert Into Customer (Customer) Values ('" & Customer.Value & "');")


End Sub

 

[JANR]

Did you try my suggestion? The NotInList event holds the "new" value in the NewData variable, use a refrence to this variable, not the control on the form.

JR

 

[SeanD]

Yes, tried it also, but won't work.
If I check with a MsgBox NewData, I get an empty box.

 

[JANR]

Is this a combobox?

JR

 

[SeanD]

No it's a list with inputfield.
Not a combobox

 

[JANR]

Well only comboboxes have a NotInList event so you have to rethink your methode.

So your button approach seems appropriate.

JR

 

[SeanD]

It's a listbox with inputfield.
I'm workong in Access 2007 And I have the Dutch version, so it's hard to translate. (I'm Belgian)
The listbox does have the function NotInList.
You can wether select a String from the listbox or type in a String yourself

 

[JANR]

It's a listbox with inputfield.

Never heard of it. Is it some sort of ActiveX control?

I can't find any NotInList event in listboxes in Access 2010/2007

Perhaps someone else can chime in, but for this sort of thing you really should use the dedicated contol which is Combobox.

JR

 

[SeanD]

It's like this:
http://www.google.be/imgres?imgurl=...TtAaK7uTvBg&page=1&ndsp=24&ved=1t:429,r:0,s:0
But it's also allowed to typ in some words.

 

[DCrake]

I think you are confusing your self with the Allow value list edits
Can you doa screen dump of the property sheet.

 

[JANR]

The link you gave as far as I can see this uses a combobox to set some formfilter, not inserting records.

See little demo of using a combobox NotInList event.

JR

 

[vbaInet]

Are you talking about Edit List Items? Probably not the exact name as I don't have 2007 in front of me at the moment.

In the Property Sheet of the control, what Tab is this NotInList under? Events or Data?

 

[SeanD]

Are you talking about Edit List Items? Probably not the exact name as I don't have 2007 in front of me at the moment.

In the Property Sheet of the control, what Tab is this NotInList under? Events or Data?

it's under Events

 

[vbaInet]

Can you do this:

In the Got Focus event of this control, put this:

Msgbox Me.[COLOR=Red]NameOfControl[/COLOR].ControlType = acListBox

Open your form, click the control and tell us what comes up.

 

[SeanD]

he can't recognize NameOfControl

 

[vbaInet]

Change NameOfControl to the name of the "listbox".

 

[SeanD]

It returend false.