DAO recordset in Word

[Jack Russell]

I am trying to populate a combo box on a form in a Word template from
an Access database. I can get the whole table or the first record but
I cannot get a SQL statement to work. My code is below:

Dim myWorkspace As Workspace
Dim myDB As DAO.Database
Dim myRS As DAO.Recordset
Dim myquery As QueryDef
Dim mySQL As String


Set myWorkspace = CreateWorkspace("ODBCWorkspace", "admin", "",
dbUseODBC)
Set myDB = myWorkspace.OpenDatabase("codelist", , True) 'codelist is a
DSN
mySQL = "SELECT GreatPlainsBook.[ACCOUNT NUMBER]FROM GreatPlainsBook
WHERE GreatPlainsBook.[DIRECTORATE DETAIL]='LEISURE'"
Set myRS = myDB.OpenRecordset(mySQL, dbOpenSnapshot)
myRS.MoveFirst
MsgBox (myRS.RecordCount)
For i = 0 To myRS.RecordCount - 1

Me.ComboBox1.AddItem (myRS.Fields(0).Value)
myRS.MoveNext
Next i

End Sub

This code returns a record count of -1

Help please!

 

[Martin Seelhofer]

Hi Jack

When using DAO (or ADO, as well) you usually don't rely on the
RecordCount-property since it does not return the number of
records for Snapshots. The -1 is returned by design (and for
performance reasons) to indicate "unknown number of records".
(The actual number is accessible after having looped through
the recordset, though.)

Set myRS = myDB.OpenRecordset(mySQL, dbOpenSnapshot)

This line is ok.

myRS.MoveFirst

The first record is automatically chosen, so this call is useless.

MsgBox (myRS.RecordCount)
For i = 0 To myRS.RecordCount - 1

Me.ComboBox1.AddItem (myRS.Fields(0).Value)
myRS.MoveNext
Next i

This loop does not work (by design). See comments above.

Here's the proper way to do it:

Set myRS = myDB.OpenRecordset(mySQL, dbOpenSnapshot)
' loop until end-of-file mark read
' (this is an analogy to file access)
Do Until myRS.Eof
Me.ComboBox1.AddItem (myRS.Fields(0).Value)
myRS.MoveNext
Loop
' RecordCount now holds the actual number of records, since
' we have gone through the entire recordset.
MsgBox myRS.RecordCount


Cheers,
Martin

 

[Jezebel]

IN DAO you need to populate the recordset before RecordCount will return a
valid number: SImplest way is to do a MoveLast, but you need to check if the
set is empty or you get an error ...

Set myRS = ..
If not myRS.EOF then
myRS.MoveLast
myRS.MoveFirst
End if

But why check it all? Unless the recordset is huge, you can simply use

Set myRS = ...
Do until myRS.EOF
ComboBox1.AddItem myRS(0)
myRS.MoveNext
Loop

Msgbox ComboBox1.ListCount & " books retrieved...."

 

[Daniel Caron]

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Daniel Caron]

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Daniel Caron]

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Perry]

Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

 

[Perry]

Duplicate answer, but hey what the hack, huh?
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

 

[Daniel Caron]

Thanks you very much for your very quick answer....it did work immediatly.
Let say that the found record is not the want I want then I'd want a
FindNext..how would you do it now ?

Daniel

Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Perry]

If you know what y're looking for, why use the "Like" operator instead of
using the "=" operator
to get you yr result directly ?

Krgrds,
Perry

Thanks you very much for your very quick answer....it did work immediatly.
Let say that the found record is not the want I want then I'd want a
FindNext..how would you do it now ?

Daniel

Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Daniel Caron]

That's exactly what I am trying to do but it tells me

Error 2061 - Too Few Parameters, 1 expected.

X$ = Me.txtChamp.Text
'quer = "[ParsNo] = '*" & X$ & "*'"
quer = "[ParsNo] = X$" <--- That the one I am fiddling with.

Regards Perry

P.S. Thanks again

If you know what y're looking for, why use the "Like" operator instead of
using the "=" operator
to get you yr result directly ?

Krgrds,
Perry

Thanks you very much for your very quick answer....it did work
immediatly.
Let say that the found record is not the want I want then I'd want a
FindNext..how would you do it now ?

Daniel

Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as
String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel

 

[Daniel Caron]

Ok I got going now..

That's exactly what I am trying to do but it tells me

Error 2061 - Too Few Parameters, 1 expected.

X$ = Me.txtChamp.Text
'quer = "[ParsNo] = '*" & X$ & "*'"
quer = "[ParsNo] = X$" <--- That the one I am fiddling with.

Regards Perry

P.S. Thanks again

If you know what y're looking for, why use the "Like" operator instead of
using the "=" operator
to get you yr result directly ?

Krgrds,
Perry

Thanks you very much for your very quick answer....it did work
immediatly.
Let say that the found record is not the want I want then I'd want a
FindNext..how would you do it now ?

Daniel

"Perry" <[email protected]> a écrit dans le message de (e-mail address removed)...
Try following:

Set DBPars= _
DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
Set rsPars=DAO.OpenRecordset _
(" SELECT * FROM PARS Where " & quer)
rsPars.MoveFirst
Debug.Print rsPars(0), rsPars(1)
'....etc

Krgrds,
Perry

Based upon DAO36 and the following..

' in Search UserForm
In Search UserForm General Section

Dim DbPars as DAO.Database, rsPars as DAO.Recordset, X$ , quer as
String

Sub cmdSearch()

Set DBPars=DAO.OpenDatabase(ActiveDocument.path &"\" &"Pars.mdb")
Set rsPars=DAO.OpenRecordset("PARS")

If me.optNoReference.Value then
X$=Me.txtField.Text
quer = "[RefNo] Like ' * " & X$ & " * ' "
rsPars.FindFirst quer <-------- Runtime Error here

Runtime Error 3251
Operation is not supported for this type of Object

If rsPars.NoMatch then
msgbox "This Record does not exist.",64,"Data not found."
exit sub
else
...
...
...
endif
End if

What can I do to remedy to this problem ?
In Stand Alone VB6 Pro, it works like a charm

I am using a UserForm with a button

' in Main UserForm
Sub cmdSearch_Click ()
frmSearch.Show
EndSub

Any Helps is appreciated.

Regards

Daniel