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?
I seem to be missing an understanding of the difference between a project and a database. I am using a .mdb and writing code with success until I try to use the recordset feature which I took from my "VBA programming" book.
My code is simple...
Dim db As Database Dim rec As Recordset Dim strSQL As String Dim intCount As Integer Dim listEmails As String
strSQL = "SELECT Email from Member"
db = CurrentDb() rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rec.EOF listEmails = listEmails & Chr$(10) & rec(Email) Loop
rec.Close
MsgBox listEmails
But when I run it I get a compile error because in line 1 the term 'database' is only a class in "a project".
Can anyone explain the issue and better still the solution.
I know this is probably a silly question but I had a number of checkboxes in a table and had set their default value to be 0 however I did not do this in the table properties of these fields.
I can understand some things like formatting dates in a form but storing it differently in the table but why did access not automatically input 0s in all the fields where I did not check the box?
I have made a cross-tab query which works fine. I also have made a report which is based on this cross-tab query. Due to nature of the cross-tab query the Value column(s) is dynamic. The report I made is based on all available data types (columns). The report works if all data type is available but the report fails if some of the data types are not available (i.e columns are missing due to not having any value or data). I hope I am explaining this correctly,
Is there a way I can use expressions in the report to place a conditional clause that if the Control Source doesn't exist place a Null or 0 in the report or in its place.
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)
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.
I have 2 tables. One is Quotation and the other one is Contacts.
In Quotation Table i use a combo box to select a Customer from Customer Table. Also in Quotation Table a have a combo box (TO) to select a name from Contacts Table. The problem is that i want to select contacts only related to Customers and not view the complete list.
For example Vodafone Company has several contacts. When i press Vodafone in my quotation form then when i go to (To) combo box i would like to view only the related contacts.
I tried the SELECT statement WHERE but i can to figure out how i will limit the list depending the Customer Name? Please help.
I'm trying to use a combo box to change the values in a list box. For instance. Lets say I have a combo that has 3 values; sports, cars and beer. If the user selects sports from the combo, I'd like a list a sports to appear in a list box that I have stored in a data table. If the user selects cars, I'd like values from a data table to appear in the same list box. And so on. I'd like the combo to relate to the list box. Any ideas??
I am very new to database design and application building. I work as general office support for a very small non profit. I have been tasked with developing a database to track HR and timesheet data and turning that into benefit reports. Actually, I was asked to create some spreadsheets to do this - but I strongly feel that access is a better solution than excel for this as we have around 150 individuals to track between all staff (part time, temporary, fulltime etc + regular volunteers).
So, the wall that I've hit that brings me to actually post instead of just search....
Some of our employees have more than one position, and in turn more than one rate of pay per month.
I created a compensation table that has the employee ID (from the employee table) and fields for position title, rate of pay and if that is per month or hour
I have a main timesheet table that assigns employee and month; and a details table that takes the timesheet ID and compensation ID with fund codes and hours worked.
My problem is that I don't know how to limit the combo box on the details sheet to just the positions assigned to the employee ID that matches the timesheet being worked with.
I tried all the design view access query options I could think of to limit the records in the combo and I always end up with all positions/rates of pay listed with no regard to employee ID....
Ok what I have is a List box with four rows. (Name, Assignment, Location, Description) These are all labeled with a frame at the top that when that frame is selected that row is put into alphabetical order. The next thing I have is a combo box which also sorts the List box by "major location". The combo box has the following (All, Fort Mills, Corporate, Pequot Lakes, Savage, Retail) When one of those is selected the location row then shows only one of the following locations and the others are removed. Here is where the problem comes into place. I want to be able to select a major location, then be able to sort with the frame. When I select a major location, and then click on the frame, lets say "Name" the list resets back to everything from that table and not just that certain "Major Location" that I have selected. I need to sort whatever is selected in that "Major Location" by whatever is selected in that frame. thanks for any help, I know its a hard one.
Does anyone know of a way to allow a user to add further info to another field on the same record that's created by a combo box that is using the Not In List method?
Dim strsql As String Dim i As Integer Dim Msg As String
'Exit this sub if the combo box is cleared If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list of Callers names." & vbCr & vbCr Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Caller...") If i = vbYes Then strsql = "Insert Into tblCallersName([CallersName]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError Response = acDataErrAdded Else Response = acDataErrContinue End If
I need to log a persons name & phone number without having to re enter it again the next time.
To log the name I use a combo box that lists all the previously added names & it also allows you to add further details if the name does not already exist
I now need to work out how to also add the phone number without having to manually go into the names table to add it in.
If anyone understands what I'm talking about then you're halfway there :D
I have a combo box in which it's value changes according to user input. It is working but it is annoying to have to click the combo box for it's list to refresh. How to make it seemless?
on my form i have two unbound combo boxes and an unbound listbox. when i delete or go to a new record these still contain the data from the previous record. what is the code to clear them? have looked through the propeties and there is no useful 'clear' event that i can see. :confused: cheers!
I have a number of combo boxes linked to respective individual tables, for example tool combo look up the tool table which is composed of ToolID and Tool Type.
I want to be able to add additional tools directly into the combo box on the form when I come across a tool type that isn't in there already. However, my current combo tells me that I can't as I am trying to enter an item that isn't in the list.
So, if I then change the option on the combo on 'Limit to List' to No from Yes, it then gives me an error about column widths that need changing.
I don't want to do that but I do want to enter directly - can somebody point me in the right direction please.
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.
I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.
First Approach: I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria. This is the code I used: __________________________________________________ _______________ Private Sub Preview_Click()
End Sub __________________________________________________ _______________ But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?
Second Approach: On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.
This is the code I followed: __________________________________________________ _______________ Private Sub btnClear_Click() Dim intIndex As Integer Me.cmbCountry = 0
End Sub
Private Sub btnsearch_Click() Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter
Me.frmqrybyCountry1.Requery
End Sub
Private Function BuildFilter() As Variant Dim varWhere As Variant
varWhere = Null ' Main filter
'Check if there is a filter to return... If IsNull(varWhere) Then varWhere = "" Else varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter If Right(varWhere, 5) = " AND " Then varWhere = Left(varWhere, Len(varWhere) - 5) End If End If
BuildFilter = varWhere
End Function __________________________________________________ _______________ After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.
Please help me, I would really want to figure this problem out. Thank you so much.
:mad: Hi all I cannot get it to work, I have a combo box that on double click should open a form to add the item with all the relevant details, when ài double click on th combo box it does open the form but not in a add mode, it actually opens it on the first record of the DB, th vb instruction is as following:
Private Sub CP_DblClick(Cancel As Integer) On Error GoTo Err_CP_DblClick Dim ImgEventTypeID As Long
If IsNull(Me![CP]) Then Me![CP].Text = "" Else IngEventTypeID = Me![CP] Me![CP] = Null End If DoCmd.OpenForm "Crew Members NV", , , , , acDialog, "GoToNew" Me![CP].Requery If IngEventTypeID <> 0 Then Me![CP] = IngEventTypeID
Exit_CP_DblClick: Exit Sub
Err_CP_DblClick: MsgBox Err.Description Resume Exit_CP_DblClick End Sub