I'm sure this is simple, but I can't find it anywhere...
How do I refer to an instance of a form using its key value?
For example, I have a collection 'colForms' and two forms in that collection with the keys 'Key1' and 'Key2'. How do I refer to the instance that has Key1 in code (for exampe, to close or requery the Key1 instance of the form from another form)?
Thanks in advance,
David
dminstance
I need to open multi instance form.
1) i have a table (SQL) "ActiveForm" which contains various fields including a field called "FormName" (Varchar (30)).
2) in the access form called "Menu" i place a treeview control and when i "click" in the node, i read the table rsProcedure="SELECT .* FROM ActiveForm WHERE Active=true";
3) Dim Istanzaform As Integer
Dim dbs As Object
Dim myFrm As AccessObject
rsProcedure.MoveFirst
rsProcedure.Find "DescrizioneForm = '" & Node.Text & "'", , adSearchForward
[Code] ....
I am using the method from allen browne [URL] .... to open a form and add it to a collection and when removing it it closes. actually, usually it does work so but i have now a form which does not close until i am hitting the reset button in VBE , is there something i could check why it's not functioning as desired ?
Just to add, this form has a subForm as well (might be the cause ?)
Hi,
I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:
Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True
The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.
I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.
Any suggestions would be much appreciated.
Thanks,
Keith.
my code does the following, user selects excel file, opens it, renames sheets, basically needs first sheet to be sheet1. rest don't matter Changes the formats in column a to number and 15dp, saves the file as .xls and then links the file to the database.
Code:
Private Sub Command288_Click()
Dim s As String
Dim i As Long
[Code]....
How to set focus to a non Default instance of a form.
Environment
A2007 ADP Project
Document Window Option - Tabbed Documents
MS SQL 2012 Express DB
Windows 7 64 Bit
I using Allen Browne's method to open more that one version of form, storing each form object in a collection declared in a module. No problem there.
Now I'm trying to add a command button on an form to set focus to one of these non-default instances already open.
The form I am trying to set focus to has a the following related properties
Default View: Split Form
Pop-up: No
Modal: No
The form that has the command button on it is of the same type.
Here is the code I've tried:
'Code on Calling Form
Private Sub cmdProjectList_Click()
Dim FunctionResult As Boolean
If AppForms.GoToForm("ProjectList") = False Then
AppForms.Load_ProjectList
[Code] .....
The code compiles and executes with seemingly no problems. It finds the form loaded, then cycles though and finds the form in Forms but the SetFocus call seems to do nothing. When I run the code against a defualt instance ( one not opened using Allen Browne's method) it works fine and sets focus to it as expected.
Hi All!
Well I got a problem and guessing this is possible to do like this but well after few hours and staying at the same point maybe some help can be greet
I have a database witch I have 2 important field and a command for this. A field called ID with a number and another called GenerationID who contain number between 1 to 12 depending of which generation is. I got this Generation ID from a subform. What I want is this:
Depending of the number in generationID field show a different button like ID3 with generationID = 3 or ID2 if generationID = 2 etc until 12 and then other command buttn is not visible.
I don t know how to put the code but the code probably look at this:
If generationID = 3 Then
ID1.Visible = False
ID2.Visible = False
ID3.Visible = True
Else If
generationID = 2 Then
ID1.Visible = False
ID2.Visible = True
Id3.Visible = False
End If
End If
or if you have a better way all is welcome.
How can i refered to a table value through a form?
Thank you!
Hello - I have a form with a txtbox that shows a date. I need the querry to refer to that date as selection criteria. I have the following in the criteria of the query: =[forms]![frmUpdatePayments]![txtDate]. I know the problem is I need # at the beginning and at the end of the date, but how do I do that?
View 1 Replies View RelatedAn instance of a form can be opened with:
Dim frm As Form
Set frm = New Form_formname
How can this be done using a variable as the formname?
I have a main form with a continuous sub-form. On the main form I have a series of text boxes that I want to use as a makeshift status bar. The text boxes would be filled in if a value was entered into certain records on my sub-form.The problem I have is since the sub-form is continuous, how would I refer to the specific record on the sub-form that I want to check for a value?
View 6 Replies View RelatedI have Main Form is named MainFrm and it has
two Sub Forms :
- OrdersSubFrm
- DetSubFrm
I want :
if OrdersSubFrm is not contains any records DetSubFrm is not enabled
In my DB i would like to make a form that displays different calculated values based on other forms and queries like income running cost etc., but i do not know how to refer in an unbound control to value of a control in another form. I get always #names? error. However it worked when loaded that form i refered to. Is there a way to do it without loading the refered form?
View 3 Replies View RelatedThere is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form
frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
[Forms]![frmNavigation]![NavigationSubform] .[Forms]![frmAssignDwgToPkgs]![Combo15]
Hi,
I have a form(frmentry) where a user creates a record and it has a primary key that is entered manually the primary field is called "AR_Number". The user enters the details in this form and then click a button called "butapprove" and it launches another form called 'frmentryapproval'. When this second form loads up i need it to be on the record that the user was just entering, wondering what the best way is to load that record into the form.
currently I am using this code:
Private Sub butapproval_Click()
Dim appformname As String
Dim apparnumber As String
appformname = "frmentryapproval"
apparnumber = "[AR_Number]=" & Me![ARNo]
DoCmd.OpenForm appformname, , , apparnumber
End Sub
but when using that it does not work properly and the values entered on the 2nd form are not saved at all, let alone to the required record.
I have seen some things on GoToRecord & FindRecord but don't know how to use them, would the be useful in this situation?
Thanks for your help and sorry for the n00b question.
Ryan
Hi,
I have around 50 jobs on a table. These have a 'start date' of various dates in a year. A field called 'complete' indicates whether a job has been closed off or not and a field called 'frequency' determines how often the jobs recurr (in days).
I need a query (which I'll run every night) which will examine all the incomplete jobs on the table and copy all this data to new jobs and change the 'start date' to the previous 'start date' + 'frequency'.
That in itself is not the problem. My problem is how to tell Access that when the current and next instances of a job exist, not to copy it a third time.
In other words, after the query runs there should always be 2 instances of the job on the table...current and next.
Can someone please advise me what I should do?!
Thanks in advance
Hi guys,
Still trying to get used to this Access lark.
A table contains a column with numbers i.e - 1,1,3,4,2,2,2,3,4,4
If i just want a query that shows one of each i.e - 1,2,3,4
what do i need in the criteria line?
Thanks in advance
I am trying to ONLY allow One instance of a value in my table.
tblUserSecurity
admn
dev
If either of these exist (They are Yes/No) then do not allow. This is being checked via a combo box on my form using the after update event. Only One Administrator and One Developer. DCount or DLookup ?
Hi all,
I have some records containing the following data
ABC (UK)
ABC (UK) Ltd
ABC UK) Ltd
ABC UK)
I need to pull out all the data with a ) but not a ( and add a ( to the start of the word before the ).
So for example, the four examples above would change to:
ABC (UK)
ABC (UK) Ltd
ABC (UK) Ltd
ABC (UK)
I have selected the companies that need updating, and I can pull the data to the left of the ), but I don't know how to find the first instance from right to left of " ", or find the last instance from left to right of " ".
Can anyone help?
Thanks.
I'm making a database that keeps track of particular lots of ingredients going into certain recipes.
The relevant tables are
tblSupplements (SupplementID, SupplementName)
tblSupplementLots (LotID, SupplementID, ArrivalDate)
tblRecipes (RecipeID, RecipeName)
tblRecipeItems(RecipeID, SupplementID, Quantity)
tblSupplementRecipeBatches (BatchID, RecipeID, MixingDate)
tblSupplementRecipeBatchItems(BatchID,LotID)
I have the top 4 tables filled out via forms and it all works swimmingly.
I'm having a hard time coming up with a scheme for filling out the bottom two.
I would like to have a form which will display the BatchID (automatically generated), allows the user to enter the MixingDate, gives a combobox bound to RecipeID which is filled from a pulldown menu based on RecipeID and RecipeName from tblRecipes.
All these I can do, but the next step is befuddling me: based on the RecipeID, I need to make a list consisting of the varying number of supplements which comprise the given recipe and allow the user to enter the LotID for each one (preferably from a pulldown menu).
I've tried a bunch of things (subforms linked on RecipeID, listboxes, VBA update queries attached to buttons). I just can't seem to understand how to have a variable number of subrecords appear and attach a new field (LotID) to each. Perhaps my table design is awkward?
I have two problems excel instance does not close at the end and two when I try to do the looking using text it works but soon as I try using date/time it returns nothing.
Code:
Private Sub Command84_Click()
Dim objExcel As Excel.Application
On Error Resume Next
[Code].....
I use the following code to get the first and second instances of a "/" character. How to get the position of the third instance.
iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")
I just want to run a series of DoCmd.TransferSpreadsheet commands to export several queries to one Excel Workbook with seperate sheet names for each query.
However my problem is I want this to be a new instance of Excel that the user will then SaveAs after it is complete. I don't want to save it to a specific path first because the Db is on a shared drive and My Documents will have a different path for each user based on their user name. (corporate environment)
There must be some way to just have it open a new workbook without saving isn't there??
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3
AddressID Address Owner
The data is being pulled from two tables:
tblAddress
AddressID (pk)
HouseNum
Street
Apt
tblOwner
OwnerID (pk)
AddressID (fk)
LastName
FirstName
Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
Scott
what the difference between the two variations of using the New keyword to create a new instance and using the Set. I mean Code 1 would always be better because you dont have to ever use the Set keyword
1.Code:
Dim wb As New Excel.Workbook
2.Code:
Dim wb As Excel.Workbook
Set wb = New Excel.Workbook
Are there benefits and cons to both i.e. memory allocation etc..
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]
[Code] ....