How can I show data by category? I have 2 tables
1) Categories, field 1 in Category Id, Field 2 is Category name
2) Contacts, Field 1 is Category name which is the relationship with the category name field in the categories table, the rest of the fields are adress, phone, fax ect, ect.
I would like to be able to view the contacts by category name, but I don't know how to do it. I have created a report but the report shows all the contacts, not by category.
What is the first step in accomplishing this? I am not very adept with queries so please I can really use some giudance. I have also been on the MSOffice traiing site but that didn't help me much. Of course I didn't know what I training to do :).
I am trying to transfer daily data that I get from three different queries all into one Excel sheet. I take it that you have to make one over-arching query which I have made called Awaiting Base.
I currently have two froms, "add record" and "add record cont." The reason I have two seperate forms is because when clients create a new record information needs to be saved to two different tables and when creating one from with fields from both tables I ran into many problems. The two tables are named : tblMain, tblFileLoc Currently there is a textbox on both forms named "fileID" the FileID in the first form is from tblMain and is the primary key for that table, the FileID on the second form "Add Record Cont." is just a normal field. When clients enter in the new FileID in the first form "Add Record" and then move onto the next form "Add Record Cont." i need access to bring the entered FileID from the first form and Fill it in the FileID field in the second form. Currently I have tried making the control source for the textbox on the second form = the textbox on the first form but it brought up an error.
I'm trying to modify an existing database to make it easier and faster to search for homeowners. Currently, there is a search window that has two radio buttons, and a list box. one radio button is to search by address and another by last name. When you click one of these, it shows all the records in the list box. The problem is that 1. this is time consuming scrolling through over 300 records, and 2. more search options are needed, to include tag numbers of vehicles, and also phone numbers of residents. What Im trying to do is replace the radio buttons with a text box that will search all of the wanted fields, and produce a list of results in the list box. I'd rather not use a search button and just have the list box filter out as you type, but if a button must be used then thats fine. I've tried different ways, and searched all over the new for weeks, but cannot find the solution to my problem. Here is the current and original code. Thanks in advance for your help....Mike
Type of Search = Radio Buttons By Search Type = List Box HOMEOWNERS = table where all the info is located GET = cmd button to open form with results selected in list box
--------- Private Sub Option35_GotFocus()
End Sub
Private Sub Option37_GotFocus()
End Sub
Private Sub Type_of_Search_AfterUpdate() With CodeContextObject If .[Type of Search] = 1 Then .[By Search Type].RowSource = "" .[Search Text].Caption = "Select the Last Name to Search for" .[By Search Type].ColumnCount = 3 .[By Search Type].ColumnWidths = "1.5 in;1 in;0 in" .[By Search Type].BoundColumn = 3 .[By Search Type].RowSource = "SELECT DISTINCTROW HOMEOWNERS.LastName, HOMEOWNERS.FirstName, HOMEOWNERS.Address FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.LastName) Is Null)) ORDER BY HOMEOWNERS.LastName, HOMEOWNERS.Address;" ElseIf .[Type of Search] = 2 Then .[By Search Type].RowSource = "" .[Search Text].Caption = "Select the Address to Search for" .[By Search Type].ColumnCount = 2 .[By Search Type].ColumnWidths = "1.5 in;1 in" .[By Search Type].BoundColumn = 1 .[By Search Type].RowSource = "SELECT HOMEOWNERS.Address, HOMEOWNERS.LastName FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.Address) Is Null)) ORDER BY HOMEOWNERS.Address;" End If End With End Sub Private Sub Get_Click() On Error GoTo Get_Click_Err Dim Criteria As String Dim MyRS As DAO.Recordset
Set MyRS = Forms![BASIC DATA].RecordsetClone Criteria = "[Address] = """ & Me![By Search Type] & """" MyRS.FindFirst Criteria If Not MyRS.NoMatch Then Forms![BASIC DATA].Bookmark = MyRS.Bookmark End If MyRS.close Set MyRS = Nothing
I have an Excel sheet that I'd like to bring into an Access Form -- populate the Excel cells with data from textboxes on the Access Form and print it. I tried copy/pasting various things in, but it's pasting even text from a cell as an Excel object (let alone joined cells and images). Where can I find more information about this?
I found http://support.microsoft.com/?kbid=210288 But, when I put that code into the form for saving text from a text box to an Excel cell, it tells me that the word "Set" is a "Compile error: Invalid outside procedure."
Also, how do I refer to a joined cell? I've found that I can copy from a joined cell, paste into a host cell and select "Paste Link". This puts an absolute reference to the joined cell in the host cell (=$A$7, for instance) and when I change the joined cell the host cell is changed. Pasting =$A$7 seems to merely put the face value of "=$A$7 into the host cell.
:eek: I have 'Arrival date' and 'Number of Nights' fields and i'm trying to find all clients that are staying on a certain date. I enter the date in a form and reference this in a query. In the Arrival Date criteria i have:
<=[Forms]![frmFindDate]![txtFindDate] And >=([Forms]![frmFindDate]![txtFindDate]-[tblStays]![NoNights])
which i thought might do it, but i get a message saying 'You canceled the previous operation.':confused:
If anyone can understand what I'm on about could you please help, it would be much appreciated.
New to this, but found some helpful tips/code already that I've integrated into my project.
You may well be familar the issue tracker database available for download from Office Online, well I'm using this for a little project I'm running.
What I want to do is tie up a few snippets as follows:
1-On my form I have a button called "Close" 2-When I click the Close button I want to check two fields, "closure" and "fix" and ensure that they have content i.e. not null 3-If they have content then I want to alter the "Status" field of the displayed record from Open or Suspended to closed (these are the three options for this field) 4-If the "closure" and/or "fix" fields are empty then I want a msgbox to pop up promting the user to fill out the relevent field/s, otherwise mark the record as closed
So;
Click close button, check contents of two fields, pop up a message if either or both empty, enter details in empty field/s, click button again and as both fields are now complete,mark the records status field as closed.
I've got so far but can't quite tie it all together, is what I'm asking possible? What do you need from me to help answer the question?
I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1 ID // Response // Cats // Dogs // Elephants //.... Column 10 1 // I like cats // I like cats //(null)//(null)// ... (null)// 2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..// 3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data 2. Section 2: Show all responses from the Dogs bucket where there is data 3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions How can i in Q1 retrive only numeric data from F1 and display that data i a field? How can i in Q1 retrive only text from F1 and display that data i a field?
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I have a database with a form called "Main" where users input data and then print a report from it. "Main" has fields in it from another form "Members". This data (from "Members") is shown on "Main' by Dlookup coding, and therefore cannot be selected for input by the user. Now, lets say a user inputs data into "Main" and prints the report on 12/30/2012. On the next day, a member's name is changed and I update that data in the "Members" form. On 12/31, I would like to print the report again, but it shows the updated member's name instead of what is was like on 12/30. How can I keep the old data in case I want to print the report in the future like it was initially printed? What do I need to do to any form(s), report or what VBA code needs to be written?
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
I have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate() Dim btest As Boolean If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error" Me.VAT_registration_no.SetFocus Else btest = True End If End Sub
and to all other fields:
Private Sub textfield_Enter() If Not btest Then Me.textfield.SetFocus End If End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
i have 2 fields in a form - 'Balance' and 'Date'. Quite simply I need the date field to change automatically to todays date each time the data in the 'Balance' field is changed. Can anyone advise me on how to accomplish this.
How does one cause certain data to appear in another field based upon the data entered in another field.
For example: I enter in the field the word "Carrot" in a field called food and the word "Orange" appears in the field color. And then if I enter the word "Beans" in the field food then the word "Green" appears in the field color.
The subform 'PersonID' populates however the mainform 'PersonID' does not.
I just need to write code, or maybe do something that will copy the number from subform and paste it in the mainform field, maybe at the click of my save button.
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
I have a form with a subform. On the subform I have a field that has customer number in it, on the main form there is a field that will need to say "none" if the field with the customer number is empty, and empty if the customer number field is filled in. the field will not print out in the letter if there is no data on it.
the customer number comes from the table "CustomerNumbersData" the the field that needs to be empty on the main folder comes from table "CustomerData"
