I am looking into making an interface for the data entry person. The data
entry person would fill in an ID. Now I would want certain information about
this person(with the ID) to show up, say Name info, Contact etc. Once it is confirmed that it is the right person, the data entry person would fill in some other fields (salary break up) and this would then be stored in a separate table.
As I understand, I can get the validation info from a query. But what
exactly should I be looking into to execute the autofill. Do I need to code
the "OnClick" property of the field 'ID' which is being specified ?
Secondly, after the data entry person specifies the salary break up, what
would make this info store the new data into the existing salary table ?
General guidelines would be highly appreciated. Currently I am looking into
basic VB, because I suspect that some coding might be required.
I would like to create a text box in an existing report that uses data from another report. I create a new text box and go to it's control source and select the path.
In my case: =[Reports]![Radvantages members]![AccessTotalsAmount1]. When I run it, it sais "#Name?"
Is there another way to integrates data from another report? I don't want to use a subform becouse it doesn't work with my lay out
Is it possible to do this in a form/query/report.Basically the situation is:
- I'm creating a Database for a Gym - I need to find members who's membership has expired - I need the system to Send out reminder letters to all members to renew their membership
Soo basically I want to know if I can use a microsoft word template, say I type up a sample letter in microsoft word excluding the personal information. And I want to import this letter into my Acces document and by pressing a button say "Send letter to this person". And that button should use the memebers personal details in the sample letter and so I can now press print.
I want all this to happen in the Access document (unless there is a better way of donig this without using Word?)Also I need to do the exact same but with an e-mail function aswell.
Another situation is I want to have tick box feature which is calle Medical Condition? If it is ticked then another box appears allowing you to enter the medical details and if it's not ticked then no box appears. Is this possible?
We have an extensive reporting application in Access and would like it to be available through a web application. I've went down the road of migrating this application to SQL to use SQL Reporting Services; this is a good tool but I am wondering if MS Access has an easier solution for me other than migration to SQL.
I'm looking for a solution that will make these reports available over the web for many users. Price tag is a consideration...need to know if there is any.
Please help...thanks in advance for any thoughts!!!!
Not sure if I've done the right thing but I'm part way there...
Ok, what I'm trying to achieve is this: To have a form with various text fields, some are for input but a few would be calculating what values are entered into the forst text boxes, but this data should also be stored in the same table.
So, this is what i've done so far: I created a query with a few fields (1-4)from my table and another field that calculates these fields that looks like this.
IIF([name]>=2 And [department]>=1 And [role]<=5,"Yes","No"
I then created a form based on this query, I can now enter data into the text boxes for fields 1-3 and the text box that does the calculations works a treat.
The problem is getting the auto calculated value to be stored 'automatically' in the same table.
Can anyone advise, either an alternative to what I've done, or how I can get this field saved?
I have 3 fields on a form that are combo boxes. The first field is linked to a table with Department names. The second field has Supervisors for the departments. When I first load the form and select a department, it correctly runs the query that populates the supervisors in the department, in the second combo box. However, if I select a different department, it does not change the list of supervisors.
I understand that I can use an append query to add data to a table from a form and then have it run by using a command button. I want to do this because when I link the form directly to the table I do not want the form to create a new record every time it is invoked. (it does this automatically because I have visual basic code move to acNewRec and fill in some of the fields on load and lock them off, this is neccessary) I also do not want it to create the record immediately because the user might then decide to cancel their actions and leave the form - then I have half a record's worth of data in my tables! :D
I could just use VB and recordsets to do it but SQL is more elegant. The problem is, when I use the append query it appends an extra record to the table for every single record that already exists within it. So a table with 1500 records will have 1500 new entries containing duplicate data!
This is all a mess, so what would be a better approach? The user does not need to navigate through the records on this form or make edits, it's strictly for additions. There is a subform linked to a table that is in a one-to-many relationship with the table that the parent form must update to, but I'm quite happy to leave that one linked directly.
Any suggestions appreciated ;) I've probably done something thick..
Have the data settings for the form all allow=true And tried the VBA allowedits=true on form open but the form is still locked. Any suggestions about why? Is updating two tables from a two-table query not allowed or??? Thanks,
I am trying to augment my database to have a price list. Because I wanted the price list to be of record and saved, I used a form with a subform. The main form taking the price list header info price list ID, price area, and date and putting into tblPricelist) and using the sub form to table to load the actual price list info (product, price) and putting into tblpricelistsub.
What I am trying to do is to be able to bring the entire price list as it is and be able to update (or not) the price of all or some of the items and then have it save as a new entry into the tables. I am trying to do this without having to use a sub form and lookup every product all over again (500 products).
Is there a way to do a query to bring up the entire price list, turn that into a form, and then be able to update the prices that would in turn create another entry into the price list and pricelistsub tables?
I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])
This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.
Folks, can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp() Dim strDocName As String Dim strTableName As String Dim strTbl As String Dim aot As Access.AccessObject Dim strSQL As String Dim rpt As Report Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables If aot.Name = "strTbl" Then strTableName = strTbl End If
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
im creating a form in design view but the icon to show the fields from the table that i want the fields to come from isnt being displayed; how do i get it to show?
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief: tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff tableSignatories has ID for a primary key and has SignatoryTypeID as a field tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
I have a form based on a query. I'd like to add a search box to the top to look up values and then populate the rest of the form. I can't do it with the combo box wizard because the relevant (third) option doesn't show up, I assume that's because the form is based on a query not a table.
I have a table called Tbl_Query_List that houses the query names that I want to be displayed on a continuos form. I want the user to select the query by highlighting it in the continious form and then having a command button at the bottom of the form.
I have everything setup including the command button. Assuming that on the EVENTS tab I set the OnClick to [Event Procedure] but when I click on the ellipsis what do I type to make it run the highlighted query?
I have a form that has the following fields: Ticket Date From, Ticket Date To, Pawnshop, Property Category, Item Description, Make, and Serial Number.
The purpose of this form is to allow an unexperienced user of Access to query a database. Thus far, when I specify the query to include dates from/to, pawnshop, property category (eg, jewelry), and item description (ring) the records retrieved are the correct number. None of these fields contain null values. However, the Make field has some null values and the Serial Number field has an extensive number of null values.
My problem is that I have noticed because the Make field has some null values a few number of records aren't retrieved after my query (even if I leave the Make field blank). This problem is much worse with the Serial Number field. For instance, I ran a basic query via my form specifying the dates and property category-jewelry and got back 229 records but when I verified this with a separate query I got 960 records, which is the correct number.
My criteria from my querry are as follows:
Between [Forms]![PawnProperty_PawnerQueryForm].[TicketDateFrom] And [Forms]![PawnProperty_PawnerQueryForm].[TicketDateTo] [Forms]![PawnProperty_PawnerQueryForm].[PropertyCategory] Like "*" & [Forms]![PawnProperty_PawnerQueryForm]![Pawnshop] & "*" Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[PropertyDescription] & "*" Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[Make] & "*" Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[SerialNumber] & "*"
The bottom two I've had to remove because the correct number of records were not being retrieved. I suspect that the null values in the Make and Serial Number fields are the root of the problem. to make these queries all work together!!
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
Tried to find things but being a mix of a couple of different actions havn't been able to actually find it
So basically I found a awesome form somewhere that builds the SQL query based on all the users selections.
When you hit the create button it will save the query and update the sub data sheet below with your query results.
From here the part I'm lost on is getting the TOP x number of records and moving them to a new table, I wanted it to work on the 2 drop down boxes that are on the form. select your values and hit the button.
I'd imagine it would be a sub query that I'd use for the top x like I have in the past but I just can't seem to get it to work
Quick steps
1. create your query 2. check your data in the sub datasheet 3. decide the number of records to move to the new table 4. select the name 5. hit the button
This would copy the ban, xcv & dfs fields from the importeddata table to the moverecordshere table & also update persname in the moverecordshere table with the name selected in the form.
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings The field I want totalled is called Price The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).
An Example of Textboxes values on the search criteria form: Textbox - name: bob Textbox - address: Left blank Textbox - category: car,boat,truck
I Have tried creating a query with the following
field: name criteria: like “*” & name & “*” field: address criteria: like “*” & address & “*” field: category criteria: like “*” & category & “*”
SQL code: SELECT customerName ,address,category FROM prospectiveCustomers WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;
That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.
I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.
SQL CODE: SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category] WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code: Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long Const conJetDate = "#dd/mm/yyyy#"
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
