Set checkbox value to true if record match (1 Viewer)

daryll

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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
 

Attachments

  • Main Form.JPG
    Main Form.JPG
    20.9 KB · Views: 58
  • Pop-up Form.JPG
    Pop-up Form.JPG
    23.4 KB · Views: 66
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,175
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
 

daryll

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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)
 

Attachments

  • tblDiscipline.JPG
    tblDiscipline.JPG
    17 KB · Views: 49
  • tblPertinence.JPG
    tblPertinence.JPG
    14.7 KB · Views: 42
  • tblReferenceEntry.JPG
    tblReferenceEntry.JPG
    26 KB · Views: 44

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,175
Set rs = CurrentDb.OpenRecordset("SELECT Ref_ID from tblPoolPertinence WHERE Ref_ID = " & Forms![SmartDoc]![NavigationSubform].Form.[tbID])
 

daryll

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
Mr. Arnel,

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

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,175
You enclosed it in quotes and not bsng !.

rsClone.FindFirst "Discipline = 'Comm'"
 

daryll

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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

Registered User.
Local time
Today, 06:16
Joined
Jan 2, 2018
Messages
49
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.
 

Users who are viewing this thread

Top Bottom