OK. I have a report that I want to use as a master and use with about 4 different querys. In the report properties I've bound it to a query. I've tried for ages to change the record sources with on click command bottons on another form. I think things have become complicated because there's a subreport on the report I AND a there's bunch of code to make things invisible in the on page event of the report.
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 a main form that has 10 sub forms Each sub form’s record source is link to a different Query.
It takes more then a minute to open the form, (because it’s running the query for all sub forms). So I changed the sub forms source to SELECT * FROM tblTest WHERE false;
I also changed the main form. When the button on the main form is clicked, it’s adding the following:
Me.SubMySub.Form.RecordSource = "select * from qMyQuery" Me. SubMySub.Form.Requery
However, after I close the main form, the sub form’s record source stays linked to SELECT * FROM qMyQuery;
And will take the same long time again to open the main form.
I have a navigation form that has a tabbed form on one of its tabs. The tabbed form has a 'current client' query record source and allows one to choose from a list of current clients and when a client is selected - details relating to client are displayed on various tabs on tabbed form.
I want to duplicate tabbed form and change record source to a query selecting 'exited clients' so I can see same information but for exited clients.
I have created a new tab on navigation form for my new exited clients tabbed form and changed record source by creating a new exited client query but when I change record source on exited client tabbed form it automatically changes record source of current client tabbed form to the exited client query and visa versa.
Everytime i make a report in Access, first thing i do is build a query and then use it as a record source. I try the other way, I go to create report design directly and do the drag and drop of fields.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct - SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click() If Right(strsql, 1) <> "'" Then 'check if statement was built Else strsql = strsql & ";" 'add trailing ; to statement MsgBox strsql DoCmd.OpenReport "tblincidents", acViewNormal, , strsql End If End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
How can I set the record source of a report to a saved query through VBA. I am trying to use the same report for a number of uses, all of the info on the report is the same, but the only difference is the query that the information is based on. I have this simple code below, how do I add a record source to it (if it can be done)
I have an application written in Access 2007 and packaged using the Packaging Solutions for deployment with Runtime.My problem is that I've written an updated version, and after carefully saving my original Back End away from the install site, uninstalling Runtime and the FE, installing the new FE with a blank copy of the BE having the same name and Runtime, and copying the filled BE into the same folder with the FE (replacing the linked, but empty, BE) I find that SOME of my tables in the BE are not being recognized. This isn't true of all tables. I can open some of the forms and find everything there. In other cases I get the following error message "The record source "tblName" specified on this form or report does not exist."
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database ' Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset) ' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done 2)If it can be done, how can I do it?
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.
If the data source changes for an Access database, do I need to recreate all the tables, queries, and reports? I am having quite a time with this issue. Thanks for any assistance you can provide.
hi there first to start of im dutch so chances are you wont understand everything of what im saying;).
For an assignment for school i have too make an acces database. im doing pretty fine except a couple of things. the assignment is too make an database for a hotel. i made 3 tables 1 for customer data 1 for the booking 1 for the appartment data. now im making a booking form. but i ran in to this problem: the customer data table has customer number as main key. if i make a form all the coloms customername, customer adress, customer streetname, ect get their data from looking up what the customer number is and then getting it from the right colum. but on the booking form i need to have the data for the companions who are staying at the hotel with the customer too. the companion has the same coloms as the customer the companions are staying at the hotel too they too are registert in the customer table. but if i try to copy the customer coloms like customername, customer adress , i dont know how too make it that they look up the companion number and not the customer number.
thanks in advance for the reply's, if anything is unclear please say so kasper
I have a linked table and want to change it's data source to another table entirely -- ie: different file name.
Can this be done with linked tables, or can you just link to another file with the same name but in another directory? If linked tables have this limitation, how would you accomplish what I'm trying to do. I want to keep the same name for the linked table.
Ultimately, I want to populate a listbox with the different files in a directory and change tblSource to point at the chosen data source.
I have an Access 2013 database that includes among its tables 4 externally linked ODBC tables in a SQLServer DB. They are linked tables, not imported. There are also relationships between these tables for some of the queries and reports in the Access DB. I need to make a copy of the database and change the source of the ODBC links to a different SQLServer DB. I have not been able to figure out how to do that without rebuilding the links with a new source definition created from scratch.
I have a Form with a combobox and 3 checkboxes now i want to change the Rowsource of the Combobox by clicking on the checkboxes. When i click the Checkbox the Rowsource gets Changed as i want it but i can not use the new Values cause its giving me the error "The value you entered is not valid for this field"
The Funny thing is its always bound to the type of data i choose first. When i first select the Week i can select only Weeks (17,18,19 etc) When i choose Day first it only accepts Dates (01.01.2014 etc)There is no Control Source set . And the Bound Column for all Sources is 1
Here the Code
DD_Zeitraum is the Combobox CB_Day CB_Week CB_Month are the Checkboxes
Code: Private Sub CB_Day_Click() If Me.CB_Day.Value = -1 Then Me.CB_Month = 0 Me.CB_Week = 0 Me.DD_Zeitraum = "" Me.DD_Zeitraum.RowSource = "" Me.DD_Zeitraum.RowSource = Dist_Datum Else Me.CB_Day.Value = -1 End If End Sub
Suppose I have a front end and backend (tables) database which I want to deploye. Currently Access tries to find the backend in the same absolute location (rather than relative position). This causes a problem as drive letters change and sometimes I would want it in a specific location on my computer and another place on deployed computer. Is there anyway to change source of linked tables after compiling to ACCDE
I have a tabbed form in a navigation form with a chart on it. The records source of the chart is a query. The query runs when you click the tab and takes a long time. I changed the Row Source of chart to "" and that eliminates the query running on form load. I've read many posts on changing that row source when a command button is clicked. I tried
in the onclick event of the command button. This doesn't work. I've tried many variations of the syntax. I don't know if I have to tell the query to run after the row source is changed.
On a side note, the query criteria is based on beginning and end dates entered into text boxes on the form. This all works if the query loads when the form is opened.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
The following code works to change the column header name for a listbox in form view when the data source is a local table, but not when the data comes from a sharepoint list.
Code: sqlstatement = "SELECT ID, PONum as [PO Number], ActDate as [Date], VendorName as [Vendor Name], Service, BuildingNumber as [Building Number], ReservationDescription as [Description], POAmount as [Amount], QuoteType as [Type of Quote], Comments" & _ " FROM ActivityLog" & _ " WHERE (Activity = 'AcceptReservation') AND (PSCName = '" & Me.PSCCombo4.Column(0) & "')" & _ " ORDER BY ActDate;" 'MsgBox sqlstatement Me.EditPOListBox.RowSource = sqlstatement sqlstatement = ""
Also it appears that there is no such thing as a caption property for a sharepoint list column.
I have o form based on a query that I can search in for last name etc. And then I have a button for opening a report for the person that displays.
Here is the problem now: If I have two persons with the same last name I get a report of several pages also showing the person that is not displayed in the form but have the same last name. What can I do in the query just to get the person only on display showing on the form? ID is the primary key
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.
