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.
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.
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:
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
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:
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.
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.
Sure this one is simple but seem to be stuck (Friday!?!)
Is there a way to keep a running total of the length of text being entered into a text box?
I have a text box which I only want 100 chars to be entered and would like a count for the users of the number they have left to use. Using the len() function in the on_change event does not seem to pick up the length of the text in the box until it has updated.
Recently I'm designing a Data Entry interface, which contains more than 10 textbox (fields). Instead of asking users to move cursor manually after each data entry, I want the system can automatically move the cursor to the next textbox.
I tried to use keyUp Event set for each textbox. It did work but not in the way as I expected. I only typed in one character in that textbox without finishing the whole word. The system has already moved cursor to next textbox.
How can I solve this problem?
or is there any other solution for automatically moving the cursor to the next textbox after filling the data for each textbox?
I am developing a form whereby the user types a single character or a sequence of characters into textbox and a query WHERE clause is compiled upon each character being entered.
The query is the Row Source of a listbox which I requery upon each change in the text box contents.
In the Change event I cannot access the new contents of the textbox until focus moves to another control and then back to the textbox. This is not how I expect this event to work.
I have a form with 7 types of weights (7 textbox rows) showing 7 weights in kgs and the others in lbs.When the user update any weight-kgs textbox, the opposite weight-lbs textbox will be updated automatically, and viceversa.Now, I'd like a fast way to code this action without writing the After Update_event for every textbox (they're 14).I have already setup textbox tags differently with "WeightKGS" and "WeightLBS", thinking about using "for each ... next" statement, but I have some problems to resolve what I want.
I am populating a table using combo boxes in a continuous form. The box box alone works fine without any issue of repeated data. But as you know obviously the combo box only populates data in a single column of the table, in which case mine is the LASTNAME; so now I am attempting to populate a unbound textbox with the FIRSTNAME side-by-side with the combo box for the same record.
In the After Update event of the cboLASTNAME:
Code: Me.txtFIRSTNAME = Me.cboLASTNAME.Column(3)
However, after updating the the combo the textbox data is repeated.
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
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?
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
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
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????
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
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
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"
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
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.
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
I have a DB with TblInspector and TblViolation Data. Here is what I am having trouble with. On my form I have a combo box named Inspector and a text box named ARNumber. What I would like to have happen is anytime that I select John Doe from combo box Inspector it pulls his ARNumber from TblInspector and saves his ARNumber in TblViolation Data.
I have fields Inspector and ARNumber in my TblViolation Data. I am pretty sure it is an After Update Event. I just don't know what code to put where. This is what I currently have on my combo box. It pulls the number and displays it in the text box on the form but does not save it to the table.
Private Sub Inspector_AfterUpdate() Me.ARNumber = Me.Inspector.Column(1) End Sub
Ihave implemented custom events to sync between open forms. I noticed that if the code is engaging in middle of the way referncing a form with form constant (Form_formName) then the listeners stops listening so the event isn't firing, without getting the reason of this i just avoid using it Now, i have a problem that i want to open multiple instances of the same form and i didn't find another way than set frm = new form_formName as this is the only way i seem to be able to reference a closed form, so my question as followes
1. whether the "breaking" issue can be solved without avoiding "Form_FormName" 2. or a way to open
