My 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...
Sorry 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.
Dear All, Ok, I would like to rephrase my question :) , how do I retrieve records even when only one of the 3 text fields in my form are filled up? I have to fill up all of the 3 fields for the records to show up.
SELECT * FROM tblApplicants WHERE (COURSE Like "*" & forms!frmFS!txtCourse & "*" OR COURSE Like "*" & forms!frmFS!txtCourse2 & "*" OR COURSE like "*" & forms!frmFS!txtCourse3 & "*") ORDER BY names;
In the form frmOrders of my database i have a combobox where i can choose a ClientID and the dropdown list shows me all the clients in upgoing order with their related orders in descent order.But the drop down list doesn't show all available clients. The two hundred heighest clientnumbers aren shown. However if i type one of these clientnumbers in the textbox of the combobox, the client is found.If i go to the property sheet of the combobox and activate the query that serves as the recordsource of the combobox, all clients are shown!!!I control of the records of these clients in the table Orders, doesn't show any difference with all the other records.What can be the reason for these behaviour. Is it a question of a limit of records that can be shown via a combo box?
I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.
To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process.
Also, The other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records. Anything else I could put into the criteria to show records that may have a blank field?
I have a question on number of records displaying in datasheet view on a form. Is there a way to limit the number of records showing on open.I have a command button on a Navigation form that opens a frmEnterPartsOrder using a Macro.
I have set the Macro to OpenForm, View Datasheet then GoToRecord, Record New..Works perfectly, but it does open the form and fill the screen with records, putting focus on the "new" record at the bottom of the form. Is there a way to set it to open this form, but only display say last 10 records and then focus on "new" record.
I have a form with a sub form. The parent form has a field called 'ReportedBy'. The user enters their name in this field. I want the subform to display all records that have been created by the name in ReportedBy. I have based the subform source object on the following sql query:
SELECT * FROM tblFault WHERE tblfault.reportedby=[forms]![frmfault]![reportedby];
The sub form only shows one record even though there are more records in the table????
In the current db there are some tables and queries, forms designed around them
With one table Test http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
When I have a simple table, users pictures inside maps (jpeg,png...) defined as OLE object, when creating the form from that tables, don't have picture shown, only name of the picture? I tried all the options, package, copy, paste... nothing...always the same... just picture name but no picture.
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I 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?
Mainform width is 22"(Maximum) Subreport Wdth is also 22" (Maximum)
When i run the mainform, the subreport shows correctly but the vertical scrollbar is at most right side of main form so i have to scroll main form horizontally first to access the vertical scroll bar of subreport.
To solve, changing subreport orientation to right-to-left shows vertical scroll bar at most left but still if i scroll horizontally on mainform, that scrollbar hides as its not freezed.
Consider ActiveX Control of Microsoft ScrollBars 2.0 .....
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
I have a database with many Quires I want to create in the main form "welcome Form" many txt boxes cells
To show a total records in Each Quires
For Example I have the below list queries
Expired Employees PassportExpired Employees Work PermitExpired Employees VisasExpired Employees Insurance
In the "Welcome Forms" I will create a txt boxes Cells for Each one of those Queries to be Show only No "total Record in Each one". It is like a dashboard to monitor daily without opening the Report or Query...
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...
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...
Nutshell: 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.
I 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.
I have created query that shows some fields from selected tables. The information that shows from all fields, when I "run" the query can't be edited and I can't add new text on the bottom of field. How can I do this?
I have attached a picture of my query. You maybe understand better if you see it. Will appreciate it alot. Need help very quickly.
I have a listbox inside name of workers which is coming from table (Workersdetail) on the otherside I have report from the same table which still showing all records of worker, this report execute through the form and by selection of listbox values. look into my following codes.
Dim strWhereworker As String strWhereworker = "workername = Forms![workermenu]!workerslist"<--(Workerlist is the name of listbox) Select Case Me!reporttoview Case 3 'DoCmd.OpenReport "workerindividual1", acViewReport, , strWhereworker
Problem is, in the report all workers with related values showing while I want to get only the worker name I select from the listbox, I could not understand where I made mistake.
In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.
I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.
I am trying to create an available list of employees to assign to an event. Basically, when I assign someone to the event and need to assign additional people, I don't want the ones who have already been selected to show up in the drop down.
Sheet1 is the linked Excel sheet where all of the employees are contained. Assigned officers is the table where those selected to work are stored.
Using the following code, I get a prompt for sheet1.[employee id]
Code: SELECT Sheet1.[Officer Name], Sheet1.[Employee Number], Sheet1.[Employee Number] FROM Sheet1 WHERE (((Sheet1.[Employee Number]) Not In ([Assigned Officer].[Employee ID]))); ;
