SQL and Checkbox problem

M

mlb5000

Hey,

I need to check the value of a checkbox in the On Current Event to
check whether it is checked or not. If it is, I need to lock all the
values of the form and its subform. Here is my code:

Private Sub Form_Current()

Dim SQL As String

Dim CLOSED As String

CLOSED = "SELECT CLOSED FROM PVT_TBL" & _
"WHERE PVT_ID = Me.PROJ_ID"


If CLOSED = "-1" Then
'Lock down fields for "Main" Subform Page 0
Me.PROJ_ID.Locked = True
Me!OPEN.Locked = True
Me!RFI.Locked = True
Me!VAR.Locked = True
Me!FWI.Locked = True
Me!RFI_DATE.Locked = True
Me!VAR_DATE.Locked = True
Me!RESP_DATE.Locked = True
Me!FWI_DATE.Locked = True
Me!WARRANTY_CLAIM.Locked = True
Me!NCR.Locked = True
Me!NCR_Number.Locked = True
Me!INTERNAL.Locked = True
Me!EXTERNAL.Locked = True
Me!CAR.Locked = True
Me!CAR_Number.Locked = True
Me!DWG_PROCESS.Locked = True
Me!WBS_NO.Locked = True
Me!ACTION_BY.Locked = True
'Lock down fields for "Cause/Description" Subform Page 1
Me!INT_CAUSE.Locked = True
Me!INT_CAUSE_WHO.Locked = True
Me!EXT_CAUSE.Locked = True
Me!EXT_CAUSE_WHO.Locked = True
Me!EXT_CAUSE_NAME.Locked = True
Me!PVT_DESCRIPTION.Locked = True
Me!PVT_RESPONSE.Locked = True
Me!PVT_WORK_DONE.Locked = True
Me!PVT_WORK_DONE_DATE.Locked = True
End If

End Sub

It compiles but it doesn't do anything. Where am I going wrong?

Thanks,
Matt
 
M

Mr B

You are assigning your sql statement to the variable that you are expecting
to hold the value of "CLOSED" as returned by the sql statement.

It is not clear from what you have said or posted, but it would appear that
the field "CLOSED" in your sql statememt may be a "Yes/No" field. If this is
true then you would be better off to change your variable to a boolean type
variable rather than a string.

You will also need to set a reverence to DAO and declare a variable to
reference it.

You need to use something like this (air code):

dim rs as DAO.Recordset
Dim SQL As String
Dim CLOSED as boolean

SQL = "SELECT CLOSED FROM PVT_TBL" & _
"WHERE PVT_ID = Me.PROJ_ID"
set rs = currentdb.openrecordset (SQL)
CLOSED = rs.Fields("CLOSED").value
rs.close
set rs = nothing

'this will give you the value from your sql statement
'now check the value of the variable CLOSED and do whatever you want

if CLOSED = -1 then

Your code

endif
 
M

mlb5000

Mr said:
You are assigning your sql statement to the variable that you are expecting
to hold the value of "CLOSED" as returned by the sql statement.

It is not clear from what you have said or posted, but it would appear that
the field "CLOSED" in your sql statememt may be a "Yes/No" field. If this is
true then you would be better off to change your variable to a boolean type
variable rather than a string.

You will also need to set a reverence to DAO and declare a variable to
reference it.

You need to use something like this (air code):

dim rs as DAO.Recordset
Dim SQL As String
Dim CLOSED as boolean

SQL = "SELECT CLOSED FROM PVT_TBL" & _
"WHERE PVT_ID = Me.PROJ_ID"
set rs = currentdb.openrecordset (SQL)
CLOSED = rs.Fields("CLOSED").value
rs.close
set rs = nothing

'this will give you the value from your sql statement
'now check the value of the variable CLOSED and do whatever you want

if CLOSED = -1 then

Your code

endif
Click to expand...

Hey,

Thanks for the help. Pehaps you can help with this as well. it tells
me there's a Syntax error in my FROM clause...but it's just the name of
the table. Also, it takes me to the set rs = currentdb.openrecordset
(SQL) line whenever it debugs. Any suggestions?

Matt
 
J

John Smith

If you look at the content of your variable you will find that it is "SELECT
CLOSED FROM PVT_TBLWHERE PVT_ID = Me.PROJ_ID". You need to include a space
before the word WHERE. In cases such as this insert a MsgBox SQL or
Debug.Print SQL so that you can check exactly what you are building in your code.

HTH
John
##################################
Don't Print - Save trees
 
Top