Help with custom search form - should I use rec

I have a search form that retrieves records in a subform based on criteria 
entered into unbound text boxes on the main/parent form.  The subform is tied 
to a SQL recordset that contains a field called “Select”.  The field’s 
datatype is Yes/No.

On the main form I have a button called “View Selected” with the following 
code in the OnClick event:

Private Sub cmdViewSelected_Click()
On Error GoTo Err_cmdViewSelected_Click

Dim i As Integer

i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True)

If i = 0 Then
    MsgBox "No Record(s) Selected", vbInformation
    Exit Sub
End If

DoCmd.OpenForm "MainForm", , , "[Select] = " & True

Exit_cmdViewSelected_Click:
    Exit Sub

Err_cmdViewSelected_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewSelected_Click
    
End Sub  

Basically, the user has the option to select a subset of the records 
returned by the search by clicking the “Select” checkbox and then clicking 
the “View Selected” button, which opens another form (“Main Form”) that 
displays more details.

I plan to clear the “Select” field via an Update query, inserted in both the 
parent form’s OnClose event and in the above code.

DoCmd.RunSQL "UPDATE ProjectTbl SET ProjectTbl.[Select] = False WHERE 
(((ProjectTbl.[Select])=True))"

The problem is that there are a number of users using the database and I see 
the potential of one or more users checking the “Select” checkbox for 
different records and then clicking the “View Selected” button and having 
unexpected results/records returned in the detailed form (unexpected to the 
user, since he/she just expects to have the records he/she selected display 
in the detailed form).

Is there a better way to have the “View Selected” functionality within my 
search form than the way I’m doing it?  Perhaps by using the “clone” method 
to clone the recordset or by creating another workspace.  I’m just not sure 
of where to being with the coding.

Your assistance is greatly appreciated!

Thanks,

Manuel

0
Utf
4/26/2010 10:00:03 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
681 Views

Similar Articles

[PageSpeed] 3

Manuel wrote:

>I have a search form that retrieves records in a subform based on criteria 
>entered into unbound text boxes on the main/parent form.  The subform is tied 
>to a SQL recordset that contains a field called �Select�.  The field�s 
>datatype is Yes/No.
>
>On the main form I have a button called �View Selected� with the following 
>code in the OnClick event:
>
>Private Sub cmdViewSelected_Click()
>On Error GoTo Err_cmdViewSelected_Click
>
>Dim i As Integer
>
>i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True)
>
>If i = 0 Then
>    MsgBox "No Record(s) Selected", vbInformation
>    Exit Sub
>End If
>
>DoCmd.OpenForm "MainForm", , , "[Select] = " & True
>
>Exit_cmdViewSelected_Click:
>    Exit Sub
>
>Err_cmdViewSelected_Click:
>    MsgBox Err.Description
>    Resume Exit_cmdViewSelected_Click
>    
>End Sub  
>
>Basically, the user has the option to select a subset of the records 
>returned by the search by clicking the �Select� checkbox and then clicking 
>the �View Selected� button, which opens another form (�Main Form�) that 
>displays more details.
>
>I plan to clear the �Select� field via an Update query, inserted in both the 
>parent form�s OnClose event and in the above code.
>
>DoCmd.RunSQL "UPDATE ProjectTbl SET ProjectTbl.[Select] = False WHERE 
>(((ProjectTbl.[Select])=True))"
>
>The problem is that there are a number of users using the database and I see 
>the potential of one or more users checking the �Select� checkbox for 
>different records and then clicking the �View Selected� button and having 
>unexpected results/records returned in the detailed form (unexpected to the 
>user, since he/she just expects to have the records he/she selected display 
>in the detailed form).
>
>Is there a better way to have the �View Selected� functionality within my 
>search form than the way I�m doing it?  Perhaps by using the �clone� method 
>to clone the recordset or by creating another workspace.  I�m just not sure 
>of where to being with the coding.


To get different sets of selected records for different
users, the select information must be in a different place
for each user.  One way to do that is to use a temporary
database with a temprary table for this purpose.  The temp
db would need to be on the user's machine (or a private
share).  See Tony's discussion at
http://www.granite.ab.ca/access/temptables.htm

An alternative, of the form's recordset is relatively small,
is to use a collection as Albert explains in his Multi
Select Example db at
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

-- 
Marsh
MVP [MS Access]
0
Marshall
4/27/2010 2:42:22 PM
Manuel -

This is one of the few cases where you want a table in the front-end 
application.  This table would only contain two fields - the primary key of 
the table with the data you are looking at and the [Select] field.  The 
[Select] field should have a default value of False.    The [Select] field 
should not be in the back-end table.

Then right before you open the form, delete all records from this table and 
populate it with all the primary keys from the main table.  Leave the 
[Select] field out of this append query as it will default to False.  Your 
form should be based on a query that joins the local table with the back-end 
table.  The user will click on the SELECT checkboxes as you have in place, 
and can run queries or subforms based on their own selections.  You don't 
need to worry about clearing the [Select] field if you are replacing the 
records when the user opens the form.  

The only downside to this is that the front-end applications may need to be 
compacted/repaired occasionally if there are a lot of records involved.

Hope that helps!

-- 
Daryl S


"Manuel" wrote:

> I have a search form that retrieves records in a subform based on criteria 
> entered into unbound text boxes on the main/parent form.  The subform is tied 
> to a SQL recordset that contains a field called “Select”.  The field’s 
> datatype is Yes/No.
> 
> On the main form I have a button called “View Selected” with the following 
> code in the OnClick event:
> 
> Private Sub cmdViewSelected_Click()
> On Error GoTo Err_cmdViewSelected_Click
> 
> Dim i As Integer
> 
> i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True)
> 
> If i = 0 Then
>     MsgBox "No Record(s) Selected", vbInformation
>     Exit Sub
> End If
> 
> DoCmd.OpenForm "MainForm", , , "[Select] = " & True
> 
> Exit_cmdViewSelected_Click:
>     Exit Sub
> 
> Err_cmdViewSelected_Click:
>     MsgBox Err.Description
>     Resume Exit_cmdViewSelected_Click
>     
> End Sub  
> 
> Basically, the user has the option to select a subset of the records 
> returned by the search by clicking the “Select” checkbox and then clicking 
> the “View Selected” button, which opens another form (“Main Form”) that 
> displays more details.
> 
> I plan to clear the “Select” field via an Update query, inserted in both the 
> parent form’s OnClose event and in the above code.
> 
> DoCmd.RunSQL "UPDATE ProjectTbl SET ProjectTbl.[Select] = False WHERE 
> (((ProjectTbl.[Select])=True))"
> 
> The problem is that there are a number of users using the database and I see 
> the potential of one or more users checking the “Select” checkbox for 
> different records and then clicking the “View Selected” button and having 
> unexpected results/records returned in the detailed form (unexpected to the 
> user, since he/she just expects to have the records he/she selected display 
> in the detailed form).
> 
> Is there a better way to have the “View Selected” functionality within my 
> search form than the way I’m doing it?  Perhaps by using the “clone” method 
> to clone the recordset or by creating another workspace.  I’m just not sure 
> of where to being with the coding.
> 
> Your assistance is greatly appreciated!
> 
> Thanks,
> 
> Manuel
> 
0
Utf
4/27/2010 3:41:01 PM
Reply:

Similar Artilces:

Can we create custom SSRS reports for CRM Online using Visual Stud
Can we use Visual Studio (or SQL Server Business Intelligence Development Studio) to create custom reports that can be uploaded to a CRM Online deployment, or even download standard reports and modify them? I don't know how that would work because I don't know if we would have access to the SQL Server database. Hi Michael, you can add new reports into CRM Online - as when you add Reports into any Microsoft CRM Deployment, the upload rountine replaces the Connection Details in the Report with the correct ones for the CRM Deployment you are uploading into. However whilst you can...

sumifs help
I have the following formula. =SUMIFS(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) to =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

Search a string in all worksheets ?
I am searching for a string say ABC in all worksheets in Excel 97. However, it seems that it only allows me to search it in the current worksheet. Is there any suggestion ? Hi Peter............ In XL2k, I just highlighted all three sheets using Right-click on one tab and choosing Select all sheets..........then did Edit > Find > and typed my search string in the window and hit "Find next" repeatedly and it cycled through all three sheets finding the string...........is that what you're wanting to do? Vaya con Dios, Chuck, CABGx3 "Peter" <anonymous@di...

Help !
I need to create a data input screen on excel where multiple users at the same time will use them & input data. This data then needs to be stored as a database as well, where i can use it to understand trends Thank you. and the question is ...? <abrahamsaj@gmail.com> wrote in message news:1132155054.927936.191640@z14g2000cwz.googlegroups.com... >I need to create a data input screen on excel where multiple users > at the same time will use them & input data. > This data then needs to be stored as a database as well, where i can > use it to understand trends >...

Help with this thing
It was working in the window "Transactions >> Sales >> Transactions of Sales", but exactly were publishing a Quotation, which i wanna print, more nevertheless was shut up to me network, then I closed the window and it threw several messages to me of error, and from that then it was blocked the quotation that was working. My question is as I can unblock this document? ...

need to add custom entity to email selection list
We have created a custom entity called Employee which has an email address as one of its fields. We would like to be able to select an employee record while creating an email activity. How do I add this entity to the list of entities to choose from in the To field in the email activity? You can't. The list of entities you can email to are set within CRM. One option to get around this is to load employees as Contacts - with a Contact type = Employee. "Jeanenne" wrote: > We have created a custom entity called Employee which has an email address as > one of its fie...

Form records has only 1 but should have many
I have a main form with a button when click will execute: Private Sub btnCreateEmployee_Click() DoCmd.OpenForm "frmEmployee", acNormal, , , acFormEdit, acWindowNormal End Sub Most of the time when the frmEmployee opens up will show only 1 record on the record selector. I need to return to the main form and click the button again to open up the frmEmployee again to see there are many records on the record selector, any idea? Alan, Try leaving out some of the arguments. DoCmd.OpenForm "frmEmployee", acNormal This should do the trick. Also make sure there is not...

Help with Outlook Setup!!!
I have XP Professional installed and Office XP Professional. I have several users setup for kids, as well as my admin account. I want to setup a Limited Account in addition to the Admin account for myself for daily use. Am I just suppose to pick a different account name and login password and then setup my MSN Messenger and Outlook POP email with same username and password that I used to setup the Admin account? I don't need to share any contacts or anything, I just setup the admin email in order to setup Outlook. I want to input my contact info etc., into the Limited account and use ...

How do I display search terms in report?
Hi, I would like to know how to include my query parameters into my report. For example, if I search a flower database for "pink" and "blue" I'd like a report which gives me all the corresponding flowers and specifies which for search term each was found. (ie. rose- pink; lilac- blue; etc) Thanks! On the form you use to open the report, create an unbound text box called "txtParameters". Set txtparameters.visible=false Use code to fill in the value of txtparameters as the user chooses what they want a report of. Create a control in the header of your re...

HELP !!! I have a ARRAY Formula HELP !!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the numbe are anything from nothing to 10000. I want the hourly number to appea in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = $100.00 G2 Should be $100.00 2nd Hour D48 = $100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = $1...

exch 5.5 help
I am in a progress to upgrade Exchange 5.5 (on NT4) to Exhange 2k3 (on 2k3). I setup a test machine and upgrade the OS to w2k3. 1st I want to connect the 5.5 to AD, so I should install ADC. Can anyone tell me the steps? Frorestprep, domainprep, setup adc, and upgrade to exchange 2k3? If you run through the steps in the E2K3 deployment tools they will walk you through everything. -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS" with no warranties, and confers no rights. � 2004 Microsoft Corporation. Al...

Help with Formula Please 02-19-10
Need a Formula for the following: Data Table A B C D E F G H I 1 Tom A W 2 H 30 84 30 2 Peter A W 3 H 3 Nick B L 1 A 70 Columns F1:I3 from Data Table has break scores for each player. Below is the Result Table where I need to show a summary report for high breaks. I have no problem with Break as I use the Large function. I need a formula to insert in A1 and A2 to place the name for the corresponding breaks below. Result Table High Breaks A B Name Break 1 ...

Form updating on it's own!
I have a front-end / back-end situation where the tables reside on the back end machine and the forms on the front end. When I opened the form, which allows for simply data entry via some text fields, as I was typing into the first field, the form cleared! When I checked the tables, the data I had typed to that point was there... Almost like the back end PULLED the data from the other machine! Any ideas on what can cause this to happen? TIA... Dale: Yes, I know about the tab order. There are about 15 fields on this form. I was in the middle of the very first one in the tab order.....

Need help in data copying.
Hi I have an invoicing file in excel (Sheet1). I need to store the dat which is invoiced into another sheet. My Invoice Data starting from Ro 8 and column B to F (The first item is from B8-F8, second item i B9-F9). B-Item Code, C-Item Name, D-Qty, E-Price, F-Total. Once I print the invoice, I need to transfer the data to another shee (Sheet2) . When I create another invoice, the new data should be added below t the previous data in Sheet2. So that I can have all the items I sol in Sheet2. Can someone help me sending a macro for it??? I will be grateful to you. Thanks in advance Tom -...

help...help...help
I just installed Microsoft Office XP Professionaql with no problem. However, whenever I try to perform any task such as opening contacts area in order to create an entry, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Other information that might be important is that I use a pst file. The error message also occurs when I try to open the Contact folder from the folder list as well as when I try to perform any function. It was a clean install not an upgrade. Could you ...

Macro Help
Hi, I had alot of help yesterday from Jacob with the following macro, but am getting a 'run time error 13' when trying to run the macro, and i cannot see why. Any help much appreciated Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row If Range("E" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp ..Subject = "Change Password for system" & Range("A" & lngRow)...

help me #2
how to restrict entering of same values or data in excel cell Hi if you mean the 'preventing of duplicate entries' you may check the following site http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Frank Kabel Frankfurt, Germany mangesh khati wrote: > how to restrict entering of same values or data in excel > cell ...

Create and set up custom forms in OWA
Hey there! I read, that it is possible to convert custom forms to asp so that they can be used in OWA. Is there another tool than Microsoft Outlook HTML Form Converter to do the conversion-part? Do you know of an article that describes exactely how to set up such a form and where to install it etc. Like: What to do after I turned the Outlook custom form into an ActiveServerPage? Thanks for any help, Reto. ...

Pop Form Based On individual Customer
How would you assign a pop up message for an individual customer for example let say i go to the record john doe I want to assign a pop up message for that specific customer so who ever enters into that record the message will pop up automatically with notes or the user cld add notes. I have the following Customer Table Pop Up Message Table CustomerID(PK) MessageID(PK) LastName CustomerID(Lookup Customer Table) FirstName Note SS# Address1 Address2 City State Zip Phone ...

Problem with LeadTools CreateWindow inCFormView -Help !!!
Hi , I have been using LeadTools in Visual Studio 2005. I have a tabctrl and dialogs in each tab and developed using CFormView. So a tabCtrl is a child of CFromView and tab1 is a child of TabCtrl. I want to insert a LEAD control in one of the tabs .i.e, Dialog.I am unable to insert a leadcontrol but inserting a control in the view was easy.The problem is I am not able to get the HWND associated to a particular dialog which is super child of View. Can somebody help me in creating a lead control in the dialog configdlg .h LAnnotationWindow m_LAnnoWnd; FormView.cpp CMyTabCtrl m_myCtrlTab; ...

HelpProvider and HTML Help interaction
I have an application with a .chm help-file. But I have some questions about the behaviour of the help-window. I use a modal application window and I can start the help. The help windows appears but it is allways in foreground of my application window. I can set the input focus on my window, the help window becomes inactive but I cannot move my window over the help window. So I have to close the help window or minimize it or move it aside of my application window when I want to go on in my application. The other problem is when I minimize the help window and then open a dialog...

CString help
I'm looking at a website on CString Management: http://www.codeproject.com:80/string/cstringmgmt.asp In the section entitled, "CString to char * II: Using GetBuffer," the author stresses calling ReleaseBuffer after calling GetBuffer. Is this always necessary? I often use CString::GetBuffer when using CStrings in MessageBox dialogs like so: MessageBox(m_hWnd, cString.GetBuffer(0), lpTitle, MB_OK); Should I be adding a ReleaseBuffer after a MessageBox call? Should I be passing my string data to the MessageBox in another way? Regards, Joe > Is this always necessary?...

Your Help is Appreciated
Dear all I am using MS Excel 2003 and I am trying to plot a graph/chart/graphical representation of the following: I would like to somehow plot : Time Price Meetings 16/09/2002 23/09/2002 225 27-Sep-02 30/09/2002 07/10/2002 14/10/2002 368 21/10/2002 23-Oct-02 28/10/2002 04/11/2002 354 11/11/2002 18/11/2002 235 25/11/2002 02/12/2002 I dont mind how the data is displayed but I envisaged some kind of bar chart to represent the prices and a line chart / crosses to indicate the dates of the meetings. Can anyone help with this or am I being stupid? Thanks for everything i...

Money-Changing Account number to handle Vanguard changes
Hi: Vanguard has changed the account numbers for its brokerage accounts. This has caused money to download brand new accounts with nothing in them and stop updating my old account numbered accounts. I changed the account numbers in Account Detail page but still not working. Anyone who has any info on what I'm missing please pass along. I cannot lose years and years of past data by just switching to the newly downloaded accounts. Greg PS: Microsoft Money Plus Premium, most recent version On the old accounts, turn off on-line updates. You should then be able to merge the old accoun...

Can We Use "cout" in MFC? How About "string"?
How do we use "cout" in MFC. How about string maria("lennox")? None of them works. Please explain; maria Why in the world would you want to? Where do you think it is going to put it? For debug tracing, see my Logging Listbox Control on my MVP Tips site. joe On Thu, 28 Aug 2003 21:19:44 -0400, maria <<>> wrote: >How do we use "cout" in MFC. How about >string maria("lennox")? >None of them works. Please explain; > >maria Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web: http://www.flounder.com MVP Tips: http:...