473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

NotInList function gives error on: Dim db As Database

40
I have created a form with a combo box that looks up values in tblFrequency.
If the value is new I want to add it to the table and I have tried couple scripts I found online but it always comes out with an error. This is script that i would like as I seem to understand it more or less:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_frequency_NotInList(NewData As String, Response As Integer)
  2.     Dim db As Database
  3.     Set db = CurrentDb
  4.  
  5.     'Add the new value to the field
  6.     db.Execute "INSERT INTO tblFrequency (Update_Frequency) VALUES (""" & NewData & """)", dbFailOnError
  7.  
  8.     'Tell Access you've added the value
  9.     Response = acDataErrAdded
  10.  
  11.     db.Close
  12.     Set db = Nothing
  13. End Sub
  14.  
However with this and other one I tried error is shown and debugger shows this line selected: Dim db As Database
Any ideas on what could be the problem? I am using Access 2000
Sep 26 '07 #1
4 1447
FishVal
2,653 Expert 2GB
Hi, there.

Have you referenced "Microsofrt DAO x.x object library" ?
And you don't need to create object variable at all to run Execute method one time. And what you expect from db.Close command?
Additionally DoCmd.RunSQL does the same.
Sep 26 '07 #2
HyBry
40
well.... I have nothing of that.
I am a total noob at this. And to be honest the code was copied from somewhere and just adjusted the table and field names.
I played with the DoCmd.runSQl and got some results but not yet working
Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_frequency_NotInList(NewData As String, Response As Integer)
  2.     Dim question As Integer
  3.     question = MsgBox("Add " & NewData & " to the list?", _
  4.                        vbQuestion + vbYesNo)
  5.     If question = vbYes Then
  6.     DoCmd.RunSQL "INSERT INTO tblFrequency (Update_Frequency) VALUES (""" & NewData & """)"
  7.     Else
  8.     Response = acDataErrContinue
  9.     End If
  10. End Sub
  11.  
It adds the new value to the table but it does not allow it to be used and displays default not in list error message after I have clicked yes to add it to the table. Tried adding: update_frequency.requery without success...
Sep 26 '07 #3
FishVal
2,653 Expert 2GB
Setting
Response = acDataErrAdded
will avoid error message.
Sep 26 '07 #4
HyBry
40
Brilliant!!!
works like magic.

Thank you very much!!
Sep 26 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: David Deacon | last post by:
Hi i have the following code in a CustomerID field if the user enters a notinlist customer then they should dbl click to open the customer form However this error occurs "You tried to assign a...
1
by: Steve Leferve | last post by:
Can someone tell what what objects I have to work with on the 'NotInList' event? Basically I want to have a pop-up prompt the user if they want to add the data they typed into the database. ...
3
by: mal | last post by:
Sorry for repost - system added to another subject for some reason Have tried numerous ideas from the group to solve this one. It is such a simple example that it should be straightforward ! I...
4
by: CAD Fiend | last post by:
Hello, I have a combo box (cmbFirstName) on a form that is hitting a query (qryFirstName) and then putting that selected (or typed by user) value to the table field (name FirstName) on the table...
7
by: Bryan | last post by:
I am trying to allow the user to add an item to a list if it is not found in a combobox. When the NotInList event is triggered I run a function "AddItem" that has a custom dialog box to add an...
1
imrosie
by: imrosie | last post by:
Hello (from Rosie the newbie), I recently got help with a wonderful event to perform this from 'thescripts'...it recognizes that a name is not in the list an allows for (after parsing first and...
2
FireMedic
by: FireMedic | last post by:
Hello all, I am creating an application that has an equipment inventory and maintenance form that has numerous subforms that are displayed depending on the category of equipment selected. I have...
4
by: EManning | last post by:
I have a combobox whose rowsource is a union query. This query displays a person's name in "lastname, firstname" format and in "firstname lastname" format. The query results look like this: ...
6
by: Volker Neurath | last post by:
Hi all, I have a Problem with combobox-property "NotInList" and an unbound Form. The situation: On my main form i have three comboboxes for data-exchange (here: Names of distributor,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.