Access 2003 programming problem

[Michelle]

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 Sanford]

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

 

[Michelle]

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.

 

[Michelle]

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?

 

[Steve Sanford]

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?

 

[Michelle]

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?

 

[Michelle]

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!

 

[John W. Vinson]

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.

 

[Steve Sanford]

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 Sanford]

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

 

[Michelle]

Steve,
This database is split with the tables in a back-end database. Can you tell
me how to go about copying it? In the interim I will try to answer your
questions.

The tables tblBWDept and tblDSR have autonumbers as keys.(Not my design) so
I have an append query that links the two tables together creating the
ztblDDT temp table with the key being DeptID and DSRCodes. ztblDDT table has
the 5 check box fields DSRTask1,DSRTask2,DSRTask3 etc.

The main form has a record source of qryBWDept and the subform's
recordsource is ztblDDT. After the user completes the form I have a query
(qryDSRTasks) that's selecting only the records with a check box selected.
This is the SQLStmt code that creates the recordset.

I have to combine the records in my recordset with the outcome of your
"TaskCode = "( I changed this code back to original code) code and somehow
append to perm table tblDSRDMTasks. Can I add your code to the SQL statement
that creates the recordset? This is the last step in my process. Once these
fields are written to my perm table, the entry process will be complete.

Thanks

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.)

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]

This database is split with the tables in a back-end database. Can you tell
me how to go about copying it? In the interim I will try to answer your
questions.

Do a Compact & Repair, then use WinZip or another prog that is equivalent to
compress it. Create a new Winzip file (an 'Archive') and drop the FE & BE in
it.
You don't have to make a copy....... except for backups

The tables tblBWDept and tblDSR have autonumbers as keys.(Not my design) so
I have an append query that links the two tables together creating the

An append query adds records. It cannot link tables. A Select query links
tables/queries

I have to combine the records in my recordset with the outcome of your
"TaskCode = "( I changed this code back to original code) code and somehow
append to perm table tblDSRDMTasks. Can I add your code to the SQL statement

What is "perm" table?

 

[Michelle]

I'm using a temporary table(ztblDDT) to load the subform. After the user
selects the task for a specific dept and DSRCode in the subform I'm using an
append query to write the results to a permanent table(tblDSRDMTasks).

You gave me the code to convert the 5 check boxes to 1 TaskCode.
TaskCode = CStr(-97*(rs!DSRTask1 = True) + -98*(rs!DSRTask12 = True) +
-99*(rs!DSRTask3 = True) + -100*(rs!DSRTask4 = True) + -101*(rs!DSRTask5 =
True)).
Is there a way I can get this to work in reverse? It would read
TaskCode(TaskCode in table has only 1 of these values 97,98,99,100,101) and
fill in the appropriate DSRTask check box in another table? I'm trying to put
this in an update query.

Thanks

 

[Steve Sanford]

Is there a way I can get this to work in reverse? It would read

TaskCode(TaskCode in table has only 1 of these values 97,98,99,100,101) and
fill in the appropriate DSRTask check box in another table? I'm trying to put
this in an update query.

Not sure if you want code or a (saved) query.......so...

For a saved query, start with this -

Create a new query, switch to SQL view and paste in the following:

UPDATE tblAnother SET tblAnother.DSRTask1 = [forms]![DSR].[DSRTaskCode]=97,
tblAnother.DSRTask2 = [forms]![DSR].[DSRTaskCode]=98, tblAnother.DSRTask3 =
[forms]![DSR].[DSRTaskCode]=99, tblAnother.DSRTask4 =
[forms]![DSR].[DSRTaskCode]=100, tblAnother.DSRTask5 =
[forms]![DSR].[DSRTaskCode]=101
WHERE (((tblAnother.id_FK)=[forms]![DSR].[rec_ID]));


Obviously you will have to change the table name, the form name and the
field names to match yours.


For VBA code, try:

'Public Sub TestDSR_Update()
' NOTE: there MUST have a reference set to
' Microsoft DAO 3.6 Object Library
'(or whatever your number is for your version of Access)

Dim DSRTC As Integer 'DSRTaskCode number 97, 98, etc
Dim REC_ID As Long ' FK in table Another
Dim strUpdateSQL As String

' record(s) in table tblAnother to be updated
REC_ID = Forms!MyForm.MyRecordID

'get the DSRTaskCode number
DSRTC = Forms!MyForm.DSRTaskCode


'## change "tblAnother" to the name of your table ##
'## change "id_FK" to the name of the linking field ##

'create the SQL string for an UPDATE query
strUpdateSQL = "UPDATE tblAnother SET"

strUpdateSQL = strUpdateSQL & " DSRTask1 = " & DSRTC & " = 97,"
strUpdateSQL = strUpdateSQL & " DSRTask2 = " & DSRTC & " = 98,"
strUpdateSQL = strUpdateSQL & " DSRTask3 = " & DSRTC & " = 99,"
strUpdateSQL = strUpdateSQL & " DSRTask4 = " & DSRTC & " = 100,"
strUpdateSQL = strUpdateSQL & " DSRTask5 = " & DSRTC & " = 101"

strUpdateSQL = strUpdateSQL & " WHERE id_FK = " & REC_ID & ";"

'Do it
CurrentDb.Execute strUpdateSQL, dbFailOnError


'End Sub


Just an observation.... it seems a lot of work to convert the check boxes to
a number, then turn around and convert the number back to check boxes. Can
you save the check box status and convert to the Task Code when you need it?


HTH...

 

[Michelle]

Steve,

Thanks so much! The check boxes are used because that's what the user is
used to seeing! Its strictly cosmetic for them. The real Task Codes are
97,98,99 ...
What I'm trying to do is always load DSR codes from the DSR file into the
subform. There is a chance there will always be more being added and I want
to capture those new ones. But I want to update those checkboxes with the
selections they already have in the permanent tblDSRDMtasks file. So when
they open the form the boxes they already selected show. Basically that's the
reason for having a temporary file in the first place is a) user friendly and
b) the way its really stored. I know this probably isn't the most efficient
way to do this but it's the best I could come up with. Once I get the check
boxes filled out on the screen the user has to be able to update and/or
change them! That's going to be a feat in itself!

Is there a way I can get this to work in reverse? It would read
TaskCode(TaskCode in table has only 1 of these values 97,98,99,100,101) and
fill in the appropriate DSRTask check box in another table? I'm trying to put
this in an update query.

Not sure if you want code or a (saved) query.......so...

For a saved query, start with this -

Create a new query, switch to SQL view and paste in the following:

UPDATE tblAnother SET tblAnother.DSRTask1 = [forms]![DSR].[DSRTaskCode]=97,
tblAnother.DSRTask2 = [forms]![DSR].[DSRTaskCode]=98, tblAnother.DSRTask3 =
[forms]![DSR].[DSRTaskCode]=99, tblAnother.DSRTask4 =
[forms]![DSR].[DSRTaskCode]=100, tblAnother.DSRTask5 =
[forms]![DSR].[DSRTaskCode]=101
WHERE (((tblAnother.id_FK)=[forms]![DSR].[rec_ID]));


Obviously you will have to change the table name, the form name and the
field names to match yours.


For VBA code, try:

'Public Sub TestDSR_Update()
' NOTE: there MUST have a reference set to
' Microsoft DAO 3.6 Object Library
'(or whatever your number is for your version of Access)

Dim DSRTC As Integer 'DSRTaskCode number 97, 98, etc
Dim REC_ID As Long ' FK in table Another
Dim strUpdateSQL As String

' record(s) in table tblAnother to be updated
REC_ID = Forms!MyForm.MyRecordID

'get the DSRTaskCode number
DSRTC = Forms!MyForm.DSRTaskCode


'## change "tblAnother" to the name of your table ##
'## change "id_FK" to the name of the linking field ##

'create the SQL string for an UPDATE query
strUpdateSQL = "UPDATE tblAnother SET"

strUpdateSQL = strUpdateSQL & " DSRTask1 = " & DSRTC & " = 97,"
strUpdateSQL = strUpdateSQL & " DSRTask2 = " & DSRTC & " = 98,"
strUpdateSQL = strUpdateSQL & " DSRTask3 = " & DSRTC & " = 99,"
strUpdateSQL = strUpdateSQL & " DSRTask4 = " & DSRTC & " = 100,"
strUpdateSQL = strUpdateSQL & " DSRTask5 = " & DSRTC & " = 101"

strUpdateSQL = strUpdateSQL & " WHERE id_FK = " & REC_ID & ";"

'Do it
CurrentDb.Execute strUpdateSQL, dbFailOnError


'End Sub


Just an observation.... it seems a lot of work to convert the check boxes to
a number, then turn around and convert the number back to check boxes. Can
you save the check box status and convert to the Task Code when you need it?


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

Thanks

 

[Michelle]

Steve,

What if you have a multiple field key? In this case its DeptID and DSRPartNo?

Steve,

Thanks so much! The check boxes are used because that's what the user is
used to seeing! Its strictly cosmetic for them. The real Task Codes are
97,98,99 ...
What I'm trying to do is always load DSR codes from the DSR file into the
subform. There is a chance there will always be more being added and I want
to capture those new ones. But I want to update those checkboxes with the
selections they already have in the permanent tblDSRDMtasks file. So when
they open the form the boxes they already selected show. Basically that's the
reason for having a temporary file in the first place is a) user friendly and
b) the way its really stored. I know this probably isn't the most efficient
way to do this but it's the best I could come up with. Once I get the check
boxes filled out on the screen the user has to be able to update and/or
change them! That's going to be a feat in itself!

Is there a way I can get this to work in reverse? It would read
TaskCode(TaskCode in table has only 1 of these values 97,98,99,100,101) and
fill in the appropriate DSRTask check box in another table? I'm trying to put
this in an update query.

Not sure if you want code or a (saved) query.......so...

For a saved query, start with this -

Create a new query, switch to SQL view and paste in the following:

UPDATE tblAnother SET tblAnother.DSRTask1 = [forms]![DSR].[DSRTaskCode]=97,
tblAnother.DSRTask2 = [forms]![DSR].[DSRTaskCode]=98, tblAnother.DSRTask3 =
[forms]![DSR].[DSRTaskCode]=99, tblAnother.DSRTask4 =
[forms]![DSR].[DSRTaskCode]=100, tblAnother.DSRTask5 =
[forms]![DSR].[DSRTaskCode]=101
WHERE (((tblAnother.id_FK)=[forms]![DSR].[rec_ID]));


Obviously you will have to change the table name, the form name and the
field names to match yours.


For VBA code, try:

'Public Sub TestDSR_Update()
' NOTE: there MUST have a reference set to
' Microsoft DAO 3.6 Object Library
'(or whatever your number is for your version of Access)

Dim DSRTC As Integer 'DSRTaskCode number 97, 98, etc
Dim REC_ID As Long ' FK in table Another
Dim strUpdateSQL As String

' record(s) in table tblAnother to be updated
REC_ID = Forms!MyForm.MyRecordID

'get the DSRTaskCode number
DSRTC = Forms!MyForm.DSRTaskCode


'## change "tblAnother" to the name of your table ##
'## change "id_FK" to the name of the linking field ##

'create the SQL string for an UPDATE query
strUpdateSQL = "UPDATE tblAnother SET"

strUpdateSQL = strUpdateSQL & " DSRTask1 = " & DSRTC & " = 97,"
strUpdateSQL = strUpdateSQL & " DSRTask2 = " & DSRTC & " = 98,"
strUpdateSQL = strUpdateSQL & " DSRTask3 = " & DSRTC & " = 99,"
strUpdateSQL = strUpdateSQL & " DSRTask4 = " & DSRTC & " = 100,"
strUpdateSQL = strUpdateSQL & " DSRTask5 = " & DSRTC & " = 101"

strUpdateSQL = strUpdateSQL & " WHERE id_FK = " & REC_ID & ";"

'Do it
CurrentDb.Execute strUpdateSQL, dbFailOnError


'End Sub


Just an observation.... it seems a lot of work to convert the check boxes to
a number, then turn around and convert the number back to check boxes. Can
you save the check box status and convert to the Task Code when you need it?


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

Thanks