Updating multiple fields in one query

[nej2323]

Hello - I am not a newbee - but I really do not like code - and I need an easy query to update about 70 fields - from 1's to Yes.

I have an online form that used check boxes - I need to download this info into my database into Yes/No fields.

I know I have figured this out before - so I know it is doable - but the way to do it escapes me right now.

ANY HELP?? Would be much appreciated.

 

[vbaInet]

Have you looked at using the Append Query? Follow the query wizard, select your fields and follow the process till the end. Then change it from SELECT query to an APPEND query.

In the Criteria row put 1
In the Update To row put Yes

Welcome to the forum

 

[nej2323]

I need an update query because I already have the data in a table that has the 1 - in it - I could make up 70 separate update queries but I wanted an easier way

 

[vbaInet]

Sorry I meant Update not Append.

70 fields on ONE table?

 

[nej2323]

Ya - the form I am using restates the same info 4 times for 4 different peoples information - I know it should just have the same info once and the info should be submitted 4 times - but it is a form for a school with users that area not very computer savy and I want all the info on one continuous form.

Once the data is in the database and updated - it is then sorted into 2 tables - without multiple data

So again my problem with making up 70 queries - I can do it but I would like and easier way.

 

[vbaInet]

I fear that your tables may not be normalised seeing that you have 35 fields on one table. I wouldn't go into too much detail but you can google the term and if you want more links I could give.

If your fields are positioned from 1 to 70, where 1 is the second field NOT the first. The first is 0 in this case.

dim i as integer, rst as DAO.Recordset, db as DAO.database

set db = Currentdb
set rst = db.openrecordset("tableName")

with rst
    do while not .eof
         for i = 1 to 70
              if .fields(i).value = 1 then
                   .edit
                   .fields(i).value = Yes
                   .update
              end if
         next
         .movenext
    loop
end with

rst.close
set rst = nothing
db.close
set db = nothing

Have you thought about using Excel for this instead?

 

[nej2323]

Well I may not have exactly 70 fields but there are allot all the fields are listing when I download them from the excel file (the info from the website is downloaded into an excel file) - I need to use Access because I need the data to be in a database so I can query different info different departments and events. And keep a running Membership database for an orginization so - Excel will not work for what I need.

So here is my problem - where do I put the code - again I knew I was going to have to use code - but I am terrible at using it.

 

[vbaInet]

Excel will be your quickest option. Get the 1s converted in Excel first then import into your db. Makes your life easier. You get the formula right for one cell, copy it down (or fill it) to the bottom and across.

=If(A1=1, Yes, A1)

For the code, put it in a module. The function in the module should be declared as Public.

http://baldyweb.com/Function.htm

If you want to know more about functions, you would need to do some more research. Google is your friend.

 

[nej2323]

Well neither of them worked - I but in the code - and it didn't give me a public option but a General Option - do I then have to run the code - again lost when it comes to code.

In Excel - I tried to put in the formula and it told me it was a circular referance - there has to be a simpler way. Again I am stuck - I really am not a code person.

Is there a simple way to do it with a query??

 

[vbaInet]

If there was a simple way using a query then I would have suggested it. What you're asking for is not a simple task in Access if you don't know how. Simple to me but not to you and hard to explain. When you create a new module write this two lines below where it says Option Compare Databases:

Public Function ChangeToYes()

End Function

In between those two lines you dump the code. Save the module and call it mdlChange1s

For the excel option, you write that code in a blank cell, not in the cell that contains the value.

 

[nej2323]

Okay I put in the code - now do I have to run the code in a macro or something - sorry for all the questions I am completely clueless when it comes to code.

 

[vbaInet]

Have you changed where it says "tableName" to the name of the table?

Are you sure it's in a module?

Are the fields from 1 to 70? If they are not then it would override other fields if the condition is met. As mentioned, 1 being the second column.

 

[nej2323]

I changed it to the Table name - and it is a module - I went to the run function and it gave me an error. -

this is what I have - it give me the error on the If - (i) line - oh and I had way more columns then I thought I did

Public Function ChangeToYes()

Dim i As Integer, rst As DAO.Recordset, db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset("Download 1 MembershipPSO")

With rst
Do While Not .EOF
For i = 1 To 140
If .Fields(i).Value = 1 Then
.Edit
.Fields(i).Value = Yes
.Update
End If
Next
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

End Function

 

[vbaInet]

140 columns, split into two parts makes it one table with 70 tables. You will have problems in the future with your db when it begins to grow and by the time it would be even more difficult to normalise.

What is the exact error message? Does it run any update at all?

In the mean time replace it:

If (.Fields(i).Value & "") = "1" Then