Using a Check Box in a Subform to select records

I have a Form with a subform and two combo field are used to filter for 
records in the subform.  The records in the subform have a bound check box.  
When I check one record on the subform and then move my forcus off the 
subform, every record becomes checked.

Any suggestions?

Thanks 
0
Utf
10/25/2007 1:44:03 PM
access 16762 articles. 3 followers. Follow

4 Replies
1031 Views

Similar Articles

[PageSpeed] 37

Are you sure the check box is a bound control?  The behaviour you are getting 
suggests that its more likely to be unbound.  What does it say in the 
control's ControlSource property in its properties sheet?

If it is bound to a column in the underlying recordset then the behaviour 
could be explained by a number of things:

1.  If the checkbox is bound to a column which is just one row in a table 
joined one-to-many to another table in a query. 

2.  If there is some code in the checkbox's AfterUpdate event procedure 
which is updating the values in the column in all the rows in the recordset.  
This is theoretically possible but unlikely, as its not the sort of thing 
you'd be unaware of.

Ken Sheridan
Stafford, England

"Database Girl" wrote:

> I have a Form with a subform and two combo field are used to filter for 
> records in the subform.  The records in the subform have a bound check box.  
> When I check one record on the subform and then move my forcus off the 
> subform, every record becomes checked.
> 
> Any suggestions?
> 
> Thanks 

0
Utf
10/25/2007 5:04:03 PM
I figured it out.  I some how unbound the check box. OOPS.  How I have 
another issue.  I would like to add code to uncheck the records in the 
subform if the user refilters the subform using the combo box I have on the 
main form.  I am trying to uncheck on the AfterUpdate on the combo box, but I 
just can not seem to get is right.

Any hints???

"Ken Sheridan" wrote:

> Are you sure the check box is a bound control?  The behaviour you are getting 
> suggests that its more likely to be unbound.  What does it say in the 
> control's ControlSource property in its properties sheet?
> 
> If it is bound to a column in the underlying recordset then the behaviour 
> could be explained by a number of things:
> 
> 1.  If the checkbox is bound to a column which is just one row in a table 
> joined one-to-many to another table in a query. 
> 
> 2.  If there is some code in the checkbox's AfterUpdate event procedure 
> which is updating the values in the column in all the rows in the recordset.  
> This is theoretically possible but unlikely, as its not the sort of thing 
> you'd be unaware of.
> 
> Ken Sheridan
> Stafford, England
> 
> "Database Girl" wrote:
> 
> > I have a Form with a subform and two combo field are used to filter for 
> > records in the subform.  The records in the subform have a bound check box.  
> > When I check one record on the subform and then move my forcus off the 
> > subform, every record becomes checked.
> > 
> > Any suggestions?
> > 
> > Thanks 
> 
0
Utf
10/25/2007 6:10:01 PM
Provided you can establish a criterion to identify the rows in the subform's 
underlying recordset you can execute an SQL statement in code to update the 
rows, then requery the subform.

Let's assume the subform is based on a table called MyTable and its linked 
to the parent form on columns called MyID and the column to which the 
checkbox is bound is called MyBool, then the code for the AfterUpdate event 
of the combo box in the parent form would go like this:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    
    strSQL = "UPDATE MyTable " & _
        "SET MyBool = FALSE " & _
        "WHERE MyID = " & Me.MyID

    cmd.CommandText = strSQL
    cmd.Execute

    Me.YourSubFormControl.Requery

This assumes the MyID columns are a number data type.  If they were text 
you'd need to wrap the value in quotes:

    "WHERE MyID = """ & Me.MyID & """"

YourSubFormControl would be the name of the subform control in the parent 
form which houses the subform.

Ken Sheridan
Stafford, England

"Database Girl" wrote:

> I figured it out.  I some how unbound the check box. OOPS.  How I have 
> another issue.  I would like to add code to uncheck the records in the 
> subform if the user refilters the subform using the combo box I have on the 
> main form.  I am trying to uncheck on the AfterUpdate on the combo box, but I 
> just can not seem to get is right.
> 
> Any hints???
> 
> "Ken Sheridan" wrote:
> 
> > Are you sure the check box is a bound control?  The behaviour you are getting 
> > suggests that its more likely to be unbound.  What does it say in the 
> > control's ControlSource property in its properties sheet?
> > 
> > If it is bound to a column in the underlying recordset then the behaviour 
> > could be explained by a number of things:
> > 
> > 1.  If the checkbox is bound to a column which is just one row in a table 
> > joined one-to-many to another table in a query. 
> > 
> > 2.  If there is some code in the checkbox's AfterUpdate event procedure 
> > which is updating the values in the column in all the rows in the recordset.  
> > This is theoretically possible but unlikely, as its not the sort of thing 
> > you'd be unaware of.
> > 
> > Ken Sheridan
> > Stafford, England
> > 
> > "Database Girl" wrote:
> > 
> > > I have a Form with a subform and two combo field are used to filter for 
> > > records in the subform.  The records in the subform have a bound check box.  
> > > When I check one record on the subform and then move my forcus off the 
> > > subform, every record becomes checked.
> > > 
> > > Any suggestions?
> > > 
> > > Thanks 
> > 

0
Utf
10/26/2007 9:33:00 AM
It works, thanks.

"Ken Sheridan" wrote:

> Provided you can establish a criterion to identify the rows in the subform's 
> underlying recordset you can execute an SQL statement in code to update the 
> rows, then requery the subform.
> 
> Let's assume the subform is based on a table called MyTable and its linked 
> to the parent form on columns called MyID and the column to which the 
> checkbox is bound is called MyBool, then the code for the AfterUpdate event 
> of the combo box in the parent form would go like this:
> 
>     Dim cmd As ADODB.Command
>     Dim strSQL As String
>     
>     Set cmd = New ADODB.Command
>     cmd.ActiveConnection = CurrentProject.Connection
>     cmd.CommandType = adCmdText
>     
>     strSQL = "UPDATE MyTable " & _
>         "SET MyBool = FALSE " & _
>         "WHERE MyID = " & Me.MyID
> 
>     cmd.CommandText = strSQL
>     cmd.Execute
> 
>     Me.YourSubFormControl.Requery
> 
> This assumes the MyID columns are a number data type.  If they were text 
> you'd need to wrap the value in quotes:
> 
>     "WHERE MyID = """ & Me.MyID & """"
> 
> YourSubFormControl would be the name of the subform control in the parent 
> form which houses the subform.
> 
> Ken Sheridan
> Stafford, England
> 
> "Database Girl" wrote:
> 
> > I figured it out.  I some how unbound the check box. OOPS.  How I have 
> > another issue.  I would like to add code to uncheck the records in the 
> > subform if the user refilters the subform using the combo box I have on the 
> > main form.  I am trying to uncheck on the AfterUpdate on the combo box, but I 
> > just can not seem to get is right.
> > 
> > Any hints???
> > 
> > "Ken Sheridan" wrote:
> > 
> > > Are you sure the check box is a bound control?  The behaviour you are getting 
> > > suggests that its more likely to be unbound.  What does it say in the 
> > > control's ControlSource property in its properties sheet?
> > > 
> > > If it is bound to a column in the underlying recordset then the behaviour 
> > > could be explained by a number of things:
> > > 
> > > 1.  If the checkbox is bound to a column which is just one row in a table 
> > > joined one-to-many to another table in a query. 
> > > 
> > > 2.  If there is some code in the checkbox's AfterUpdate event procedure 
> > > which is updating the values in the column in all the rows in the recordset.  
> > > This is theoretically possible but unlikely, as its not the sort of thing 
> > > you'd be unaware of.
> > > 
> > > Ken Sheridan
> > > Stafford, England
> > > 
> > > "Database Girl" wrote:
> > > 
> > > > I have a Form with a subform and two combo field are used to filter for 
> > > > records in the subform.  The records in the subform have a bound check box.  
> > > > When I check one record on the subform and then move my forcus off the 
> > > > subform, every record becomes checked.
> > > > 
> > > > Any suggestions?
> > > > 
> > > > Thanks 
> > > 
> 
0
Utf
10/26/2007 12:08:01 PM
Reply:

Similar Artilces:

Inventory Transfer selecting incorrect stock items
Hello We are doing stock transfers and when we select items from the inventory list, it is putting a completely different item into the transfer????? I have re-indexed but it made no difference. -- Jenny ...

Using wildcards w/ sumproduct
I'm not familiar with wildcards in formulas, and I can't seem to figure out exactly what to do here. If the first 3 letters in cells of column A contain FPC, then sum corresponding rows in column P, then by the total number of cells in column AT where the first 3 letters are FPC. =SUMPRODUCT((LEFT(A1:A20014,3*(closed!$A$3:$A$20003="FPC"))),(closed!$P$3:$P$20003)/SUMPRODUCT((LEFT(A1:A20014,3*(closed!$AT$3:$AT$20003="FPC"))))) Any assistance is greatly appreciated. Thanx, ~Julz Hi if I understood you correctly try =SUMPRODUCT(--(LEFT(closed!$A$3:$A$20003)=&qu...

why does excel ignore page formating after selecting rows to repe.
I have a Pivot table to which I wish to print excluding the "Page" items but include the Table headings. I have also set the document to print 1 page wide by up to 999 pages tall. It previews fine except for when I select the table headings (row $3$3) to be repeated at the top. After I make that selection, the preview shows all the data being printed on only 5 pages instead of 31. I don't know what I am missing to cause this unexpected result. ...

How can I find out which apps are using .NET and which not?
I appear to have three versions of .NET (2, 3, 3.5) with their associated special packs. How can I find out which apps are using .NET. (Please, no 'cut-one-out' method, I do not want to uninstall and see what has stopped working.) I am aware that the different .NETS are individually and separately usable by different apps - my questions is what app uses which? Thanks for any pointers occam wrote: > I appear to have three versions of .NET (2, 3, 3.5) with their > associated special packs. > > How can I find out which apps are using .NET. (Please, no 'c...

Outlook using RPC over HTTP and Passwords
We have implemented mandatory password changes every 60 days and the remote users with Outlook over http and not given an opportunity to change the password when it expires. After the 60 days they can't get in and I need to change their password on the server. Is there something I need to do? or fix? VPN users are being prompted to change their passwords and can change them themselves. -- Regards Chris Can they change them from OWA? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/...

Using the Google Calendar .net API from an Access application
I have a scheduling/calendar application in access. I also use Goggle calendar. Would it be possible to use .net from Access to send my schedular entries to my Goggle calendar? OP <OP@discussions.microsoft.com> wrote: >I have a scheduling/calendar application in access. I also use Goggle >calendar. Would it be possible to use .net from Access to send my schedular >entries to my Goggle calendar? It might be possible but you'd have to do a lot of digging through the Google calendar documentation looking for the various APIs and figuring out how to execu...

FWD: Use pack
--nozugocqswhapz Content-Type: multipart/related; boundary="ocmbostjnqamcd"; type="multipart/alternative" --ocmbostjnqamcd Content-Type: multipart/alternative; boundary="wtcazseafbqr" --wtcazseafbqr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to prote...

Stop Text Boxes From Containing Same Info?
How to I get text entered in one text box to STOP automatically appearing in another text box on a different page? When creating a brochure using one of the templates, I key the company address in the return address section of the middle panel of Page 1. (When I rest my mouse pointer over this textbox it is called the "Address Text Box" as shown in a yellow tip box.) After I key the return address on Page 1, it automatically appears in the "Address Text Box" on Page 2. If I edit the text box on Page 2, it changes the text on Page 1, etc. How can I stop these te...

Code to display random records in form
I have a database and these are my tables. tblReference ReferenceID Question tblAnswer AnswerID ReferenceID Question GivenAnswer On my form I have a start button, which I’d like to add a code to, so when the user clicks it a random record from the Question field of tblReference will be displayed in the first field of the form; Below that field will be an empty field to give an answer. Next to the start button I have a continue button. I’d like to add code to this button, so when it’s pressed a new record will get created in tblAnswer that contains the question ...

Using a form instead of a parameter query.
We are using Access 2007. There are several parameters that my db users have to input to "filter" the data that will complete reports for the agency. I have no trouble using date fields on a form that allows the user to define the date range. However, the users need to specify foreign key fields (long int.) and I wish them to enter more than one value in the field at one time. Using the In () operator works fine on the query itself, but how can I have the users type several integers in a text box on a form that will form the parameter for the query? Any ideas? And than...

Is there a Print Preview without using the Preview app?
Is there a way to see what a document will look like on a printed page while working within Office? I know I can do Print>Preview, but that views the document in Preview, not in Office. "Office" is a suite of applications programs - knowing which of those programs you're referring to would make it a bit easier to answer your question:) In general, however, Office never has done the printing & the current Apple guidelines for printing allow for the apps to no longer have to provide a preview service which was [and is] generated by the OS & printer driver in the first ...

Corrupt emails when using multipart content types
I am running Exchange 2003 with latest SPs and am running into a very strange problem. When we receive certain emails, the contents of the messages have been corrupted. Exclamation points (!) seem to appear randomly in the text (usually an exclamation point followed by a space). The messages both contain HTML and TEXT content, the header is: Content-Type: multipart/alternative; boundary="----=_NextPart_000_365D_01C59758.21CA9D10" The text version content type is: ------=_NextPart_000_365D_01C59758.21CA9D10 Content-Type: text/plain; charset="iso-8859-1" Content-Tr...

Drop down boxes and linking to db
I am attempting to create a purchase order form and I would like in the ship to address to place a drop down box of all my suppliers. When the user clicks on the drop down box, a list of suppliers will appear. The user will then choose a supplier name and click on it. After clicking on it, I would like the form to automatically display the address, contact info. and other relevant information. I am lost as to how to do this. Thank you. Francis --- Message posted from http://www.ExcelForum.com/ You could use Data|Validation to show the list of suppliers. See Debra Dalgleish's sit...

Populating appropirate account in look up box when selecting contact
Hi There, I wonder anyone can point me in the right direction can help out there. We have a CRM Case form where it has Customer lookup field (Regarding lookup for Account/Contacts) and also Responsible contact lookup field (lookup for Contact records). What we want happen is as soon as Responsible contact is selected from a lookup, automatic population of appropirate Account in Customer look up field. We believe it should be possible because contacts in the system are already linked to Account records any help is much appreciated. cheers Kyaw Hi Kyaw, You will need to write a ...

Problem with TOC using various TOC styles in the same table (Wrd20
Hello, I'm having a weird problem with the table of contents. I have been editing a long document and am regularly updating the TOC. I have just noticed that the spaces between the lines were various in the TOC and thus used the style inspector to find out why. It appeared that TOC 1, TOC 2, TOC 3, TOC 4...styles were all there in the TOC. I have no idea why, I'm creating the TOC repeatedly, removing or replacing the previous one and when creating, I'm choosing TOC 1 as the style but when the TOC is inserted, the problem is the same. Single space here, 1.5 space th...

searching for text inside a text box
I have large ammounts of text stored in text boxes all across my spreadhseets, and it would be really useful to be able to find text in them. However, I noticed that Find (Ctrl+F) doesn't work while the cursor is in a text box. Is there any way to perform such a search? Thanks for any help. ...

Select a worksheet
I'm using the below formula to determine the specific type of equipment from a list named: database =IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$2,"Desktop",IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$5,"Notebook","Error!!!!!!!")) value of X2 = DSK and value of X5 = NBK What I need help with is a way of using the value returned to automatically open the relevant worksheet, ie: If "Desktop" is returned the Desktop worksheet needs to open and if "Notebook" is returned the notebook worksheet needs to open automatically. All wo...

Mass add item site records
I need to 'mass add' a new Site Record to my inventory items. I thought there was a window to do this? but now I can locate it. Can someone direct me to it? I can go to Item Quantities Maintenance window, and add a site record, but this would be one at a time. Dynamics GP, V10 Thanks! -- Doug Hi Doug On the site card you will see an assign button on the bottom right. Here you can assign this site to the inventory. Cheers -- Fliehigh "Doug" wrote: > I need to 'mass add' a new Site Record to my inventory items. I thought > there was a window ...

How to create multiple subfolders using VBA
Hello and thanks for reading my question. I need some VBA programming help. I have a main folder with subfolders in it. I want to add a new subfolder to each subfolder in the directory. It would look something like this Main Folder Subfolder1 NewSubfolder Subfolder2 NewSubfolder Subfolder3 NewSubfolder Subfolder4 NewSubfolder I was able to use this VBA to do it for one folder, but I need it to look through all Subfolders MkDir ("C:\MainFolder\Subfolder1\Subfolder1.1") How do I look through each subfolder and add a new subfolder under each...

Text Boxes Randomly Offset After Saving
Hi, I'm using Excel 2007 and I'm using text boxes in a visual hierarchy system on a spreadsheet. When I save, everything looks nice and orderly but when I open the spreadsheet again, some text boxes have jumped around and the lines that were snapped to them don't look like they're snapped anymore. Then when I move the text boxes a little bit, the lines immediately snap to them again. Some text boxes are even all the way in the bottom corner, squished together. I've tried grouping and ungrouping text boxes but it doesn't seem to do much. I am using the "...

HELP! GP won't let me overwrite the starting check number...
example: A check was skipped and either situation a. needs to be used now or b. need to start the checks at a different number... either way, this darn thing won't let me overwrite the check number field. I can't find the setting in setup pallette to allow that - where is it, or how do I fix this??? Both are under Cards > Financial > Checkbook. The next check number is on the left side. Whether to allow overriding or reusing check numbers is on the right side. -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpre...

Report creation in Excel Using VB.Net App.
Hello All, We're just looking into options for creating report output for our application. We've used Crystal before and found limitations, so have taken to writing reports direclty into MS Excel, (add in DLL, and create sheet and data via direct code) and it appears to be working just fine so far. I was just curious if anyone else has taken this approach and if it's worked well for them. Thanks & all the best, Ed. ...

Can e-mail accounts use the domain password ?
We have many users with XP Pro laptops who are forced to change domain passwords every 60 days. They use the same password for their Outlook e-mail account. Thus they must also remember to change their Outlook e-mail account password at the same time. Is there any way for the Outlook e-mail account to use their domain password ? Paul Offerle wrote: > We have many users with XP Pro laptops who are forced to > change domain passwords every 60 days. They use the same > password for their Outlook e-mail account. Thus they must > also remember to change their Outlook e-mail accoun...

Integration Manager script to use Exchange Rate
I had an integration that we used for one company and the 3rd party product converted everything to Canadian currency. We now have to use this same integration for a US company. I can't get the 3rd party export to change so instead I created a separate integration for this file, but I need a way to pick up the Exchange Rate when integrating. I can only use scripts by example and can't find anything related to this. I can either prompt the user at the beginning of the query (not sure where?) or somehow link it to the Exchange table. Can someone please help me get started? I wou...

I want to create a search box in access
I have a database that I can create a query on, to bring up certain details that I need to look at, without all the fluff. When i run the query, I need to create either a macro, or a query, that will show me a search box, so that I can search forspecific postcodes in my query. is this possible with Access 2007? Is anything similar to this possible, that will save me having to go to the filter everytime i want to search for a new postcode? I've spoke to MS and trawled the internet, and no one seems to be able to help me. Anything would be appreciated. You must use a form to do this. ...