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:
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 am having trouble requerying a combo box.I have created a Drawing Register database which allows users to create a new job as well as set up a drawing register for that job if the use checks a box in the job set-up form which indicates that particular job will have a register.Using the Job-Set up form I created a query which brings pulls the job number, job name and register check box and filtered it to show only those jobs that the box is checked. To this query I also added the relevant fields from the drawing register table. The Job Set Up table is linked to the Drawing Register table by the Contract No. field. Unfortunately I cannot set this field in the Job Set Up table.
Using this query I created a Drawing Register form with the sub-form Drawing Register Details. I added a combo box which shows the lists of all the drawings and allows me to view a specific drawing when selected. The problem I am getting is that the list is showing ALL drawings for EVERY job with a register and not just those drawings for the job I am currently updating. I tried using the following code with no luck
Private Sub Form_Load() Forms![DwgRegFRM].Form![Combo15].Requery End Sub
Private Sub Combo15_GotFocus() [Combo15].Requery End Sub
I want that when I choose to open the register for a particular job the combo box only lists the drawings for that particular job.
Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."
Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"
This is the code that I'm using:
Private Sub cmbWorkgroup_AfterUpdate() Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery End Sub
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
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.
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.
I have a main form populated by Dlookups.When I click on a textbox I open another form which is used to change data in my main table. Once this is closed the main form is still displayed but the amended data isnt reflected in this form. How do i get the data to change, I have tried requerying the form and the query that is used for the dlookups but I cant get it to work.
I have a Main form with a combo box that pulls names from a table. Alongside the combo box is a command button that opens a form which allows the user to edit the names in the table.
The problem: When a user edits a name and closes the edit form, the edit is not immediately displayed in the combo box. However, when the Main form is closed and reopened, the edit is properly displayed. Can I somehow refresh the combo box in the On Close event of the edit form?
I have a form with a tabbed control on it. The tabbed control has 4 tabs that each have a report on them. When I select a company name on the main form, I requery these 4 reports to show data related to that company.
For some strange reason, 2 of my reports requery with no problem, and 2 of them give me a message that the report can't be found. Here is the code:
Code: Private Sub cboCompanyName_AfterUpdate() On Error GoTo cboCompanyName_AfterUpdate_Err ' DoCmd.SearchForRecord , "", acFirst, "[CompanyID] = " & str(Nz(Screen.ActiveControl, 0))
' After selecting a company, requery the subreports to display the appropriate data
As a side note, if I chose the company, say "ok" to the error, and then go run the queries that these reports are based on, I get the correct records. Also, if I chose the company and run the 2 failing reports standalone, they work fine, so I know the problem isn't the reports or the queries themselves.
I have also checked and rechecked for any spelling mistakes and have found none. I should mention that the error message is "Programs cannot find the field 'SubrptCompany_Payback' referred to in your expression".
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
Greetings. I have a database that displays forecasts of our faculty needs. I now have 2 tables, Managers and classes. The Managers are bound to the form, the Classes to the subform. The subform changes every time I choose a new manager, as planned. But the Subform keeps requerying itsself, and I can't enter/modify data as a result.
I have the following code in the on change event of a combo box:
Surname.Dropdown
*Surname is the name of the combo box... Is there any code i can put somewhere so that the list part of the combo box disappears when an item is clicked?
I have a query based on a table which gives a breakdown of sales by month, sale type and by cost centre (department).
I also have another query which summarises the sales by sale type and cost centre so that and is based on the first query.
the second query is a subform of the first. I tried the second query as an update query but couldn't figure out how to execute it in order to refresh the subform. I've also tried to requery the second query having recreated it as a select query but I'm not able to refresh the data.
The aim is to refresh the data in the subform as any of the underlying data is changed in the original form.
I'm ok with VBA and with macros so if anyone has any advise or ideas I'd appreciate it.
I have a combo box on a form and when an item is selected other controls are populated from various tables. I'm currently using the OnChange event, but have tried others. It works great if you select an item, but is you type text in it does not work properly. I've tried lots of things but with every scenario am able to break it by typing certain text.
Is there any way to put a drop down list on the form (that is NOT a combo box)? Another option I found under "More Controls" is a "Microsoft Forms 2.0" combo box which you can set to be a DDL, but other features of it do not seem to work properly on the form. I'm not able to populate it with data. Documentation on MSDN is extremely light. Is there a way to populate this with a list?
Is there any event for the standard combo box that only fires if you select an item from the list - not if you type in text?
I am stumped as to how to get a form to update/refresh. Heres what I have! My main form:[Daily Log From] is opened, with a subform:[DailyDSQry subform] populated by a Query. This form has a command button that opens another form: [NewDSLog] in "acAdd","acDialog". What I need is: when the [NewDSLog] form closes, the [DailyDSQry Subform] needs to requery/update so that it displays the new entry.
I have tried the following in a command "CLOSE" button in form [NewDSLog]: docmd.Runcommand AcCmdRecordSave Me!Forms![Daily Log]![DailyDSQry subform].Refesh Me!Forms![DailyDSQry subform].[Query].!Requery doCmd.close
but no LUCK.....Keep getting errors Any Ideas Thanks all, I do appreciate the help Enviva
OK ive got my 2 vaguely working combos which select the surgery, and the staff.
On the same form I want a couple of tickboxes which show for example, whether a person attended the course, if they had to pay etc (all yes/no answers).
How do I get the tickbox to update? i have tried to requery it but nothing happens, it just stays as what the last thing done to it was.
I know how to assign an event to a combo box, but I want to be able to launch a specific link, app, or email template, based on the actual selection from the combo box. So when I make a selection, whatever program or event I assign, to that specific selection, will launch.
I have a list box (List25) that is generated from a query (qryThisBox). One of the fields in that query uses the criteria [Forms]![MyForm]![SelectedField] to filter.I also have combo boxes in the form that List25 displays in. Unfortunately, I want to requery the list box in the form, using a combo box that calls the same underlying table field, SelectedField.
How can I do this? Can I somehow dynamically change the criteria in qryThisBox to change when it gets requeried?How do I stack combo box requeries, eg: Combo Box 1 gets selected - does a requery
and/or
Combo Box 2 gets selected - and filters the results of the requery for Combo Box 1.
On clicking a button to open a form I want to filter a list in a combo Box located on the forms subform
The main form is called 'Frm_orders' The sub form is called 'sub' The combo box field is called 'CostCodeID' and I want to filter column 0 of the combobox which is Tbl_InvoiceCostCodes.Type to show records with a type of 'Repairs'
I'm working on a bit of code that before a combobox is changed checks with the user to confirm that they want the change to go ahead, if yes a recorded is added to a table
This all work fine apart from if no is selected - I am trying to get the combobox to undo the change however when you select no the msgbox pops up fine but the combobox does not undo
Can't see where I'm going wrong I thought undo worked for comboboxes
Code: ' Displays a message box with the yes and no options. Response = MsgBox(prompt:="Do you wish to change the status of this Job? 'Yes' or 'No'.", Buttons:=vbYesNo) ' If Yes button selected If Response = vbYes Then
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
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.
