Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-13-2018, 12:02 AM   #1
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Exclamation Set checkbox value to true if record match

Relationship: 1(RefNo) to many(discipline)

Main Form
  • Fetch its records from a query (Select * from tblEntry 'the main table)
  • Contain the discipline object
  • Discipline object having the on_click event and if triggers, it open the Pop-up form
  • Discipline field calls concatDiscipline module and fetch its record from tblPertinence

Pop-up form
  • Contains checkbox and description object
  • Fetch its records from a query (Select * from tblDiscipline)
  • The window for setting and viewing (viewing-based on ticked checkboxes) as per the records from/to tblPertinence

tblPertinence
  • Stores the ID and Discipline being entered by user from Pop-up Form.


Everything fine except that I need my Pop-up form to have the checkbox set to true if the ID from Main form/table match to those IDs on tblPertinence according to which record the user clicked to. I'm new to vba and most of my work was made by googling. Kindly see attachment. Thanks
Attached Images
File Type: jpg Main Form.JPG (20.9 KB, 40 views)
File Type: jpg Pop-up Form.JPG (23.4 KB, 42 views)


Last edited by daryll; 01-13-2018 at 12:51 AM.
daryll is offline   Reply With Quote
Old 01-13-2018, 12:56 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Set checkbox value to true if record match

Which field is common to both forms?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-13-2018, 01:15 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Set checkbox value to true if record match

add code the the Load Event of the Pop form:
Code:
Private Sub Form_Load()
Dim rs As Dao.Recordset
Dim rsClone As DAO.Recordset
' use the MainForm's ID
Set rs = CurrentDB.OpenRecordset("SELECT DisciplineID FROM tblPertinence WHERE ID=" & Forms!MainForm!ID)
With rs
	If Not (.BOF AND .EOF) Then 
		.MoveFirst
		' open copy of recordset of Pop-up form
		Set rsClone = Me.RecordSetClone
		While Not .EOF
			'rsClone!Checked, the checkbox on the pop-up form
			rsClone.FindFirst "DisciplineID=" & !DisciplineID
			If Not rsClone.NoMatch Then
				rsClone.Edit
				rsClone!Checked=True
				rsClone.Update
			End If
			.MoveNext
		Wend
		rs.Close
		rsClone.Close
		Me.Dirty=False
	End If
End With
set rs=Nothing
set rsClone=Nothing
End Sub

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-13-2018, 03:02 AM   #4
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Hello Mr. Arnel,

I set my rs to:
Code:
Set rs = CurrentDb.OpenRecordset("SELECT Ref_ID from tblPoolPertinence WHERE Ref_ID = " & Forms![SmartDoc]![NavigationSubform].Form.[Pool Reference Entry].Form.[tbID])
I didn't get this part. It throws an error of "The Mircorsoft Access database engine does not recognize 'Ref_ID' as a valid field name or expression.
'rsClone!Checked, the checkbox on the pop-up form
Code:
rsClone.FindFirst "Ref_ID=" & !Ref_ID
Field name of what?

Thanks
daryll is offline   Reply With Quote
Old 01-13-2018, 03:40 AM   #5
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Quote:
Originally Posted by ridders View Post
Which field is common to both forms?
Additional Info

tblRefenceEntry
  • The Temp Table ( I need temp table so user can finalize prior sending it to master table)
  • Generates the Master ID

tblReference
  • The master table
  • Stores the ID generated from tblReferenceEntry
  • Stores all information entered by end users through tblReferenceEntry

tblDiscipline
  • The source lookup of my Pop-up form (Discipline Form)
  • Keeps hold of the ID generated by tblReferenceEntry which is stored in a variable (tbID). When user click on save, the tbID will then be carried over to pertinence table during docmd.runsql
  • no foreign ID stored from any table

tblPertinence
  • Stores the ID generated from tblReferenceEntry
  • Stores the information selected by the user through Pop-up form (Discipline form)
Attached Images
File Type: jpg tblDiscipline.JPG (17.0 KB, 26 views)
File Type: jpg tblPertinence.JPG (14.7 KB, 26 views)
File Type: jpg tblReferenceEntry.JPG (26.0 KB, 24 views)
daryll is offline   Reply With Quote
Old 01-13-2018, 09:07 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Set checkbox value to true if record match

Set rs = CurrentDb.OpenRecordset("SELECT Ref_ID from tblPoolPertinence WHERE Ref_ID = " & Forms![SmartDoc]![NavigationSubform].Form.[tbID])
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-13-2018, 10:05 AM   #7
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Mr. Arnel,

Quote:
Originally Posted by arnelgp View Post
Set rs = CurrentDb.OpenRecordset("SELECT Ref_ID from tblPoolPertinence WHERE Ref_ID = " & Forms![SmartDoc]![NavigationSubform].Form.[tbID])
I'm trying to learn your approach slowly but positively, absorbing the meaning of every line by googling. Now i'm a little bit positive, trial and error mode. So i've re-set my rs to:
Code:
Set rs = CurrentDb.OpenRecordset("SELECT Discipline from tblPoolDiscipline")
and manually set
Code:
rsClone.FindFirst (!Discipline = "Comm")
tblPoolDiscipline -> the record source (21 records) of my pop-up form (discipline form). It contains the ID (autonum), Discipline, Description, Select(Y/N), Active(Y/N). No foreign key from any table.

On that line where i set my rs, i inserted a break. While traversing line by line by pressing F8 key, on the fourth traverse it goes inside on this code
Code:
If Not rsClone.NoMatch Then
                rsClone.Edit
                rsClone!Select = True
                rsClone.Update
End If

daryll is offline   Reply With Quote
Old 01-13-2018, 10:07 AM   #8
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

However, it set the first checkbox to True which corresponds to "Boiler" which is supposed to be on the fourth checkbox the "Comm". Can you tell me why is it so?
daryll is offline   Reply With Quote
Old 01-13-2018, 10:12 AM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Set checkbox value to true if record match

You enclosed it in quotes and not bsng !.

rsClone.FindFirst "Discipline = 'Comm'"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
daryll (01-13-2018)
Old 01-13-2018, 11:05 AM   #10
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Quote:
Originally Posted by arnelgp View Post
You enclosed it in quotes and not bsng !.

rsClone.FindFirst "Discipline = 'Comm'"
This is the code, and it works.
Code:
rsClone.FindFirst ("Discipline = '" & Forms![SmartDoc]![NavigationSubform].Form.[Pool Reference Entry].Form.[tbDiscipline] & "'")
But i need this
Code:
& Forms![SmartDoc]![NavigationSubform].Form.[Pool Reference Entry].Form.[tbDiscipline]
to be replaced by a result of SELECT Pertinence from tblPoolPertinence WHERE Ref_ID = Forms![SmartDoc]![NavigationSubform].Form.[Pool Reference Entry].Form.[tbID]. As the table was designed 1 to many. Where the result, i then compare it to the rsClone. I'm sorry, I need your help again.
daryll is offline   Reply With Quote
Old 01-13-2018, 11:21 AM   #11
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Should it require second rsClone? Like, rsClone2 = rsClone1 if false rsClone1.movenext until it match. If match rsClone2.movenext, then again rsClone2 = rsClone1 loop till end of rsClone2? Sounds like it is the logic, i dont't know how to implement it.
daryll is offline   Reply With Quote
Old 01-13-2018, 12:21 PM   #12
daryll
Newly Registered User
 
Join Date: Jan 2018
Posts: 28
Thanks: 12
Thanked 1 Time in 1 Post
daryll is on a distinguished road
Re: Set checkbox value to true if record match

Thanks to all of your help.

I got it working. This link is also a big help and merged with Arnel's post.
https://bytes.com/topic/access/answe...ecordsets-once

daryll is offline   Reply With Quote
Reply

Tags
access2010 accessvba , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
set checkbox to true with 10 at a time Sniperbooya Modules & VBA 4 06-27-2016 05:08 AM
visible is True if checkbox is checked azmirnordin Reports 2 07-12-2011 06:17 AM
Images visible when checkbox = true james_IT Forms 4 09-06-2008 11:38 AM
If (checkbox) = true, add a unstored value to a field? Mikk Queries 0 03-05-2006 08:52 AM
RE: Making an image visible if a record has a true checkbox in a report jezzamaster Reports 2 07-28-2005 07:24 PM




All times are GMT -8. The time now is 11:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World