Record Set won't work

[claven123]

0




I can't seem to get this code to work. I'm trying to pull data from a table.
I get an error on the rs1.OpenRecordset "[TblMembers]" line.

Thanks,

Private Sub CmdPrntTrukOff_Click()

Dim db As DAO.Database
Dim  rs1 As DAO.Recordset
Set db = CurrentDb()

rs1.OpenRecordset  "[TblMembers]"

Dim Word As New Word.Application
Set Word =  CreateObject("Word.Application")

Dim WrdPrnt As String
WrdPrnt  = Application.CurrentProject.Path
WrdPrnt = WrdPrnt +  "\TruckOfficers.dotx"

Word.Documents.Add WrdPrnt
Word.Visible =  True

Dim txtCpt1 As Variant

txtCpt1 = ""

If  (rs1.Fields(4).Value = [Capt #1]) Then txtCpt1 = txtCpt1 & "Help:"  & rs1.Fields(2)
 
With Word.ActiveDocument.Bookmarks


.Item("Cpt1201").Range.Text  = txtCpt1

End With

rs1.Close
 
Set rs1 = Nothing

End  Sub

[ajetrumpet]

there is a double space between ''openrecordset'' and the double quotes.

take the brackets off of the table name.

also, there is no ''recordset.openrecordset'' method in DAO. only ADO does. it is used with the ''db'' object. so change it to:

db.openrecordset("table")

also, you don't need both of these:

Dim Word As New Word.Application
Set Word =  CreateObject("Word.Application")

you're using both binding methods by doing it, and it is not the desirable way to use resources.

use either:

Dim Word As Word.Application
set word = new word.application 'not necessary
word.visible = true

or:

Dim Word As object
Set Word =  CreateObject("Word.Application")
word.visible=true

[claven123]

Well, that seemed to do the trick.

1. how do I see the recordset?

2. How do I do something to it.


My code gets a bug at this line....

If (rs1.Fields(4).Value = [Capt #1]) Then txtCpt1 = txtCpt1 & "Help:" & rs1.Fields(2)

I guess I don't know what to do with the recordset when I get it.

I want to pull the name of the person who matches the position they hold and place it on the word template.

The fields are [Position] and the [LastName] the position name is Capt #1
These are all in the TblMembers or my new recordset.

I really need to do this for several position names.. but that is for later.

Thanks,

Dennis

[ajetrumpet]

Dennis,

recordsets should seriously be scrapped by MS, because they take more code than most others in this language, and it's always a pain.

But regardless, I'm not going to explain the WHYs, but here are the rules that will help you continue this progress:

*Sets cannot be read at all until they are made completely visible to the language. To do this, this code ALWAYS need to be written after it is opened:

recordset.movelast
recordset.movefirst

*recordset fields can be referenced more than one way, but the most common is to use the "!" character. You are not doing it this way, so take your line of code that is in error:

If (rs1.Fields(4).Value = [Capt #1]) Then txtCpt1 = txtCpt1 & "Help:" & rs1.Fields(2)

There is actually nothing wrong with it, and I'm guessing the error is a result of not providing the navigation code I gave to you above. But you should change some things so you don't get lost.

First, break your conditions. It's hard to read otherwise:

If (rs1.Fields(4).Value = [Capt #1]) Then 
   txtCpt1 = txtCpt1 & "Help:" & .........

Second, unlike most other languages, conditions don't need to be enclosed in anything, brackets or parenthesis:

If rs1.Fields(4).Value = [Capt #1] Then

Third, be aware that the ''field'' object in a recordset indexes at 0. So the count starts there. In your code,

rs1.field(4)

refers to the 5th field in the table, for the same reason that (0) refers to field #1.

And lastly, in vba, the ''.value'' property is hardly ever needed when writing common code. It is interpreted as ''implied'' by visual basic. Thus, this:

If rs1.Fields(4).Value

can be this:

If rs1.Fields(4)

That's not necessary, but it saves time if you're working the fingers pretty hard.

[claven123]

So, I will use this...

if rs1.fields(4) = [Capt #1] Then
  txtCpt1 = txtCpt1 & "rs1.fields(2)"

With the second half, I want the value of the Name to be printed out in word, not the Capt #1. The person that is Capt #1 will have his name on the form.

In my thinking if the position of Fields(4) is Capt #1 then his name [Fields(2)]will be sent to word to be placed at the index.


Will this work?


Thanks so much....

BTW, is there another way I should be doing this besides recordsets? I can make queries and send the data that way. I was going to make a query for each position...


Thanks,

[claven123]

I get an error in the same line, at the THEN statement.

Word opens, but I get an Run-time error '2465'

Can't find the field '|' referred to in the expression.


When I hover over the yellow text I see that a message comes up that says....

rs1.Fields(4) = "Lt Ole 3" which is one of the other positions, not Capt #1.

The txtCpt1 = ""

I'm confused.

Thanks,

Dennis

[ajetrumpet]

I get an error in the same line, at the THEN statement.

Word opens, but I get an Run-time error '2465'

Can't find the field '|' referred to in the expression.


When I hover over the yellow text I see that a message comes up that says....

rs1.Fields(4) = "Lt Ole 3" which is one of the other positions, not Capt #1.

The txtCpt1 = ""

I'm confused.

Thanks,

Dennis

are you looking for the VALUE or field NAME? there's a difference. for the field name:

rs1.Fields(4).name

for the value in the field:

rs1.Fields(4)

and if you're saying 'position', but meaning 'record', then you need to MOVE to that record. Have you worked with Java? That, and other similar languages use things called pointers that have to be 'pointed' to an object before you can use it for anything. this is the same thing. when you move records in a vb recordset, the method that you use moves a pointer. After that, you are basically 'on' the record, just as though you would type in the same criteria in a query and get one record back.

is that the problem maybe? if you kept my example the way it was, verbatim, then you're on the first record, which I'm sure you don't want. So move the pointer. you can do it via these:

rs1.movenext 'moves 1 down
rs1.move LONGVALUEHERE 'moves the pointer this number of records down

check these out in the help menu. all of the movement-type methods are common sense.

[claven123]

That would make sense, since that person is the Lieutenant of the old engine 3. He is the first person in the table.

No, I am looking for the value. I know the name of the field. It's Position.


Thanks.


Now, what I need to do is loop through every record and see which one matches the criteria of being Captain of Engine 1201, which is Capt #1. This is a value stored in the position field in the table TblMembers. Then I hope to return the value of the LastName field or even better combine the LastName and FirstName fields.

Then send this information to the bookmark in word.

If Not rs1.EOF Then


rs1.MoveFirst



Dim DoUntil()
      

'can I place my code from above here?
 

     Do Until

' can it loop records until it finds the "Capt #1"  Then it becomes true...  and returns the output to word...


     Loop

In theory can I set up a loop for every position? Say 14 of them?

Dennis

[ajetrumpet]

you need to do a little research on the recordset, Dennis.

Here is how you loop on through the recs:

rs1.movelast
rs1.movefirst

do until rs1.eof
   'if capt #1 then
      'send whatever to word
   end if
         rs1.movenext
loop

and this is how you loop the fields in a single rec:

for each fld in rs1.fields '"fld" has to be dimmed as "dao.field"
   'rs1.fields(fld.name) 'this gives the value of that field
next fld

I can't help you with the word part, but that's the database portion of it!

[claven123]

That's the sad part, is I have been. However, I can't seem to find a good place to look. Everyplace I go is different and it's more confusing. Some of the better places seem to use the northwinds database. Which is fine, but that does not help me.

The book I have does not use DAO, but uses ADO and makes note of a connection. It goes over the recordset and looping etc...

I keep getting the error on opening the record set. I think I might use the ADO option.

Or, would it be easier to just open a saved query?

Thanks for all your help it has been very appreciated!!

[ajetrumpet]

Dennis,

ADO is much more difficult to learn. Why not just give me the entire code that you have now, and I'll give it back to yo with the database portion of it done, OK?

This is so simple, it'll take me 30 seconds I'm sure. And BTW, as soon as I get done with my dreamweaver work for my new domain's site, people will not have to worry about not being able to find help on VBA in English. The site will have a VBA library integrated into it, where you can enter a request and access pre-written code directly from the DLLs from which it is found.

I've seen so many people say what you've just said, and I'm kind of sick of it. I have no idea why peoplle can't understand basic like I do, but I guess that's for the same reason that I ask the same stupid questions on javascript forums. They're probably thinking the same thing.

The funny part about stuff like this is that, if you're not a technology professional, it's easy to think that all of these OOP languages are extremely different and sport very different concepts, but the reality is that all of them are the same. The syntax simply changes, and that's about all. It's just like SQL. Access uses a dot, oracle doesn't, yada yada...

anyway, give me what you got and I'll throw it back up here for you.

[claven123]

Here is what I have, it's a mess. I do understand about not being able to find what I need. I would love to be able to learn this stuff and know what I'm doing, but I can't seem to find a good reference for it. Someone said someplace that you can program it nine different ways and a million ways to screw it up.

Here is what I have so far....

Private Sub CmdPrntTrukOff_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim Word As Object
Dim WrdPrnt As String
Dim txtCpt1 As Variant

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TblMembers", dbOpenDynset)
Set Word = CreateObject("Word.Application")

db.OpenRecordset ("TblMembers")

Word.Visible = True

'WrdPrnt = Application.CurrentProject.Path
'WrdPrnt = WrdPrnt + "\TruckOfficers.dotx"

'Word.Documents.Add WrdPrnt
'Word.Visible = True

txtCpt1 = ""

If (rs1.Fields(4) = [Capt #1]) Then txtCpt1 = txtCpt1 & "Help:" & rs1.Fields(2)
 
With Word.ActiveDocument.Bookmarks

Item("Cpt1201").Range.Text = txtCpt1

End With

rs1.Close
Set rs1 = Nothing

End Sub

I'm very thank full for your help, and hope to see your website for help. I would love to learn how to do this instead of always asking for help. I think a course in this stuff would be great.

Thanks

[ajetrumpet]

so, given what you had, here's how you loop it, check the position field for the val of "capt #1", and 'do something' with the concatenation of the 'first name' and 'last name' fields:

Private Sub CmdPrntTrukOff_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim Word As Object 'RENAME THIS VAR (same as app name is not typical)
Dim WrdPrnt As String
Dim txtCpt1 As Variant

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TblMembers", dbOpenDynaset) 'WAS MISSPELLED
Set Word = CreateObject("Word.Application")

Word.Visible = True

'WrdPrnt = Application.CurrentProject.Path
'WrdPrnt = WrdPrnt + "\TruckOfficers.dotx"

'Word.Documents.Add WrdPrnt
'Word.Visible = True

txtCpt1 = ""

with rs1
   .movelast
   .movefirst
      do until .eof
         if .fields(4) = "capt #1" then
            txtcapt1 = txtCpt1 & "Help:" & rs1.fields(2) & " " & rs1.fields(3) & vbcrlf
         end if
               .movenext
      loop
end with
 
With Word.ActiveDocument.Bookmarks

Item("Cpt1201").Range.Text = txtCpt1

End With

rs1.Close
set db=nothing
Set rs1 = Nothing

End Sub

Now, with that of course, the var when it gets sent to the word app will look something like:

Help: John Doe
Help: Jane Doe
Help: Next Person

So, if you want to throw those values, one at a time into different bm's in the word app, before the ''end if'' section in the loop, write the code to do that. If you want to send the variable over with all of the consolidated info in it, like I've just written, then leave it like it is.

the ''vbcrlf'' stands for 'visual basic character line feed'. Without that, the consolidation of all the info would look like so:

Help: John DoeHelp: Jane DoeHelp: Next Person

make sense?

hope that helps you on your way.

and when my website is done, I'm sure there will appear a link in my signature. So if you want to check out those resources in the future, keep an eye on that. I'm sure I'll be done with it soon.

Have a good night!

[ssanfu]

The fields are [Position] and the [LastName]  the position name is Capt #1

FYI, "POSITION" is a reserved word in Access/SQL and shouldn't be used for object names. For a list of reserved words, see http://allenbrowne.com/AppIssueBadWord.html#P

.......

BTW, is there another way I should be doing this besides recordsets? I can make queries and send the data that way. I was going to make a query for each position...

There are several ways to get the data, one of which is to use a recordset.
Looping thru the recordset might take a while, depending on how large the recordset is.

Instead of opening the recordset on a table, you could use a SQL ("SELECT ...FROM ... WHERE... ") statement with the where clause to return the specific record.

Or you might try using the .Findfirst method. It does all the heavy lifting......

I couldn't test it, but this is what I came up with.

Private Sub CmdPrntTrukOff_Click()

   Dim db As DAO.Database
   Dim rs1 As DAO.Recordset

   Dim Word As Object
   Dim WrdPrnt As String
   'Dim txtCpt1 As Variant
   Dim strCriteria As String

   Set db = CurrentDb()
   Set rs1 = db.OpenRecordset("TblMembers")
   ' Populate recordset.
   rs1.MoveLast

   Set Word = CreateObject("Word.Application")
   Word.Visible = True

   WrdPrnt = Application.CurrentProject.Path
   WrdPrnt = WrdPrnt + "\TruckOfficers.dotx"

   Word.Documents.Add WrdPrnt

   'txtCpt1 = ""

'----- 1st search ----
   ' build search string
   strCriteria = "[Position] = 'Capt #1' "
   
   ' Find first record satisfying search string.
   rs1.FindFirst strCriteria
   
   ' always check if match found
   If rs1.NoMatch Then
      'not found
      Word.ActiveDocument.Bookmarks.Item("Cpt1201").Range.Text = "Not assigned"
   Else
      'found
      Word.ActiveDocument.Bookmarks.Item("Cpt1201").Range.Text = "Help:" & rs1.Fields("LastName") & ", " & rs1.Fields("FirstName")
   End If

'----- 2nd search ----
   ' build search string
   strCriteria = "[Position] = 'Lt Ole 3' "
   
   ' Find first record satisfying search string.
   rs1.FindFirst strCriteria

   ' always check if match found
   If rs1.NoMatch Then
      'not found
      Word.ActiveDocument.Bookmarks.Item("Lt Ole 3'").Range.Text = "Not assigned"
   Else
      'found
      Word.ActiveDocument.Bookmarks.Item("Lt Ole 3'").Range.Text = "Help:" & rs1.Fields("LastName") & ", " & rs1.Fields("FirstName")
   End If

'----- 3rd search ----
'
'
'

Clean_up:
   rs1.Close
   Set rs1 = Nothing
   Set db = Nothing

   ' Still need to save and close the word object you created


End Sub

[claven123]

THANKS for the help, been busy at work so can't test it out. I will do that and let you know. Thanks for helping me out with this.

Dennis