Supressing NotInList Response...
Jun 29, 2005
Looking for a way to supress this message box...
The text you entered isn't an item in the list.
Select an item from the list or enter text that matches one of the listed items.
... when the NotInList event fires.
Trying to just open an unbound modal form, pass a couple values to it, and set focus on a particular combobox for additional information to be added to my lookup table:
Private Sub cboPostalCode_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
Const Message1 = "The postal code you have entered is not currently on record."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown Postal Code"
Const NL = vbCrLf & vbCrLf
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
DoCmd.OpenForm "frmPostalCode"
Forms!frmPostalCode!cboCountryID = Me.cboCountryID
Forms!frmPostalCode!txtPostalCode = NewData
Forms!frmPostalCode.SetFocus
Forms!frmPostalCode!txtCity.SetFocus
Else
Me.cboPostalCode.Undo
End If
Exit_ErrorHandler:
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
Any thoughts?
Regards,
~Chad
View Replies
ADVERTISEMENT
Apr 15, 2008
Hi
I have a query that uses 2 variables from a form. All the query is doing is checking whether the 2 variables exist in a table. 99% of the time there will not be a match. Does anybody know how you supress the query from being displayed when no results are found?
Thanks
Andrew
View 2 Replies
View Related
Nov 1, 2005
I have a database which tracks onsite and offsite files. I need to create a pop up message box using the toggle button function, with a "yes" or "no" response, so when a file is onsite and you click the offsite box, a pop up message box will display the following message, "Are you sure you want this file offsite?" and if yes is clicked, it will switch to the offsite field. If no is clicked, it stay as is, (onsite would be the default). The same thing applies for when a file is offsite and you want to return it onsite, when you click in the onsite box, the pop up message box would display "Are you sure you want this file onsite" if yes is clicked, it will switch to yes, and if no is clicked, it stay as is. Can this be done?
Thanks!
View 1 Replies
View Related
May 18, 2005
I added the notinlist event to an unbound combo (I use access 2003) and used the ADO code but no matter what I try I can't get it working. Out of desperation I substituted the DAO code and "bingo" I have a working combo which adds any data I type in etc, no errors no problems. My question now is; Do I now stick with the old rule "if it ain't broke don't fix it" and continue using the DAO code or do I continue to pull my hair out (not much left now) trying to get the ADO code working?
View 2 Replies
View Related
Nov 21, 2005
This is the NotInList I have been using for some time (below). it works well but only updates one field. I need to update two fields now, City and ZipCode. Can I add data to two fields each time the notinlist is envoked?
On Error GoTo Err_ErrorHandler
Const Message1 = " Is Not In The Database."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf
Dim db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox(NewData & Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTbl or Qry")
With rs
.AddNew
.Fields("MyField") = NewData
.Update
.Close
End With
Response = acDataErrAdded
Else
Me.MyField.Undo
Response = acDataErrContinue
End If
Exit_ErrorHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
View 3 Replies
View Related
Aug 8, 2005
In my database I have many names in alphabetical order. What I want to do is create something where, for example, if I type a 'B' in a text-like box the list of names from the database automatically scrolls/moves to the beginning of the Bs either in table view or preferrably in another text-like box that mirrors the table contents. Then if I enter an 'E' after the 'B' the cursor moves to the beginning of the names with 'BE' in them, and so forth for each additional letter. Each move would happen immediately after each key is pressed, without using the <enter> key, probably like a 'keypress' event.
I would appreciate some direction or if someone knows where there might be some existing code or examples.
Thanks ahead of time for your effort!!!
View 1 Replies
View Related
Mar 9, 2006
hey
Well I'm haveing some problems here.
When I type something in my combobox that aint in the list it doesnt go to the precedure NotInList.
I need this for both of these comboboxes and I tried an altarnative way with rowsource but that aint working aswell
so here is the code
Private Sub cboReeks_AfterUpdate()
If cboReeks.Text <> cboReeks.RowSource Then
Call Foutmelding("U hebt een foutieve waarde ingevoerd")
cboReeks.Value = ""
End If
End Sub
Private Sub cboRonde_NotInList(NewData As String, Response As Integer)
Response = 0
Call Foutmelding("U hebt een foutieve waarde ingevoerd")
cboRonde.Value = ""
End Sub
Thanks Cobra
View 5 Replies
View Related
Dec 19, 2006
Hi all. This is the code that I have used in my NotInList event
Private Sub Suppliers_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Suppliers_NotInList
'-- We may need to add another Product
Response = MsgBox("[" & NewData & "] is not yet a valid Supplier..." & vbCr & vbCr & _
"Would you like to add this Supplier to your DataBase?", vbYesNo)
If Response = vbYes Then
'-- Create a new Product record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into TableSupplier (Suppliers) " & _
"Values(""" & NewData & """)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded '-- will requery!!
Else
Response = acDataErrContinue
End If
Exit_Suppliers_NotInList:
On Error Resume Next
Set db = Nothing
Exit Sub
Err_Suppliers_NotInList:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_Suppliers_NotInList
End Sub
The problem i'm having is that the code is not working. However, I have used the same code but modified to suit on another field elsewhere in the form which works fine. Any ideas????
View 4 Replies
View Related
Oct 10, 2006
Hi
I have developed a DB for work and it has grown, it is about 60Meg, all of a sudden when I am opening Design View in Reports, or queries it started running really slow.
I always Compact on Close
I have Split the DB.
Still no Joy, I have a brand new Dell PC so nothing wrong there.
What is happening? it is a nightmare to get anything done.
Please advise.
H
View 3 Replies
View Related
Nov 19, 2007
The database response is significantly slow when our application requests data over a network. Are there any settings to adjust network response? The network is connected via a T1 line.
According to information I've found online, our application is the front end to the database and the database sits open on the server.
View 1 Replies
View Related
Apr 11, 2006
OKay so i have a combobox with 2 fields, City and CityCode. (i.e. Albany, ALBA) I want to be able to type in a new citycode or city so that when it's not in the list the user can add it to the list along with the field that they're not entering which would be city or citycode. Please help! thanks
View 1 Replies
View Related
May 13, 2007
Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please
Private Sub CDT_NotInList(NewData As String, Response As Integer)
On Error GoTo CDT_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add to the list now?" _
, vbQuestion + vbYesNo, "DentureBase")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new CDT has been added to the list." _
, vbInformation, "DentureBase"
Response = acDataErrAdded
Else
MsgBox "Please choose a CDT from the list." _
, vbInformation, "DentureBase"
Response = acDataErrContinue
End If
CDT_NotInList_Exit:
Exit Sub
CDT_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CDT_NotInList_Exit
End Sub
Thanks very much
View 9 Replies
View Related
Apr 10, 2005
I have a combo box which is used to find a record on the form. I have set LimitToList = Yes and put in code for the event NotInList.
My problem is that my code is working fine allowing me to produce my own message, but then the standard message appears as well saying "The text you entered is not an item in the list"
Has anyone any ideas please?
View 2 Replies
View Related
Oct 3, 2006
I have 2 Cascading Combo boxes setup and working correctly.cbo1 lists all agents and cbo2 lists the branches that relate to each agent.tbl_agents and tbl_branches are setup with a 1 to many relationship.I have used the code (explained here (http://www.fontstuff.com/access/acctut20.htm)) that allows the user to add a new agent if they are not in the list. This works correctly. Now I want to let the user add a new branch for the agent they have just added. The code appears to work correctly from the front end, but when you check tbl_branch, there is a new entry but it is missing the appropriate agentID foreign key (ie, it's not related to the new agent).Is there something that I'm missing? I assume this is possible, but I must be doing something wrong.Maybe I need to create a little pop-up form for data entry?any help appreciated.
View 7 Replies
View Related
May 15, 2006
I have an 18.8MB Access application consisting of the following:
140 tables
410 Queries
67 Forms
5 Macros
26 Modules
It's not a lot of data storage, but is very computationally and mathematically intensive. As we continue to develop and expand the application, I noticed last week that we suddenly experienced a massive falloff in performance. Access is taking an incredibly long amount of time to traverse the querydefs collection, find objects, etc. It seems that the object collections themselves are not indexed, meaning that it takes a much longer amount of time to run a compiled and saved query than it does to simply build the SQL string and execute from within code. I continue to hunt for coding bottlenecks and any other efficiency problems I can find. Has anyone else experienced this? Is there a "critical mass" for the object containers?
View 5 Replies
View Related
Feb 8, 2007
Here's my crosstab report
Answer Name1 Name2 Name3
Yes 90 95 85
No 10 10 35
Total 100 105 120
I need to devide the number of Yes responses for Name1 by the total number of responses. I tried doing it like this =(([Name1]=Yes)/sum([Name1])), but that isn't working.
Thanks in advance,
Dave
View 2 Replies
View Related
Dec 20, 2012
I have a table of issues (tblIssues). Each issue has legislation that it must be dealt under. Some issues relate to more than one piece of legislation. Some legislation deals with more than issue. Ideally, therefore, I'd like to have a separate table of legislation (tblLegislation).
Where any issue refers to two (or three) pieces of legislation, can I store that in a single field? Or do I simply need to create a record in tblLegislation for the combined legislation?
View 2 Replies
View Related
Jul 28, 2005
OK here is what I have:
Table Name: Student
Fields: Auto (Primary Key)
MOI (number)
Name (Text)
I have 2 forms:
1) Student ,which contains student info I have created a combo box in this form for MOI, so whenever I type an MOI ID that have been previously entered I get the student Name, if it's not previously entered I used the NotInList Event, that asks the user if he wants to add the new value in the list if Yes, it will forward him to the second form
2) student_info, which is an entry form the user enters the name of the student and go back to the student form.
The following is the NotInList event code:
Private Sub Combo10_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new student.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the student_info form in data entry
' mode as a dialog form, passing the new MOI in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in student_info form's Form_Load event
' procedure.
DoCmd.OpenForm "student_info", , , , acAdd, acDialog, NewData
End If
' Look for the student the user created in the student_info form.
Result = DLookup("[MOI]", "student", _
"[MOI]='" & NewData & "'")
If IsNull(Result) Then
' If the student was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub
The code is working perfectly except for one problem:
When I'm in student_info form to enter the name of new student and I want to go back to the Student Form I have this error message:
data type mismatch in criteria expression
I figured maybe because I'm passing a number field and in the DLookup criteria it assigns the MOI (which is number) to NewData which is String as follow:
DLookup("[MOI]", "student", _
"[MOI]='" & NewData & "'")
Attach it the database for better understanding of my prolem.
Any help will be very much appreicated!
Thanks,
CS.
View 14 Replies
View Related
Aug 1, 2013
I have two tables: Products and Invoice. Product table is for creating product list which will have additional information about products for calculations latter. Invoice is table which contains information about invoices and bought products. Invoice table column Product is combo box which I created that user wouldn't type same product in 100 variations (list is from table product column product description).
One invoice can contain more than one product which is not in product. NotinList add notinlist products to list but only one product at time (at least I know only this way).
Is it possible to add multiple records with notinlist? what i would like to to is:
1. user adds new invoice and access doesn't bothers with error messages about item not in list
2. after invoice have been added, access checks if all product is in list and offers to add those ones which is not in list to add to list.
View 6 Replies
View Related
May 31, 2013
User pick a record from a list and are able to doubleclick that record which will open another form with that record's ID. If the field is Null, then another form opens allowing the user to create a new record. The part that doesn't work, is if that record that was in the combo, gets deleted from somewhere else. Then the ID is still being shown in the combo (that's OK but it's no longer null), so double clicking the field does not bring up the correct Create New record form.
What I would like to have is something similar to the NotInList message that states something like, "That record has been deleted". This is what I have so far,If not in list
Code:
Private Sub APID_NotInList(NewData As String, Response As Integer)
MsgBox "Please select an ... or double-click this field to add a new entry to the list."
Response = acDataErrContinue
APID = Null
End Sub
On double clicking the combo
Code:
Private Sub APID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
[code]....
View 5 Replies
View Related
Oct 14, 2005
Hi, All--
I am designing a database to capture the data of returned surveys. I want to design the database to facilitate data analysis through crosstabs or other aggregation queries.
If I design a table where each record is the complete survey responses to all survey items in a returned survey, this is not friendly for such query analysis. (In this, each field would be a survey item). Call this the horizontal method.
The other way would be to have a reference table containing the survey items , and have responses entered in a seperate table linked by item id and response id (from a third table containing a record for each submitted survey). Call this the vertical method. This would take more time to set up but would probably be easier to query.
The item response table would become quite long contaiging every item response for every survey turned although each record is short.
Does anyone have any opinion on this, or perhaps a completely different approach that I haven't thought of that would be easy to set up but also easy to query?
Thanks.
Paul
View 2 Replies
View Related
Sep 15, 2004
Hey. Basically I have several tables. One table (vistiors) has a combo box in one field called Hostee. Each hostee is listed in another table with all their information. When a hostee is selected in the hostee field of the visitors table I simply need it to count how many people including the currently selected person are being hosted by the person and check if it is greater or less than the amount of people set in the host table which has a field called Limit that has a number which is the maximum amount of people one person is willing to host.
If its greater than the amount in the host table a warning message should come up informing the person (but still allow the assignment to occur.)
As a note I have the combo box set to show the first name of the individual but select both the first name and ID
the code is below for the row source:
SELECT [Hosts].[First Name], [Hosts].[id] FROM Hosts;
If someone could tell me how I could get it to show multiple things within the combo box that'd be great.
IE Right now it shows just a first name liek Alex, Rachel, Bill. I'd like it to show the first name as well as the last name and a third additional field. IE Alex Lastname T1 or Rachel Lastname F4 etc.
Can anyone show me the general code to do this? I do know some VB but I'm used to using SQL with PHP primarily. I am new to access and only know it vaguely. Thanks in advance!
View 2 Replies
View Related
Nov 20, 2013
I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).
I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.
View 3 Replies
View Related
Feb 24, 2014
I have a DB that holds multiple possible email responses, originally set up for just straight up create email based on this information. the Button to created the email works great. Now what I would like to be able to do is Click a 2nd button that instead of creating a new email, it would open a reply to the current opened email. I would still want to push the information to it like it does in the current new email button.
View 1 Replies
View Related
Nov 26, 2005
I have a data entry form where country(ies) (there can be more than one for
each project) is/are entered in a sub-form on a tab control. Countries not
listed are entered by the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
This works fine. However, if I restrict the country names by a region
selection on the main form (for example if region Africa is selected on the
the African countries are preselected) the NotIn List does not work. Any way to work around this?
Another question, I want the user to select a region (a combo box ) on the
main form and when a new country (using the NotInList event) is entered in the subform both values go into
the same record in tblCountry (which has two fields CountryName and Region).
For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) "
strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
But it does not work (Region is the control on the main form named frmAddPro).
Any suggestions would be welcome. Thanks.
Niels
View 4 Replies
View Related
Mar 22, 2014
SQL related with Spec combo box:
Code:
SELECT S.Key, S.Spec FROM tblSpecs AS S ORDER BY S.Spec;
NotInList code:
Code:
Private Sub cboSpecs_NotInList(NewData As String, _
Response As Integer) ' MODIFY SPEC ENTRY.
10 Dim db As Database
[Code]....
1) For some reason, txtMessage is not displayed on the form when NotInList is processed. I see it get populated properly in debug mode, but it never gets on the form. But txtMessage gets displayed normally in Add and Delete events.
It seems to do with the way NotInList works because txtMessage is displayed if I use acDataErrContinue. But that's not what I want; it leaves the combobox list open and can't save record if I close the form.
2) DLookup is used to extract the old Spec value to display in the message. I can see the old Spec in debug mode in cboSpec.column, but Listindex is -1 and unusable.
I don't know how to point to the proper record in the list. The only other way I can think of is to loop through the list comparing the Key, and then extract the Spec.
I don't know if it's more efficient to use lookup or loop.
View 4 Replies
View Related
