Pick List

I'm trying to get a form to display a pick list so I can choose a staff name from the whole database and the form will go to that record. In the after update of my combobox I have:

Private Sub STAFF_ID_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "LNAME='" & STAFF_ID.Column(1) & "'"
If rst.NoMatch Then
MsgBox "The selected record can not be displayed because it is filtered out. " _
& "To display this record, you must first turn off record filtering.", _
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
and this works OK except when I try to make it pick on 2 columns I can't get it to work. E.g. if I select Joe Smith it just goes to the first Smith. I thought if I used AND on the findfirst line and put in the first name it would work. What am I doing wrong?

I also need to make it so if I type a letter the list will go to that part of the list but I'm not sure how to do this.

View Replies


SQL Failing To Pick Up List Box Value

Hi all

I have a form which is used for entering holiday requests.

It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??

Thanks all


View Replies View Related

Multi Pick List Within A Table

I have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table):
But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc

Can this be done in a table within Access?

Thank You,

View Replies View Related

Pick List For Report - Version: 2003 (11.0)

Have a database that stores the minutes of meetings in a table called "tblMinutesOfMeeting".
I have a query called "qryMinutesOfMeeting" that has a date parameter so the user can select a specific date for the particular recorded minutes interested in when the "rptMinutesOfMeeting" is run. This depends on the user to remember the specific date that the minutes were recorded and can be a hassle at times because of the random dates of when some of the meetings are recorded. It would be nice and more user friendly when the report ran that it would let the user pick a specific date from a list of "minutes" sorted by dates and pull that one up on the report screen.

Any suggestions will be greatly appreciated,


View Replies View Related

Pick From The Combo List - Version: Any Version

You use a combo box to display a list of items from which a user can select one or more. A combo box looks just like a text box, except that it has a down arrow at its right border. To see the combo list, click that arrow to open the control's drop-down list. When you select an item, Access displays that item in the control's text box component. However, you don't have to choose an item from the list--you can type it yourself in the text box component.

To force users to choose from the list instead of entering data themselves, set the combo box's Limit To List property to Yes. The default is No--this setting accepts an entry that's not on the list.

If you choose the No setting, the control accepts entries that aren't in the list; a setting of Yes limits users to the items in the control's list.

If you attempt to enter an item that isn't in the list when you've set the property to Yes, Access displays an error message warning that you can't enter a non-list item. When you receive this message, click OK to clear the message, and then press Esc to delete the offending entry. The error message can be a nuisance, and, fortunately, you can suppress it.

To do so, attach the following code to the control's NotInList property:

Private Sub comboboxname_NotInList(NewData As String, Response As Integer)

Response = acDataErrContinue

End Sub

The next time you enter an item that's not on the list, Access still won't accept the value, but it won't display the error message, either. That's because the acDataErrContinue (intrinsic) constant tells Access to ignore the error and continue.

Edited by: GlenKruger on Mon Apr 17 13:47:48 EDT 2006.

View Replies View Related

Select Where Not In Doesn't Seem To Pick Up Data

I can see which records are missing from the table I want to import to but this query picks up no records:
SELECT [Field1] FROM Table_Linked WHERE [Field1] NOT IN (SELECT [Field1] FROM TableA);

Yet, when I run them separately, all fields are displayed.

Am I doing something wrong with the NOT IN part of the sttement that access doesn't like? If you run this in SQL server it works fine.

View Replies View Related

Query Doesnt Pick Up All Records, Please Help!

I have a query which picks picks up all records from a table. One field however seems to randomly not pick up data from certain records even though the data is in the table.

This has been puzzling me for some time now, does anyone have any ideas / suggestions?

View Replies View Related

Randomly Pick Records For Each User

I have a query with a list of Customer SSNs and Claim Rep, I want to randomly pick 5 Customer SSNs for each of the Claim Rep.
I can't figure out on how to do that, I was able to create an SQL code to just randomly pick the customer SSN.
I don't know anything about Visual Basic codes, but if that is the only solution, please go ahead post it.
Thank you very much in advanced.

View Replies View Related

Pick Dates At Two Week Intervals


How to produce dates at specific intervals


user inputs

February 6 2004

and a time frame like

26 dates

Id like to auto gen a list like

Feb 20 2004
March 5 2004

etc etc

any idea on the coding for that?

View Replies View Related

Pick Latest Record From Query?

I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?

Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.

SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;

View Replies View Related

Pick Autonumber Start - Version: 2003 (11.0)

Good morning,

I seem to recall being able to tell an autonumber field what number to start with. Can I do this?



View Replies View Related

Drop Down Box To Pick Table From Outside DB - Version: 2003 (11.0)

Hi, just wondering what the code would be I have part of it but I'm not sure what the code is to make a box popup and ask you which table you want(the reason I want this is to avoid the navigating to the W drive and through all the folders etc of course I have a short cut but still this makes it fool proof for someone else to run it)

if there are any other options I am completely open to them

Function OpenDatabaseMsgboxTest()
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"W:923COMMONTEID FilesInfoworksSuncor Master DataCERTSCERTSData.mdb", acTable, (*OPTION TO SELECT WHICH TABLE I WANT TO IMPORT*), _
End Function

View Replies View Related

Combo Boxes No Longer Show Any Data To Pick - Version: 2003 (11.0)

Hi everyone. 99% of our users are no longer able to view the contents of a combo box on all of our Access databases. I can see the contents so I can't duplicate the error. However I was able to see it in action. When you click on the arrow of the combo box, it's blank. There is a long list there with nothing in it. When I design the form and look at the query that is the data source, it works fine. Running the query from the combo boxes properties returns all the data that should be in the combo box...weird huh? There are no errors, no broken or missing references...any ideas?



View Replies View Related

Date Pick Or Calendar Control On TxtDate Search - Version: 2003 (11.0)

Greetings All,

I have been looking over the site for Calendar contorl solution or a form Calendar. I am looking for a calendar that I can use like a search cbobox on a form to pull a specific date record. I have hundreds of dated records and the cbobox is getting quite long to scroll through.

I am looking for something like a Calendar Control that I could pick the date from but I would like a similar function as the search cbobox that shows only dates in the recordset. I am thinking the calendar would show the dates in the recordset as bold, colored etc or some conditional format along with how many records in that date set.

Anyone could point me in the right direction would be much appreciated. Thanks

View Replies View Related

Nned To Pick Your Brains About Tier Inputs And Structure - Version: Any Version

Hi all,

Let me start with a brief overview of what I am trying to do.
I am working on a deal pricing database to use in our firm and am coming up a bit short
about some of the basic logic/functionality.

Each deal can have 2 sections , for each section I need the following.
Country, City,Type,SubType , Volumes , Price

for each different combination in the first 4 (country to subtype)
there is a different cost associated with it ,
for the price you can have a flat price or a tiered price,
when a saleperson goes into the deal form they select from combo boxes the
country, city, type and subtype .They can then enter a volume and select to use either
a flat price (volume * price) or a tiered price ( which will have 4 tiers) .
Once the sales person has entered in all the details , bear in mind he can add
many country, city, etc.. combinations to a deal he submits it to the pricing
team for approval .
The pricing team will open the same form and review the pricing the saleperson has entered.
The breakdown of the deal (country, city, etc) will be in a listbox so I was thinking the pricing team
can double click on the line in the list box and the details will populate the text boxes.

my problem comes in because I don't know how to get the calculation in one line in the list box and secondly
the table structure I need for the tiered pricing .
Should I have 4 lines in my table for a tiered deal segment then group it and calculate it in a query?
or should I have a seperate table for just the price and have a high tier and low tier column even for a flat price?
(just make the high tier ridiculously high)

I know this probably doesn't make a lot of sense so please let me know if you need anymore detail.

Thanks for taking a look

View Replies View Related

Code To Generate A List Of Access Reports In A List Box - Version: 2002 (10.0) XP

For some reason, the attached code does not work. It is supposed to generate a list of the AccessObjects.Reports in a List Box. I have been able to debug.print the first Case and the reports are being generated in the code. However, when I open the form, the list box it empty. In the List Box Properties, I have set RowSourceType to ListReports. Any ideas as to what is wrong?


View Replies View Related

Base Query Results On A List Of Values In A List Box ? - Version: 2003 (11.0)

I want to allow my users to generate a report based on a list of Values they enter [StudentID]. Is this done with a list box on a form ? or can I use a Lookup field on a form or unbound text box's on a form and have the query reference them. I have not done this before and I do not know the best approach

View Replies View Related

Create A Randomise List From A Table And Save The List For Each Month

Hi All a newbie here so any help will be appreciated,

sorry for the long post but trying to give you all the information you might need.

I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.

But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.

So if this will work in access they can all come for 9am

I don't mind creating a new database and adding the additional information, if that's what it would take.

My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.

Database details (Microsoft Access 2002 version)

Table Name = details
Field name = ID (auto-generated)
Field name = FirstName (text)
Field name = Surname (text)

If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.

There will be approximately 90 to 125 names.

Thank you in advance for all your help in this matter


View Replies View Related

List In Main Form, List In Subform - Version: 2002 (10.0) XP

Hi all,

I need help. I have a main form and a subform. I tried having a list of records on the main form whereas if you click on each record, the list in the subform gets displayed. Access is not letting me have a list of records in the main form. It tels me that I can only have one record at a timeon the main form and just navigate to the next record to see the detailed list in the subform.

Does anyone know how to get around this???

Please help.

Thank you.


View Replies View Related

Data Entry Using A List Box & Value List - Version: 2003 (11.0)


I have a simple table that uses several list boxes with a value list for data entry, however I ONLY want the user to be able to chose from what's in the list box (from the value list) and nothing else. As it stands now, they can key whatever they'd like (a number that is.) I don't know how to restrict the data entry to ONLY the list box. Any suggestions would be great. Thanks!

View Replies View Related

List Box Value List Question (Tupples) - Version: 2003 (11.0)

Hi all,

I am trying to find out what makes a value list 2D and what makes it 1D.

If you use the wizard to set up a list box of values, you can specify how many columns the data will have in it and it will then act accordingly. So you can have a list of tupples. THe problem is, if you edit this list in any way, it seems to break the magic that made it a 2D list in the first place and it reverts to a 1D list of values. I can see no difference in either the lists or the properties of a list which has been generated through the wizard with 2 columns...and a list with 1 column (every value).

I have tried to find out the secret delimeter but have failed (it is not vbcrlf). Does anybody know how Access knows that a listbox is comprised of tupples and not of singles values? I have manipulated the ColumnCount and BoundColumn fields to no avail.

View Replies View Related

List Table, Limiting Field List..


The subject is probably confusing but I'll try to explain.

FieldID = Number
FieldName = Text

FieldID = Number
FieldName = Text
T1_ID = Number

Table1.FieldID 1-> * Table2.T1_ID (one to many)

Now, I'm trying to create a 3rd table that has field populated by Table1 and a field populated by Table2 but I want to limit the field populated by table2 by what is selected in the field populated by table1.




Table1Field = Number, ComboBox-Source: Table1, BoundColumn=1
Table2Field = Number, ComboBox-Source: Table2, BoundColumn=1

If Table1Field = 1st then
Table2Field Will only allow January, February, or March as selections, since ONLY they are a member of the "1sr" group via the table1 list.

Is this possible, or do i have to do some run-time checking or something?
Jaeden "Sifo Dyas" al'Raec Ruiner

View Replies View Related

Preventing Combo List Opening On Not In List


I have a combo box to enter a staff name.

For most people the staff name will be entered from a sheet using a bar code reader, but I still want the combo box to be there in case the reader breaks down.

I have the limit to list set to yes, and am trying to write some error code in case they read a different type of barcode.

I am catching and processing the error OK, and ask them to scan the staff name if they got it wrong.

My problem is that after they click OK to enter the correct name, the combo box shows its list of staff members to be selected from.

I don't want this to happen, I just want to see the empty box for them to try again.

Does anyone know how to stop the list opening by default.

This field is the first to be filled in for the record, (all others are hidden at this point) so I could just throw throw the record away and start again. Can anyone tell me how to do this.



View Replies View Related

List Of Manufacturers & A List Of Their Distributors

I have a list of manufacturers & a list of their distributors. The relation is many to many. How to build one or two tables & to select correctly the primary key.


View Replies View Related

List Box Values Updated From Another List Box Value


I am having trouble getting one list box (Field Office) to dictate
what values are coming up in another list box (Employees). I am
trying to do this on a Data Access page. Can anyone help me here?Thanks,


View Replies View Related

NEW List Box To List Box NEW - Version: 2000 (9.0)

This is what I believe to be an "improved" list box to list box procedure. 1) It only uses one table. 2) It uses a hidden text box on the form to hold the EmpID values of the ones to be put in the "To" box. 3) The entries in each box are alphabetical by last name. 4) The appropriate buttons will turn off when they are no longer usable.

I rewrote the code so that those of you who do not have the AddItem listbox method available can still use it. However, I did leave those lines in as comments, so if you want, you can activate those lines. You'll then need to REM the lines setting the RowSource properties of the list boxes.

EDIT: Replaced attachment per Frank's request. Previous file had a bug. If you downloaded the sample, please replace it with the current file.

Edited by: DougY on Thu Jan 4 13:15:59 EST 2007.

View Replies View Related

List Of Access-keywords And List Of Macro Keywords - Version: 2003 (11.0)

Hi there,

Excel comes with a list of keywords in the file VBALIST.XLS (at least in the German version). I can find all VBA-keywords and all EXCEL-VBA-keywords in that list.

Is there anything similar for Access? Or where can I find a keywordlist? Maybe even as a doc or xls file?

And the second thing would be: Is there any list for macro keywords (or functions)?

I'd rather not type all of the keywords down, could take me a while

Thanks in advance!

View Replies View Related

Field List In List Box

I have a list box which contains a Field list of all the fields in a query in my database. Is there any way in which I can exclude a column from this list? Each record has an AutoID which I don't want the user to see, but this appears as a field.

Thanks in advance,


View Replies View Related

List Box Help

I have a list box with names of individuals. What I would like to do is when one of the names is selected it will take it off the list and do this for all the names in the list until all are removed then will reset and have the names avaialble once again. Is this possible and if so how can it be done? Thanks.

View Replies View Related

To Do List

Has anyone got, or know of somewhere that has, a good example of a To Do list application for MS Access (2000 version)? I've been looking for something to use to keep track of all the daily tasks I need to perform and haven't got time to create something for myself.

View Replies View Related

List Box Help

I am having problems figuring out how to run a macro if and only if a selection is made in a separate list box. Any help would be great.

View Replies View Related

List Box Help

I am trying to resolve a problem that I have been thinking about for a while. I have a table of assets containing about 15 columns of data. One of my columns [system_registration_name] holds the data type of the PC for example:
Are for Networked

Are for Laptops

I would like to have a list box in my form that shows all of the assets. But then either with a radio button or combo box make the row source for the list box change. I could do this by manually creating queries that do it. But I would also like it so the user using the form can combine options like:
Only Laptops
Only Networked
All Assets

With other options like
In Ascending Bldg order
In Descending Bldg Order
Asc Room Number
Desc Room Number

It seems like the only way to do this is to create every single different type possible as queries and setting that as the row source via radio buttons.

Does anyone have any alternate idea's for me.

Thank You


View Replies View Related

Not In List

I'm in the process of moving over to SQL fom Access. I've created a sql db and now created a access data project i used to have a not in list procedure in my access 2000 db but it does not seem to work now i moved. Can any one give me any assitane please. the code is:

Dim db As Database
If (MsgBox("Do you want to add """ & NewData & """ to the list of subjects?", 36, "New Subject") = 6) Then

Set db = DBEngine.Workspaces(0).Databases(0)
db.Execute "INSERT INTO Subjects (Subject) VALUES (""" & NewData & """);"

End If

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved