Pick List

Aug 25, 2005

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

Feb 26, 2008

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 1 Replies View Related

Multi Pick List Within A Table

Mar 6, 2006

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 1 Replies View Related

Forms :: Code To Pick A Field From A Record Selected Via List

Dec 11, 2014

I have a query with the following structure;

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.

View 4 Replies View Related

Queries :: Produce Query Using Combo / Pick List Field Held In Main Table

Jun 17, 2014

My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).

I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.

So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).

Which all works fine

However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.

But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.

I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.

View 1 Replies View Related

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

Aug 22, 2006

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 1 Replies View Related

Query Doesnt Pick Up All Records, Please Help!

Feb 19, 2007

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 2 Replies View Related

Randomly Pick Records For Each User

Feb 23, 2007

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 4 Replies View Related

Pick Latest Record From Query?

Dec 16, 2005

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 2 Replies View Related

Queries :: Pick Up Data For Last Two Months

Mar 3, 2015

I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??

CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)

View 2 Replies View Related

Queries :: Building A Query To Pick Between Set Months / Days But Different

May 4, 2014

I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).

As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.

View 1 Replies View Related

Modules & VBA :: Pick Field Based On Number Of Clicks

Apr 23, 2015

I'm incrementing a counter every time I click on a combobox by using the on click event and this code:

POcount = POcount + 1

I have 5 fields on my form: GWS1, GWS2, GWS3, GWS4 and GWS5.I want to store the value of the combobox when clicked into the correct GWS field. For example: If I click the combobox 3 times it would store the value of the combobox after the 1st click in GWS1, after the 2nd click in GWS2 after the 3rd click in GWS3.I tried

me."GWS & POcount" = combobox1

but that's invalid syntax.

View 4 Replies View Related

Pick Listbox Item To Open Specific Record?

May 19, 2012

i have a list box that fills based on the following code.....

Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL

There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.

DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"

The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....

View 1 Replies View Related

Queries :: Criteria To Pick Range Of Date Dependent On Variable?

May 8, 2013

I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.

I have successfully used the following but the records returned are only the specific date choosen not that date and all prior

Like Nz(([TempVars]![tvcldate]),"*")

So I have tried the following but it doesn't work.

Like Nz(<=([TempVars]![tvcldate]),"*")

View 10 Replies View Related

Modules & VBA :: Pick ArrayID From A Form And Send Corresponding X And Y Values To Excel

Jun 13, 2013

I have hit a roadblock trying to send data to excel. I have created a table of Arrays where each record has an ArrayID, a record ID, an X unit and a Y unit. I want to be able to pick an ArrayID from a form and send the corresponding X and Y values to excel as part of a Linest calculation.

View 3 Replies View Related

Queries :: Allow User To Pick A Record From Continuous Form And Edit It In A New Window

Apr 3, 2014

I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.


Private Sub lblEdit_Click()
DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog
End Sub

View 6 Replies View Related

General :: Randomly Pick Record From Table And Display Results In Two Text Boxes?

Sep 1, 2013

I have one table (500 Club) with two fields (ID) (Name) i would like to randomly pick a record from the table and display the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.

View 6 Replies View Related

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

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

Dec 14, 2006

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 1 Replies View Related

Reports :: Exclude Individuals From False List If They Are In True List

Jul 15, 2013

I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.

Implant Period
>>Exclude individual?
>>>Individual ID

I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?

View 4 Replies View Related

Use Multi-select List Box To Filter A Report With Two List Boxes

Nov 20, 2013

Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items

[Code] .....

View 14 Replies View Related

Created List Box With One Column - Display All Characters Of List Item

Jan 2, 2014

I have created listbox with one column (contains one column only), now i would like to display all the characters of list item (want scroll bar to listbox).

How do i display all text of list item, I have already fixed Column Widths to max length (22";0.1"). However when scrolling to right, it is going to next blank column of list box, which is created only to change Column Widths property.

View 12 Replies View Related

Queries :: Return List Of Records From Original List

Apr 23, 2013

I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.

I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.


"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"

my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".

the sheet has the keyword in each row and next to the column:
where "list" is the external data.

View 3 Replies View Related

How To Transfer Multiple Select Item In List Box To Another List Box

Jun 2, 2012

How To Transfer MultipleSelect Item In Listbox to another Listbox ?

View 7 Replies View Related

Select From List BOX Of List Of Choices And Store This Into A Table

Aug 21, 2013

I created a form and created on it a list box which is a query that grabs certain number of fields from different tables. I would like the user to select from this list box of a choice and then store their selection into a table.This list box has three fields, but it needs to store the id rather than the item, the user would see the name of the item but the id of the item would be store into another table, called bid. It store all these three fields when a user selection one of the item from the list.

View 13 Replies View Related

List Table, Limiting Field List..

Oct 14, 2006


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 1 Replies View Related

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