HELP! I have a form in Access that has 5 check boxes in a subform.
DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
form. I couldn't create an option group- it wouldn't work. These need to be
evaluated into 1 field. This will be added to a permanent table. ztblDDT is
temp table. I'm using the below code. It processes the second record and
ends. Where am I making my mistake?

Dim rs As ADODB.Recordset
Dim strSQLStmt As String
Dim TaskCode As String

strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
(((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
(((ztblDDT.DSRTask5) = Yes))"

Set rs = New ADODB.Recordset
rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

With rs
rs.MoveFirst

Do Until rs.EOF

'Now that I have only the records that were checked
'they need to be converted for the TaskCode field in the permanent table.

If rs!DSRTask1 = yes Then
TaskCode = "97"
ElseIf rs!DSRTask2 = yes Then
TaskCode = "98"
ElseIf rs!DSRTask3 = yes Then
TaskCode = "99"
ElseIf rs!DSRTask4 = yes Then
TaskCode = "100"
ElseIf rs!DSRTask5 = yes Then
TaskCode = "101"
End If
Debug.Print rs.GetString
Debug.Print TaskCode
rs.MoveNext
Loop



End With

Hi Michelle,

I don't use ADO, so I don't know why this happens, but if you comment out
this line:

Debug.Print rs.GetString

then the code you provided runs without errors. I made a table and added 5
records.

Also, although I use A2K, the If() statements should use "TRUE" instead of
"YES".



I modified your code a little. <g>
'--------------------------------------------------
Dim rs As ADODB.Recordset
Dim strSQLStmt As String
Dim TaskCode As String

' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
(((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
(((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"

strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
(((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
(((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
(((ztblDDT.DSRTask5)=Yes));"


Set rs = New ADODB.Recordset
rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

' can't get the recordcount - the cursor type is a forward-only cursor -
always returns -1

With rs
rs.MoveFirst
Do Until rs.EOF

'Now that I have only the records that were checked
'they need to be converted for the TaskCode field in the permanent
table.
TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
(rs!DSRTask5 = True))

' Debug.Print rs.GetString
Debug.Print TaskCode
rs.MoveNext
Loop
End With
'--------------------------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Michelle" wrote:

> HELP! I have a form in Access that has 5 check boxes in a subform.
> DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> form. I couldn't create an option group- it wouldn't work. These need to be
> evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> temp table. I'm using the below code. It processes the second record and
> ends. Where am I making my mistake?
>
> Dim rs As ADODB.Recordset
> Dim strSQLStmt As String
> Dim TaskCode As String
>
> strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> (((ztblDDT.DSRTask5) = Yes))"
>
> Set rs = New ADODB.Recordset
> rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> adLockReadOnly
>
> With rs
> rs.MoveFirst
>
> Do Until rs.EOF
>
> 'Now that I have only the records that were checked
> 'they need to be converted for the TaskCode field in the permanent table.
>
> If rs!DSRTask1 = yes Then
> TaskCode = "97"
> ElseIf rs!DSRTask2 = yes Then
> TaskCode = "98"
> ElseIf rs!DSRTask3 = yes Then
> TaskCode = "99"
> ElseIf rs!DSRTask4 = yes Then
> TaskCode = "100"
> ElseIf rs!DSRTask5 = yes Then
> TaskCode = "101"
> End If
> Debug.Print rs.GetString
> Debug.Print TaskCode
> rs.MoveNext
> Loop
>
>
>
> End With

Steve,

Thank you for your help! I tried the code changes and I'm getting closer! I
changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
and it now reads the correct number of records. I checked 3 records and it
read 3 records. It seems like it can't read the first or last record. I
checked all 5"s and got 3 (0)'s.
"Steve Sanford" wrote:

> Hi Michelle,
>
> I don't use ADO, so I don't know why this happens, but if you comment out
> this line:
>
> Debug.Print rs.GetString
>
> then the code you provided runs without errors. I made a table and added 5
> records.
>
> Also, although I use A2K, the If() statements should use "TRUE" instead of
> "YES".
>
>
>
> I modified your code a little. <g>
> '--------------------------------------------------
> Dim rs As ADODB.Recordset
> Dim strSQLStmt As String
> Dim TaskCode As String
>
> ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
>
> strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> (((ztblDDT.DSRTask5)=Yes));"
>
>
> Set rs = New ADODB.Recordset
> rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> adLockReadOnly
>
> ' can't get the recordcount - the cursor type is a forward-only cursor -
> always returns -1
>
> With rs
> rs.MoveFirst
> Do Until rs.EOF
>
> 'Now that I have only the records that were checked
> 'they need to be converted for the TaskCode field in the permanent
> table.
> TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> (rs!DSRTask5 = True))
>
> ' Debug.Print rs.GetString
> Debug.Print TaskCode
> rs.MoveNext
> Loop
> End With
> '--------------------------------------------------
>
> HTH
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Michelle" wrote:
>
> > HELP! I have a form in Access that has 5 check boxes in a subform.
> > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > form. I couldn't create an option group- it wouldn't work. These need to be
> > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > temp table. I'm using the below code. It processes the second record and
> > ends. Where am I making my mistake?
> >
> > Dim rs As ADODB.Recordset
> > Dim strSQLStmt As String
> > Dim TaskCode As String
> >
> > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > (((ztblDDT.DSRTask5) = Yes))"
> >
> > Set rs = New ADODB.Recordset
> > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > adLockReadOnly
> >
> > With rs
> > rs.MoveFirst
> >
> > Do Until rs.EOF
> >
> > 'Now that I have only the records that were checked
> > 'they need to be converted for the TaskCode field in the permanent table.
> >
> > If rs!DSRTask1 = yes Then
> > TaskCode = "97"
> > ElseIf rs!DSRTask2 = yes Then
> > TaskCode = "98"
> > ElseIf rs!DSRTask3 = yes Then
> > TaskCode = "99"
> > ElseIf rs!DSRTask4 = yes Then
> > TaskCode = "100"
> > ElseIf rs!DSRTask5 = yes Then
> > TaskCode = "101"
> > End If
> > Debug.Print rs.GetString
> > Debug.Print TaskCode
> > rs.MoveNext
> > Loop
> >
> >
> >
> > End With

Steve,

I got the code to work. Thanks! Now, how do I get records from a) my
recordset and b) the task code that was converted into an append query. Is
this possible?
Is the Task Code included in the recordset?

"Michelle" wrote:

> Steve,
>
> Thank you for your help! I tried the code changes and I'm getting closer! I
> changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> and it now reads the correct number of records. I checked 3 records and it
> read 3 records. It seems like it can't read the first or last record. I
> checked all 5"s and got 3 (0)'s.
> "Steve Sanford" wrote:
>
> > Hi Michelle,
> >
> > I don't use ADO, so I don't know why this happens, but if you comment out
> > this line:
> >
> > Debug.Print rs.GetString
> >
> > then the code you provided runs without errors. I made a table and added 5
> > records.
> >
> > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > "YES".
> >
> >
> >
> > I modified your code a little. <g>
> > '--------------------------------------------------
> > Dim rs As ADODB.Recordset
> > Dim strSQLStmt As String
> > Dim TaskCode As String
> >
> > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> >
> > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > (((ztblDDT.DSRTask5)=Yes));"
> >
> >
> > Set rs = New ADODB.Recordset
> > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > adLockReadOnly
> >
> > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > always returns -1
> >
> > With rs
> > rs.MoveFirst
> > Do Until rs.EOF
> >
> > 'Now that I have only the records that were checked
> > 'they need to be converted for the TaskCode field in the permanent
> > table.
> > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > (rs!DSRTask5 = True))
> >
> > ' Debug.Print rs.GetString
> > Debug.Print TaskCode
> > rs.MoveNext
> > Loop
> > End With
> > '--------------------------------------------------
> >
> > HTH
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Michelle" wrote:
> >
> > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > temp table. I'm using the below code. It processes the second record and
> > > ends. Where am I making my mistake?
> > >
> > > Dim rs As ADODB.Recordset
> > > Dim strSQLStmt As String
> > > Dim TaskCode As String
> > >
> > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > (((ztblDDT.DSRTask5) = Yes))"
> > >
> > > Set rs = New ADODB.Recordset
> > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > adLockReadOnly
> > >
> > > With rs
> > > rs.MoveFirst
> > >
> > > Do Until rs.EOF
> > >
> > > 'Now that I have only the records that were checked
> > > 'they need to be converted for the TaskCode field in the permanent table.
> > >
> > > If rs!DSRTask1 = yes Then
> > > TaskCode = "97"
> > > ElseIf rs!DSRTask2 = yes Then
> > > TaskCode = "98"
> > > ElseIf rs!DSRTask3 = yes Then
> > > TaskCode = "99"
> > > ElseIf rs!DSRTask4 = yes Then
> > > TaskCode = "100"
> > > ElseIf rs!DSRTask5 = yes Then
> > > TaskCode = "101"
> > > End If
> > > Debug.Print rs.GetString
> > > Debug.Print TaskCode
> > > rs.MoveNext
> > > Loop
> > >
> > >
> > >
> > > End With

A) For the current record of a recordset, you refer to a field using this
syntax:

rs!DSRPartNo 'note the bang ( ! ), not a dot ( . )

B) I don't understand what you are asking: "the task code that was
converted into an append query" ???

C) As far as I can tell, the "Task Code" is not included in the recordset rs.



Instead of using a subform and code, why can't you use a option group? Would
you explain what you are trying to do?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Michelle" wrote:

> Steve,
>
> I got the code to work. Thanks! Now, how do I get records from a) my
> recordset and b) the task code that was converted into an append query. Is
> this possible?
> Is the Task Code included in the recordset?
>
> "Michelle" wrote:
>
> > Steve,
> >
> > Thank you for your help! I tried the code changes and I'm getting closer! I
> > changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> > and it now reads the correct number of records. I checked 3 records and it
> > read 3 records. It seems like it can't read the first or last record. I
> > checked all 5"s and got 3 (0)'s.
> > "Steve Sanford" wrote:
> >
> > > Hi Michelle,
> > >
> > > I don't use ADO, so I don't know why this happens, but if you comment out
> > > this line:
> > >
> > > Debug.Print rs.GetString
> > >
> > > then the code you provided runs without errors. I made a table and added 5
> > > records.
> > >
> > > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > > "YES".
> > >
> > >
> > >
> > > I modified your code a little. <g>
> > > '--------------------------------------------------
> > > Dim rs As ADODB.Recordset
> > > Dim strSQLStmt As String
> > > Dim TaskCode As String
> > >
> > > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> > >
> > > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > > (((ztblDDT.DSRTask5)=Yes));"
> > >
> > >
> > > Set rs = New ADODB.Recordset
> > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > adLockReadOnly
> > >
> > > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > > always returns -1
> > >
> > > With rs
> > > rs.MoveFirst
> > > Do Until rs.EOF
> > >
> > > 'Now that I have only the records that were checked
> > > 'they need to be converted for the TaskCode field in the permanent
> > > table.
> > > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > > (rs!DSRTask5 = True))
> > >
> > > ' Debug.Print rs.GetString
> > > Debug.Print TaskCode
> > > rs.MoveNext
> > > Loop
> > > End With
> > > '--------------------------------------------------
> > >
> > > HTH
> > > --
> > > Steve S
> > > --------------------------------
> > > "Veni, Vidi, Velcro"
> > > (I came; I saw; I stuck around.)
> > >
> > >
> > > "Michelle" wrote:
> > >
> > > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > > temp table. I'm using the below code. It processes the second record and
> > > > ends. Where am I making my mistake?
> > > >
> > > > Dim rs As ADODB.Recordset
> > > > Dim strSQLStmt As String
> > > > Dim TaskCode As String
> > > >
> > > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > > (((ztblDDT.DSRTask5) = Yes))"
> > > >
> > > > Set rs = New ADODB.Recordset
> > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > adLockReadOnly
> > > >
> > > > With rs
> > > > rs.MoveFirst
> > > >
> > > > Do Until rs.EOF
> > > >
> > > > 'Now that I have only the records that were checked
> > > > 'they need to be converted for the TaskCode field in the permanent table.
> > > >
> > > > If rs!DSRTask1 = yes Then
> > > > TaskCode = "97"
> > > > ElseIf rs!DSRTask2 = yes Then
> > > > TaskCode = "98"
> > > > ElseIf rs!DSRTask3 = yes Then
> > > > TaskCode = "99"
> > > > ElseIf rs!DSRTask4 = yes Then
> > > > TaskCode = "100"
> > > > ElseIf rs!DSRTask5 = yes Then
> > > > TaskCode = "101"
> > > > End If
> > > > Debug.Print rs.GetString
> > > > Debug.Print TaskCode
> > > > rs.MoveNext
> > > > Loop
> > > >
> > > >
> > > >
> > > > End With

Sure!
The problem is when I started this project I had 2 unrelated tables that I
had to link together. DeptID and DSRCodes. Then I had to create a new field
TaskCodes. The TaskCodes really didn't belong in any of the other two tables.
The DeptID and DSRCodes have a one to many relationship. TaskCode is a many.
So I created a "dummy record" in the DSRCodes table so I could get it on a
screen. I have a form that has the DeptID(main) with 75 DSRCodes in a
subform. I tried to put and option group in my subform but I couldn't get it
to work! So I alternatively put in the 5 check boxes. The user can select
from 5 check boxes. I, I/H, C, A, N. Then I have to convert their selection
into 1 field with a value of 97,98,99,100,100 respectively. The DeptID,
DSRCode and TaskCode is stored in a new table. Only the DSRCodes that has a
check box selected is written to the perm table.
To update these fields, I will pull from the DSR table to load the screen
and then Load the values from the perm table so the information is always
robust.

Basically that's what I'm trying to do! What do you think?
"Steve Sanford" wrote:

>
> A) For the current record of a recordset, you refer to a field using this
> syntax:
>
> rs!DSRPartNo 'note the bang ( ! ), not a dot ( . )
>
> B) I don't understand what you are asking: "the task code that was
> converted into an append query" ???
>
> C) As far as I can tell, the "Task Code" is not included in the recordset rs.
>
>
>
> Instead of using a subform and code, why can't you use a option group? Would
> you explain what you are trying to do?
>
>
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Michelle" wrote:
>
> > Steve,
> >
> > I got the code to work. Thanks! Now, how do I get records from a) my
> > recordset and b) the task code that was converted into an append query. Is
> > this possible?
> > Is the Task Code included in the recordset?
> >
> > "Michelle" wrote:
> >
> > > Steve,
> > >
> > > Thank you for your help! I tried the code changes and I'm getting closer! I
> > > changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> > > and it now reads the correct number of records. I checked 3 records and it
> > > read 3 records. It seems like it can't read the first or last record. I
> > > checked all 5"s and got 3 (0)'s.
> > > "Steve Sanford" wrote:
> > >
> > > > Hi Michelle,
> > > >
> > > > I don't use ADO, so I don't know why this happens, but if you comment out
> > > > this line:
> > > >
> > > > Debug.Print rs.GetString
> > > >
> > > > then the code you provided runs without errors. I made a table and added 5
> > > > records.
> > > >
> > > > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > > > "YES".
> > > >
> > > >
> > > >
> > > > I modified your code a little. <g>
> > > > '--------------------------------------------------
> > > > Dim rs As ADODB.Recordset
> > > > Dim strSQLStmt As String
> > > > Dim TaskCode As String
> > > >
> > > > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > > > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > > > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > > > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > > > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> > > >
> > > > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > > > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > > > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > > > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > > > (((ztblDDT.DSRTask5)=Yes));"
> > > >
> > > >
> > > > Set rs = New ADODB.Recordset
> > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > adLockReadOnly
> > > >
> > > > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > > > always returns -1
> > > >
> > > > With rs
> > > > rs.MoveFirst
> > > > Do Until rs.EOF
> > > >
> > > > 'Now that I have only the records that were checked
> > > > 'they need to be converted for the TaskCode field in the permanent
> > > > table.
> > > > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > > > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > > > (rs!DSRTask5 = True))
> > > >
> > > > ' Debug.Print rs.GetString
> > > > Debug.Print TaskCode
> > > > rs.MoveNext
> > > > Loop
> > > > End With
> > > > '--------------------------------------------------
> > > >
> > > > HTH
> > > > --
> > > > Steve S
> > > > --------------------------------
> > > > "Veni, Vidi, Velcro"
> > > > (I came; I saw; I stuck around.)
> > > >
> > > >
> > > > "Michelle" wrote:
> > > >
> > > > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > > > temp table. I'm using the below code. It processes the second record and
> > > > > ends. Where am I making my mistake?
> > > > >
> > > > > Dim rs As ADODB.Recordset
> > > > > Dim strSQLStmt As String
> > > > > Dim TaskCode As String
> > > > >
> > > > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > > > (((ztblDDT.DSRTask5) = Yes))"
> > > > >
> > > > > Set rs = New ADODB.Recordset
> > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > adLockReadOnly
> > > > >
> > > > > With rs
> > > > > rs.MoveFirst
> > > > >
> > > > > Do Until rs.EOF
> > > > >
> > > > > 'Now that I have only the records that were checked
> > > > > 'they need to be converted for the TaskCode field in the permanent table.
> > > > >
> > > > > If rs!DSRTask1 = yes Then
> > > > > TaskCode = "97"
> > > > > ElseIf rs!DSRTask2 = yes Then
> > > > > TaskCode = "98"
> > > > > ElseIf rs!DSRTask3 = yes Then
> > > > > TaskCode = "99"
> > > > > ElseIf rs!DSRTask4 = yes Then
> > > > > TaskCode = "100"
> > > > > ElseIf rs!DSRTask5 = yes Then
> > > > > TaskCode = "101"
> > > > > End If
> > > > > Debug.Print rs.GetString
> > > > > Debug.Print TaskCode
> > > > > rs.MoveNext
> > > > > Loop
> > > > >
> > > > >
> > > > >
> > > > > End With

Steve,

I think if I can merge your TaskCode = CStr(-97 * (ztblDSRTask1 = True) Or
-98 * (ztblDSRTask2 = True) Or -99 * (ztblDSRTask3 - True) Or ... statement
into the strSQLStmt statement I can then make it an append query and populate
the perm table. When I tried to do this I got all -1 for the records! Close
but no cigar!

"Michelle" wrote:

> Sure!
> The problem is when I started this project I had 2 unrelated tables that I
> had to link together. DeptID and DSRCodes. Then I had to create a new field
> TaskCodes. The TaskCodes really didn't belong in any of the other two tables.
> The DeptID and DSRCodes have a one to many relationship. TaskCode is a many.
> So I created a "dummy record" in the DSRCodes table so I could get it on a
> screen. I have a form that has the DeptID(main) with 75 DSRCodes in a
> subform. I tried to put and option group in my subform but I couldn't get it
> to work! So I alternatively put in the 5 check boxes. The user can select
> from 5 check boxes. I, I/H, C, A, N. Then I have to convert their selection
> into 1 field with a value of 97,98,99,100,100 respectively. The DeptID,
> DSRCode and TaskCode is stored in a new table. Only the DSRCodes that has a
> check box selected is written to the perm table.
> To update these fields, I will pull from the DSR table to load the screen
> and then Load the values from the perm table so the information is always
> robust.
>
> Basically that's what I'm trying to do! What do you think?
> "Steve Sanford" wrote:
>
> >
> > A) For the current record of a recordset, you refer to a field using this
> > syntax:
> >
> > rs!DSRPartNo 'note the bang ( ! ), not a dot ( . )
> >
> > B) I don't understand what you are asking: "the task code that was
> > converted into an append query" ???
> >
> > C) As far as I can tell, the "Task Code" is not included in the recordset rs.
> >
> >
> >
> > Instead of using a subform and code, why can't you use a option group? Would
> > you explain what you are trying to do?
> >
> >
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Michelle" wrote:
> >
> > > Steve,
> > >
> > > I got the code to work. Thanks! Now, how do I get records from a) my
> > > recordset and b) the task code that was converted into an append query. Is
> > > this possible?
> > > Is the Task Code included in the recordset?
> > >
> > > "Michelle" wrote:
> > >
> > > > Steve,
> > > >
> > > > Thank you for your help! I tried the code changes and I'm getting closer! I
> > > > changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> > > > and it now reads the correct number of records. I checked 3 records and it
> > > > read 3 records. It seems like it can't read the first or last record. I
> > > > checked all 5"s and got 3 (0)'s.
> > > > "Steve Sanford" wrote:
> > > >
> > > > > Hi Michelle,
> > > > >
> > > > > I don't use ADO, so I don't know why this happens, but if you comment out
> > > > > this line:
> > > > >
> > > > > Debug.Print rs.GetString
> > > > >
> > > > > then the code you provided runs without errors. I made a table and added 5
> > > > > records.
> > > > >
> > > > > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > > > > "YES".
> > > > >
> > > > >
> > > > >
> > > > > I modified your code a little. <g>
> > > > > '--------------------------------------------------
> > > > > Dim rs As ADODB.Recordset
> > > > > Dim strSQLStmt As String
> > > > > Dim TaskCode As String
> > > > >
> > > > > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > > > > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > > > > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > > > > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > > > > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> > > > >
> > > > > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > > > > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > > > > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > > > > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > > > > (((ztblDDT.DSRTask5)=Yes));"
> > > > >
> > > > >
> > > > > Set rs = New ADODB.Recordset
> > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > adLockReadOnly
> > > > >
> > > > > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > > > > always returns -1
> > > > >
> > > > > With rs
> > > > > rs.MoveFirst
> > > > > Do Until rs.EOF
> > > > >
> > > > > 'Now that I have only the records that were checked
> > > > > 'they need to be converted for the TaskCode field in the permanent
> > > > > table.
> > > > > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > > > > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > > > > (rs!DSRTask5 = True))
> > > > >
> > > > > ' Debug.Print rs.GetString
> > > > > Debug.Print TaskCode
> > > > > rs.MoveNext
> > > > > Loop
> > > > > End With
> > > > > '--------------------------------------------------
> > > > >
> > > > > HTH
> > > > > --
> > > > > Steve S
> > > > > --------------------------------
> > > > > "Veni, Vidi, Velcro"
> > > > > (I came; I saw; I stuck around.)
> > > > >
> > > > >
> > > > > "Michelle" wrote:
> > > > >
> > > > > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > > > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > > > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > > > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > > > > temp table. I'm using the below code. It processes the second record and
> > > > > > ends. Where am I making my mistake?
> > > > > >
> > > > > > Dim rs As ADODB.Recordset
> > > > > > Dim strSQLStmt As String
> > > > > > Dim TaskCode As String
> > > > > >
> > > > > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > > > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > > > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask5) = Yes))"
> > > > > >
> > > > > > Set rs = New ADODB.Recordset
> > > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > > adLockReadOnly
> > > > > >
> > > > > > With rs
> > > > > > rs.MoveFirst
> > > > > >
> > > > > > Do Until rs.EOF
> > > > > >
> > > > > > 'Now that I have only the records that were checked
> > > > > > 'they need to be converted for the TaskCode field in the permanent table.
> > > > > >
> > > > > > If rs!DSRTask1 = yes Then
> > > > > > TaskCode = "97"
> > > > > > ElseIf rs!DSRTask2 = yes Then
> > > > > > TaskCode = "98"
> > > > > > ElseIf rs!DSRTask3 = yes Then
> > > > > > TaskCode = "99"
> > > > > > ElseIf rs!DSRTask4 = yes Then
> > > > > > TaskCode = "100"
> > > > > > ElseIf rs!DSRTask5 = yes Then
> > > > > > TaskCode = "101"
> > > > > > End If
> > > > > > Debug.Print rs.GetString
> > > > > > Debug.Print TaskCode
> > > > > > rs.MoveNext
> > > > > > Loop
> > > > > >
> > > > > >
> > > > > >
> > > > > > End With

On Wed, 1 Oct 2008 13:39:01 -0700, Michelle
<(E-Mail Removed)> wrote:

>Steve,
>
>I think if I can merge your TaskCode = CStr(-97 * (ztblDSRTask1 = True) Or
>-98 * (ztblDSRTask2 = True) Or -99 * (ztblDSRTask3 - True) Or ... statement
>into the strSQLStmt statement I can then make it an append query and populate
>the perm table. When I tried to do this I got all -1 for the records! Close
>but no cigar!

The "Or" operator isn't doing what you think it's doing. OR is a logical
operator, just as + and - are arithmatic operators:

<Expr1> OR <Expr2>

returns TRUE (-1) if either Expr1 or Expr2 is true (nonzero); it returns FALSE
(0) if both expressions are equal to 0.

The need to do this monstrous update really suggests that your table structure
and/or form interface are *wrong*. I haven't followed the previous parts of
the thread though so I can't really make any suggestions.
--

John W. Vinson [MVP]

Still lost....


You have two tables - DeptID and DSRCodes. They are related on field??
There are two more tables?? What table is the the field [TaskCodes] in??

What does the main form have for a record source? Subform?
How thes the field [TaskCodes] in??


Can you post the compacted, zipped MDB on a web page that I can download?
If not, and the mdb doesn't have sensitive info in it (just a couple of
example records - delete the rest), compact it, zip it and email it to me.
I'll take a look at it.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Michelle" wrote:

> Sure!
> The problem is when I started this project I had 2 unrelated tables that I
> had to link together. DeptID and DSRCodes. Then I had to create a new field
> TaskCodes. The TaskCodes really didn't belong in any of the other two tables.
> The DeptID and DSRCodes have a one to many relationship. TaskCode is a many.
> So I created a "dummy record" in the DSRCodes table so I could get it on a
> screen. I have a form that has the DeptID(main) with 75 DSRCodes in a
> subform. I tried to put and option group in my subform but I couldn't get it
> to work! So I alternatively put in the 5 check boxes. The user can select
> from 5 check boxes. I, I/H, C, A, N. Then I have to convert their selection
> into 1 field with a value of 97,98,99,100,100 respectively. The DeptID,
> DSRCode and TaskCode is stored in a new table. Only the DSRCodes that has a
> check box selected is written to the perm table.
> To update these fields, I will pull from the DSR table to load the screen
> and then Load the values from the perm table so the information is always
> robust.
>
> Basically that's what I'm trying to do! What do you think?
> "Steve Sanford" wrote:
>
> >
> > A) For the current record of a recordset, you refer to a field using this
> > syntax:
> >
> > rs!DSRPartNo 'note the bang ( ! ), not a dot ( . )
> >
> > B) I don't understand what you are asking: "the task code that was
> > converted into an append query" ???
> >
> > C) As far as I can tell, the "Task Code" is not included in the recordset rs.
> >
> >
> >
> > Instead of using a subform and code, why can't you use a option group? Would
> > you explain what you are trying to do?
> >
> >
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Michelle" wrote:
> >
> > > Steve,
> > >
> > > I got the code to work. Thanks! Now, how do I get records from a) my
> > > recordset and b) the task code that was converted into an append query. Is
> > > this possible?
> > > Is the Task Code included in the recordset?
> > >
> > > "Michelle" wrote:
> > >
> > > > Steve,
> > > >
> > > > Thank you for your help! I tried the code changes and I'm getting closer! I
> > > > changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> > > > and it now reads the correct number of records. I checked 3 records and it
> > > > read 3 records. It seems like it can't read the first or last record. I
> > > > checked all 5"s and got 3 (0)'s.
> > > > "Steve Sanford" wrote:
> > > >
> > > > > Hi Michelle,
> > > > >
> > > > > I don't use ADO, so I don't know why this happens, but if you comment out
> > > > > this line:
> > > > >
> > > > > Debug.Print rs.GetString
> > > > >
> > > > > then the code you provided runs without errors. I made a table and added 5
> > > > > records.
> > > > >
> > > > > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > > > > "YES".
> > > > >
> > > > >
> > > > >
> > > > > I modified your code a little. <g>
> > > > > '--------------------------------------------------
> > > > > Dim rs As ADODB.Recordset
> > > > > Dim strSQLStmt As String
> > > > > Dim TaskCode As String
> > > > >
> > > > > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > > > > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > > > > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > > > > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > > > > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> > > > >
> > > > > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > > > > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > > > > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > > > > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > > > > (((ztblDDT.DSRTask5)=Yes));"
> > > > >
> > > > >
> > > > > Set rs = New ADODB.Recordset
> > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > adLockReadOnly
> > > > >
> > > > > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > > > > always returns -1
> > > > >
> > > > > With rs
> > > > > rs.MoveFirst
> > > > > Do Until rs.EOF
> > > > >
> > > > > 'Now that I have only the records that were checked
> > > > > 'they need to be converted for the TaskCode field in the permanent
> > > > > table.
> > > > > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > > > > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > > > > (rs!DSRTask5 = True))
> > > > >
> > > > > ' Debug.Print rs.GetString
> > > > > Debug.Print TaskCode
> > > > > rs.MoveNext
> > > > > Loop
> > > > > End With
> > > > > '--------------------------------------------------
> > > > >
> > > > > HTH
> > > > > --
> > > > > Steve S
> > > > > --------------------------------
> > > > > "Veni, Vidi, Velcro"
> > > > > (I came; I saw; I stuck around.)
> > > > >
> > > > >
> > > > > "Michelle" wrote:
> > > > >
> > > > > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > > > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > > > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > > > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > > > > temp table. I'm using the below code. It processes the second record and
> > > > > > ends. Where am I making my mistake?
> > > > > >
> > > > > > Dim rs As ADODB.Recordset
> > > > > > Dim strSQLStmt As String
> > > > > > Dim TaskCode As String
> > > > > >
> > > > > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > > > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > > > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask5) = Yes))"
> > > > > >
> > > > > > Set rs = New ADODB.Recordset
> > > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > > adLockReadOnly
> > > > > >
> > > > > > With rs
> > > > > > rs.MoveFirst
> > > > > >
> > > > > > Do Until rs.EOF
> > > > > >
> > > > > > 'Now that I have only the records that were checked
> > > > > > 'they need to be converted for the TaskCode field in the permanent table.
> > > > > >
> > > > > > If rs!DSRTask1 = yes Then
> > > > > > TaskCode = "97"
> > > > > > ElseIf rs!DSRTask2 = yes Then
> > > > > > TaskCode = "98"
> > > > > > ElseIf rs!DSRTask3 = yes Then
> > > > > > TaskCode = "99"
> > > > > > ElseIf rs!DSRTask4 = yes Then
> > > > > > TaskCode = "100"
> > > > > > ElseIf rs!DSRTask5 = yes Then
> > > > > > TaskCode = "101"
> > > > > > End If
> > > > > > Debug.Print rs.GetString
> > > > > > Debug.Print TaskCode
> > > > > > rs.MoveNext
> > > > > > Loop
> > > > > >
> > > > > >
> > > > > >
> > > > > > End With

Michelle,

After reading John's post, I see that you changed the formula... this is the
original:

TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
(rs!DSRTask5 = True))

This is what is happening: there are 5 comparisons that will result in a 0
(false) or a true (-1).
The TRUE/FALSE times the negative number will only one non zero result. So 4
zeros added to a nun zero gives the result you expected. The function CSTR()
converted the number to a string as per your code example. I used this to
replace the IF() function you used.

For example, if rs!DSRTask2 is TRUE, the other 4 boolean values must be
false (according to your post). So you have

-97 * (rs!DSRTask1 = True) + // False = -97 * 0 = 0

-98 * (rs!DSRTask2 = True) + //TRUE = -98 * -1 = 98

-99 * (rs!DSRTask3 = True) + // False = -99 * 0 = 0

-100 * (rs!DSRTask4 = True) + //False = -100 * 0 = 0

-101 * (rs!DSRTask5 = True) // False = -101 * 0 = 0


Add the results and you get 0 + 98 + 0 + 0 + 0 = 98 (which is Task2 = TRUE)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Michelle" wrote:

> Steve,
>
> I think if I can merge your TaskCode = CStr(-97 * (ztblDSRTask1 = True) Or
> -98 * (ztblDSRTask2 = True) Or -99 * (ztblDSRTask3 - True) Or ... statement
> into the strSQLStmt statement I can then make it an append query and populate
> the perm table. When I tried to do this I got all -1 for the records! Close
> but no cigar!
>
> "Michelle" wrote:
>
> > Sure!
> > The problem is when I started this project I had 2 unrelated tables that I
> > had to link together. DeptID and DSRCodes. Then I had to create a new field
> > TaskCodes. The TaskCodes really didn't belong in any of the other two tables.
> > The DeptID and DSRCodes have a one to many relationship. TaskCode is a many.
> > So I created a "dummy record" in the DSRCodes table so I could get it on a
> > screen. I have a form that has the DeptID(main) with 75 DSRCodes in a
> > subform. I tried to put and option group in my subform but I couldn't get it
> > to work! So I alternatively put in the 5 check boxes. The user can select
> > from 5 check boxes. I, I/H, C, A, N. Then I have to convert their selection
> > into 1 field with a value of 97,98,99,100,100 respectively. The DeptID,
> > DSRCode and TaskCode is stored in a new table. Only the DSRCodes that has a
> > check box selected is written to the perm table.
> > To update these fields, I will pull from the DSR table to load the screen
> > and then Load the values from the perm table so the information is always
> > robust.
> >
> > Basically that's what I'm trying to do! What do you think?
> > "Steve Sanford" wrote:
> >
> > >
> > > A) For the current record of a recordset, you refer to a field using this
> > > syntax:
> > >
> > > rs!DSRPartNo 'note the bang ( ! ), not a dot ( . )
> > >
> > > B) I don't understand what you are asking: "the task code that was
> > > converted into an append query" ???
> > >
> > > C) As far as I can tell, the "Task Code" is not included in the recordset rs.
> > >
> > >
> > >
> > > Instead of using a subform and code, why can't you use a option group? Would
> > > you explain what you are trying to do?
> > >
> > >
> > > --
> > > Steve S
> > > --------------------------------
> > > "Veni, Vidi, Velcro"
> > > (I came; I saw; I stuck around.)
> > >
> > >
> > > "Michelle" wrote:
> > >
> > > > Steve,
> > > >
> > > > I got the code to work. Thanks! Now, how do I get records from a) my
> > > > recordset and b) the task code that was converted into an append query. Is
> > > > this possible?
> > > > Is the Task Code included in the recordset?
> > > >
> > > > "Michelle" wrote:
> > > >
> > > > > Steve,
> > > > >
> > > > > Thank you for your help! I tried the code changes and I'm getting closer! I
> > > > > changed the "+" signs to OR since it can only choose 1 out the 5 DSRTaskCodes
> > > > > and it now reads the correct number of records. I checked 3 records and it
> > > > > read 3 records. It seems like it can't read the first or last record. I
> > > > > checked all 5"s and got 3 (0)'s.
> > > > > "Steve Sanford" wrote:
> > > > >
> > > > > > Hi Michelle,
> > > > > >
> > > > > > I don't use ADO, so I don't know why this happens, but if you comment out
> > > > > > this line:
> > > > > >
> > > > > > Debug.Print rs.GetString
> > > > > >
> > > > > > then the code you provided runs without errors. I made a table and added 5
> > > > > > records.
> > > > > >
> > > > > > Also, although I use A2K, the If() statements should use "TRUE" instead of
> > > > > > "YES".
> > > > > >
> > > > > >
> > > > > >
> > > > > > I modified your code a little. <g>
> > > > > > '--------------------------------------------------
> > > > > > Dim rs As ADODB.Recordset
> > > > > > Dim strSQLStmt As String
> > > > > > Dim TaskCode As String
> > > > > >
> > > > > > ' strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo,
> > > > > > ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4,
> > > > > > ztblDDT.DSRTask5 FROM ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask2) = Yes)) Or (((ztblDDT.DSRTask3) = Yes)) Or
> > > > > > (((ztblDDT.DSRTask4) = Yes)) Or (((ztblDDT.DSRTask5) = Yes))"
> > > > > >
> > > > > > strSQLStmt = "SELECT ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
> > > > > > ztblDDT.DSRTask4, ztblDDT.DSRTask5 From ztblDDT WHERE
> > > > > > (((ztblDDT.DSRTask1)=Yes)) OR (((ztblDDT.DSRTask2)=Yes)) OR
> > > > > > (((ztblDDT.DSRTask3)=Yes)) OR (((ztblDDT.DSRTask4)=Yes)) OR
> > > > > > (((ztblDDT.DSRTask5)=Yes));"
> > > > > >
> > > > > >
> > > > > > Set rs = New ADODB.Recordset
> > > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > > adLockReadOnly
> > > > > >
> > > > > > ' can't get the recordcount - the cursor type is a forward-only cursor -
> > > > > > always returns -1
> > > > > >
> > > > > > With rs
> > > > > > rs.MoveFirst
> > > > > > Do Until rs.EOF
> > > > > >
> > > > > > 'Now that I have only the records that were checked
> > > > > > 'they need to be converted for the TaskCode field in the permanent
> > > > > > table.
> > > > > > TaskCode = CStr(-97 * (rs!DSRTask1 = True) + -98 * (rs!DSRTask2 =
> > > > > > True) + -99 * (rs!DSRTask3 = True) + -100 * (rs!DSRTask4 = True) + -101 *
> > > > > > (rs!DSRTask5 = True))
> > > > > >
> > > > > > ' Debug.Print rs.GetString
> > > > > > Debug.Print TaskCode
> > > > > > rs.MoveNext
> > > > > > Loop
> > > > > > End With
> > > > > > '--------------------------------------------------
> > > > > >
> > > > > > HTH
> > > > > > --
> > > > > > Steve S
> > > > > > --------------------------------
> > > > > > "Veni, Vidi, Velcro"
> > > > > > (I came; I saw; I stuck around.)
> > > > > >
> > > > > >
> > > > > > "Michelle" wrote:
> > > > > >
> > > > > > > HELP! I have a form in Access that has 5 check boxes in a subform.
> > > > > > > DSRTaskCode is new field. I had to put a dummy field in DSR table to put on
> > > > > > > form. I couldn't create an option group- it wouldn't work. These need to be
> > > > > > > evaluated into 1 field. This will be added to a permanent table. ztblDDT is
> > > > > > > temp table. I'm using the below code. It processes the second record and
> > > > > > > ends. Where am I making my mistake?
> > > > > > >
> > > > > > > Dim rs As ADODB.Recordset
> > > > > > > Dim strSQLStmt As String
> > > > > > > Dim TaskCode As String
> > > > > > >
> > > > > > > strSQLStmt = "SELECT ztblDDT.DeptID, ztblDDT.DSRPartNo, ztblDDT.DSRTask1,
> > > > > > > ztblDDT.DSRTask2, ztblDDT.DSRTask3, ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM
> > > > > > > ztblDDT WHERE (((ztblDDT.DSRTask1) = Yes)) Or (((ztblDDT.DSRTask2) = Yes)) Or
> > > > > > > (((ztblDDT.DSRTask3) = Yes)) Or (((ztblDDT.DSRTask4) = Yes)) Or
> > > > > > > (((ztblDDT.DSRTask5) = Yes))"
> > > > > > >
> > > > > > > Set rs = New ADODB.Recordset
> > > > > > > rs.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly,
> > > > > > > adLockReadOnly
> > > > > > >
> > > > > > > With rs
> > > > > > > rs.MoveFirst
> > > > > > >
> > > > > > > Do Until rs.EOF
> > > > > > >
> > > > > > > 'Now that I have only the records that were checked
> > > > > > > 'they need to be converted for the TaskCode field in the permanent table.
> > > > > > >
> > > > > > > If rs!DSRTask1 = yes Then
> > > > > > > TaskCode = "97"
> > > > > > > ElseIf rs!DSRTask2 = yes Then
> > > > > > > TaskCode = "98"
> > > > > > > ElseIf rs!DSRTask3 = yes Then
> > > > > > > TaskCode = "99"
> > > > > > > ElseIf rs!DSRTask4 = yes Then
> > > > > > > TaskCode = "100"
> > > > > > > ElseIf rs!DSRTask5 = yes Then
> > > > > > > TaskCode = "101"
> > > > > > > End If
> > > > > > > Debug.Print rs.GetString
> > > > > > > Debug.Print TaskCode
> > > > > > > rs.MoveNext
> > > > > > > Loop
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > End With