"Natural" Name Format In Combo List
Jul 6, 2005
Hi - I'm very new and totally self-taught = ignorant. Sorry.
I have a table called Contacts that includes the following name fields:
Prefix
First_Name
MI
Last_Name
Suffix
There is also a form called Contacts, where a user can input data on new people. On this form is a field called Full Name that automatically takes the info filled into the above fields and generates a "natural name format" so that the user enters:
Prefix: Dr.
First_Name: Long
MI: J.
Last_Name: Silver
Suffix: phD
and Full Name displays "Dr. Long J. Silver, phD"
To handle situations where some fields may not have data I use the following code of nested IIf's:
IIf(IsNull[Prefix],
IIF(IsNull[MI],
IIf(IsNull[Suffix],
[First_Name]+" "+[Last_Name],
[First_Name]+" "+[Last_Name]+" "+[Suffix]),
IIf(IsNull[Suffix],
[First_Name]+" "+[MI]+" "+[Last_Name],
[First_Name]+" "+[MI]+" "+[Last_Name]+" "+[Suffix]))
,IIF(IsNull[MI],
IIf(IsNull[Suffix],
[Prefix]+" "+[First_Name]+" "+[Last_Name],
[Prefix]+" "+[First_Name]+" "+[Last_Name]+" "+[Suffix]),
IIf(IsNull[Suffix],
[Prefix]+" "+[First_Name]+" "+[MI]+" "+[Last_Name],
[Prefix]+" "+[First_Name]+" "+[MI]+" "+[Last_Name]+" "+[Suffix])))
PROBLEM:
I also have a form called Collected at Time of Visit. On it is a combo box where the user can select a returning Patient, from the table Contacts, to record a new financial transaction. The combo box is working fine, but I want to FORMAT the appearance of the names so they look like the above "Dr. Long J. Silver, phD", rather than showing the columns, as is usual.
I have tried variations on the above code, but get errors, either that I'm referring to fields that don't exist or to objects that don't exist, even when I use the wizard to navigate to the correct fields in the correct table.
Can anyone help me get this to work?
I might as well also mention, the form Contacts also has a pick list Contact_Type that includes Patient, Doctor, Organization, etc as options. I'd like to combo box in question to FILTER so it only displays Patients.
Help with that, too? I'll be very appreciative!
Thanks in advance!
View Replies
ADVERTISEMENT
Jan 26, 2006
I have an unbound listbox whose row source is a query. The listbox displays receipt info for orders. Each order could have more than one receipt.
Order Receipt No
100 1
100 2
100 3
The list box works how it should. I have a question on formatting it. I would like the information to be more easily read. I am wondering if it's possible to insert a line in the listbox after every unique order no, or number unique orders or change font color etc. Is there anyway to do any of these. I have searched with no luck.
View 2 Replies
View Related
Jun 28, 2013
I have a table which contains list of data like,
10025.325
100.245
1245.8464
12546.356
etc.. etc.....
Now, I need a data which contains numbers 5 digits before decimal point & 3 digits after decimal point.
like xxxxx.xxx
View 5 Replies
View Related
Nov 21, 2005
I have a problem with one of my combo box. The format setting of the combo box doesn't change anything to the way I see my data. The row source is based on a query of which the 'data sheet view' shows my field with three decimal number. Whatever setting I change on my combo boxe parameter, the data in the list are always shown with 2 decimals. What can i do to view my data with 3 decimals?
View 13 Replies
View Related
Jun 6, 2006
I have a combo box with three columns, something like:
Category Item ID
AOI broken 27
AOI no staff 32
SMT fat 1
SMT lazy 2
SMT short 3
TP awake 7
TP bashful 42
TP candy 45
TP dopey 8
[ID] is my bound column. The list is actually about 90 records long; [Category] is just used to help the user narrow down the list to the correct Item. I want to keep the list sorted by [Category], [Item].
After the user makes a choice, what I want to stay visible on-screen, as a confirmation of the choice that the user made, is [Item]. However, what shows on the screen is [Category]. Is there any way around this? It seems like what shows on the screen is always just the contents of the first column.
I realize I could do this by using a separate combo box for [Category], to first limit the contents of [Item]. But my form is a continuous form, and this seems likely to be very confusing to the user, because when you change [Category] in one record, it looks like it changes all of them, and the previous [Item] selections look like they disappear.
Thanks for any suggestions
View 4 Replies
View Related
May 22, 2006
Hi there, I hope someone will take pity on me and help me as I am new to databases so here goes.
I have combo boxes in a form that the user would like to be able to press the Enter key and just go to a new line, not go to the next field. This is so they can have say 4 sentences but have each one on a new line in the combo box. Hope this makes sense.
Thanks
View 2 Replies
View Related
Aug 23, 2007
I have a query that I want the Now() function in the year only format and in an expression combine with the auto number one extra field to get the following:date YYYY-------Autonumber---------color----------exp1: ---2007--------------23--------------green-------2007-23-greenThanks for the help
View 1 Replies
View Related
Mar 19, 2015
I have a listbox on a form with several fields, 3 of which are currency fields.When I filter the listbox using afterupdate from a combobox on the same form, the listbox loses the currency format on the fields.
Code:
cboprop_AfterUpdate()
Dim ListFilter As String
ListFilter = "SELECT [qry_inv_form].[inv_no], [qry_inv_form].[prop_ref], [qry_inv_form].[inv_date2], [qry_inv_form].[inv_desc_type], [qry_inv_form].[inv_net], [qry_inv_form].[inv_vat], [qry_inv_form].[inv_total], [qry_inv_form].[year_month], [qry_inv_form].[month_text] " & _
"FROM qry_inv_form " & _
"WHERE [qry_inv_form].[prop_ref] = '" & Me.cboprop & "'"
Me.inv.RowSource = ListFilter
Me.inv.requery
I tried wrapping the fields in Format(fieldname, "Currency") but that came back with a syntax error.
View 2 Replies
View Related
Feb 6, 2005
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??
View 3 Replies
View Related
Feb 14, 2006
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.
Ady:)
View 1 Replies
View Related
Oct 27, 2006
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 hope this makes sence.
View 2 Replies
View Related
Mar 1, 2006
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:
View 3 Replies
View Related
Apr 4, 2006
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
View 2 Replies
View Related
May 20, 2006
I hope someone will be able to help....
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.
Pls help...
Thanks in advance.
jan :confused:
View 8 Replies
View Related
Jun 22, 2006
Hi everyone,
I have a form with a field named TREATMENT with the following options :-
1 Cryotherapy
2 Radiotherapy
3 Chemotherapy
4 None
5 Other (specify)
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.
Any suggestions??
Many thanks in advance.
Will
View 3 Replies
View Related
Sep 9, 2006
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?
View 3 Replies
View Related
Sep 26, 2006
ok i have three tables..
Booking_Table
Booking_ID
Job_Date
Company_ID
Booker
Passenger
Company_Table
Company_ID
Company_Name
Address
Phone_No
Company_Staff_Table
Company_Staff_ID
Company_ID
Full_Name
Phone_No
each booking will only ever involve one company at a time
company 'A' will have several staff
person 'A' from company 'A' may book a job for themselves
person 'A' from company 'A' may also book a job for person 'B' from company 'A'
i have created a booking form
i have added a combo list displaying all the company names..(control="comp_combo")
i have also added a combo list displaying all staff names from all companies (control="staff_combo")
when the user selects company 'A' from the first combo.. i would like the staff combo to update and only display staff members for that company
how do i do this.
View 1 Replies
View Related
Dec 8, 2006
Hello,
I'm a beginner in the Access world.. had a two day crash course.
I am trying to populate my list box when I make a select in my combo box.
eg: Combo box-select vendor, which then would populate my Product Name box.
Can someone take a look at this datebase and give me some advice? I don't have a huge understanding of code..so please bare with me.
Thanks
:o
View 5 Replies
View Related
Dec 4, 2004
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.
View 4 Replies
View Related
Aug 21, 2004
Hi all
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 appreciate any help.
View 1 Replies
View Related
Aug 20, 2013
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....
View 1 Replies
View Related
Jun 30, 2005
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.
View 2 Replies
View Related
Dec 6, 2006
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
See attached db
thanks
View 2 Replies
View Related
May 16, 2007
Hi,
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?
Thank you
View 6 Replies
View Related
May 1, 2005
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!
View 1 Replies
View Related
Oct 19, 2005
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.
View 12 Replies
View Related
