SubForm RecordSet Properties
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 Replies
ADVERTISEMENT
Jan 25, 2014
Is it possible to retrieve the format of a field when looping through recordset.
e.g you can get the field type by using
Quote:
rs.Fields(y).Type
But the type for decimal or percentage is the same. It is the format that is different
I need to be able to see what is formatted as a percentage and what is formatted as fixed.
View 7 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
Feb 9, 2005
Am sure this is an easy question but I'm missing something:
How in the heck are you supposed to get to the subform properties box. The only way I know is to click on the very thin border area surrounding the form and then try and right click your way to properties. There has to be an easier way. Usually I have to fight my way to the properties box as the area loses focus by the time I right click and I have to reselect and try and right click again...ARGH!
View 3 Replies
View Related
Mar 9, 2015
I am trying to get the properties of enabled and locked set in a continuious subform depending on a yes/no field in each record. I have tried using the answer in thread 160062, but this only works when it is not a subform (unless I have done something stupid!)If the main form is frmMain and the continuious subform is frmSub, where am I going wrong with this code which is in the subforms Class Objects?
Private Sub Form_Current() If Me.MaterialIssue = True Then CRMSignOffDate.Enabled = True
CRMSignOffBy.Enabled = True CRMSignOffDate.Locked = False
CRMSignOffBy.Locked = False Else CRMSignOffDate.Enabled = False
CRMSignOffBy.Enabled = False CRMSignOffDate.Locked = True
CRMSignOffBy.Locked = True End IfEnd Submany.
View 3 Replies
View Related
Jan 13, 2015
I have a form that uses a listbox. I can select multiple items and then update a subform. When I press a button with VBA and use the filter property on the subform for the first time, nothing happens. When I press the button a second time, it does - and it works thereafter with different selections. It only happens that first time, after loading the form and pressing the button for the first time.
I have tested that the list items are accessible via the vba variables and stepping through my code and the problem starts with the filter properties.
the statement:
Me.sbfrmCluster.Form.FilterOn = True
Me.sbfrmCluster.Form.Filter = strFilter
is not switched on first time (I have tried a requery and refresh statement after this). When I step through the code the first time, the FilterOn property remains false. A second button press and it is fine.
View 2 Replies
View Related
Feb 2, 2014
I have a Form with embedded Subform, everything works well as far as moving through records. Scrolling through records on main table via Form brings up correct records of child table on Subform, so the link between the 2 tables is done properly.
I use this logic to lock/unlock a field in the subform.
Code:
Private Sub Form_Current()
If IsNull(Me.Parent!Spec2) Then
Me!Value2.BorderStyle = Transparent
Me!Value2.SpecialEffect = Flat
Me!Value2.BackStyle = Transparent
[Code] ...
I confirmed with debug that the logic does set the properties properly. They are updated once and that's that. Any further passes through the logic do not update on the form. They do pass through correctly, changes are just ignored.
It doesn't matter if I use the logic from the Form and refer down, or in the Subform and refer up, the Subform never gets updated. I tried Repaint, Refresh, Requery, nothing works. I cannot count the permutations I've tried.
View 9 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
Dec 8, 2006
Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
View 5 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
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 14 Replies
View Related
Nov 5, 2014
I am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.
C
'Add Wastage value to flooring area section
Private Sub Wastage_AfterUpdate()
Dim rsFlArea As DAO.Recordset
Dim Wastage As Double
Dim Item As String
Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone
[Code] .....
View 6 Replies
View Related
Apr 27, 2007
Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?
Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?
For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.
Premy
View 14 Replies
View Related
Jul 7, 2013
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
View 1 Replies
View Related
Aug 17, 2007
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records:
userID / Date
u1 / d1
u1 / d2
u2 / d1
u2 / d2
u3 / d2
...
1st Select Query (where Date = d1):
u1 / d1
u2 / d1
Desired 2nd Query based off 1st Query (where ?):
u1 / d1
u1 / d2
u2 / d1
u2 / d2
View 3 Replies
View Related
Jan 21, 2015
I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.
sql example below. Which is the Access Sql in the Query
sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC" & _
"SET tbl2.Name = tbl1!Name" & _
"WHERE (((tbl2.Name) Is Null));"
Example.
Table1
Field1.Names = John
Table2
Field1.Names = "Need to UPDATE the name 'John' here"
I tried the Recordset .EDIT but I couldn't get it to work using two tables.
View 4 Replies
View Related
Sep 13, 2006
Hi,
I have created a sub form and wanted to make it's background transparent and also be able to enter new data and change it on the form. I sat Data entry = Yes, but I still can't enter any data. And I can't find the Back Style property at all.
Any help how to do that will be very much appreciated.
B
View 1 Replies
View Related
Mar 27, 2006
I was doing my assignment with the table in access, and my sister came up to me and ask me “why it is important to check the properties of each field in a table before developing a form based on that table.” Can anyone help me and answer this, because I don’t know how to answer her question.:confused: :confused::confused:
View 1 Replies
View Related
Mar 14, 2005
hi all,
i m referencing a control like me!<controlname> in my code.
but in 1 screen its not giving me option after me!<ctrlname>.(only value property).
but in other screen i m able to access the properties.
So what could be the reason for this.I particularly want to access the enabled property to set it dynamically.
Thanks
View 4 Replies
View Related
May 2, 2006
How could I implement a feature which determines if something is at least 7 days before a booking at a theatre, but no more than 2 months. For example, something like... If =Now() - 7 Then... Elseif =Now() Then...
I don't really know how to go about it.
Hirer Date is when the booking is to be held (e.g., 12/01/2006) and Booking ID is when it was booked over the phone. (121205/M = 12/12/05 in the Morning) If someone could help me I'd appreciate it greatly. :D
View 2 Replies
View Related
Sep 15, 2004
Hi everybody!
Is it possible to set the properties for an access database not to confirm the changes when running an update query. I have done it under tools/properties etc. but then I make it in access in general. I want it to apply on one singel access database.
Anyone?
eroness
View 2 Replies
View Related
Apr 19, 2006
ciao people
i've been looking around for days, hope u can solve my problem!
my form is made up of several combo boxes
i wanna write a code which after a selection in any combo box sets the properties of the latter as follow: enabled NO and locked YES (just the opposite of the default value)
can u please help me?
View 2 Replies
View Related
