General :: Subform Recordset Clone / Certain Fields Blank
Jul 11, 2014
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?
View Replies
ADVERTISEMENT
Oct 11, 2005
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
View 14 Replies
View Related
Mar 4, 2008
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
View 8 Replies
View Related
Sep 27, 2013
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?
View 3 Replies
View Related
Oct 4, 2006
Hi,
Overview:
Ok so i have document database, and i have a number of forms these forms have a tab control layout on them. There is a 'Search' tab, a 'Add' tab, and a 'Edit' tab. Now what i have is a edit log table linked with the documents table on two seperate subforms on the 'Search' page. They are linked by the 'Document Number' which is not the primary key. The 'Add' page is linked to the document subform and the 'Edit' page is linked to the Log subform.
Aim:
My aim is to have a changes log for each document so that when i select a document in the document subform that the all the changes made to that document are shown on the log subform.
Problem:
When i open the 'Search' tab there are somtimes more than one record with the same 'document number' in the log subform. This is normal as users may update the same document more than once causing there to be 2 or more of the same document number listed under the log subform; but... this also causes the document subform to display a duplicate of the document with that 'document number'.
Proposed Solution:
I dont know if there is a way but i was wondering if there is a piece of code that will automaticly hide any records that have the same document number on the document subform. Or if there is a better way please tell me.
Thanks for taking the time to read this, i hope i havent wasted your time by asking a simple and stupid question.
,Leon
View 3 Replies
View Related
Aug 17, 2007
ok, i have a subform and when i'm on the last field in the form and hit tab it clears the form, BUT if i go off that record and come back to it the datas there.
thanx
View 1 Replies
View Related
Sep 7, 2013
I have a [Order Central table] that compiles products from four different categories that employees can order from.
Employees using the database will select from four categories on buttons which opens up the order form EX: [Category 1 form].
They enter their order info on [Category 1 form] into a subform sourced from [Category 1 Table] called [Category 1 subform].
Is there a way that they could select the "Category 1" button, which would run a macro opening [Category 1 form] with the [Category 1 subform] having a where condition of 1=0 (or just so that the subform appears blank)?
View 7 Replies
View Related
Dec 15, 2012
I have a Database with 2 new fields
DB = ResolvedDateReport
Linked Tables to CSv File = tbl_ResolvedDateReport
CSV files holds the two new fields called.
Owner and Owner Name
What do I need to do to if the fields are blank to uplaod the data into the DB.
Have tried multi times and have failed.
Incident ID+ is the primary key field.
Here is my attempt for one field !!
UPDATE ResolvedDateReport RIGHT JOIN tbl_ResolvedDateReport ON ResolvedDateReport.[Incident ID+] = tbl_ResolvedDateReport.[Incident ID+] SET tbl_ResolvedDateReport.Owner = [ResolvedDateReport].[Owner];
Then I can update my pivot tables from Access to show the new fields with data.
View 1 Replies
View Related
Nov 29, 2012
I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?
View 2 Replies
View Related
Aug 14, 2015
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
View 4 Replies
View Related
Oct 11, 2013
Below is a snippet of the code that I use, the user is promoters for a week, this has always worked and still does for any number less than 245. I dont know if 246 will work yet as I dont have that data.
Is there anything that would cause an integer with value 245 not to work? The query does still return values when I run it manually with value 245 and the strings for the query are all correct, hence why it works perfectly until you try and run it for week 245.
Code:
' Excel Objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range
[Code] .....
View 7 Replies
View Related
May 29, 2015
I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.
In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.
View 14 Replies
View Related
Feb 5, 2005
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.
I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)
I can make the form change using the following code:
Private Sub Notes_DblClick(Cancel As Integer)
Me.Parent.subformhere.SourceObject = "eventsfullrecord"
End Sub
What code do i need to make it open to the record i clicked in the summary form, rather than just opening to the first record every time?
Not very good at passing recordsets
Thank you
View 4 Replies
View Related
Jun 13, 2005
Hello to all,
What is the simplest way to create an empty table with the same characteristics (same fields number/names/properties) as an existing table and how to rename / delete a table ?
I have a table (A) with autonumber primary key, with vba code i want to copy it in an other table (B) then i want to delete (A) and rename (B) to (A) all this to renumber autonumber primary key.
Each time i open the form and quit it without enter data i lost a number so sometimes i want to re-organize the table to renumber and compress autonumber.
Thanks in advance for help.
VINCENT
View 10 Replies
View Related
Nov 7, 2005
Hi.
We use an Access 97 database over our network.
We need to put it on additional PCs, but do not have current licenses for this.
Is there an Access 97 clone that we could use?
It wuld simply need to serve as the Front-End of the database and be able to connect safely to the MS Access 97 Back-End.
Russ
View 6 Replies
View Related
Mar 3, 2006
So far I had several Subforms which would open in a mainForm depending on the combo box value. My combo box values were exactly the same as subform names so that I can easily change subform.recordsource = combo.text and it would show the proper form. Now, I'm thining, instead of having all those forms, I could have only one form and just changing it's recordSource property with the sma combo box, but this time, combo.value would be the name of the SQL string that the form needs to take as it's recordSet.
What I want to know is, if this will work at all, and if it would, where do I have to set my vars...in the Form_load()?!:confused:
tnx
D
View 1 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Nov 10, 2014
I've been trying : double click a field in a subform and paste the query results into excel. I already have good code to paste in the recordset, but this recordset must reference a control value in a subform.
The query works and shows the proper results manually, but when I run the Sub it throws a "run-time error 3061: Too few parameter. Expected 1."
I can't seem to set the parameters right. Tried EVERYTHING--except the RIGHT thing.
View 4 Replies
View Related
May 8, 2015
I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;
Code:
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
v1 = Me![Today's Date].Value
v2 = Me!Insured.Value
[Code] ....
Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?
It's a many to many relationship and I've tried adding the following line to the code:
v5=Me![Endorsements Umb XS].[UmbID]
same=v5
and it works but it only copies the first record out of the set.
View 14 Replies
View Related
Sep 14, 2013
How it works: You search for a User, and then you search for a Serial.Now, what if I want to edit the table? (inside the subform, the results). I added an openRecordSet, and Parameters, but I still get the error saying that Recordset cannot be updated.Why? I opened and sent parameters but still fails.
View 2 Replies
View Related
Dec 16, 2013
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If
I am getting this error: Object doesn't support this property or method
View 1 Replies
View Related
Feb 9, 2005
I have a tabular sub form, which is just used to show the user data. It is not editable, and the user can not do anything on the form other than choose a record from the PK, and thenview the data.
How can I prevent the a blank row from automatically being added to the bottom of the subform. I only want rows with data in them to be shown.
Thanks.
View 3 Replies
View Related
Feb 22, 2006
I have a main form with many subforms. The sub forms are all continuous but one and I want the editors to be able to put in more then one record if needed.
My problem is I have my tabs set up so that when the box is empty it will tab to the next subform. When I do this, a extra blank record is automatically saved in the subform above. How do I get rid of the blank record?
Thank you in advance for any advice shared.
View 14 Replies
View Related
Jan 19, 2006
Hi - I have what maybe a relatively easy problem to solve.
I have a list of locations each with a unique id. I also have another table with location details in it. There is a relationship setup between the two tables. However in the first table I have some locations that do not have a match in the second table. Therefore that field is left blank. When create a query to show information from the two tables, any records that have a blank location match field do not get shown in the output. Is there anyway to display records with blank fields?
Thanks
Nick
View 4 Replies
View Related
Mar 21, 2006
In an Input form, how can I blank out certain fields such that the field data is shown, but grayed out, and other fields are shown normally?
View 1 Replies
View Related
Jan 30, 2006
Hi All,
A bit new to this, any help most appreciated.
I'm trying to set up a table which will have multiple fields (a recordset? is that right, if so I don't know how to set one up in access). Its for a skill set which is utilised by specific lines in a production plant.
Each line in the plant has a set of skills required. I have a skills table (SkillID and Skill), what I want to do is have a Skillset which I can then link to each line
So for example Skillset 1 with a SkillsetID will also have in that table skill 1, skill 2, skill 3, but all taken from the Skill table. However that would involve having the SkillID field numerous times in the same table, but this cannot be done?
The idea is that for each line I can link the Line table to the Skillset table and that tells you what skills are needed for that line.
I hope this makes sense.
Thanks all.
View 2 Replies
View Related
