Modules & VBA :: Msgbox With Combobox Value Ii Not In List Event
Jan 23, 2015
I currently use this code to trigger a not in list event, ask the user if they want to add it to the respective table, and then add it.
I'd like to be able to add the text in the combo box that triggers the event to show up in the msgbox.
Private Sub cboCategory_NotInList(NewData As String, Response As Integer)
Const Message1 = "The data you have entered " & me.cbocategory.text &" is not in the current dataset."
Const Message2 = "Add now?"
Const Title = "Unknown entry in CATEGORY Field..."
Const NL = vbCrLf & vbCrLf
I have a basic form linked with a subform inside of it. The main form has a list of customers in a combobox and the subform lists all the things they have ordered from us. This all works perfectly fine, I can add data to the list of things ordered and it's ok.
I decided instead of using a whole extra form to add customers I'd just have a not in list event and allow users to add customers through there, bit more intuitive and cut down on forms.
It works to certain extent except one small problem, if I add a customer I have to close and reopen the form to be able to add/remove data from them in the subform. If I leave the form open and try to enter in data in the subform immediately it just shows whatever customer I had last on the combobox and adds it to the last customer as well.
It works perfectly fine if I reopen it so I thought it was some kind of query or update snafu but all the VBA code examples I found don't seem to do anything. Not exactly sure where to go from here.
PHP Code:
Private Sub cboDept_NotInList(NewData As String, Response As Integer) Dim oRS As DAO.Recordset, i As Integer, sMsg As String Dim oRSClone As DAO.Recordset Response = acDataErrContinue If MsgBox("Add dept?", vbYesNo) = vbYes Then Set oRS = CurrentDb.OpenRecordset("tblDepartments", dbOpenDynaset)
So I have a form for addresses that checks against a table of US zip codes, and throws up a warning if the City/State/Zip entered isn't found on the table. This works great. But I was wondering if there was a way to list the available combinations based on the zip code entered. I want the program to tell the user that the city is wrong, but here is a list of what it could be.Is it possible for a msgbox to list all records where a field = value?
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 a combo box with three columns, the first one is the bound one, the second is text in English, and the third is text in Spanish. Currently when the form is open, both the English and Spanish texts columns are visible. What I would like to do is set up a command button on a different form that will open the form with just the English showing in the combo boxes, and another button for Spanish. I've tried the following code which opens the form, but the combo box is disabled altogether.
I am trying to write a code that will execute at the change even of the combobox/Listbox and when a character is typed in it then all the data from "DocumentType" field whose first character matches with the first character typed in Combo/Listbox will be stored in it.
The following code doesn't work:
Private Sub ComboBox4_Change() Dim strText, strFind As String strText = Me.ComboBox4.Text If Len(Trim(strText)) > 0 Then strFind = "BarcodeRef like '" & strText & "*'" End If
It may seem a rather simple question, though it continues to confound me -and I have searched to no avail :D
How do you trigger an event depending on what button is pressed on a msgbox?
For example, using a vbYesNo msgbox on a form, how can I assign events to either the Yes or the No option? Say, if "Yes" is clicked the focus would be set to a control on the form, and if "No" is clicked access would close down.
Any ideas?
I've currently got on the on click event of a command button (the bit in italics is fine, the bit in bold is not working!):-
Private Sub Command3_Click() On Error GoTo Err_Command3_Click
Dim stDocName As String
MsgBox "Please select where you wish to save the exported file", vbOKOnly, "Save To Location"
I have a form (MS Access 2003) that has a button that when pressed, assigns the current time to field [Time1] using
Code: Me.Time1 = Now()
I have another field [Time2] that adds 15 minutes to [Time1] using the following in the Control Source field
Code: =DateAdd("n",15,[Time1])
I was wanting to use these to force a message box when the system time reached Time2 and tried the following code on the Form ON TIMER event (Interval at 1000 ms) but it does not work.
Code:
If Time() > Me.Time2 then MSGBox "Time to show message" End if
I tried putting in a manual time in the VBA such as:
Code:
If Time() > #2:05:00 PM# MSGBox "Time to show message" End if
which worked. So, I am guessing there is something with my orignal code using Me.Time2 that is causing the problem.
I have a form (about patients) containing a listbox (list of visit dates) and a subform (showing medications at that date). When you click on the listbox, the subform is re-queried with the selected date as a parameter to show the medications that were in use on that date. This is working fine.
However, when you move to a new record (patient), the subform is initially blank because the listbox hasn't been clicked on yet. This means that it looks like the person isn't on any medications, which is causing some confusion.
I need a way to have the subform show the "top" (most recent) date because that is the current medication list, and the thing most people want to see first. It is always the top of the list, so I would like to do something like this (pseudocode)
Code: Private Sub Form_Current() Forms![MedSearch].SelectDateBox.Requery Send MouseClick Event to line 0 of SelectDateBox End Sub
I have 1 combo box contains 2 columns look-up directly from the properties(Not VBA)
Now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef Dim Query As String Dim st As String Dim rs As Recordset st = "SOV" Set qDef = CurrentDb.QueryDefs("get_query_reason")
This is something very simple, but I am messing it up terribely. If someone could please help me out with this.
I have a form which has a combo box. This combo box contains a list of Query names. What I am trying to do is simpely, when you highlight on any of the query names and click the run query button it should fire the query the result should be displayed in the datasheet view. I am not able to get a hang on how to do this.
It would be really helpful if someone could help me out with this as i need to get this done ASAP.
Hi, I'm new here and I thought I had already posted this but i couldn't find it so I am posting again... sorry if I've posted twice
When I pass a value from MyTextBox to MyComboBox using:
Me.MyTextBox = Me.MyComboBox
It does Not trigger the AfterUpdate Event of the ComboBox
I need it to! Any Suggestions??
I have tried: copy and pasting by but that creates a problem
Me.MyTextBox = BarCodeData$ 'passes the variable value to MyTextBox Me.MyTextBox .SetFocus Me.MyTextBox .SelStart = 0 Me.MyTextBox .SelLength = Len(Me.MyTextBox ) + 1 SendKeys "^c" 'copies the value of of MyTextBox
as soon as I add the below line, it no longer copies the value in MyTextBox Me.MyComboBox.SetFocus
The onEnter Event of MyComboBox has the following code that works fine.
If I ran all the code above, all works but the "Copy" and thus anything that may be in the clipboard is pasted into MyComboBox, and the AfterUpDate of MyComboBox triggers.
I know that there are compelling reason NOT to use the SendKeys but I was just trying something
What I'd really like to do is eliminate MyTextBox and pass the Variable directly to MyComboBox:
Me.MyComboBox= BarCodeData$
And have it force the AfterUpdate Event of MyComboBox
I have a form I use to search for clients when I hit a command button, using surname etc. I'd like a yes no button to pop up rather than the clunky ok button route I currently have. I'm a beginner with VBA and most this I've gleaned from internet.
If there are matching results, everything is fine. If there are no matching results, I've managed to get my desired messagebox come up by using the code below in the onload event of the resulting search results form, however the yes no buttons do nothing and the results form loads blank as though no records were found.
I'd like Yes to send users to the client input form, and No to return them to the search form.
Private Sub Form_Open(Cancel As Integer) If Me.Recordset.RecordCount = 0 Then MsgBox "Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found" Select Case intanswer Case vbYes
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code: If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
I have a database in which users have the ability to search by last name...nothing fancy there. However, the users continue to forget to click the Find Client button and instead just type what name they are looking for in the LASTNAME control thus changing the record that they are currently in!!!
I am looking for a way to have a msgbox pop up any time the LASTNAME control is changed. I found the code below but this works for the entire record and I don't want them to have to click Yes every time they make change.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer) If MsgBox("Changes have been made to this record." _ & vbCrLf & vbCrLf & "Do you want to save these changes?" _ , vbYesNo, "Changes Made...") = vbYes Then DoCmd.Save Else DoCmd.RunCommand acCmdUndo End If End Sub
So each combination has an ID. It's called the SuWID. I want to Transfer the data to a fixed Excel sheet. I wrote the following code
Dim xlApp As Object ' Excel.Application Dim xlBook As Object ' Excel.Workbook Dim xlSheet As Object ' Excel.Worksheet Dim rst As DAO.Recordset Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True
[Code] ....
The only Thing what i still want to have is that, before it opens the Excel form, which works already perfectly well, that a msgbox will Show up and ask me, which SuWID do you want to see in the Excel sheet.
i have a form to enter a new client if the client is existed then a message box appear and tell me that this client is existed and his number id is ## with two buttons yes and no.if i click yes i should go to a report that contains information about that user and this report take his data from a query
new client save code --------------------- Dim MSG As Integer Dim ExistentID As Long ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"), 0) If ExistentID > 0 Then
[code]....
when i click yes the report should open with the existent id but he open a input box to ente an id and his title is tbl_personal_information.PID it's the ID field that it's named P_ID in the query
JOB TABLE jobid(pk) date companyid(fk) staffid(fk)
COMPANY companyid(pk) name
STAFF staffid(pk) companyid(fk) name
the primary and foreign keys are joined and RI is enforced. like this infact http://www.cambridgeupholstery.co.uk/relate.JPG
using a method ive just learnt ;) thanx pat and rv the user can choose a company and a staff member for each job. the relevant id is placed in the job fields and thats all good.
the form looks like this. http://www.cambridgeupholstery.co.uk/myform.JPG
eventually there will be many companies and many more staff members. what i would like to happen on the form is when the user selects which company they are dealing with...only the relevant staff who work for that company are displayed in the listbox.. in other words i want to filter the staff names by the chosen companyid.
what code do i need to place and where.. my company list box is called "complist" and my staff list box is called "stafflist" and the form they are on is called "enterjob"
i hope that is clear.. thank you very much for any potential replies.
If you click on the main form called frmpatient you will see 2 text boxs one called Primary Insurance the other Called Secondary Insurance
Above those text boxes are to hyper links which open the list box
What I'm trying to do is if a user double clicks the name of the Insurance From the List Box it will insert that Name of the Insurance Into either The Primary Insurance or Secondary Insurance Text Fields
I do not Want to Use combo Boxes because I have about 1,000 Insurances in my database.
Hi I have placed a ListView on the form. I have written a DblClick event for the ListView. The application is a multi-user one. The issue is that the DblClick event was working for few days and now it does not work for any of them but it is working only in my system.
Is it related to reference ? I developed the tool on my system and placed in a shared folder from which everybody uses. Do I need to give reference in all the system ? Please help. I am stuck with this problem.
How can I show a msgbox with the text "Update in progress! Please wait a while", when I execute the following make table querye on form load?
Code: Private Sub Form_Load() 'Turns the Access warning messages off DoCmd.SetWarnings False DoCmd.OpenQuery "q_tblUdtrk2" 'Turns the Access warning messages back on DoCmd.SetWarnings True