New Form Instance - Suppress Record Retrieval?

Mar 11, 2005

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.

View Replies


ADVERTISEMENT

Latest Date Record Retrieval

Apr 17, 2008

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:

View 4 Replies View Related

Forms :: Unexpected Data Retrieval In Form

Aug 31, 2014

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...

View 5 Replies View Related

Conditionally Suppress On A Form

Oct 10, 2005

Hi all

Long time searcher, first time poster!

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!.

View 2 Replies View Related

Hwo To Refer To A Form Instance

Feb 17, 2005

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

View 1 Replies View Related

How To Open Multi Instance Form

Oct 21, 2013

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] ....

View 4 Replies View Related

Modules & VBA :: Removing Form Instance From Collection Does Not Close Form

Nov 17, 2014

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 ?)

View 2 Replies View Related

Modules & VBA :: Excel Instance Won't Close Form Access

Nov 10, 2013

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]....

View 14 Replies View Related

Modules & VBA :: Setting Focus To Non Default Instance Of A Form

Mar 21, 2014

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.

View 11 Replies View Related

Modules & VBA :: Datasheet Mode Form For Both Data Entry And Data Retrieval

Feb 9, 2015

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.

<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden

Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False

[code]...

View 3 Replies View Related

Letter Storage & Retrieval

Nov 6, 2006

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.

View 2 Replies View Related

Image Storage And Retrieval

Feb 5, 2008

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?

Any ideas and tips on where to start?

Thanks,

Roy

View 2 Replies View Related

Random Data Retrieval

Jan 30, 2006

Hi,

Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.

Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.

:confused: .

View 1 Replies View Related

Combining 2 Columns For Retrieval

Jan 11, 2008

Hi,

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?

Thanks

View 1 Replies View Related

How Do I Specify Retrieval Order Of Records

Apr 19, 2005

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

View 4 Replies View Related

Queries :: Recordset Retrieval Using If Statement (With And)

Feb 24, 2014

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")

[Code] .....

View 14 Replies View Related

Looking For Advice About Using Access As A Document Retrieval Tool

Mar 1, 2006

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.

Thanks in advance.

View 1 Replies View Related

What To Use In This Instance

Mar 21, 2005

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

View 11 Replies View Related

Suppress Messagebox

Dec 20, 2006

Hello all:

Code:

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.

Any ideas?

Many, many thanks

Dion

View 2 Replies View Related

Need Current And Next Instance Of A Job

Jul 6, 2005

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

View 2 Replies View Related

Selecting One Instance

Dec 16, 2005

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

View 2 Replies View Related

Suppress Fields In Query

May 18, 2005

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?

View 2 Replies View Related

Suppress Error On Report

Aug 24, 2006

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?

thanks

Green

View 1 Replies View Related

Suppress An Access Message??

Dec 27, 2004

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!

View 6 Replies View Related

Suppress Labels & Fields

Jul 20, 2005

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?

Thanks for all of your help!
Dradich

View 1 Replies View Related

Forms :: Allow Only One Instance Of Value In Table

Jul 31, 2014

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 ?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved