Forms :: Set Up A Series Of Controls Then Bind To SQL Queries?
Jul 10, 2014
I have about 12 tables in a 2010 database and I want to create a single summary form showing key details of the database. Most of these will be count fields using various subset of the tables e,g. total members, count of new members this year, count of those not attending a meeting etc. More than one table will be represented on the form.How would I set up a series of controls that I then bind to SQL queries? Or is this not the way to do it?
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 have a table in access which captures a couple of bits of information. The database is for tenant management and payment records. This is what i am trying to achieve:
In one table I record payment information, when they paid, and how much they paid.
In the lease table I capture information such as first pay date, and the payment schedule, whether this be weekly, monthly, fortnightly etc.
I would like to create a table of sorts which has the next twelve months worth of payments dates. Then when i add a payment to my payment table it will match it up with the correct payment date. This will allow me to track arrears and missed payments.
Let's say that you have a cookie jar that's to be shared with two children, let's call them Jack and Jill.
You know that to start with (t = 0), there are 100 cookies in the cookie jar
Now the strange thing about Jack and Jill is that they start eating cookies on different days and that their cookie appetite depends on the number of cookies in the jar when they start, Specifically
Jack has an appetite of 10% of the total cookie jar and he starts eating on day one.
Jill eats after Jack and she has an appetite of 5% of the jar.
So what this would look like is:
Day 0 - Cookie Jar has 100 cookies Day 1 - Jack eats, Cookie Jar has 100 - 100(10%) = 90 cookies Day 2 - Jill eats, Cookie Jar has 90 - 90(5%) = 85.5
Problem:
1. Given that I have a table containing the starting number of cookies e.g.:
CookieTable Startingcookies 100
2. Given that I have a table containing a record for Jack and a record for Jill, each with two fields: Eating Start date, Appetite % e.g.:
I have 3 forms that all use the same sub form. The 3 main forms show 3 different project types, where the sub form shows what other projects that a company is engaged with. Rather than create 3 different queries for my sub form, i would like to just filter it using criteria that looks at the CompanyID field on my 3 main forms. Currently my criteria is like this:
[Forms]![frmProjects_Detail_IND]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_CSS]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_TAP]![txtcompanyid]
Is there a way to ignore the parameters that are null?
I have been struggling with understanding Queries related to Time Series. I find it difficult to get the right answer or understanding how to ask the right question Access style.
Table below is a sample data/table example for my question. The intent is to build a query that will return the latest date for each unique/Distinct name. I have Time Series Table that I haven't been able to do this, it doesn't return with Distinct Name. I Also have included a sample database.
TABLE Name Date_1 Distance ------- --------- ----------- A 1/1/2014 10 B 1/3/2014 5 C 2/1/2014 10 A 1/3/2014 16 A 2/5/2014 3 B 5/1/2014 5 C 6/2/2014 6 C 7/1/2014 7
ANSWER SHOULD BE. Name Date_1 Distance ------- --------- ----------- A 2/5/2014 3 B 5/1/2014 5 C 7/1/2014 7
I am trying to develop a form in the light of budgeting, therefore, I am designing a form with all possible options being an accountant I can think (it just a try nothing else)
Now here's the scenario
Fiscal Year Period Month Working days
Period - Month - working (are my heading) 1 - Jan - 22 2 - Feb - 18 and so on
This is what I am trying to learn, if I select period 1 "Jan", application/code fill -out remaining months automatically, e.g. Period 2 "Feb"; Period 3 "Mar" .....
and if I select Period 1 "Mar", then period 2 "Apr", Period 3 "May" ......
Currently, I am using combobox, but problem is, if I selection period 1 = "Jan", then I am unable to restrict period 2 using "JAN" as both combobox are separate.
I am using Ms Access 2007 and I am new to it..I have table of following fileds
ID (as a primary key)
AccountingYear (Filed for current accounting Year e.g., 14-15; 15-16)
Series (text- as R, Y, B etc to denote series code)
OrderNo (field to store custom autonumber Order No e.g., for Series R-1, R-2, for series Y Y-1, Y-2, etc)
I have created a form using all these fields..Now what i want to do is I want to create custom order no as first 2 digit of accounting year+Series+Autonumber of respective series as
14R-1 14R-2 14Y-1 14Y-2 14Y-3 14B-1
15R-1 15R-2 15Y-1 15Y-2
Means autonumber starts with 1 for every accounting year of respective Series..how to do this and what code or formula should be applied and where the code should be inserted as I am new to Ms access
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
The fields in the form are currently pull downs or manual enrty fields (all based on the columns of the table... "Bound" I think you call it).
I also have fields that I manually brought into my form as text boxes that I am using to populate a series of numbers. I have named them P1, P2, P3 etc. They are unbound fields.
I have a field that I am totalling the unbound fields. It is a column in my table. At one point the control source name was the table coulmn name.
I have changed the control source to read =[p1]+[p2]+[p3]+ etc.
The total does show up in the field on my form
HOWEVER.... When I save the data the total number does not get brought into the table. All of the other fields do.
I am creating a table which is called a New Connection Analysis from there I have created a form that is linked to the table. I the form I have created a combo box to show the different division e.g. Central, Western, Northern. Now the combo box is created but when I enter the data in the form once I select the division e.g. Central when I save it the data doesn't go to the Division list on the table.....How can that be fixed????
I have a report that I would like to bind to a combo box. I have built the combo box with the table that I need but I need it to select and print a specific report when the user selects the correct item from the combo box. I really do not know if this is possible and my coding ability is next to nil, but having it would really help my users.
Thanks for whatever help you can render and if necessary, I will send you the database and show you what I need.
I keep getting #Name? error. I'm trying to not use the form wizard and manually bind a text box on a form to a field in a table. I've placed a textbox on a form, then right click the text box and click properties, this brings up the properties page. In the control source property, I correctly type the name of the field. I save and switch views from design view to form view, I should see the first data in the textbox, but I see #Name?
I've attached a sample database. In this database, I'd like to have the combo boxes under the Week Ending label populate the data for the fruit name to the left of each box. For example, if I choose Week Ending 2 in the box next to Banana, I'd like to have the boxes to the right of the combo box auto populate with the data in the table. I already have started some coding, but I'm not getting what I want to see.
Here's the problem: the codes will only display the top row of data in the 9-7 and 9-14 tables, respectively. That is, if I choose week 2 for Banana and week 2 for Orange, the numbers that populate will be the same. Instead of this, what I would like to see happen is that if week 2 is selected for Orange, then have the second row of data, from the 9-7 table, populate into the textboxes on the Products form.
I've constructed a trivia database that consists of trivia questions that are classified into different categories. The DB also keeps track of which questions were used at which establishment. I am designing a form that will generate multiple rounds of trivia based on user criteria.
As seen here the user can specify the number of rounds and questions per round. Maximum number of rounds is 8 - the tab visibility is changed AfterUpdate of the number of rounds field.I have queries set up that do what I need for a single round - they choose random questions based on category, subcategory, difficulty and if they haven't been used at that client's before.
how to deal with multiple rounds without it becoming a headache. My tab layout has controls txtCategory1, txtSubCategory1, txtDifficulty1, txtType1, txtCategory2, and so on. Currently the queries use the values in the controls for Round 1. I would have to make eight copies of these queries (one copy for each round) in order to generate each round based on specific criteria, which would make a ton of queries. The other option is to use SQL statements in VBA, but this method doesn't like the [Forms]![frmCreateTriviaNight]![cboCategory1] in the code - I have to concatenate the names of the variables in and the code would be a mess.
I have a form with a combobox, textbox, a button and a listbox.
Currently the button checks if the textbox is empty and if it is, it runs a SQL query to select data using the combobox value in the where clause, else it selects data using the textbox value in the where clause
I need to be able to type or select values in both controls and use those values in the SQL query as a where.
I want to use a tab form where the user can click add, search, and edit records all from 3 tabs. The problem is I only want to be able to add data on one form, edit data on another, and just read only on the search. This seems to cause a dilemma, how can I specifiy them to add, edit, and read only on one form using tabs?
I am making a database that will be used by multiple people on a network, and they have different resoloutions on their own computers. when the database is loaded on their respective computers, the form controls are all off.
On the computer i designed them on, the forms fit perfectly on screen, as do the controls, i would like to know if there is some vba code i could incorporate into the form open event, or module code that i can use in order to dynamically re-seize the forms and all of the controls to suit different resoloutions?
There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".
I have a main form, 2 sub forms, and 2 text-boxes on the main form. I have a search box now for the first subform. Works perfectly. Once the users filtered based on name, I want them to be able to select that filtered record which would filter another subform.
Right? ok.
My first attempt was to assign the ID that was selected to a textbox on the main form, then automatically requery the second subform based on that value. I used this way on my search box. I'm sure there is a way to go directly from clicking the value to requerying the second subform, but i don't know it.
That being said even the indirect first attempt isn't working. It all has to do with the ".", "!", and ordering of this stuff. It has to do something with Parent child, but beyond that I'm at a loss.
I did find this very useful site [URL] ...... It shows what code you can use depending on where you are and what you're doing. Even with that as a resource, i'm still at a loss.
