Re: cycle through a table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 01/08/05 

Date: Fri, 7 Jan 2005 21:15:58 -0500

John's given you SQL, not VBA code. 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Phil" <phil@discussion.microsoft.com> wrote in message 
news:9D540ABC-2627-43D6-8872-C20C21919F28@microsoft.com...
> Hi again John,
>
> I've spent hours adding and revmoing lib and trying different code to get
> this "Update" finction to work and so far I still get "sub or function not
> defined".  The libs that I currently have are:
>
> Visual Basic for Applications
> Microsoft Access 9.0 Object Library
> OLE Automation
> Microsoft ActiveX Data Objects 2.1 Library
> Microsoft DAO 2.6 Object Library
>
> Is it possible to find our what lib the "Update" finction is in?  Do I
> possibly have a lib problem?  I'm about to give up on this utility 
> function I
> wanted to add to my db.
>
> Thanks,
> Phil
>
> "John Spencer (MVP)" wrote:
>
>> Well, in an SQL statement there wouldn't be any continuation characters 
>> allowed.
>>
>> You can also use the switch function.
>>
>> UPDATE Students
>> Set Grade = Switch ([Grade] = "1st","2nd",[Grade]="2nd","3rd",...)
>> Where Grade <> "12th"
>>
>> As far as the error you were getting, you may be missing a library 
>> reference.
>>
>> To do its job, Access (like most modern programs) makes use of various 
>> external
>> program and object libraries that provide functionality that may be 
>> shared among
>> applications.  For example, Access always uses the Visual Basic for 
>> Applications
>> library, the version-appropriate Access Object Library, and the OLE 
>> Automation
>> library.  References to the specific library files, including their 
>> locations,
>> are stored with your database.  But these library modules may not be in 
>> the same
>> location on different machines, especially if they have different 
>> versions of
>> Microsoft Office.  If you move a database from one machine to another, 
>> these
>> references may be "broken";  that is, one or more of the library files 
>> may not
>> be where the stored reference says it is.
>>
>> When this happens, you need to take steps to let Access repair the broken
>> reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.
>>
>> Here are MVP Doug Steele's instructions for how to do it:
>>
>> *** Quote ***
>>
>> Any time functions that previously worked suddenly don't, the first thing 
>> to
>> suspect is a  references problem.
>>
>> This can be caused by differences in either the location or file version 
>> of
>> certain files between the machine where the application was developed, 
>> and where
>> it's being run (or the file missing completely from the target machine). 
>> Such
>> differences are common when new software is installed.
>>
>> On the machine(s) where it's not working, open any code module (or open 
>> the
>> Debug Window, using Ctrl-G, provided you haven't selected the "keep debug 
>> window
>> on top" option). Select Tools | References from the menu bar. Examine all 
>> of the
>> selected references.
>>
>> If any of the selected references have "MISSING:" in front of them, 
>> unselect
>> them, and back out of the dialog. If you really need the reference(s) you 
>> just
>> unselected (you can tell by doing a Compile All Modules), go back in and
>> reselect them.
>>
>> If none have "MISSING:", select an additional reference at random, back 
>> out of
>> the dialog, then go back in and unselect the reference you just added. If 
>> that
>> doesn't solve the problem, try to unselect as many of the selected 
>> references as
>> you can (Access may not let you unselect them all), back out of the 
>> dialog, then
>> go back in and reselect the references you just unselected. (NOTE: write 
>> down
>> what the references are before you delete them, because they'll be in a
>> different order when you go back in)
>>
>> For far more than you could ever want to know about this problem, check 
>> out
>>    http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
>>
>> Just so you know: the problem will occur even if the library that 
>> contains the
>> specific function that's failing doesn't have a problem.
>>
>> **** End Quote ****
>>
>> So, follow those instructions and see if your problem goes away.
>>
>>
>> Phil wrote:
>> >
>> > Sorry John,
>> >
>> > I left out a continuation (_) character.  But now I get a syntax error.
>> > I've tried messing with the set command and the DLookup, but no luck. 
>> > Here
>> > is my code:
>> >
>> > Update [Students] _
>> >     Set [Grade] = DLookup("[Grade]", "[Grade]", "Grade Like """ & _
>> >     CStr(Val([Students].[Grade]) + 1) & "*""") WHERE [Students].[Grade] 
>> > <>
>> > "12th"
>> >
>> > The students table is "students" with the s on the end.  In my original
>> > posting I just said student.
>> >
>> > Thanks,
>> > Phil
>> >
>> > "John Spencer (MVP)" wrote:
>> >
>> > > Perhaps the following
>> > >
>> > > UPDATE [Student]
>> > > SET [Grade] = DLookup("[Grade]","[GradeTable]","Grade Like """ &
>> > > CStr(Val([Student].[Grade]) + 1) & "*""")
>> > > WHERE [Student].[Grade] <> "12th"
>> > >
>> > > Phil wrote:
>> > > >
>> > > > Hi,
>> > > > I have a DB with among other tables has a "grade" and a "student" 
>> > > > table.
>> > > > The grade table has one field with records of: 1st, 2nd, 3rd....12. 
>> > > > The
>> > > > student table has among other fields: lastname, firstname, 
>> > > > grade......  The
>> > > > grade field of the student table is selected using a drop-down box 
>> > > > with data
>> > > > source being the grade table.
>> > > >
>> > > > I want to place a command button on a form that when it is clicked 
>> > > > all the
>> > > > students grade would be changed to the next higher grade.  On other 
>> > > > words, if
>> > > > the student table has a student in "1st" grade, the grade field 
>> > > > would be
>> > > > changed to the "2nd" grade.  I can do the code for changing the 
>> > > > grade, but I
>> > > > don't know how to cycle though each record in the student table to 
>> > > > be able to
>> > > > change the grade field.
>> > > >
>> > > > Any help would be appreciated.
>> > > >
>> > > > Thanks
>> > > > --
>> > > > Phil
>> > >
>>
Quantcast