I am trying to populate a list box with an event after update in a combo box. I can get the formula to work using 2 criteria, the problem is i nee to add a third criteria. When I try to add it I get the run-time 13 error.
Here is the code I am trying to use:
Private Sub cboStatusRFQ_AfterUpdate() Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool.RFQ Contact] " & _ "FROM Consolidated_Master_Req_Pool " & _ "WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool.RFQ Supplier] = '" & Nz(cboStatusRFQ.Value) & "'" And "[cosolidated_master_req_pool.Status] = '" & "[SUPPLIER_RFQ FOLLOW-UP]" & "'" & _ "ORDER BY [Consolidated_Master_Req_Pool.RFQ Contact];" Me.cboSupplier = Null End Sub
I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.
I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.
I am creating an input form to assign an engineer to a project for x days.
The form consists of two Text Boxes for the Engineer Code and Project Name. I also have a Multi Select List Box which is linked to a table. The Table contains two fields ID-Autonumber and Date-ShortDate. This table contains all dates for the next three years.
I ideally want the user to select the Engineer Code and Project and the desired dates the engineer is to be assigned and add these to another table.
I have tried the following code but while the two Text Boxes update fine and the number of selected dates are added the dates themselves are not.
Can anyone see where I am going wrong and guide me in the right direction
Private Sub Command4_Click() On Error GoTo Err_Command4_Click
Dim db As ADODB.Connection Dim r As ADODB.Recordset Set db = CurrentProject.Connection Set r = New ADODB.Recordset
For Each var In Me.List0.ItemsSelected r.MoveLast r.AddNew r.Fields("Date") = Me.List0.Value r.Fields("EngineerCode") = Me.EngineerCode.Value r.Fields("Project") = Me.Project.Value r.Update Next var
I have a list box which is bound to a field in a table - however, when I select a value (which comes from a lookup query) it is not writing the value to the table, which is basically making my database useless!
I've also tried using an update query using the following code (before trying this I made the list box unbound):
UPDATE tblgroup SET tblgroup.Price = [Forms]![frmMain].[lstPrice].[value] WHERE (((tblgroup.Group)=[forms]![frmMain].[txtGroup].[value]));
I have a private sub on a subform called txtsearchstring_change.
The txtsearchstring box is = to the client_id on the main form.
Basically when the user clicks on the next record button (which is on the main form) it should update the txtsearchstring_change on the subform and then display the new results in the list box.
Im not to sure how to do this. I understand im calling a sub from another form, so i did try a public not a private sub. And i have added on the next and previous button - txtsearchstring_change.
When I add something that is not current in the recordsource for the combo I still get the msg box: "is not on the list, Do you want to add it? click yes or no. I click yes and then I now get this error: "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"
As I said earlier the NotOnListEvent worked just great until I added the AfterUpdate Event. Any suggestions for me? Thanks in advance
The Source Table is updating but the Table the Form populates is not updating.
Please help. This is driving me crazy. and thanks!
I have a form in Access 2002 that a field contains a Listbox of States (US states). If I neglect to click into the listbox and alter the state name and then leave the form, I will get no entry. There an item on the list highlighted each time the form is opened. I would like the highlighted list item to act as it was selected, and then become the value stored in the field. But, I can't seem to figure out a way to ensure this happens.
hello all i have a problem, i have a form bounded to a query that displays the books infos, in this form i have a list control that displays the list of authos based on the code of the book the problem is when i have more than one record in the opened form ie more than one book and i move to the second record the list doesnt change and displays the authors of the first book when the form first opened anyone has an idea how can the list be updated automatically when i move between records thanks a lot
I have a list box on a continous form that dynamically pulls it's information by why of a query where a field in the query is equal to the value of a field on the form.
The problem is it only does it for the first record and every record after that has values based on the first record. Is it possible for the list box to show different values for each record or is there an alternative method of pulling the information.
I realize there are several threads already dedicated to this particular topic but my situation is slightly different... First, my tables are set up like so:
There are 3 racks to a room and a total of 90 rooms. A room can hold up to 3 students. A list box holds values that I have typed in (the room numbers). I went with typing them in rather than pulling them from a query because of the way my tables are set up. If I pull from a query as the tables stand, room numbers show up 3 times, once for each rack. I want to be able to fill in text boxes with information on students assigned to the room I choose from my list box (FName, LName, etc...) I'm kind of at a loss here on how to go about getting the info that I want. Using the column property to populate text fields is probably going to be much easier but a query is needed (I think) and using a query causes room numbers to show up 3 times in my list box... Anyone want to throw me a bone?? Thanks! :)
Greeting, - i have a form, that has a List Box coming from a table ,, This list box when you click on it, it shows that field in the main form - and i have a front end and a backend to this database, - When i add a new entry in my main form, it shows in the List Box just fine, but when i click on that list box, it doesn't show the entry on anyone else on the network - works ok locally
here is an eg: my main table has firstName, LastName, PhoneNumber, Address
Form : main form has firstName, LastName, PhoneNumber, Address List Box inside my Form has: First Name
so i when i click on the firstName in my list box, it will show me the contact person in my main form
as i mentioned i have couple of people has the database open (split Database) anyone on the network, could see the update on the list box , but when they click on the new created name doesn't show in the main form, unless you close access, and reopen it again
i hope i was clear expaling,
any idea on how to have the List Box update itself without closing and opening Ms Access ??
I am an Ms Access newbie and need some help on how to update 2 fields from a drop down list.
The drop down list has 5 columns. The first column is the partname that is bound and updates the table with that partname. I want to take the listprice which is the 5th column and update a field in the table with the listprice that corresponds to the part name.
The coding below works fine. It presents a form with a list box of counties. Allows the user to select ALL, one or several counties and returns a query containing the clients from those counties.
The fields showing in the query are First, Last, Add1, FLAGToMap, City, Prov and Sector_Name.
I want to add in there a choice to select only the records that have are TRUE (-1) in the FLAGToMap field - just like the ALL button, this would be an ALL Selected Button let's say.
I would not know where to begin as I copied and adapted the coding below from a sample database and don't understand - at all - how the query is generated. The only coding in the form is the one below.
Private Sub cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
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")
I have a form which uses a list box to select which record to display. The code is all generated by Access during form design. It is a method I have used numerous times inthe past in various database without problem (even in the current database I am developing).But for some reason on this one form I get the following error;-
"The expression After Update you entered as the property setting produced the ollowing error; A problem occurred while database was communication with OLE Server or Active X Control".
I have a form where I select a vendor name out of a combo box.
The rest of the vendor information then defaults into combo boxes below. I want these to actually be list boxes so that the user does not have to select the rest of the vendor information. The only problem is that when I turn them into list boxes the data does not save into the underlying table. How do I get the list box information to save??
Hi Newbie here - Both to the forum and MS Access!! - I'm trying to build a db at work that allows us to track our residents activities on a daily basis and then report monthly, etc. What I want to do is to able to use either a list box or a combo box to highlight more than one resident and more than one activity (our residents go out on group activities, so it would be ideal if we could assign a resident to several activities or an activity to several residents) and then add the records to the relevant tables. I've looked at multiselect in list boxes but that will only allow me to add one single record at a time which would make the job v-e-r-y laborious. Combo box will only allow me to add one record at a time. Any suggestions? Thanks in advance.
Hi, i'll try my best to explain what i would like to do.
I have a form (based on a Table "Tbl_AOG_Demands" which has a combo box called "Combo30" Using this give users access to all request details "regardless of status"
I also have a list box "List21" which does nothing other than shows ONLY open requests. (also based on Tbl_AOG_Demands) but filtered through SQL.
As there are many requests in the combo box (historic data) i would like to make things easier for the user by the following.
When a user clicks on a request in the list box, it will populate the rest of the form with the corresponding data. (and also the combo box "if this is possible)
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
I want to have a list box, which is populated with information from a table/query, to 'filter' what it displays based on what i type in a text box, but i need it to search multiple fields. E.g. say I have a field called name and another called address, and say there are 3 johns in the name field of the table, i want the list box to filter all the other names out and just show me the johns and update automatically. but using the same text box i would like to also be able to search addresses and filter them.
code for the 'On Change' event of the text box to reload the query in the list box. I need to put in the 'criteria' section of the query, i have sound this so far but unsure if its right for every field in the query:
need help., have a form, have put a combo box to display values from another table, works ok, but when a type a new value, it displays 'not in list', so how to add the value to the table without opening the other table or any other suggestion, should i select values in combo box from a query... :eek:
I have an add record command button that will bring up a blank record where the user can fill in the information. My question is how do I get this record to be added to the combo list that will bring up the record i just filled out? I hope that makes sense. thanks
I have minimum year and maximum year value (for example 2006 is minimum & 2009 is maximum. How could I get a combo box to display all the years within the range of the minimum and maximum year (in this case, it should give me an option of 2006, 2007, 2008, 2009).
FYI - This database is used to track projects and each project has a commencement date and an estimated completion date. the minimum year is derived from the commencement date and the maximum date is derived from the estimated completion date. I am building a search form whereby the year selected in the combo box should display all the projects commencing, continuing, and ending that year.
If the 5th option (Other) is selected, the user is required to specify what 'Other' represents.
I have created a combo box with the first 4 options and in instances of 'Other' the user can enter specific treatment that is not in the list (1 to 4).
What I would like to do is whenever the user enters a value that is not on the list (1 to 4), I would like to insert the word OTHER in front of the value entered. In other words all values not on the list will be prefixed by 'OTHER'. E.G. if 'Surgery' is entered, I would like the value 'OTHER : Surgery' to be stored in the Treatment field of the table.
This will help in the generation of statistics from the table.
This might be a silly question - I appologize in advance if it is. - let me know! :) I have searched a bit and found nothing quite along the same lines.
I have realized that I only use Combo boxes, avoiding List boxes entirely. I had some early difficulties with List Boxes that I can't recall now, yet a lot of my combo boxes are limited to list. Most posts I read on this forum seem to mention Combo rather than List boxes (I may have a bias here?).
It looks like a combo box that is limited to list is pretty much a list box. If this is right, is there ever any advantage to using a list box that I am missing out on?
From Access help: List boxes: What they are and how they work In many cases, it's quicker and easier to select a value from a list than to remember a value to type. A list of choices also helps to ensure that the value that's entered in a field is correct.
The list in a list box consists of rows of data. In a form, a list box can have one or more columns, which can appear with or without headings. If a multiple-column list box is bound, Microsoft Access stores the values from one of the columns. In a data access page, a list box has one column without a heading.
You can use an unbound list box to store a value that you can use with another control. For example, you could use an unbound list box to limit the values in another list box or in a custom dialog box. You could also use an unbound list box to find a record based on the value you select in the list box.
On a form, you can use a combo box instead of a list box; it takes up less room, and you can type new values in it, as well as select values from a list. Learn about combo boxes on forms.
On a data access page, you can use a drop-down list box instead of a list box; it takes up less room; however, you can't type new values in it. Learn about drop down list boxes on data access pages.
Combo boxes: What they are and how they work In many cases, it's quicker and easier to select a value from a list than to remember a value to type. With a combo box, you can do either without using too much space on a form. A combo box is like a text box and a list box combined.
When you enter text or select a value in a combo box, if the combo box is bound, the entered or selected value is inserted into the field that the combo box is bound to.
The list in a combo box consists of rows of data. Rows can have one or more columns, which can appear with or without headings. If a multiple-column combo box is bound, Microsoft Access stores the values from one of the columns.
You can use an unbound combo box to store a value that you can use with another control. For example, you could use an unbound combo box to limit the values in another combo box or in a custom dialog box. You could also use an unbound combo box to find a record based on the value you select in the combo box.
Combo boxes have a LimitToList property that you can use to control whether any value can be entered in the list, or only text that matches one of the values in the list can be entered.
If you have room on your form and you want a list to appear at all times, or if you always want to limit data entries to values in the list, you might want to use a list box instead of a combo box. For more information on list boxes, click .
Is the last para from above: If you have room on your form and you want a list to appear at all times, or if you always want to limit data entries to values in the list, you might want to use a list box instead of a combo box. For more information on list boxes, click the only advantage?
