Filtering old information 2

[JBuckley2000]

Hey guys,

In a table, I have certain fields that in the past the user would insert a "Y" if a product met certain criteria. What I want to do instead, is have the user put in an ID # for one field, instead of having 10 different fields for the 10 different codes---how can I filter out all of the Y's from before, so I can delete the seperate columns and just have one column for an ID #? THanks,

Jason

 

[PaulF]

create the ID field in your table, then write code that will loop thru the table and update the ID field with the value based on which field contains the X.

Dim db as DAO.Database, rst As DAO.Recordset
Dim strCode as String
set db = CurrentDB
set rst = db.OpenRecordset("TableName&quot
with rst
.movefirst
Do While not .EOF
If Field1 = "Y" then
strCode = "1"
ElseIF ![Field2] = "Y" then
strCode = "2"
...... thru field #10
End if
.Edit
![IdField] = strCode
.Update
strCode = ""
.MoveNext
Loop
End With

rst.Close
Set db = Nothing
Set rst = Nothing



PaulF

 

[JBuckley2000]

Thanks Paul for replying so quickly,

My problem is I am not good at code...I just use the wizards and much simpler things since I am new at Access. Where do I put the code...I understand most of it, but I don't know where to start. I am sorry I don't know too much about it, and if it seems like too much to help me out here, I understand. Thanks for replying,

Jason

 

[PaulF]

Jason

no problem..... what I tend to do, especially in a situation like this one where I'm only going to use the code once. is to build a form with one command button on it. Attach the code to the click event for the button, then click on the button and it should work. BTW this code "assumes" that only one field contains a "Y". If you have more than one field per record that contains the "Y' then you'd have to revamp the code to account for it.
I'll be on and off line for the next hour or so if you have any problems... and If I'm not here, someone else will probably jump in and help.


PaulF

 

[JBuckley2000]

Hey Paul,

Well, I am having a problem when I press the button--it says I am having a Click error or something for some reason. I wrote out the code, but I do not where to put my specific information into it---here is what I have (The table I am filtering is called "RA" and the field I am wanting to transfer into number codes is "DEFECT CODE":

SUB Button1_Click()


Dim db as DAO.Database, rst As DAO.Recordset
Dim strCode As String
set db = CurrentDB
set rst = db.OpenRecordset("RA&quot
with rst
.movefirst
Do While not .EOF
If Field1 = "Y" then
strCode = "1"
ElseIF ![Field2] = "Y" then
strCode = "2"
...... thru field #10
End if
.Edit
![IdField] = strCode
.Update
strCode = ""
.MoveNext
Loop
End With

rst.Close
Set db = Nothing
Set rst = Nothing


Other than what you have told me, I just don't know how to make your code work in my database. Sorry I haven't had any training in Access yet and I have had to teach myself everything I know. Thanks for taking the time to write me back. Have a great day,

Jason

 

[MichaelRed]

Do While not .EOF
 If [b]![/b][i][Field1][/i] = "Y" then
  strCode = "1"
 ElseIF ![i][Field2][/i] = "Y" then
  strCode = "2"
 ...... thru [i]field10[/i]
 End if
 .Edit
 ![IdField] = strCode

The "!" in bold in the Line If [Field1] is just a typo PaulF left out of the original.

The items in italics are what Paul wants you to replace ... with the field names from you table "RA". Also you need to "finish" the pattern of 'elseif' situations for the fields 3 through 10 (thats what he means by the
[tab]"..... thru Field #10"


If you still get an err, please note the exact and entire error messge. It really will help here.

note that the "....." stuff really means for you to finish the pattern, it is not intended to literally be part of the code.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JBuckley2000]

Hey Michael,

I don't know if you copied your information to the post correctly, but I am not seeing anything in italics or bold...and what are the "b's" and "i's" for? Sorry I am so moronic in programming. Thanks and I am thinking I am getting everything else you guys are telling me---


Jason

 

[MichaelRed]

Mea culpa!

I didn't understand some of the interaction in the "tgml". using the markup for "code" over-rode the mark up for Bold/Italics - these ended up being the 'strange & things. The code - should have looked something like the below:


Do While not .EOF
If ![Field1] = "Y" then
strCode = "1"
ElseIF ![Field2] = "Y" then
strCode = "2"
...... thru field10
End if
.Edit
![IdField] = strCode



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JBuckley2000]

Hey Michael,

I was replacing the code that you gave me, but when I am putting in ".movefirst" or ".EOF", it is telling me that a statement is expected. What does this mean? SHould I just type out the remaining code then maybe it will work>?

J

 

[MichaelRed]

What I sent was only meant to 'replace' the corresponding lines in PaulF's original code.

So the whole enchaldia:
PaulF's Starting point
---------CUT Here to Use Code -----------------

SUB Button1_Click()


Dim db as DAO.Database, rst As DAO.Recordset
Dim strCode As String
set db = CurrentDB
set rst = db.OpenRecordset("RA&quot
with rst
.movefirst

'Michael's Touch up

Do While not .EOF
If ![Field1] = "Y" then
strCode = "1"
ElseIF ![Field2] = "Y" then
strCode = "2"

'[tab]'Copy the above 2 lines; change the
'[tab]'Field2 to Fieldn
'[tab]'and Change "n" to "n
'[tab]'for field3 ...... thru field10

End if
.Edit
![IdField] = strCode

'end Michael's "touchup"
'Continue w/ PaulF's original post/code

.Update
strCode = ""
.MoveNext
Loop
End With

rst.Close
Set db = Nothing
Set rst = Nothing

End Sub
----------------------- End Cut of Code ----------------

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over