I am new to Access and am learning the ropes.
I have constructed a basic database for our Church Craft Fair.
It consists of two tables, one for suppliers the other for inventory.
The tables are linked with a one to many rule and each has a key field
of Supplier ID. I have a form for entering Supplier details and another
for entering Inventory which has a subform. It all seems OK.
I have used the wizards and then customised to suit.
The problem I have is that I need to have a report of items sold with their value.
I have done this with the wizard generating the report from the inventory table.
However I also need to include fields from the Customer table, such as supplier name
and tax details. I have tried to include these fields by entering the control source
from the Customer table using the expresion builder[customers]![SupplierName] but this
just generates an error on the form #Name?. I have also tried launching the report from
a form based on the customer table and using the customer data on the form as the control source.
This does not give errors, but the data is incorrect and only relates to the first customer record,
ie on the inventory list for each customer, every customer has the first customers name.
Can anyone help me with this?
I have spent hours trying to work out how to do this correctly.
i really hope someone could help me with this, i have a query (Exp Query) which calls from 2 tables Expenditure and VAT.
My VAT table lists all the VAT Months and their relative Quarter Number, VATMonth is the primary key but because it contained other text, i had to make it a text value rather than format it as a date. so the problem is when i make a report, it sorts the VATMonth in alphabetical order, eg June 2004,July 2004, May 2004.
my VAT table has an ID field which is an autonumber, so i use that to order the list in things like my drop down box on a form. so how can i do the same thing in my report.
the report consists of 3 months which i need to SELECT VATMonth FROM Exp Query and ORDER BY VAT.ID (also from Exp Query) can i do this and if so could someone tell me how it would be written, i just keep getting syntax errors.
I am trying to get Access to print out a field that is city, state, zip except when no city is set.
My control source criteria is =IIf([City] Is Null," ",([City] & ", CA " & [Zip Code])) I've tried IsNull([City]) instead of [City] Is Null to no avail. All I get in the box is #error. I can get just [City] or just [Zip Code] to work but even if I just put in =[City] & ", CA " & [Zip Code] I still get #error.
Please help! I just got my hair cut so I don't have that much to pull out.
I have a report with subreports contained in it. Each subreport gets it's data from a different table.
I noticed subreport 1,2,3,4 are pulling the data from their corresponding tables. However, subreport 5,6, and 7 are pulling their data from the table relating to subreport 1.
I've checked the subreports themselves. They are referencing their own tables if I open them individually but not when opening the main report.
The report names all seem to be correct.
I've never seen anything like this before. Does anyone have some ideas??
I 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 added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
Hi all, at the moment all of my field are control sourced. I heard there is a way whereby the field could be unbound and one button it the control source for them all. Im not entirely sure what this means or how to go about it. But i think it would solve the problem of if my users start entering data into a record and then decide to leave it, this record is automatically added to the table, with little or no data. As the data in the table is used for statistics, the table showing records which are not actually there is causing problems. Im not sure if i explained this well, but i would be grateful of any help. Thanx
I have a subform listing items for purchase. Some are services and are not taxable, some are products and are taxable.
So far, I am calculating the sales tax for each record (item paid for), based on whether the thing is taxable or not and putting that result in a text box (txtSales_Tax) on the form. Since these calculated amounts aren't stored in a field, I can't SUM them, since SUM only works on data in table fields. Can someone tell me how to get a total?
Otherwise, I'll get rid of txtSales_Tax entirely, if someone can tell me how to write a SUM function, as a Control Source, that includes an IIf, or a WHERE, so it only adds items where the yes/no field Taxable=True.
I've got an IIf with a SUM function in it but, even in the footer, that only works for the current record. I need a SUM that has a nested IIf or WHERE in order to include all the records.
I have a drop down list of Last name and first name.
I want this to be the only unlocked feature on the page. When a name is selected the other information is shown (the persons cc#, Exp date, phone number, email etc.)
I am almost sure its a control source, but i'm not 100% as i'm relatively new to Access..
Another issue I would like to know is how to show the Last Name, First Name in the selected drop down after its selected, currently its only showing the Last name.
is it possible to change the control source of an unbound text box ?
For example i have a textbox which simply displays the number of current records displayed i.e. =DCount("*", "queryname")
Now i wanted that with the click of a button (or something), i wanted to change it's controlsource to =DCount("*", "queryname2") ... however, as a result i got #name instead of the correct count.
I have created a new field in a Form and named the Control Source "Active". However, when I vew in Form View there is a "0" in the field and it also does not allow me to type anything in the field.
I have a drop down list of Last name and first name.
I want this to be the only unlocked feature on the page. When a name is selected the other information is shown (the persons cc#, Exp date, phone number, email etc.)
I am almost sure its a control source, but i'm not 100% as i'm relatively new to Access..
Another issue I would like to know is how to show the Last Name, First Name in the selected drop down after its selected, currently its only showing the Last name.
I want to select JPEG's and Visio files to import and viewable in the report format. Can anyone give me an idea of how to achieve this...I have been told I need a control source...
Health-related services are not charged sales tax, but products are.
In order to have one subform to do the job (rather than one for non-taxable and one for taxable items), I'm trying to create separate running sums of taxable and not taxable items from the same query, but am not getting far. "WHERE" statements don't seem to be working in Control Source code.
Currently, there's a cboItem_Name that populates txtItem_Cost. There's txtQuantity and txtExtended_Price. All working fine. The cbo query includes whether the item is taxable or not, from a YES/NO field in the table.
I'm trying to get txtTaxable_Sub_Total to SUM[Item_Cost]*[Quantity] where [Taxable] = YES, but it ain't happenin'.......
Is there a better way to go about this, or can someone suggest correct syntax to me?
Greetings, I have a search form with a list box that displays query results. Double clicking on a row in the list box opens a detail form with the detail of the selected record. The other way to get to a detail form is by clicking on a command button that displays the detail of each of the records in the query result, one by one. The problem is that there are many more fields in the detail form than the query that is its' control source. Since the detail form displays an individual record, it seems like it should be able to go into the table and fetch the rest of the data. No?
I have created my query based on a table in order to find how many data are in the list, then I have added a textbox in one of the form and I added this line in the control source:
SELECT AcademicAudioCount.Total FROM AcademicAudioCount;
i have manually built my form in Access 02. Now i want to bind a particular control to a table - ie. when the user hits 'Submit' the record will get written to the correct tables. simple data entry
i have a table called tblShipping, in which there is a field called Goods. in the control source i have the expression: =[tblShipping]![Goods] but this is not working. in the text box (in normal view of my form) appears: "#Name?"
This is a very simple problem, and it seems to me like it should work but it doesn't. On my form, I have a field called [mindate], which looks up the earliest date from a table. I also have a field called [maxdate] which looks up the latest date in my table. These both work great. My third text box [difference] is supposed to find the difference between the two above dates in days, but it doesn't work.:eek: The control source of this [difference] text box is this:
I'm having a wee problem with a form containing a DTPicker Control. It should display the date for a given record, as defined by the control source, however instead it seems to always displays the default date instead. Does anyone know why this might be happening? I have tried removing the default date but that's not an option, and when the user loops through the records in the control source it populates all of the other controls except the dtpicker control!
Hi all. Been away for some time due to the necessity of working two full-time jobs and maintaining house and family. I'm now back to one job and have a bit of time on my hands so I'm making a little db for myself.
I keep going to the video store and renting movies that I already have so I decided to input them into a db and play with it to resharpen my dull skills.
Very small and simple. I have a query that's fed by two tables. This is what I want to feed one form that will display in datasheet view when a selection is made from a cbo on another form. Not a problem. What I want to do is have the one query and one form for two or more cbo controls, each passing a different criteria. For example, in cboGenre when Action is chosen, the datasheet view shows all the movies with that genre. Then when another is chosen, it requeries with those (the requerying part isn't a problem). Then, when PG-13 is chosen from cboRating, the datasheet shows all the movies with that rating.
I can do this when it comes to passing where conditions when opening a report, but a report isn't really the cleanest way to go.
Hope everyone has had a great year (at least as long as I've been here) and look forward to your responses and also browsing the information here again.
is it possible to set 2 control source to a textbox.i have a textbox in a form where it gets the value from subform =[Child9].[Form]![Text25] and i need to save it in a table in field INV_TOTAL
hello, I made a text box and I am trying to assign an SQL query to it.
I tried putting this in the "control source" =(SELECT 4) But it keeps telling me #Name? It's driving me completely instane!!! why can't I do an SQL query in the control source??
However its setting the value on Load - is there a way to set it as the control source so if the values change in the sunforms the mainform automatically recalculates.
I get an error if I try the above as the control source.
For the following code chk is a check box, ischecked is a yes/no field.
Dim sSQL as string sSQL = "Select [ischecked] from tbluser where userId='abc'" chk.ControlSource = sSQL chk.Requery
But when I run the following vba code, no matter ischecked is yes or no, it always shows me the checkbox is unchecked. Am I doing anything wrong? If yes how to correct it?
