Modules & VBA :: Setting Subdata Sheetname Property?
Aug 14, 2013
I am opening a query from a docmd.openquery action in a button... however, I would like to set the name of a sub query in VBA so that I can have one "main" query open with different sub queries. My main query is a "member roster" with basic person information, and the sub queries (linked on the person's id number) contain various other types of information.I have three different variations on the sub query, and I would like to reuse the main query and just change the sub query in code. This doesn't work.
Code:
Private Sub cmdMembershipRoster_Click()
DoCmd.Openquery "Rpt_MemberRoster", , subdatasheetname = "rpt_MemberHistory"
View Replies
ADVERTISEMENT
Dec 15, 2014
I have a report, on a control tab, on a main form.
On the form are two buttons: one to show all items, and one to filter them based on a boolean field called showitem.
The buttons work with the code below.
What I want to do but cannot seem to figure out is to have the report default to no filter.
The bound query has no criteria.
I'm trying to set the filter property via the on open or on load event and even if I isolate the report, cannot seem to reach it.
Code:
Private Sub b_hide_items_Click()
Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem <> 1"
Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = True
Me.Profile_Timeline_wNotes_subreport.Requery
End Sub
Private Sub b_show_all_Click()
Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem = 0"
Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = False
Me.Profile_Timeline_wNotes_subreport.Requery
End Sub
View 14 Replies
View Related
Feb 5, 2014
I tried to dynamically set image OnClick property to function with couple of attributes. Everything works well, but... it launches the function automatically without any mouse click! What is causing this? I want to execute the function only on the result of mouse click.
Piece of code:
Forms!Form1.Controls("Image" & i).OnClick = fclick(PiltID)
Private Function fclick(u)
DoCmd.OpenForm "Form2"
Forms!Form2.Image0.Picture = u
End Function
View 6 Replies
View Related
Dec 4, 2004
I want to add a column to an existing table using the sql statement Alter table. The new column needs to be a yes/no type. The following code almost works but it only sets the column to a general logical type not specifically the yes/no check box .
DoCmd.RunSQL "Alter Table TblIssues Add [" & Issue & "] logical;"
Any ideas?
View 4 Replies
View Related
Aug 19, 2005
Ok, here is my question. I am just a little rusty since it has been a while since I have been in Access.
So I have a option group: option 1, option 2, option 3
If option 1 is selected I want textbox 1 visible, but textbox 2 and 3 not visible.
If option 2 is selected I want textbox 2 visible, but textbox 1 and 3 not visible.
If option 3 is selected I want textbox 3 visible, but textbox 1 and 2 not visible.
When the form first opens, all textboxes are not visible. What is the correct way to do this: code, macro, etc? and where should I place it? Should it be in the Afterupdate property of the option group?
Thanks!!
View 1 Replies
View Related
Aug 16, 2007
I dont' know what happend but now when i tried to add something to one of my subforms i get this error
The LinkMasterFields property setting has produced this error:'The object doesn't contain the Automation object'Claims Header."
i had it working not sure when it got screwed up.
i'm freaking out. i have to get this working correctly.
EDIT:
just to make sure it's ok to do this.
my subform is running from a table that has my uid from my main form claim_id and i have it linked from those fields.
currently the claim_id i'm looking to add to isn't in the table that my subform runs off of.
View 3 Replies
View Related
Jan 11, 2005
ERR: The expression AfterUpdate you entered as event property setting produce the following error: Return without GoSub....
can someone help me...why..b'coz b4 this i'm using the same coding but it can work for another several form....
View 3 Replies
View Related
Nov 24, 2014
Code:
The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name
Now this is the error message that I am constantly getting from any command button I hit on a certain form. Here is the code of the form.
Option Compare Database
Option Explicit
Public inputCSV As String, ORG As String
Private Sub CopyToTableBt_Click()
Debug.Print "Sub Execute calling ImportCSVForConfederation inputCSV="; inputCSV; " ORG="; ORG
ImportCSVForConfederation Me.CSVs, ORG
[Code] ....
I changed the names of the buttons, reconstructed the code under those names, went to the modules and changed names, made sure that a sub o function name is not duplicated in the project... But helas the error is still there. It used to work and suddenly does not work.
View 2 Replies
View Related
Nov 16, 2013
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
View 2 Replies
View Related
Feb 8, 2008
Hello!
This is my first time here. I am trying to perform a operation but not sure how to do it. Let me try to explain.
I have Access 2000 on my Vista computer and I have a Access database with patient medical records listed in multiple columns. I can sort them with no problems BUT what I need to do is go into the subdata sheet of each patient and do a sort out on one of the columns. My question is, is there a way that I can sort a specific column in my subdata sheet either by the checked box or no checked box? What I am having to do is go through each patient and delete each session that does not have a check mark and that is very time consuming. There has to be a quicker way of sorting and deleting what I need from that column.
I hope I explained myself ok...
Jason
View 2 Replies
View Related
Jan 5, 2014
Is there some way to make subdatashheets default to remove/off?
Thought options/datasheet/ might have a little button that defaults subdatasheets to off but to no avail.
Using Access 2010
View 3 Replies
View Related
Apr 10, 2014
I am studying a book trying to learn vba and in the book it says, "To clear the text box of all contents, simply assign an empty string, also known as empty quotes, to the text box's Value property.
Example:
Code:
me.text1.value = ""
where would I actually put this line of code since I cant seem to find the value property in the property sheet of the text box?
View 3 Replies
View Related
May 14, 2015
I am having a little problem with making an audit trail for a form with some unbound text controls on it.
Since .OldValue won't work on unbound text boxes, google suggested that I put the old value in the control's tag property. This is what I came up with:
Code:
Dim ctl, tbox As Control
Dim strName, strOLD, StrNew, NewTag As String
Dim NextOne As Label
For Each tbox In Me.Controls
If TypeName(tbox) = "TextBox" Then
Select Case tbox.Value
[Code] ....
The first loop is supposed to dynamically assign the tag value, with whatever the textbox value is, however it doesn't. I've asterisked out the line i think may be the issue.
View 10 Replies
View Related
Jul 11, 2013
I am starting to explore VBA and Modules. I recently found a relativly simple way to add an Audit Log to a database without adding two tables for each table as outlined by Allen Browne.
I used a set of code from fontstuff.com and it works great, except when using a sub form. I belive that it is most likely because the use of the Tag property looks at the subform as a control but it holds no value. I am thinking the VBA needs to be altered or the BeforeUpdate command need to specify what form to look in.
The Before Update code is
Code:
Public Sub Form_BeforeUpdate(Cancel As Integer)
Code:
If Me.NewRecord Then
Call AuditChanges("SubForm_ID", "NEW")
Else
Call AuditChanges("SubForm_ID", "EDIT")
End If
End Sub
AuditChanges is the Module Event name and CustomerID is the unique identifer for a Subform.
View 1 Replies
View Related
Sep 13, 2014
I want to retrieve and assign values of a control's properties by naming the properties. So for example:
Dim myHeight As Long
Dim myPropertName As String
myPropertyName="Height"
myHeight = 315
Forms!Myform.MyTextBox.Height = myHeight
I can retrieve the value using Eval:
Debug.Print Eval("Forms!MyForm.MyTextBox." & myPropertyName ) gives 315
How can I do the inverse operation, ie.assign the value of a property whose name I have in a string MyPropertyName? I mean other than a brute-force approach with a huge Case.
View 3 Replies
View Related
Feb 15, 2014
I have a form with 3 levels.
main - [Welders fm],
2nd level - [PipingQualificationTickets tbl Subform]
3rd level - [WelderWps fm] This is a pop up form but is related to the 2nd level formVBA Error
I have a dropdown box [Wps] (3rd Level PopUp) with an after update function to lookup a query [DLookUpWeldWPS Qy] and insert the text into [Wps(s)QualifiedFor] on the 2 level [PipingQualificationTickets tbl Subform]
Here is the code,
Forms![Welders fm]![PipingQualificationTickets tbl Subform].Form![Wps(s)] = DLookup("[Wps(s)QualifiedFor]", "[DLookUpWeldWPS Qy]", "[WelderWps tbl]![QualificationID] = " & Nz(Forms![Welders fm]![PipingQualificationTickets tbl Subform].Form![WelderWps fm]![QualificationID], 0))
I am getting an error - You entered an expression that has an invalid reference to the property form/report
The names are all correct to the forms.
View 2 Replies
View Related
Jun 19, 2015
I have a split database with a frontend that I semi-regularly update (when one of my users finds a bug I never would have thought for looking for). On my menu screen i have a text box that says: "Last Updated: ##/##/####".
At the moment I manually update the date every time I update the frontend.
Is there a way I can use the DateModifed property of the .mdb file for the frontend as the date in my textbox using VBA?
View 5 Replies
View Related
Feb 26, 2014
I have a form with a subform where I want to select (via button) which format to open the form in. Opening the form is simple, DoCmd.OpenForm (FormName),acFormDS to open in datasheet, however, the subform opens in default view (single form). Can you programmatically set the property on the subform to change the Default View from Single Form to Datasheet and back?
View 3 Replies
View Related
Feb 1, 2014
I cant change the table property to number only so is there an afterupdate code I could run on a field and check for alphanumartic charters ?
View 1 Replies
View Related
Dec 4, 2013
Access 2007
In a table I have 2 fields:
D1 , Date/Time , DefaultValue: Date()
D2 , Date/Time , DefaultValue: Date()+1
In a form (bounded to this table) I have, of course, 2 controls bounded to this fields:
txtD1 and txtD2
If the user change the value in txtD1 (using the Date picker) I like to change the DefaultValue for txtD1 to the new date and the DefaultValue for txtD2 to the new date + 1 day. Something like this:
Code:
Private Sub txtD1_AfterUpdate()
Me.txtD1.DefaultValue = Me.txtD1
Me.txtD2.DefaultValue = Me.txtD1 + 1 day
End Sub
I tried whatever crossed my mind... with the same "result" : #Name?
My Regional settings:
Short date format: dd.MM.yyyy
Date separator: Dot
Simple: 05.12.2013
View 14 Replies
View Related
Jan 21, 2015
I have the following simple variable assignment...
Dim PolRef As String
Let PolRef = Me.Policy_Ref_Num
But when I run my code I get "Invalid Use of Null" and PolRef is set to ""
This field is populated however, so I'm confused to why I cant set my variable to it.
If I don't use a variable and simply have let XXX = me.Me.Policy_Ref_Num then it still wont return the value from the field.
View 5 Replies
View Related
Jun 26, 2013
I have a button in a form that brings the user to a table to add a new record. I want the feild of "dates" to be automaticlly populated with a value that is 7 days after the previous date in the field. the code i have currently is
Code:
Private Sub Command14_Click()
DoCmd.OpenTable "Cincinnati Time Sheet", acViewNormal
DoCmd.GoToRecord , , acNewRec
End Sub
View 2 Replies
View Related
Jan 13, 2015
I am new to Access and to the forum. I made a check printing system
table name = 'burgan'
form name = 'burgan cheque'
Fields = 'PV' and 'cheque' and 'Beneficiary'
I have inserted a button (Command31).The function I would like to add on clicking the button
1. it should chose the highest value in both fields (PV and cheque)
2. Create new record
3. Increment it by 1 (both fields 'PV' and 'cheque')
4. the focus should stand on 'Beneficiary' field.
View 3 Replies
View Related
Sep 24, 2013
Windows vista
access 2007
I'm populating a report with a query which pulls criteria from a form. When the 'run' button is pressed it opens the report, running the query, to filter the data. What i'm attempting (and it works if there is data present).
The data is text, which is a filename, which populates an image control. Most of my records have an image present but for the ones that don't I think I need to turn the image control's picture property to 'blank'.
I'm just now encountering problems with the records with no pictures so when i came up with this it worked with my tests which at that time only had images present....
I have two problems.
1) When I run the code as below i get Run-Time Error 2185; you can't reference a property or method for a control unless the control has the focus.
2) when i try to set the focus on the picture control in the report to see if there is text/value present i get runtime error 2478; database doesn't allow you to use this method in the current view.
I assume this is talking about me opening the report in acViewPreview mode but i thought i needed to do this so the images are displayed in the image control.
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strDBPath As String
Dim strRelativePath As String
Dim strPath As String
'rptOriginalOwnerCategoryItem!Picture.Text.SetFocus
[B]Me!Picture.SetFocus[B]
'Test to see if the record has a relative path stored
[code]....
View 4 Replies
View Related
Aug 26, 2014
I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:
Code:
Dim sFilterValue As String
Me.FilterOn = False
sBiller = Me.txtbiller.Value
sLastSource = Me.txtbiller.ControlSource
sLastSource = "[" & sLastSource & "]"
sFilterValue = sLastSource & "=" & sBiller
Me.Filter = sFilterValue
Me.FilterOn = True
sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?
View 2 Replies
View Related
Jul 28, 2013
I am using Access 2010.
I want to set up a Library File containing code that I can call from numerous access databases.
I try to make reference to the newly created Library.accdb file in Tools - References and get the error message: Name conflicts with existing module, project or object library.
I have checked the names of all of these and there does not appear to be a conflict.
View 1 Replies
View Related
