I would like to get the Min + Max values of the data currently in the form, but without changing the sorting currently on the form.
So I was hoping for this, but it is not working. The data in the recordset are not sorted.
Code:
Set R = Me.RecordsetClone
R.Sort = "SendOn ASC"
R.MoveFirst
MinDate = R!SendOn
R.MoveLast
MaxDate = R!SendOn
Any other method except iterating through the entire recordset?
Hello All
I have some extensive experience with databases and even Access when used as a datastore but I have never written an app with Access as the front end so i'm very much a newbie. I am doing a friend a favor and fixing his Access app which someone created for him and he needed to add a field to it. I am hoping some of you Access Experts can help. We added the field and everything seemed to be working fine except know the main form for looking up and editing users seems to be broken. The main form is tied to a query which basically returns all the records in a table in last name order. The lookup is accomplished via an unbounded combo box. The AfterUpdate event for that combo box is below. I have added some troublshooting code to the sub below. The problem is that after the "Set rs = Me.Recordset.Clone" line, the recordset is empty. I checked by using a message box to return the recordcount. Once it gets to the "Me.Bookmark = rs.Bookmark" I get "Run Time Error '3021' No Current Record"
Any help anyone can give would be greatly appreciated.
--------BEGIN CODE SNIPPET-------------
Private Sub Combo81_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
MsgBox ("RecordCount: " & rs.RecordCount)
'testing debug
MsgBox ("Member ID Searched: " & Me![Combo81])
rs.FindFirst "[tblMembers.MemberID] = " & Str(Me![Combo81])
Me.Bookmark = rs.Bookmark
End Sub
--------END CODE SNIPPET-------------
Thanks
Hack
Hi Everyone,
I was wondering if anybody would be kind enough to help me with a problem that I have with regards to a 'Contacts Database' that I found under the Sample Database link on this site - posted by MStCyr. When I try to navigate through A-Z buttons I am presented with a dialogue box which states " the object doesn't contain the Automation object 'RecordsetClone', whatever that means??
I would like to use this sample DB in my project however it is presently unusable unless I can figure out how to clear this problem. If this is not achievable could you please point me in the right direction where I could find a similar, downloadable file.
Hopefully a more experienced forum member than I could please help me out.........
Best Regards
CarolW
I have a form with a continuous subform whose RecordSource is a predefined query. There are a number of controls on the main form which allow for various filtering on the subform / query and a command button to export the data to Excel.Because I want to export exactly whatever the user has filtered using the form, I take a clone of the subform recordset and pass that as a recordset object to my export function.
I've just noticed, however, that a couple of the fields in the Excel spreadsheet are always blank, even though the corresponding records on the subform show values? I can't figure out why; there is nothing unusual or distinctive about these fields (2 x Text and 1 x Date/Time) other than the fact that the contents don't seem to copy? I do get the field names - just no data for them?
The data is visible in the subform, and when I run the query on its own, the fields are populated in the returned dataset as well? So why aren't they included in the RecordsetClone?
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
I have a form (frmInfo) that I would like to use for 3 purposes.
1 - to enter to information (data entry mode)
2 - to view existinging information (for a single record)
3 - to edit existing information (for a single record)
There is a Main Menu that allows the user to choose, Enter New, View or Edit.
If they choose NEW: the form (frmInfo) will open for data entry
If they choose Edit or View, a form (frmSearch) with a listing of all jobs will open, the user then chooses which job to Edit or View...from here frmInfo is opened to display only the record chosen and the record will be editable or read-only depending on the choice from the Main form.
At what point should the frmInfo recordsource be defined?
I am going into the Form Design mode
Click in the subform
I then set tab order by selecting the box and moving the field up.
Looks correct and then I click okay.
The subform does not change.
I go back into design mode, click in subform and select tab order and it looks correct????
I have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
I am making a database that requires a list of all the crew people in our company. This list is not sorted by alphabetical order but by order of significance (or frequency of) crew person. This list is rather lengthy and if I add a crew person it can only be added to the bottom of the list. The only way I have been able to do this is by manually resorting and retyping the list. Is there any way to add a row in the middle of a table (like I can in Excel)??
Thanks, Joe
I have records which represent tasks and the tasks need to be scheduled. They can be ordered to some degree by sorting the table on specific fields and then by sorting parts of the table by specific fields, however, the final ordering needs to be done manually as it cannot be done by a field sort. So, how can you move records around manually? It would be like a CUT and PASTE INSERT. Thanks.... Lester
View 3 Replies View RelatedHi everyone. I need help on this one
I have a form with a subform. The subform is basically the sale history of the item on the main form (The main form shows a record and then the subform reports its history). This all works fantastic, except that on the subform, every time a new record is entered, it goes to the bottom of the previous one, so after a while, if I want to enter more history into the subform, I have to scroll down through all the older ones to get to the more recent ones.
How can I do it so that in the subform the most recent entry is always at the top and each time a new record is entered, the older ones move down one. This way the most recent records are always on display and older ones move down as they become less important. Likewise, the empty record used to enter data is also at the top!
Thanks!!
How is the easiest way to sort fields in a form. A database view sort is no problem but I can find no way to sort a fields in a form short of using a query. I am using Access 2010.
View 2 Replies View RelatedHi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
I have a list box and a series of 11 fields a user can search by. My problem is that the List Box has changed the column order.
Example: In the design wizard of the list box I had it such that the columns were in order 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
Now after I hit search the fields populate out of order... 4, 9, 2, 1, etc...
How to sort entered records in a subform by combo box. Notice combo box has integer value not text. I want to bo sroted by text visibled ASC. The code must be in Command control SORT (See pict enclosed)
View 2 Replies View RelatedI have an accdb file, holding all the tables on my server. Clients are linked to this one from their workstations. Speed is a constant issue mostly when more users are linked and I'm trying to find ways to make things faster.
Would it be possible to open a recordset when a form is loaded and set it as the form's data source? Would that make operation faster, at all?
The reason this idea came up is that it would be logical to use a small size recordset rather than large tables or queries that takes a while to load during starting form that use tables/queries with large number of records.
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function
Public Function Sort_1(SortName As String, FieldName1 As String)
DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'"
End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
I am new to the Access programing. One of our clients wants to export the record set that is being displayed on the form to excel. We are using ADODB Recordset to display the data on the form. We also have some computed columns. Is there any way that I could export the data to excel?
View 4 Replies View RelatedMy application crashes when trying to change the value of a text box in a continuous form. Here is the code:
Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim rst As Recordset
[Code].....
If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes! (This is a desktop application with tables linked to SP lists - not a web app)
[URL]
I have a form that shows records from ADODB recordset.When I try to apply filter to the underlying recordset it works all right but the form doesn't reflect the changes. It shows same rows as before filtering. In debug I can see that the recordset contains only filtered records. Me.Refresh (Recalc, Requery) doesn't work.
Code is as follows:
Dim rs As New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockOptimistic
Set Me.Recordset = rs
Sub combo_AfterUpdate()
Me.Recordset.Filter = "CompanyNo = 123"
End Sub
The form is in Continuous forms mode. I cant use DAO because the data comes from SQL server user-defined function.
I have a number of forms that are bound to recordsets as follows:
Dim rs As New adodb.Recordset
sqlQuery = "Select * from myTable"
rs.Open sqlQuery, sqlCNN, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rs
Set rs = Nothing
In Access 2003, users could open these forms and filter on basically any field by using the right click-> Text Filters functionality.
In Access 2010, this functionality appears to work (users can apply the filter and the Toggle Filter button in the ribbon shows a filter is applied), but all of the records are still visible in the form.
Any work-around that does not involve redesigning the form to be non-recordset bound?
am trying to change the tab order but have noticed that some of the fields are missing from the tab order form. I am attaching a screenshot to show which tabs are missing.
Screen Shot 2015-04-09 at 21.44.55.png
I have a problem with a cascading form, which writes back to another table. The scenario is:
There are three tables. Users, Departments & SubDepartments.
Each table has an Autonumber set as the primary key .
The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works.
For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.
The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:
On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.
The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];
The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.
Is it possible to change which subform is displayed by pressing a button, on the same form?
Each subform is based on a parameter query if that has relavence.