Transfering RecordSource/OrderBy Of Form To Report
Oct 17, 2006
I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:
I am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.
Code: Private Sub Command30_Click() Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere End Sub
On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.
I am using the following VBA to sort my form
Code: Me.OrderByOn = True If Me.OrderBy = "[empname] DESC" Then Me.OrderBy = "[empname] ASC" Else Me.OrderBy = "[empname] DESC" End If Me.Refresh
I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:
Code: Private Sub Command30_Click() Dim strOrder As String Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter If Me.OrderByOn Then strOrder=Me.OrderByOn
I have report in which I have to do some custom calculation in the detail section (using VBA) of the report. How do I access the fields provided by the report, which should come from the query or table fields of the query or table set in the recordsource property of the report in design view. I am able to access some of the fields but not others. Could someone explain to me how this is done.
I'm trying to have a form sorted in a particular order when it's opened.
When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.
However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.
So I tried to add the following code :
Code: Private Sub Form_Open() Me.OrderBy "employee_name ASC" Me.OrderByOn = True End Sub
I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement. permanently
I have just added a Me.Filter = "[Form1].[InDate] = #" & MyValue & "# " Me.OrderBy = "Indate DESC"
This all works fine, filters & sorts on the existing results.However this new filter & sort seems to remain no matter what I do.I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.
Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.
I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.Once I use the new me.filter it will always returned as soon an any filter on the form is applied.I definately am not setting it again anywhere. Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.
Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.
I have a tbl called table1 which has 3 fields. i have a form called form1 which list field1 for all records in table1. when i double click on field1 i want to open form2, retrieve the data from table1 using field1 as input and display the results onto form2. can this be done just using Access or do i need visual basic? i am new at Access and have not ever used visual basic. i do know java. can you tell me how to do this or suggest a book which could explain it. the access book i have does not go into this type of detail.
I've built a table and used it to build a form. One the form I have three boxes, one to enter the DoB, and two others that record the age and also put the individual into an age bracket.
In the latter two categories I have entered a formula in the Properties Box under Data & Control Source (the formulas are below) to automatically work out what the age and age bracket is when the DoB is entered. This works fine but the information isn't transferred back to the table.
Do I need to do the info in the table first and then update the form? And if so where do I enter the info in the field properties in the table design view?
I want to write Visual Basic language in the form with one button:
I have one form named "setForm" with one button and one text box: Let's set the new table "frm2", the new form "frm2"
Dim sql As String sql = "SELECT [model].* INTO frm2 FROM [model]" CurrentDb.Execute sql DoCmd.CopyObject CurrentDb.Name, "frm2", acForm, "frm1" Forms!frm2.RecordSource = "frm2"
The last statement code doesn't work: It is said that Microsoft Access cannot find the form "frm2" referred to in a macro expression or visual basic code.
But I don't know what to put for an expression. I would like to use the same form but base the RecordSource on a query called "Receiving Search by Date". :eek:
I have a query that I am using as recordsource for a form. I also have a list box that I am using to change records on the form.
My issue is I need to use the same form for similar queries. For example, I want to open the form with only "Manager1" records displayed. So I need to do a different query. I don't want to use parameters or have employees type the manager in. It needs to be just a button they click.
So my two options are - make 10 copies of the form each with their own new query recordsource for each manager, or use one form and dynamically input the recordsource before the form opens..
I want to do that -- but when I try the list box gets all screwed up. Because the list box is relying on another SELECT query to populate. I have 10 buttons each with a different recordsource query feeding into the form when I click but I get so many errors in the fields when the form opens.
I currently have a Job Management System where the Customer Order Form displays all jobs via a main form and an order details subform. This form is used for inputting new jobs and for viewing existing jobs via links from other reports such as a job schedule or customer orders report. It is always reached via a clicking of a "New Customer Order" Button where the form is filtered to new record or via double clicking on an order number in another screen and filtered via the order number.
I am now in a position where the database is growing too large and I want to archive the data in the Orders / OrderDetails Tables into Financial Years and put them in a separate backend database. However the jobs will still need to be viewed via the customer order form in order for them to be duplicated the following year.
I have created a union query of the PreviousFY and Current Orders to allow the "search jobs by customer" query to work. This works fine, but I now need to make the record source of the Customer Order form dynamic.
I have created a CurrentFY query to feed the form if it is a current job and a PrevFY query if it's from the previous FY.
I have written code to check the OrderDate against the financial year to work out which database the job is from and set recordsource according to that result. This is currently in the OnCurrent event as I also have other code there that checks details of the order to determine if fields should be editable.
However I am finding that when I parse the order data between the forms the "new record" command or the order number is not being retained or checked. If this because I don't have a recordsource set when it opens?
I tried setting the recordsource as the currentFY on open and then do the check but that didn't work.
I've got a DB, which has 3 forms ( all 'under' 3 navigation buttons/tabs ). The main form ( Client's form ) is set to show first, and currently has a recordsource of about 1350 records. I think the size of the recordsource, and the fact it 'loads' the other two forms all 'in one go', is slowing it down ( between 5 and 10 secs. to load ).
I read I should set the Visible properties of forms, not shown immediately, to false, only setting them to True in the On_Load event - when Tab is selected. Ideal I thought. However, I can't find this property in the Form's property sheet.
In Access 2010, I'm writing VBA code that assigns an ADO dataset as the recordsource for a form. The query itself varies and is constructed from a search term typed in a textbox by the user. The code below works perfectly until the dataset is empty, when no records satisfied the WHERE criteria. In that case, there is a very long pause (presumably a timeout?) and I eventually get the error message "ODBC call failed." I tried to get around this by testing the number of records in the dataset (see toward the end) before assigning it, but it makes no difference in the behavior, so I'm thinking the actual assignment isn't the issue ... something else is.
If it matters, the "FROM qry_beneficial_owners" in this case is predefined query local to my Access db. That query is based on a linked view from a SQL server.
Code: Public Sub RunSearch(SearchTerm As String) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String Dim cols As String cols = "[acct], [acctname], [planid]"
I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.
They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.
Code: Private Sub Switch_BeforeUpdate() If Me.Rack.Value = "1" And Me.Box.Value = "1" Then Form_Add.RecordSource = "Box 1-1" ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then Form_Add.RecordSource = "Box 1-2"
I would like to populate form fields with records from a table/query so that they are bound to the recordsource and the record to be displayed can be selected from a combobox. I am hoping that in this way, any changes made are propagated to the original table.
I have created a combobox from which the primary key can be selected and added all the fields from the table appearing on this form as values.
In my Change event for the combobox I have this code:
Private Sub cboID_Change() Me.RecordSource = "SELECT b01_Participants.*FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));" Me.Refresh End Sub
However, I receive an 3075 error when this code runs which seems to relate to my syntax or an extra ")".
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.
ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";
Hi, I have a list box based on a quite a complex query. I would like give the user the option to dynamically sort the list box.
How can I refer to my listbox's SQL rowsource and add a sort order using VB? i.e. Private Sub CmdButtonSortByName_Click Dim strSQL as String strSQL = me.listbox.rowsource & "ORDER BY FieldName;" me.listbox.rowsource = strSQL
I am aware of how I could do this if my SQL was in VB i.e.:
Dim strSQL as String Dim strSortOrder as String Dim optSort as Integer
Select Case optSort.Value Case 1 strSortOrder = "ORDER BY LastName;" Case 2 strSortOrder = "ORDER BY FirstName;" End Select
In my db I have a report that I would like to sort by different fields. I tried coding this using option buttons, a command button, and the "Case" statement. While putting in the OrderBy portion of the code I keep getting errors in relation to the syntax. I'm not sure how long I worked with it and got absolutely nowhere. Can someone clue me in please? Here's my code:
Private Sub Command14_Click() Select Case Me!Frame5.Value Case 1 'Sort by Military Occupational Specialty (MOS) ascending DoCmd.OpenReport ("Report1") OrderBy MOS
Case 2 'Sort by Military Occupational Specialty (MOS) descending DoCmd.OpenReport ("Report1") OrderBy MOS, DESC
Case Else MsgBox ("Please select an option before attempting to open the report.")
Hi all, Found a weird problem when editing records in continuous form view (and datasheet view) when a particular memo field is being used by OrderBy to sort the data. When i have just created a new record and tab to the next record, the record i was just in jumps to the top of the records, but in its place is an exact copy of the record above. Very confusing, and looks like a redraw bug but it happens on all PCs.
What happens is ... (letters relate to records going down the screen)
A B C D ....new record being entered then <TAB> and ...
D ....has jumped up here A B C C ....duplicate of C - but duplicate record never created in the table E ....new record
When i quit the form and come back in, everything is ok
As far as i know, OrderBy only takes effect if you refresh the form (which i'm not doing). It works fine if i take off OrderBy, or if i use another field with OrderBy. So is there some problem using OrderBy with a memo field, and how is this making my records jump about?
This may have been covered already, and if it was I appologize. Searched for it but couldnt find it.
Any how, I have a large table of records (problem is its growing). It lists customers and dates. My table has way to many records, so i need to condense it a little bit. What i need to do is transfer contracts that have been fulfilled (by date) transfer to another table that contains records of contracts that have been fulfilled. I want to do this by a push of a button, automatically transferring any records that the date has passed to the pastcontract table.
I am currently studying for my A levels and need some help with microsoft access. What I want to do is transfer information from one table in my database to another. When it transfers however, I want it to alter under a certain validation. This is what i want to do;
Admission numberArt 011649 011650 F 011651 B 011652 C 011653
This is an example of the information. The letters regard to a GCSE result. I want the grade to be transfered to a table called "point score". Each grade has a value, A* = 8 points, A = 7 points, B = 6 points and so on. When the data is transfered to the point score, I want it to just state the point score and not the grade. Can anyone help?
I have three list boxes. Two of them has data from a database in them. I want to chose one piece of data from one index, and choose two pieces of data from the other list, and click a button to send it to another list(database, that would later be exported to excel). Does anyone know how I would go about doing this?
