Check Code Please

[Afrosheen via AccessMonster.com]

The code below {sql6} is where I'm having the problem. The code for {sql5}
works in another program I have so I thought I could use the basics of it.

What I need it to do is to clear out [set to null] the field called [super1]
using the txtAssist string.

Your help would be much appreciated.

Dim SQL, txtAssist As String
Dim dtm As ADODB.Connection, sql6 As String
10 txtAssist = cmboSupervisor.Column(3)

'sql5 = "update tbl_roster set location = Null where shift = 'b-days' And Not
(tbl_roster.pp) = true"

20 If SupMan = True Then
30 MsgBox "There should be a check in the box"
40 Else
50 MsgBox "No check in the box"

60 Set dtm = CurrentProject.AccessConnection
70 sql6 = "Update tblMain set super1 = Null where super1 =" &
txtAssist
80 dtm.Execute sql6, adCmdText + adExecuteNoRecords
90 Set dtm = Nothing

100 End If

Thanks for your help.

 

[Gina Whipp]

Afrosheen,

Try setting it to Flase, NULL may not be allowed.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The code below {sql6} is where I'm having the problem. The code for {sql5}
works in another program I have so I thought I could use the basics of it.

What I need it to do is to clear out [set to null] the field called [super1]
using the txtAssist string.

Your help would be much appreciated.

Dim SQL, txtAssist As String
Dim dtm As ADODB.Connection, sql6 As String
10 txtAssist = cmboSupervisor.Column(3)

'sql5 = "update tbl_roster set location = Null where shift = 'b-days' And
Not
(tbl_roster.pp) = true"

20 If SupMan = True Then
30 MsgBox "There should be a check in the box"
40 Else
50 MsgBox "No check in the box"

60 Set dtm = CurrentProject.AccessConnection
70 sql6 = "Update tblMain set super1 = Null where super1 =" &
txtAssist
80 dtm.Execute sql6, adCmdText + adExecuteNoRecords
90 Set dtm = Nothing

100 End If

Thanks for your help.

 

[Gina Whipp]

Oops, typo... that would False

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Afrosheen,

Try setting it to Flase, NULL may not be allowed.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The code below {sql6} is where I'm having the problem. The code for {sql5}
works in another program I have so I thought I could use the basics of it.

What I need it to do is to clear out [set to null] the field called [super1]
using the txtAssist string.

Your help would be much appreciated.

Dim SQL, txtAssist As String
Dim dtm As ADODB.Connection, sql6 As String
10 txtAssist = cmboSupervisor.Column(3)

'sql5 = "update tbl_roster set location = Null where shift = 'b-days' And
Not
(tbl_roster.pp) = true"

20 If SupMan = True Then
30 MsgBox "There should be a check in the box"
40 Else
50 MsgBox "No check in the box"

60 Set dtm = CurrentProject.AccessConnection
70 sql6 = "Update tblMain set super1 = Null where super1 =" &
txtAssist
80 dtm.Execute sql6, adCmdText + adExecuteNoRecords
90 Set dtm = Nothing

100 End If

Thanks for your help.

 

[Douglas J. Steele]

If super1 is a Text field, you need quotes around the value:

sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist &
""""

I'm assuming super1 is someone's name, so I'm trying to accomodate the
possibility of an apostrophe in the name. That's the reason for three double
quotes in a row before txtAssist and four double quotes in a row afterwards.

 

[Afrosheen via AccessMonster.com]

Thanks for the reply Gina and Doug. It is a true/false question.

I appreciate the help. I really do.

Thanks again.

If super1 is a Text field, you need quotes around the value:

sql6 = "Update tblMain set super1 = Null where super1 =""" & txtAssist &
""""

I'm assuming super1 is someone's name, so I'm trying to accomodate the
possibility of an apostrophe in the name. That's the reason for three double
quotes in a row before txtAssist and four double quotes in a row afterwards.

The code below {sql6} is where I'm having the problem. The code for {sql5}
works in another program I have so I thought I could use the basics of it.

[quoted text clipped - 27 lines]

Thanks for your help.

 

[Afrosheen via AccessMonster.com]

Sorry Doug and Gina. Neither code worked. It still tells me invalid use of
Null.
The txtAssist is a text field

I even tried it this way:
sql6 = "Update tblMain set super1 = "" where super1 =""" & txtAssist & """"

Thanks for the reply Gina and Doug. It is a true/false question.

I appreciate the help. I really do.

Thanks again.

If super1 is a Text field, you need quotes around the value:

[quoted text clipped - 10 lines]

 

[John W. Vinson]

Thanks for the reply Gina and Doug. It is a true/false question.

An Access Yes/No field has ONLY two allowable values: it can be True (-1) or
False (0). It cannot be NULL, it cannot be "". I think that's the cause of the
code error you're getting!

If you need to allow the field three choices (yes, no, or NULL) then you'll
need to use a Number field and some special code to display it as a checkbox.

 

[David W. Fenton]

If you need to allow the field three choices (yes, no, or NULL)
then you'll need to use a Number field and some special code to
display it as a checkbox.

Uh, the Access checkbox control has the TripleState property to
allow for just such a setup -- no code required.

 

[Marshall Barton]

John W. Vinson wrote


Uh, the Access checkbox control has the TripleState property to
allow for just such a setup -- no code required.

But only if it's bound to a number field.

A Yes/No field can not be Null.

 

[Gina Whipp]

Afrosheen,

Is this *connected* to an SQL Server? Unless you check Allow Nulls you will
need to set the box to either No or False.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Sorry Doug and Gina. Neither code worked. It still tells me invalid use of
Null.
The txtAssist is a text field

I even tried it this way:
sql6 = "Update tblMain set super1 = "" where super1 =""" & txtAssist & """"

Thanks for the reply Gina and Doug. It is a true/false question.

I appreciate the help. I really do.

Thanks again.

If super1 is a Text field, you need quotes around the value:

[quoted text clipped - 10 lines]

 

[David W. Fenton]

But only if it's bound to a number field.

That's obvious, is it not? And besides, John said it already.

A Yes/No field can not be Null.

Right. Completely clear from the context.

There is still no code required to use a number field and the
triple-state checkbox in order to store True/False/Null.

 

[Marshall Barton]

Marshall Barton wrote


That's obvious, is it not? And besides, John said it already.


Right. Completely clear from the context.

There is still no code required to use a number field and the
triple-state checkbox in order to store True/False/Null.

Well, David, when you condense it down to it's essence like
that, it is perfectly clear, even to me ;-)

I guess I was still hung up on emphasizing the OP's Null
problem needing a Number type field and your point about no
code needed was not at issue.

 

[David W. Fenton]

Well, David, when you condense it down to it's essence like
that, it is perfectly clear, even to me ;-)

I guess I was still hung up on emphasizing the OP's Null
problem needing a Number type field and your point about no
code needed was not at issue.

Well, you'll note I wasn't responding to the original poster's
problem, but to a particular sentence written by John. That's why I
quoted only that part.

No matter -- I do this all the time, i.e., reading a posting in a
different context than the post was written, even when the poster
makes very clear what is being addressed (as I think I did).

 

[John W. Vinson]

Uh, the Access checkbox control has the TripleState property to
allow for just such a setup -- no code required.

Quite correct. Sorry for the error on my part.

 

[Afrosheen via AccessMonster.com]

Ok. Thanks for all your replies. Now to get back to the problem at hand.
First I want to say that I messed up. It is NOT a yes/no field. It is a text
field that needs to change [Super1]. The yes/no field [SupMan]is a check box
to basically say that if it is checked then this person is a supervisor. If
it is not checked then the person is not a supervisor and then supposed to
find all employees that has the supervisor id and change them to Null or make
it empty. And It still does not work. It leaves the information in the
[Super1] field.

I modified the code a little bit to make sure that the [supman] is false.


20 If SupMan = False Then
txtAssist1 = StaffId
60 Set dtm = CurrentProject.AccessConnection
sql6 = "Update tblMain set super1 = Null where super1 =""" &
txtAssist1 & """ And Not (tblMain.SupMan) = true"

'70 sql6 = "Update tblMain set super1 = False where super1 =" &
txtAssist
80 dtm.Execute sql6, adCmdText + adExecuteNoRecords
90 Set dtm = Nothing

100 End If


Thanks for all your input.

 

[John W. Vinson]

Ok. Thanks for all your replies. Now to get back to the problem at hand.
First I want to say that I messed up. It is NOT a yes/no field. It is a text
field that needs to change [Super1]. The yes/no field [SupMan]is a check box
to basically say that if it is checked then this person is a supervisor. If
it is not checked then the person is not a supervisor and then supposed to
find all employees that has the supervisor id and change them to Null or make
it empty. And It still does not work. It leaves the information in the
[Super1] field.

Afrosheen, are you still storing the supervisor's *NAME* in the Super1 field
in the Employee table?

You really should not be doing so!

The Employee table should have a SuperID field - a *numeric* field, linked to
the EmployeeID of the supervisor.

I'm confused about what you're trying to accomplish by running this update
query.

 

[Afrosheen via AccessMonster.com]

No John. The SuperId I don't believe can be a number because it contains his
StaffId a text and number for example. John Joseph Tetter would be TJJ01 as
his Staff Id. They are assigned from HR. Then there's a checkbox that when
true says that he's a supervisor and I can select his name from a combo box
that will associate an employee to that supervisor. Then whom ever his
employees are would have his TJJ01 as SuperId.

I'm trying to clear the SuperId field out all of the employees SuperId field
associated with TJJ01 if John Joseph Tetter supervisor check box is false
(not a supervisor any more).

I hope that will clarify what I'm trying to do.

Ok. Thanks for all your replies. Now to get back to the problem at hand.
First I want to say that I messed up. It is NOT a yes/no field. It is a text

[quoted text clipped - 4 lines]

it empty. And It still does not work. It leaves the information in the
[Super1] field.

Afrosheen, are you still storing the supervisor's *NAME* in the Super1 field
in the Employee table?

You really should not be doing so!

The Employee table should have a SuperID field - a *numeric* field, linked to
the EmployeeID of the supervisor.

I'm confused about what you're trying to accomplish by running this update
query.

 

[John W. Vinson]

No John. The SuperId I don't believe can be a number because it contains his
StaffId a text and number for example. John Joseph Tetter would be TJJ01 as
his Staff Id. They are assigned from HR. Then there's a checkbox that when
true says that he's a supervisor and I can select his name from a combo box
that will associate an employee to that supervisor. Then whom ever his
employees are would have his TJJ01 as SuperId.

Ahhhh... sorry, I'd missed that SuperID was a Text datatype.

I'm trying to clear the SuperId field out all of the employees SuperId field
associated with TJJ01 if John Joseph Tetter supervisor check box is false
(not a supervisor any more).

Ok... so your current code is:

20 If SupMan = False Then
txtAssist1 = StaffId
60 Set dtm = CurrentProject.AccessConnection
sql6 = "Update tblMain set super1 = Null where super1 =""" &
txtAssist1 & """ And Not (tblMain.SupMan) = true"

'70 sql6 = "Update tblMain set super1 = False where super1 =" &
txtAssist
80 dtm.Execute sql6, adCmdText + adExecuteNoRecords
90 Set dtm = Nothing

100 End If

I *think* you're just missing a comma! The second operand to the Execute
method returns the number of records affected; the *third* argument is the
Options, which should be the adCmdText + adExecuteNoRecords values. Try adding
one more comma after sql6.

You may want to step through the code in debug mode and see what sql6 contains
prior to executing the query.

 

[Afrosheen via AccessMonster.com]

I put the extra comma in.
dtm.Execute sql6, , adCmdText + adExecuteNoRecords

I put a break point in and looked at the sql string and it was correct.
I also hovered over the execute and that looked correct.

When it did execute I checked the superid of an employee and the superid was
still there in table view.

Thanks again for your help..

 

[John W. Vinson]

I put the extra comma in.
dtm.Execute sql6, , adCmdText + adExecuteNoRecords

I put a break point in and looked at the sql string and it was correct.
I also hovered over the execute and that looked correct.

When it did execute I checked the superid of an employee and the superid was
still there in table view.

Perplexing!

Try typing

?sql6

in the immediate window. Copy and paste the SQL string into the SQL window of
a new query. Open it in datasheet view; does it display the records you
expect? If you execute it by clicking the ! icon, does it give an error
message?