Data Misnatch In NotInLIst Event

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 Replies


ADVERTISEMENT

Using The NotInList Event And Updating Two Fields The Table

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

Modules & VBA :: Textbox Not Populated In NotInList Event

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

Forms :: Requerying Combo Box After NotInList Event

Sep 10, 2014

I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry form.

After this form is closed, I return to the subform to choose the company name that I just added, only it is not there because I can't figure out where to automatically requery this combobox. My duct tape solution that's working, is a command button that runs this code when clicked:

Code:

Forms!frmProjects_Detail_CSS!ASP_Project.Form!cbocompany.Requery

I have tried putting this code in the OnCurrent, OnFocus, Onclick, OnEnter....etc on the subform and on the combobox itself. So far the only way it requeries is if I use the command button.if you want to see the NotInList code, and the code on my company entry form.

View 5 Replies View Related

Modules & VBA :: Requery Combobox NotInList Event?

Mar 21, 2014

I have a form frm_GlobalSettings with a combobox cmbDescription that finds a record based on the value selected. The row source type for cmbDescription is Table/query, and the row source is a select statement on the form's underlying table.

I want the user to be able to use cmbDescription to add a new record to the form's underlying table. I currently have

Code:

Private Sub cmbDescription_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
'Get confirmation that this is not just a spelling error.
10 On Error GoTo cmbDescription_NotInList_Error
20 strTmp = "Add '" & NewData & "' as a new global setting?"

[code]....

but when a new value is entered this errors on line 70 with "runtime errror 2118 - you must save the current field before you run the requery action".I've tried various ways around this but can't get it to work.

View 2 Replies View Related

NotInList DAO Or ADO?

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

NotInList X 2

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

NotInList Error

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

NotInList Code

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

ComboBox Notinlist 2 Fields

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

Help On NotInList SQL Syntax Error Please..

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

NotInList - Still Getting Standard Message

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

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 1 Replies View Related

Cascading Combo & NotInList

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

Modules & VBA :: NotInList For Multiple Records?

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

Forms :: Combobox NotInList But Not Null Either

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

Enter New Data Event

Nov 7, 2005

Hi, I am not entirely sure where to look in the forum to answer this question, maybe someone can help me. I am just trying to create a simple code that would open a form if a user enter in a field a value that never has been entered before and open a other different form if the user enter a value already recorded. I can't find a 'not in recordset event', but there must be a way as when the form is set to Data entry with no duplicates an event occurs depending on the data being already recorded or not.

View 5 Replies View Related

MS Access - Macro To Handle NotInList And LimitToList Properties

Jun 3, 2005

:confused: I need to find a way using macros to accomplish this task: I am trying to set the LimitToList property as a YES, and enter data that is not in the list already, but instead of the NotInList property giving me the following error "The item you entered is not in the list. Please choose one from the contents of the drop down box", I want to branch to a macro entered at theNotInList property to branch to that will eliminate the error, and give me the opportunity to enter my own message with options for the user to enter the item into the list, or try to choose an item from the drop down box instead. I want to do this with a macro if possible. I have seen many VB solutions that purport to solve the issue, but to be honest, I am not very well versed in VB procedures. That's why I would like to do it with a macro. I am very competent with macro procedures! Please help ASAP. Thanks.

View 1 Replies View Related

How To Add A Record In A Table Based On NotInList Option In A Form

Jun 23, 2005

I've 2 tables:

customers
-id
-customer_name
-city_id

cities
-id
-city_name

I've a form to create new customers accounts.
The form has a drop-down control listing the cities in the "cities" table.
This drop-down control has a hidden column (id) and a visible column (city_name).
The user can choose a citie from the list and the city id (1st column in the drop-down list)
will be stored in the "city_id" field in the "customers" table.

Problem:

I need to allow users to enter cities not listed in the drop-down control.
When they entrer a new city, I need to create a new record in "cities" table for the new city.

I tried opening a dialog using the NotInList event, and the dialog pops up as espected, but also pops up an ugly msgbox wich says that the typed text isn't a listed item.

Please help :-(

View 1 Replies View Related

Data Access Page Onload Event Problem

Aug 8, 2006

This behavior is driving me crazy!

I have a data access page with an onload event for the window. All I want to do is go to a new record, update a date field with today's date and save the record. Here is the code:

<SCRIPT language=vbscript event=onload for=window>
<!--
window.MSODSC.CurrentSection.DataPage.NewRecord
request_date.value=DateValue(Now)
MSODSC.DataPages(0).Save()
-->
</SCRIPT>

When I load the page, I can see the date pop into the request_date field, but then it disappears. I made sure the value update code works by putting it on a different object's click event. The problem is when it's in the onload event for the window.

Is there something I'm missing about this event that prevents this from working? Thanks!

View 2 Replies View Related

Tables :: Updating Table Data In Event Handler

Mar 11, 2013

I am working with MS Access. The database has 2 tables.

-Parent and Student and ParentID is the Primary key as a parent may have multiple Students
-There is a form that lets me add students for a particular parent

One of the fields in the Parent Table is FeeDue. I added a field in the Parent Table called NumOfStudents..What I want to do is as follows: When a student is added on the Student Form, I want fee to be calculated automatically for display AND update the FeeDue field in Parent table. Event handler executed when a student is added (checkbox clicked)

OnClick()
{
Read NumOfStudents from Parent Table

if(student_added = true)
NumOfStudents++
else /* This is to cover student withdrawl*/
NumOfStudents--

if(NUmOfStudents = 1)
Fee= 400
else
Fee = 500
}

student_added check box is on the student form NumOfStudents and FeeDue are fields in Parent

View 1 Replies View Related

General :: OnClick Event - Checking For Null Data

Jan 10, 2014

So on a command button I have this code in the OnClick event. When I click the button it will ask me to enter the name if blank but if I enter something in that field and then delete the data is bypasses this. Does the same reason field, Why?

Code:
If IsNull(Me.CE) Then
MsgBox "Please enter your name"
Me.CE.SetFocus
Exit Sub

[Code] ....

View 1 Replies View Related

Reports :: No Data Event For Report Made Up Of 3 Subreports

Jan 15, 2015

I have three reports that I have used as the subreports for one master report. The master report shows the three subreports on a page.

When one or more of the subreports contains no data the master report still shows the empty subreport. I would like subreports with no data to be left off the master report if this is possible.

For each of the individual subreports I have used the On No Data event to display a message and then not open the report if there is no data. This works fine for the individual subreports but when I open the master report the "empty" subreport is still displayed.

Is there a way to leave the subreport off the master report when there is no data in the subreport?

View 1 Replies View Related

General :: How To Load Data From Listbox Double Click Event

Aug 22, 2014

Is it possible to load the data in input from by double clicking on data from list box ?

I have single form on which both input form and read-only form is present. as i used visible property to display or not accordingly.

User enters the data from input form. (It has been done)

User go onto read only form where combo box and list box. from drop down values load into list-box. (It has been done)

Now double click on any record then it should re-directed on input form with loading the data in editable mode for updation purpose. ???? (How this step will be done) ?

View 9 Replies View Related

Forms :: Timestamp Record After Update Event And Copy Data Into Another Form?

Oct 2, 2014

I have a simple data entry form on which there are about 30 to 40 data fields to be update by users. One of the field is called "Stage" which is a combo box with 2 entries "Formal Case" and "Informal Case" with no default value whien a form is first opened.

Users will normally create a data entry which updates the table and save.

However if in future users open any one existing record and try to change Stage field from Informal to Formal or vice a versa I would then like this action to

1) update a time stamp concatinated with the stage change value in a text field perhaps

2) save/close the form

3) open a new form with all the existing entries(plus changed status of "Stage Field") with a new record ID and new time stamp.

View 1 Replies View Related

Modules & VBA :: Event To Match Up Previous Entries And Insert Matching Data

Nov 19, 2014

The next stage I'd like to include is a match and enter pricing details. We get a lot of repeat work that is entered onto our system, due to the nature of the work we do each item is then checked individually. There is a portion of this that could be automated.Our order intake table has an amount of information, various bits of which are supplied by various customers, but not always all of the information is required or given.

Id like a price to be copied from a previous record where the "shots" field matches a previous entry.The other fields that would need to be similar are the "coverage" "drawing no" "pattern no"..But only when these fields are populated, we often, but not always put a - in these fields where the information is not given. I think this may be bad practice, but it shows us the information has not been supplied, and not just missed from the order.

To sum up, when an order is completed i'd like to have an afterupdate event that searches the previous records, where cover, drawing no / pattern no are *matching* and the shots do match, then copy the price of a matching record, where that record is within 12 months of today (using the date_rec field) and then tick an "automated_price" field so i know the price has been generated by the system.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved