Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

Checkbox added, but can't get acCheckBox DisplayControl to work

I got the YesNo column added to my table, but when I tried to use the below:
Private Sub cmdGetData_Click()
Dim strGetData As String
Dim strAddColumn As String
Dim strAlterColumn As String

strGetData = "SELECT tblArchiveMaster.* INTO tblReviewTemp " & _
             "FROM tblArchiveMaster " & _
             "WHERE tblArchiveMaster.Report = [FORMS]![frmReviews].[cboReport] " & _
             "AND tblArchiveMaster.ImportDate = [FORMS]![frmReviews].[cboImportDate] " & _
             "AND tblArchiveMaster.UserID = [FORMS]![frmReviews].[cboUserId] " & _
             "AND tblArchiveMaster.CompletionDate = [FORMS]![frmReviews].[cboCompletionDate] "

strAddColumn = "ALTER TABLE tblReviewTemp ADD COLUMN Select YESNO "

strAlterColumn = "ALTER TABLE tblReviewTemp ALTER COLUMN RowID INTEGER "

DoCmd.DeleteObject acTable, "tblReviewTemp"
DoCmd.RunSQL strGetData
DoCmd.RunSQL strAddColumn
DoCmd.RunSQL strAlterColumn
CurrentDb.TableDefs("tblReviewTemp").Fields("Select").DisplayControl = acCheckBox
Me.Refresh
MsgBox "Process completed", vbOKOnly
End Sub

I got the error message of "Compile Error", Method or Data Member Not Found and it highlited the DisplayControl words.

I tried to find the acCheckBox formatting in the Help library, but came up with nothing.  Any suggestions?

Sandra
0
ssmith94015
Asked:
ssmith94015
  • 2
1 Solution
 
ssmith94015Author Commented:
This is a desktop, Access 2003 database
0
 
ajsaastaCommented:
"Select" is a reserved word (like in "ALTER TABLE tblReviewTemp ADD COLUMN Select YESNO") and you should either avoid using column names like that or use proper notation:

* "ALTER TABLE tblReviewTemp ADD COLUMN [Select] YESNO"
* CurrentDb.TableDefs("tblReviewTemp").Fields("[Select]").DisplayControl = acCheckBox

I've NOT tested (but encountered this same problem several times) this comment so I cannot quarantee 101% exact proper notation on my example but I hope you get the idea.

Cheers,

ajsaasta
0
 
shanesuebsahakarnCommented:
Tyr:
CurrentDb.TableDefs("tblReviewTemp").Fields("Select").Properties("DisplayControl")=acCheckBox

If that doesn't work, this should:
With CurrentDb.TableDefs("tblReviewTemp").Fields("Select").Properties
   .Append .CreateProperty("DisplayControl",dbLong,acCheckBox)
End With
0
 
ssmith94015Author Commented:
I didn't event realize the issue about using "Select"! Got the idea though. Thank you both.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now