not in list problem

[maniatis]

i have a field in a table which takes date from a table named currency through a combobox.i have writen the following code in order the user to add the currency that there isn't in the list of the combobox:

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

On Error GoTo currency_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The currency " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO currency([currency]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new currency has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose currency from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboJobTitle_NotInList_Exit:
Exit Sub
cboJobTitle_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboJobTitle_NotInList_Exit
End Sub

the code works it asked me if i want to add the new value , it says that the value was added but afterwards it doesn't save it so it says the default message of access "item not in list"

what am i doing wrong?

many thnaks

 

[RuralGuy]

Replace:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

With:
CurrentDB.Execute strSQL, dbFailOnError

And it should report the error back to you.

 

[RuralGuy]

You might be having a problem because you are using an Access Reserved word [Currency].
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[maniatis]

Sorry but i changes the name of the table and the name of the field but still no result.Any other idea?

 

[RuralGuy]

Replace:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

With:
CurrentDB.Execute strSQL, dbFailOnError

And it should report the error back to you.

When you did the above, does it report an error?

 

[MStCyr]

Limit To List has to set to Yes...

 

[maniatis]

The Problem Is That It Executes But It Doesn't Append The Record To The Table Or It Doesn't Save It. I Figured It Out Because It Executes All The Code It Says That The Value Was Added Baut Then It Says Item Not In List.
What Do You Think?

 

[maniatis]

When I Did What You Suggested It Reports Syntax Error Of The Insert Into Command

 

[RuralGuy]

Try removing the ";" at the end and see what happens. Post back with the actual code that sets the string so we can see it after you changed the names.

 

[maniatis]

Try removing the ";" at the end and see what happens. Post back with the actual code that sets the string so we can see it after you changed the names.

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

'On Error GoTo currency_NotInList_Err

Dim intAnswer As Integer
Dim strSQL As String

intAnswer = MsgBox("The currency " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")

If intAnswer = vbYes Then
strSQL = "INSERT INTO invoices(currencies) " & _
"VALUES ('" & NewData & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

MsgBox "The new currency has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose currency from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboJobTitle_NotInList_Exit:
Exit Sub
currency_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboJobTitle_NotInList_Exit
End Sub

that's the code I have right now.
Let me add some more details the table which this form is Created is nameD INVOICES .In the table invoices there is a field named Currencies which is a combobox which has recordsource SELECT CURRENCIES.CURRENCY FROM CURRENCIES WHERE (((CURRENCIES.CURRENCY) Is Not Null)) ORDER BY CURRENCIES.CURRENCY;

if I change sth to the code insert into doesn't work.

 

[RuralGuy]

You need to change that "LookUp" field to a TextBox! Then you will see the problem. The Evils of Lookup Fields in Tables

 

[maniatis]

You need to change that "LookUp" field to a TextBox! Then you will see the problem. The Evils of Lookup Fields in Tables

the code I wrote is from the site www.fontstuff.com

I just copy the whole procedure.

if i change to to text box then i cannot say notinlist.

 

[RuralGuy]

I am familiar with Martin Green's work. I recommend it all of the time.

In the table invoices there is a field named Currencies which is a combobox which has recordsource SELECT CURRENCIES.CURRENCY FROM CURRENCIES WHERE (((CURRENCIES.CURRENCY) Is Not Null)) ORDER BY CURRENCIES.CURRENCY;

It is the "LookUp" field of Currencies in the Invoice table that needs to be changed to a TextBox, not your form. Look up's on forms are just fine.

 

[maniatis]

I am familiar with Martin Green's work. I recommend it all of the time.
It is the "LookUp" field of Currencies in the Invoice table that needs to be changed to a TextBox, not your form. Look up's on forms are just fine.

i did what you suggested which i found it very logical but still not working.can you test it? i cannot find what's wrong.But I am thinking that the problem is that it doesn't save the new data.

 

[RuralGuy]

Maybe if you post a stripped down version of your db with any sensitive data removed but enough data to demonstrate the problem, someone will have time to look at it and make some suggestions.

 

[maniatis]

I am familiar with Martin Green's work. I recommend it all of the time.
It is the "LookUp" field of Currencies in the Invoice table that needs to be changed to a TextBox, not your form. Look up's on forms are just fine.

i did what you suggested which i found it very logical but still not working.can you test it? i cannot find what's wrong.But I am thinking that the problem is that it doesn't save the new data.

 

[maniatis]

you are my only hope nobody else is answering.could you please test the above code in order to se what's wrong.thanks

 

[RuralGuy]

Can you post a sample of your db?

 

[maniatis]

i did it it worked it must be that i wanted to enter value not to the first column but to the second one of the table
thanks a lot you helped me understand how it maybe working.
thanks man