When you use a Yes/No field in your table and then want to have another field that will allow the user to list if yes...
_______________
Example that will be used in my form:
Are there any dependencies? Yes/No (they will choose from a drop down, yes or no)
If yes, please list dependencies: ??
_______________
How do you set up the if yes field in the table? I want it to be able to have it show as a list, not as one long text field with everything seperated by commas or semi-colons.
The subject is probably confusing but I'll try to explain. Setup:
Table1 FieldID = Number FieldName = Text
Table2 FieldID = Number FieldName = Text T1_ID = Number
Relationship 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. Table1:
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? Thanks Jaeden "Sifo Dyas" al'Raec Ruiner
I am using Access 2002 SP1 and have created a pivot table based on a query which works fine. However if I change the structure of the underlying query by adding another field, the field is not availabe in the Field List in the pivot table. I have used the refresh option but I understand it only applies to the data.
I am in the process of trying to merge two large databases. I have four tables that really should be one to work in the new DB. I just need to be able to print a list of fields in each table so that I can work on the new combined structure before making the changes. I know I can use the documenter but this produces a very comprehensive list which is much more than I need. Is there anyway of producing a similar list without the detail, just showing the field names.
I have a table that has records added to it using the following VBa code:
Const MyTable As String = "tblSampleSubmission" Const MyField As String = "SampleName" Dim db As DAO.Database Dim rs As DAO.Recordset Dim intCounter As Double Dim LastDub As Double Dim addString As String Set db = CurrentDb Set rs = db.OpenRecordset(MyTable) Randomize 'LastDub = Me.txtStartValue - Was only used to start the random function later in series addString = "" For intCounter = Me.TxtStartValue To Me.txtEndValue rs.AddNew rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & addString rs.Fields("SubmissionNumber") = Me.SubNum rs.Fields("CustomerID") = Me.CustomerID rs.Fields("SamplePrep") = Me.SamplePrep rs.Fields("Fusion") = Me.Fusion rs.Fields("XRF") = Me.XRF rs.Fields("LOI") = Me.LOI rs.Fields("Sizing") = Me.Sizing rs.Fields("Moisture") = Me.Moisture rs.Update addString = "" If Rnd < 0.02 Then 'LastDub = intCounter intCounter = intCounter - 1 addString = " DUP" End If Next intCounter rs.Close db.Close Set rs = Nothing Set db = Nothing
What I would hope to be able to do is add a "standard" randomly to each SubmssionNumber (each SubmissionNumber might be 1-100 records). The record I need to add should be chosen at random from a list of 6 or so options and added at the end or middle or start of the job (SubmissionNumber) is this something that is easy to do or should I just give up and add it manually?
Thanks to everyone who has helped me in the past, it is getting me up to speed quickly. Access seems to be quite popular as I have contacted 3 developers to help with my dB but they are all to busy to help me so I am going it alone.
Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.
I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!
This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.
I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.
On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:
MS Article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp)
I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.
I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.
Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.
In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.
I am using
Code: SELECT instructors.first, instructors.last, instructors.ID FROM instructors; in my Classes table.
What am I doing wrong?
I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.
I have a form based on a table and I just added 3 fields to the table. When I go to the form to add these fields, they do not show up in the field list. I've also tried creating a textbox and going to the Data Tab and choosing the data source but those 3 new fields are still not showing up. How can I go about adding these 3 fields?
I'm an Access novice. I have a query based report that is based on several tables. All tables are joined by the same field "customer ID," but 1 table is not available under "Add Existing Fields." I cannot figure out why that table isn't available, but I need to add a field.
I want a List Box on a Form that takes its available drop-down values from tblDiscipline and puts the value selected into the Discipline Field of tblEquipment.
The problem is I set the Control Source of the List Box to tblDiscipline (so the List Box can use these values for the drop-down selections) and when I make a selection it tries to put it back into tblDiscipline instead of tblEquipment, because I can't find anywhere to set the table that the result should be Bound to.
How can I set up a control, such as a List Box, so that it gets its values from one table but returns them to another?
I'm trying to create a combo box that takes its values from a field in a table, the thing is that this field has duplicates because:
* The table is a qualification look up table * It has 3 fields: QualificationID (autonumber), Qualification(e.g. Bachelor), Programme (e.g. Science) * E.g of duplicate values (this is not a problem as it is necessary) Bachelor of Science, Diploma of Science, Certificate of Science
What I'm trying to do is create 2 single-columned combo boxes that are a parameter for a query and it's working except the combo boxes show duplicated valuesIs there a way to get the query that is the row source for the combo box to eliminate duplicates?
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
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.
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) Me.lstlocationsperproject.Requery
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 ?
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 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.
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim ClaimID ClaimNumber fkEmpID
tblEmployee EmpID EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I had two fields in my table which I previously used in the form. I deleted the fields from the form itself but the fields are still listed under Field List and not even under any table...
Delete buttons doesn't work on it, and neither does backspace?
How do I remove it? Right click only gives me two options:
"Add Field to View" or "Edit Record Source" which returns an error saying this command cannot be used.
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.
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Situation: 3 tables. Manufacturers, Countries & Provinces/States. 1 Form for data entry in Manufacturers.
Countries table contains ID, Country and CountryCode fields. i.e. 47, Great Britain & GB
Provinces/States table contains ID, Province/State and CountryCode. as in 1, Alaska and US.
In the form the country is easily selected from a list refering directly to the Countries table.
Problem: How to make a list in the form from which the user can simply select the province for the country that has previously been selected. And not a list with all teh provinces and states from every country in the world. (This would be a really really long list...)
