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.
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent FROM LocationStatus WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.
On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.
However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.
In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...
I have a subform which has loads of check boxes for each record but i want to suppress certain boxes depending on criteria in each record. I think i need to do a For..Each statement but, as my user name suggests, I'm buggered!.
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)?
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
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 ?)
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
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.
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database Dim td As DAO.TableDef Set db = CurrentDb For Each td In db.TableDefs If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11) GoTo jumpout Next jumpout: MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName]) objWord.Application.Activate SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database Dim td As DAO.TableDef Set db = CurrentDb For Each td In db.TableDefs If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11) GoTo jumpout Next jumpout: Dim WordApp As Word.Application Set WordApp = CreateObject("Word.Application") WordApp.Visible = True WordApp.Application.Activate MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc" SendKeys MessageE Set WordApp = Nothing GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
I am trying to build a database for my digital photo collection. (having pretty much gven up on the packages I see in the market)
Since most of the images reside on CD's and DVD's, I want to create and store thumbnails and pointers (ie. location fully qualified name), rather than a copy of the full image.
1. How do I define the fields in the table for the thumbnail, and the pointer? 2. How can I use the pointer value to retrieve the full image?
I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?
I'm very confused. I have two tables in my db that should be the same except for columns(I copied one table from the other). In each I have a "default" entry as the first record in the table just to prevent my select queries from erroring out if they don't find a match for the criteria. When I use the following statement, Code:SQL="SELECT ID, Name, Picture FROM background WHERE Name ='" & Fname & "' OR Name='Default'" it returns the default value as the record, not the match for "Name", even though there is one(when I remove the OR Name='Default' it pulls the record).
When I use this query for the other table from the same .asp page, Code:SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" it returns the record where "Technician" matches even though it is below "default" in the table.
I want the query to return the "default" record only if there is NO match for the first criteria, which it does in the second case, but not the first. Is there some way to force this? Thanks in advance for any assistance provided! -Chris Gordon
Why I getting an error when trying to run the below code ? If I take out
Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .
add "And" to
If Me.ClientNameList = rs.Fields("[Client Name]") . . .
I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.
The code follows:
Private Sub ClientNameList_Click() Dim db As Database Dim rs As Recordset Dim i As Integer Set db = CurrentDb Set rs = db.OpenRecordset("Progress Tracking")
Our organization (a University) has been engaged in becoming "paperless."
As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.
The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.
At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).
My question is this: has anyone in this forum ever tried to implement such a solution with Access?
Any advice or insight would be very much appreciated.
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.
Dim rs as object Set rs = Me!RecordSet.Clone rs.Find first "[Student_ID]='" & Me![Combo134] & "'" Me.Bokmark=rs.Bookmark
If Me! Major_CD = "F16" or "616" or "611" msgbox "MUST COMPLETE SURVEY" End if
End sub
A student ID number is entered into a combobox and it returns name, Major_CD(Major Code) and Degree type. If the major code is "F16" or "616", a message is displayed.
How do I suppress this messagebox the the student has a different major code? In other words, there are students in the database with other major codes that are 111, 121, 363. I would like for the other major codes to NOT display a message box. Right now, it displays a messagebox for everyone.
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.
I have four fields in a Table. They are all four date fields. I want to do a query and see information in all four fields, but I only want to see the information if it is before a certain date. So far I have tried this:
<Date().
This works but it shows all four fields even if it is after this date. How can I suppress the fields to appear blank if the date is not before today's date?
I have a report that is linked to a query... lets say my query returns no records the report comes up with a whole bunch of #Errors on it. How do i prevent this from happening?
i am receiving an access-generated message "Data has been changed".. All of the processing works correctly.. but i get this message. i receive this message on an edit form of mine.. this form has a continious subform.. fields are account and amount.. if i wipe out the first one (account/amount).. that is when i receive this message.. very annoying.. but only happens when i wipe out the FIRST one.. if i wipe out one in the middle or end.. then i get no message.. i searched microsoft, here, and googled it and came up with nothing.. anyone have any ideas?.. thanks!
We use an Access database to review multiple banking regulations on a sample of loans each month. I have reports set up to show the findings of different requirements. Each field is populated with a "Yes" "No" "N/A" or is left blank depending on the loan characteristics.
My queries pull regulatory errors if any of the fields = "No" and the reports show all regulatory requirements for that loan. For example, if the loan has real estate as collateral and no flood determination was ran prior to loan closing, they would select "No" from the combobox and it populates the field. All questions dealing with the flood laws populate on the report.
[U]If I want to show ONLY the fields that say "No" with the corresponding label, how do I get all fields that say "Yes" "N/A" or are blank to disappear, the corresponding lable to disappear, and the data to "shrink up" so there is not a lot of blank space?
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 ?
