Re: Updating records with a fixed sequence...

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 03/07/05 

Date: Mon, 07 Mar 2005 16:59:28 -0500

In that case, you have probably not registered the dao library.

A2K and later versions default to ADO which does not have a Database Object.
Database is an Object of DAO which A97 defaults to. To use your code, you should:

Quoting Doug Steele
Database is a DAO object. By default, Access 2000 uses ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

(Hopefully this explains to you why you can't just use "DIM rst as
Recordset") 

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
Carlo Mechanic wrote:
> 
> Hi John,
> 
> I got the suggested and inserted it in the event tab of my Command0 cmd
> button. I am getting a "User-defined" type error at DIM dbAny as DAO.Database
> 
> Here is the full code:
> 
> Option Compare Database
> 
> Private Sub Command0_Click()
> Dim dbAny As DAO.Database  ' compile error in this line - User-defined type
> not defined.'
> Dim rstTarget As DAO.Recordset
> Dim rstSource As DAO.Recordset
> Dim StrSQL As String
> 
> Set dbAny = CurrentDb()
> 'Get available machines
> StrSQL = "SELECT MachineCode FROM MachineCodes " & _
>      "Where CategoryA = True " & _
>      "Where CategoryB = True " & _
>      "Where Available = True " & _
>      "Order By MachineCode"
> 
> Set rstSource = dbAny.OpenRecordset(StrSQL)
> 
> If rstTarget.RecordCount < 1 Then
>   'No machines are marked available
>   Msgbox "No Machines available"
> 
> Else
>   'Get jobs that are not assigned.  I am not sure
>   'of the criteria in this query.  You may need to modify
>   'the where criteria depending on your business rules.
>   StrSQL = "SELECT ProductionOrder, MCode, " & _
>     "FROM JobMaster_test2 " & _
>     "WHERE DateCompleted Is Null " & _
>     "AND JobNumber Is Null " & _
>     "AND MCode is Null " & _
>     "Order By Usr " & _
>     "LatesDate, PartNumber "
> 
>   Set rstTarget = dbAny.OpenRecordset(StrSQL)
>   If rstTarget.RecordCount < 1 Then
>     Msgbox "No Jobs to assign"
>   Else
>     'Do the work by stepping through all the MachineJobs
>     While Not rstTarget.EOF
> 
>       rstTarget.Edit
>         rstTarget!MachineCode = rstSource!MachineCode
>       rstTarget.Update
>       rstTarget.MoveNext
> 
>       rstSource.MoveNext
> 
>       'Whoops! reached the end of the Available Machines
>       'Go back to the first available one.
>       If rstSource.EOF Then rstSource.MoveFirst
> 
>     Wend
> 
>  End If
> End If
> 
> End Sub
> 
> "John Spencer (MVP)" wrote:
> 
> > UNTESTED SUB Follows.  Put this in a module and call it from a button when you
> > need it. Hope this helps.  I was trying to come up with some simple SQL to do
> > this, but no inspiration struck.
> >
> > Sub cmdAssignMachines()
> > Dim dbAny As DAO.Database
> > Dim rstTarget As DAO.Recordset
> > Dim rstSource As DAO.Recordset
> > Dim StrSQL As String
> >
> > Set dbAny = CurrentDb()
> > 'Get available machines
> > StrSQL = "SELECT MachineCode FROM Machines " & _
> >      "Where Available = True " & _
> >      "Order By MachineCode"
> >
> > Set rstSource = dbAny.OpenRecordset(StrSQL)
> >
> > If rstTarget.RecordCount < 1 Then
> >   'No machines are marked available
> >   MsgBox "No Machines available"
> >
> > Else
> >   'Get jobs that are not assigned.  I am not sure
> >   'of the criteria in this query.  You may need to modify
> >   'the where criteria depending on your business rules.
> >   StrSQL = "SELECT JobNumber, MachineCode, " & _
> >     "FROM MachineJobs " & _
> >     "WHERE CompleteDate Is Null " & _
> >     "AND JobNumber Is Null " & _
> >     "AND MachineCode is Null"
> >     "Order By [Priority Sequence], " & _
> >     "[Priority Date] Desc, [PartNumber]"
> >
> >   Set rstTarget = dbAny.OpenRecordset(StrSQL)
> >   If rstTarget.RecordCount < 1 Then
> >     MsgBox "No Jobs to assign"
> >   Else
> >     'Do the work by stepping through all the MachineJobs
> >     While Not rstTarget.EOF
> >
> >       rstTarget.Edit
> >         rstTarget!MachineCode = rstSource!MachineCode
> >       rstTarget.Update
> >       rstTarget.MoveNext
> >
> >       rstSource.MoveNext
> >
> >       'Whoops! reached the end of the Available Machines
> >       'Go back to the first available one.
> >       If rstSource.EOF Then rstSource.MoveFirst
> >
> >     Wend
> >
> >  End If
> > End If
> >
> > End Sub
> >
Quantcast