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
Set rst = Nothing
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.
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??
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?
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,
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
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.
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.
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?
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.
How to produce dates at specific intervals
February 6 2004
and a time frame like
Id like to auto gen a list like
Feb 20 2004
March 5 2004
any idea on the coding for that?
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;
I seem to recall being able to tell an autonumber field what number to start with. Can I do this?
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
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"W:923COMMONTEID FilesInfoworksSuncor Master DataCERTSCERTSData.mdb", acTable, (*OPTION TO SELECT WHICH TABLE I WANT TO IMPORT*), _
"(*IMPORT AS THE SAME FILE NAME*)"
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?
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
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
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?
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
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
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???
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!
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.
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
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.
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.
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,
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.
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!
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,
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.
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.
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.
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:
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.
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 & """);"
Response = DATA_ERRADDED
Response = DATA_ERRDISPLAY