Form Creation With Query Source
Aug 8, 2007do you guys use a query as your data source in you form?
if not how do you build a form with mutilple tables with the same unique id.
ADVERTISEMENT
Crosstab Query As A Form Source
Apr 21, 2005I’m trying to create a budgeting tool for my company. We operate in many countries and budget at the country level so I’ve set up a one-to-many relationship between my tblCOUNTRY and my tblEXPENSE. My tlbEXPENSE has fields ExpenseID, CountryID, Month, and Value.
In order to create a form where each country manager is able to fill out, and make changes to their budget for the next year I would like to create a crosstab query so they can have a horizontal view of the year broken out by month. The problem I’m running into is that crosstab queries are aggregates so they do not allow data entry.
The only solution I’ve thought of is to create a make table query from the crosstab query, but I still run into several problems. First, I need all twelve months to show on my crosstab even if there is not an expense yet for that month, and once I have it broken out by month how can I transpose it back into a form to be able to append it to my tblEXPENSE?
I don’t know if this is even possible with the way I have set up my tables. I guess first I’m wondering if I should change the setup of my tblEXPENSES so it has a field for each month although this would make it harder to do an annual budget rollup based on country? Or should I make a form in Excel for all the country managers to fill out, compile all the data, and append it to my database? Thanks in advance for any advice.
Form Creation Help
May 30, 2006hi there,
i also need help in my form creation. as you can tell from my post, i am clueless.
anyways, here is what i want to do.
i have an input text as:
title1
name1
address1
these will grow based on how many people information one needs to enter. so say if i have 3 people i should have:
title1
name1
address1
title2
name2
address2
title3
name3
address3
and so on
the thing is i only want to show input text 2, 3, etc when i have several names to enter.
so what i want is, a button, such that when i click on that button, it creates input text 2, 3, based on the number of people i have.
i know how to do this easily with html/javascript, but i MUST do this in ms access, so i hope somebody can help.
TIA
chocho
How To Make Creation Of A Record In A Table Trigger Creation Of New Table
Jul 17, 2013I have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?
View 8 Replies View RelatedDynamic Query And Record Source In A Form
Feb 23, 2005I have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
Thanks in Advance.
Run Query On Record Creation
Aug 9, 2007I been tasked with a project to be written in access which I am rather unfamiliar with, web design is more my area.
For part of the project I need to copy some pricing fields from a pricing table to a new record at the point that the new record is created. The new record is created when a 'new form' button is clicked.
What I would like to know is what and where I should trigger the copy query from and what function should I be using?
cheers
John
Need Help On Join Creation Query
Mar 12, 2008Hi! Im wondering why my code below Does not work!.... any suggestion???
strStatement = "SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo" & _
"SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName FROM SessionData" & _
"INNER JOIN SessionHeader ON SessionData.RunNumber = SessionHeader.RunNumber;"
getQueryname = InputBox("Enter a name for New Query?", "Enter a Label or Name for Query", "Enter Query name/Label Here", 500, 700)
Set qryEmployees = curDatabase.CreateQueryDef("getQueryname", strStatement)
Forms :: Can A Query Be Used As Control Source For Text Box On A Form
Jun 7, 2013Can a query be used as a control source for a text box on a form?
=[QueryLibrary]![Kit] is the control source do I need to change anything else?
Forms :: Filter Form Using A Query Not In Record Source
Aug 20, 2013I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
Form That Is Linked To A Query By Its Record Source - DCount?
Mar 25, 2015 I have a form that is linked to a query by its Record Source.In one of the Text boxes I want to appear the number of rows in the query where Days = 4 so I thought I would try to use DCOUNT as the Control Source for the Text Box. However every expression I try for DCOUNT throws back error? or name?. How to sort my code?
=DCount("[EPISODEUNIQUEKEY]","1-12 Show Records by Date Range","[Days] = '4'")
Form Creation All Records Not Shown
Sep 30, 2015My Database consists of three tables and a linking table, tbl graves, tbldeceased,tblmemorail and tbl memorialtablejoin.
Having completed my tables and checked that the relationship data gives the correct data I decided to embark on a simple form to display all the data on a single form.
So I used the create Form option, selected add existing fields. All of my tables are shown in the fields list.
From tblgraves I selected plot and grave Id, from tbl deceased forename and surname and from tbl memorial the inscription field.
The form was created but on examination I realised that my total records was shown as 12000 records whereas tble graves and tbl deceased has 39000 records and tblememorial 17000 records
So I created the form using just tblgraves and tbldeceased and that shows my 39201 records.
If I leave form view and go back to design view and add the inscription record from tblMemorials I see the message box as per the attachment.
Not every grave or deceased has a memorial but I would expect to see my 39000 records
I've also attached a screen shot of my relationships...
Database Locking During Query Creation - Need Help!!
Oct 13, 2005why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
Queries :: Query Creation For Drop Down
Apr 17, 2013I have an issue with designing a query.i have three tables (there is more but these should do for this)
Game Review
reviewer
Game
i need a query that will only select games that the reviewer has not reviewed.its to populate a box on a add review form so that when a reviewer has been selected, the next drop-down will only show games that have not been reviewed yet by that reviewer.
Display Field From A Query On A Form Not In The Forms Record Source
Jul 27, 2005In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source:
[qry_op500_entry_delta]![countofphone model]
I get an error stating that this is not in my field list for the form.
If the answer is No or if needed I can give more information on what I am trying to do.
Forms :: Creating New Records On A Form With A Query As Record Source
Mar 29, 2014In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
Forms :: Assign A Query To The Record-source Of A Form Dynamically?
Apr 27, 2013Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code:
Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry
Forms![PersonalFm].Recordsource = Qry
[Forms]![PersonalFm].Form.Recordsource = Qry
Forms :: Opening Form With Inappropriate Record Source Query
Jul 28, 2013I am setting up a generic form template that will enable me to maintain data in a standardised way. All generic code is stored in a module. There is very little code specific to the table being maintained. Much of the form functionality is determined by data held in supporting tables therefore data driven.
When a form is loaded a commonly named query is created that is used as the Record Source of the form. This query only ever returns one record and uses the value of textbox on the form as criteria in the unique id field.
Despite immediately creating the query in the Open event of the form, Access tries to process the form first and returns an error as the Record Source query applies to another form, the one last opened.
How can I create the query before anything on the form is referenced / processed without creating it before the form is opened?
Forms - Adding Query As Data Source In Existing Form?
Dec 2, 2012I am trying to add a query in an existing form. When I click on adding a field, I get an option to show "all tables" for not queries.
View 3 Replies View RelatedAutomating The Creation Of Multiple Records Through A Form
Feb 20, 2007Sorry for the long winded question but want to be sure i clarrify exactly what i'm after.
What I currently do with my database is each time we send an email or a letter or have a phone call with a contact it is logged in our correspondence database. This is achieved by creating a new record in the correspondence form by filling in the contactID of who the correspondence was made with and then filling out the rest of the details as per the correspondence fields below. This works fine at present but occasionally we do mass mailouts of information to say all the centres in a state. Instead of someone having to enter a correspondence record for each of the 400 centre contacts we have in a given state and repeat the date, time, and details information each time so that an individual record appears for each centre, I would like to automate this process.
What I envisage is something like the picture I’ve attached. Where the user could filter by either the state or maybe even select specific centres from a listbox (filter by state is the most important one) and then fill out the rest of the correspondence record and have Access automatically create an Individual record for each of those centres selected, in the Correspondence tbl (from research it sounds like I need some sort of a loop task performed?). I need these records to be added to the bottom of the existing correspondance table
Unfortunately I have no idea how I can achieve the actual creation of all these records. Creating the form and queries based on a form I am fine with.
My table structure is below
Tbl_Correspondance
ContactRefNum (PK)
CDate
CTime
StaffID
ContactID
CentreID
CouncilID
CompanyID
ContactMethod
Project
Details
FollowupReq
FollowupCompleted
ContactID,CentreID,CouncilID,CompanyID are all linked fields to their own tables.
I am a novice user so the simpler the solution the better. Any assistance you could give in this task would be appreciated.
Creation Of A Word Document From The Access Form
Oct 17, 2006Hi....
I have created an Access Form wherein i have given lots of text box and text areas...
Now i want to create a word document dynamically using the information that has been given by the user via the Access form...
I want to create a Table dynamically in the Word Document using the VB code behind an access form... Also through that code i want to manage the font of Word Document and most if its controls..
Can u plz help me in this or provide me with a document wherein i can get some info in regard to it i.e. to manage the Word using Code...
Thanking you,
with regards,
Kapil Sharma
Dynamic Creation Of A Word Document Using Access Form
Oct 17, 2006Hi....
I have created an Access Form wherein i have given lots of text box and text areas...
Now i want to create a word document dynamically using the information that has been given by the user via the Access form...
I want to create a Table dynamically in the Word Document using the VB code behind an access form... Also through that code i want to manage the font of Word Document and most if its controls..
Can u plz help me in this or provide me with a document wherein i can get some info in regard to it i.e. to manage the Word using Code...
Thanking you,
with regards,
Kapil Sharma
Modules & VBA :: Prevent Record Creation If Form Is Null?
May 11, 2015Nutshell: My user DB has one menu with 4 tabs (2 for adding records, and 2 for searching/editing records). Since the form opens up in the acFormAdd mode, Access already creates a new record (blank) before they type anything. It is resulting in blank false new records that are getting added to the table and they are getting annoying. My audit table is full of "user X created new record at this time" when it's just a blank record.
There must be a way to prevent this using VBA, but I cannot find it.
I've seen ways to disable "add new record" buttons and such, but nothing that disables Access from being smarter than me and creating a new record before I decide to.
Forms :: User Creation Form - Get Primary Key And Use It In Subform
Apr 1, 2015I am building a user creation form. I set it up like the screen shot in the first attachement. 3 different tabs.
In tab 1 you create the user which then gets a unique key (DocentenID).
Tab 2 you can input more information about the user (in a different table).
I want it to grab the primary key (DocentenID) and insert that into a field in the other table same goes for tab 3.
Utilities/code To Make Form & Report Creation/Edits Easier
May 26, 2005I am wondering if anyone can recommend shareware or free ware Utilities/code to make Form & Report Creation/Edits easier?
I'm thinking of something like Cub Editor http://www.peterssoftware.com/ce.htm or SmartForm+ http://www.aadconsulting.com/smtfrmplus.html
but alas .... I can't do anything that will edit the registry (company politics says Access and it's Wizards is plenty 'perfect' http://www.dbforums.com/images/smilies/frown.gif maybe I will convince the powers that be but not in short time)
Also the code (or forms / reports built) need to work on Access 2000, 2002 and 2003. or well-commented on how to modify as I'm only modest with writng VBA code.
Any ideas? Thanks!
Mark
Remove Record Source/Control Source
Sep 19, 2005I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
Queries :: Opening Design View Of Append Query Without Timing Out Source Query
Mar 4, 2015In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?