I have a subform in continuous mode which has a list of people. I have created an unbound field which has a dlookup into a telephone number table.
What I want to happen is for each person in the list to have their telephone number displayed (not recorded - just displayed) on the form alongside their name. On a single form this works fine, but on a continuous form it displays the same phone number on all the rows (presumably the first person in the lists number).
Can anybody advise how I can get the dlookup working for each record in the continous form.
I am trying to create a query for a form which returns a 'calculated' value.
One table contains ProjectNo, SchoolType1 and SchoolType2 Second table contains SchoolTypeID and SchoolTypeName
The SchoolType1 and 2 are number values which reference the SchoolTypeID.
If I query for project number and school type 1 and 2, I get the actual names for the school types (i.e. 33.3333, elementary school, learning center). But, not all project numbers have two school types (i.e. some are just elementary schools). If I create a query for project number and [iif (SchoolType2 is null, SchoolType1, SchoolType1&", "&SchoolType2)], I get the number ID's returned instead of the names (i.e. 33.3333, 3, 6) instead of what I'm looking for which is the names. (I get number ID's returned if I do something as simple as 'SchoolType1 & " " & SchoolType2')
I hope this makes sense. How can I get it to return the school type names when I do any kind of calculation on the school types?
What I am trying to do is put a subform within a subform. The trouble is I want both forms to be continuous forms.
when I put the subform within a subform, access comes up with the following message:
************************************************** ******************** A form with a subform object cannot have its DefaultView set to Continuous forms.
You tried to add a subform to a form in design view. MSAcess will reset the property to Single Form. ************************************************** *********************
which it does my higher level form becomes a form only showing single records at a time whilst the subform is a continuous form.
I am trying to develop a database for recording project risks and issues and need to assign an owner to each one. My design stage has three tables for project resources, issues and risks. The Resource table is basically ResourceID; Surname; Forename, GivenName (forename and surname concatenated) Status; Role and DateAdded. When I allocate a risk or issue I want to be able to select the ResourceID from a drop down list that references the Resource table (I have got this working) and then from that ResourceID value to automatically populate another field with the matching GivenName and that is where I am getting stuck. I have setup relationships between the tables based on ResourceID but can't see where I need to be with a query to do this. Someone a suggetsed a union query but not sure of the syntax for this. I know I could have a separate lookup for the GivenName but that invites data capture error and requires the user to undertake an extra input step that could be avoided with a bit of slick programming.
Hopefully the above provides enough information to identify the problem and possible solution - any help on this appreciated.
I am a total newbie and possibly in over my head already.
I have 2 tables:
tblEmployees ..EmployeeID autonumber PK ..FirstName text ..LastName text ..DepartmentID number ..HourlyRate currency
tblDepartments ..DepartmentID autonumber PK ..DepartmentName text
Many-to-one relationship tblEmployees to tblDepartments
I need to create a form to enter employees in tblEmployees.
FirstName, LastName, HourlyRate are all straightforward. I'm stuck (probably a HUGE mental block!) on how to get the DepartmentName into the tblEmployees from the input in the form.
In an attempt to eliminate user input error,I am Looking Up values from a field in a separate table as input for my SEMINAR field. The table containing my Look Up field has only one other field, which is an auto-numbered key field.
When I view my SEMINAR field in datasheet view, the data is as I would expect to see. However, when I use my SEMINAR field in a report or query, it returns the value of the autonumbered key field. The data type for both the SEMINAR field and the Lookup field are text. - Any Thoughts? Golfer
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).
In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).
The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.
1. What I'd like to do is do a lookup based on 2 fields in my table. I'm tracking inventory for the company I am currently working with. I have a computer table with 3 relevant fields: compID, areaId, locationId
computer table compID = PK for this table areaId = FK from area table locationId = FK from location table
area table areaId
location table locationId areaId
There is a distinct relationship between area + location. I want a lookup for the locationID, based on the area they have selected. Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box). I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort? Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
We are on Win7 with Office 2013 (32-bit). I have had to migrate my application away from a network that is open to the web. I am now on a more isolated network. My problem is that I can no longer use Outlook for e-mail.
I knew that you can use CDO to send SMTP via a Mail Relay server. We've got one of those on this isolated network... but there is a show-stopper here. When setting up the configuration portion of the CDO message, there are these references using
CDO.Configuration.Fields.Item("HTTP:schema.micro soft.com.... etc.) = value
These references are how you define the SMTP server, server port, authentication method, etc. My problem with this is I can't get to that Microsoft site from the isolated network. The SMTP Relay won't do this lookup for me and I have no general internet access.
When I looked into the CDO library, I found that there were definitions for the fields like cdoSMTPAuthenticate and cdoSMTPServer and cdoSMTPServerPort - but when I attempt to use them, Access doesn't seem to like them even though the CDO library is checked in my references list and those values are defined. For what it is worth, the library file says it is version 6.6.7501 (or something close to that), so it is not the older version. The revision date is 6/6/2012, just about right for a file that would have been included in Office 2013's library set.
How to use CDO when you can't do the HTTP lookups? (I know how to use it when the lookups work...)
I have one attribute in the table and the table is normalized. But we have the same attribute in two different forms, one with a table lookup and the other with a free form text input. I'm trying to rationalize this for a dashboard output.
Do I keep it as one attribute or do I make 2 - one for the table lookup and 1 for the free form input? What are the pros and cons of each?
media (section_id PKEY, from_pair, to_pair, from_ip, to_ip)
Network_element( Name PKEY, MAC, IP)
I'd like to be able to make sure access will only allow from_ip and to_ip to be entered if there is a corresponding value in the Network_element table, however I do not know how to enforce this.
Any help much appreciated.
Thanks
Dan
ps - if anyone has done something similar, I'd be keen to see how they gone about it.
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
I have a DB for clients that contains all kinds of info. It is getting bigger and harder to keep track of things by their ID number. The problem I am running into is that ComboBoxes and Lookups can only bind 1 column. My DB is based on Categories, then Sub-Categories, then Sub-Sub-Categories. I have done this because there is multiple criteria for each customer.
For Example, I have a Customer = ID; Location = Location_ID; Employee = Employee_ID so everything is tied to the ID, but on my phone list I need to have a list of Employee's that show the name that is tied to ID and Employee_ID.
ID is unique to each customer but I have duplicate Employee_ID for example Customer 1 has Employee 1 and Customer 2 has Employee 1, so when I use the ComboBoxes or Lookups I get Customer 1 - Employee 1 for Customer 2 - Employee 1 if I bind to Employee_ID, if I switch that to ID it is completely messed up. Now I don't care if the table stores a number, but I would like the form to display the name.
I am trying to get my forms to display a name and not an ID number and when you enter new information you can select a name and not a number.
I have 3 tables: [SurveyVendor], [Surveys] and [SurveyResults]
SurveyVendor has 2 fields: [VendorID] - PK [VendorName]
Surveys has 3 fields: [SurveyID] - PK [VendorName] - FK [SurveyName]
SurveyResults has 3 fields: [SurveyResultID] - PK [SurveyName] - FK [SurveyQuantity]
I used the LookUp wizard to establish the relationships, and I chose to include 3 fields when linking [SurveyResults].[SurveyName] to [Surveys].[SurveyID]. I'd like the ID, Survey Name and Vendor Name to be displayed when making a choice in the combo box. And I would like the column to only display the SurveyName.
However, what's happening is the VendorID shows up instead of the VendorName when I click the combo box for [SurveyName]. And the SurveyID shows up in the column results, rather than the Survey Name.
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function Public Function Sort_1(SortName As String, FieldName1 As String) DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'" End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA" id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR" id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
4) Private Sub btn_editgs_Click() On Error GoTo btn_editgs_Click_Err Dim strWhere As String strWhere = "[editar_id] = " & Me.Gastid DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere btn_editgs_Click_Exit: Exit Sub btn_editgs_Click_Err: MsgBox Error$ Resume btn_editgs_Click_Exit End Sub
The Ignore, OverTime, AnnualLeave fields are bound to a I, O, A checkboxes respectively in this continuous form. The ExtraValue bound to a EV textbox in this form.
What I want is that when the user check O or A checkboxes, the EV textbox is enabled only for this form record NOT all the form records, when the user check I checkbox, the EV textbox is disabled only for this from record, NOT all the form records.
The problem is that whenver the user check, all the form records EV text box is affected by this action.
I have a continuous form which I have placed a unbound text box. For some reason when I place a value in the box it duplicates the value in all the correponding records! Any ideas why!!
The problem is that I have a recordset Rosters. Fields are ShftDay, A, B, C The ShftDay is distinct field. I have continuous form mapped to this recordser. This continuous form have 4 text boxes, each is mapped to one of the recordset fields. The user can edit these fields. What I want to do is when the user enter the value "Na" in one of A,B or C textbox, I want the backcolor property of this textbox to become pink. What I face is that for example if I enter the "Na" value in the A textbox, all the cells of the A textbox are converted to pink. What I want is only the cell in the edited record, not all. Please Help Me.
