Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

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
Avatar of Sandra Smith
Sandra Smith
Flag of United States of America image

ASKER

This is a desktop, Access 2003 database
ASKER CERTIFIED SOLUTION
Avatar of ajsaasta
ajsaasta

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I didn't event realize the issue about using "Select"! Got the idea though. Thank you both.