Using OpenRecordset(...)

  • Thread starter Thread starter Mac@440th
  • Start date Start date
M

Mac@440th

I am a Access and Visual Basic novice and, as such, need some
assistance. What I assumed would be an easy task is proving to be a
headache for me.

Problem: Obtain a MAX(xx) record from the database based on user input
to a field (Me.WorkGpID), then display that record in another field
(str_ActionItem) on the same form. Here is the code I am currently
using:

Private Sub WorkGpID_LostFocus()
Dim dbs As Database
Dim rs As Recordset
Dim seldata As String

seldata = "SELECT MAX(W.ActionItemNr) FROM tbl_Worksheet W WHERE
W.WorkGpID = '" & Me.WorkGpID & "';"
' MsgBox (seldata)

Set dbs = CurrentDb.OpenRecordset(seldata)

' >>> Here is where I would like the results of my query to be
placed '

[str_ActionItem] = ???

rs.Close
dbs.Close
End Sub

If anyone can point me in the right direction, it would be greatly
appreciated!!

Thanks in advance for all your assistance.
 
Since you didn't provide an Alias name for the field, you can't address it
by name, so you'll have to use its ordinal position:

Me.str_ActionItem = rs.Fields(0)

Okay, that's not strictly true: normally Access will alias the field for
you, usually as Expr1, so you might be able to use rs.Fields("Expr1").
Realistically, though, you'd be best off aliasing the field yourself:

seldata = "SELECT MAX(W.ActionItemNr) As MaxValue FROM ...

and then using rs.Fields("MaxValue") or rs!MaxValue

Note, though, that you really don't need the recordset at all: you could use
the DMax function:

Private Sub WorkGpID_LostFocus()

Me.str_ActionItem = DMax("ActionItemNr", "tbl_Worksheet", "WorkGpId = " &
Me.WorkGpID)

End Sub
 
Doug,
Thanks for the assist! Works greats!

One more question: As I stated in my original query, I am very new
to Access and VB and my learning curve is very huge! Can you please
explain the meaning of the exclamation point in: rs!MaxValue

Thanks again.

Mac

Since you didn't provide an Alias name for the field, you can't address it
by name, so you'll have to use its ordinal position:

Me.str_ActionItem = rs.Fields(0)

Okay, that's not strictly true: normally Access will alias the field for
you, usually as Expr1, so you might be able to use rs.Fields("Expr1").
Realistically, though, you'd be best off aliasing the field yourself:

seldata = "SELECT MAX(W.ActionItemNr) As MaxValue FROM ...

and then using rs.Fields("MaxValue") or rs!MaxValue

Note, though, that you really don't need the recordset at all: you could use
the DMax function:

Private Sub WorkGpID_LostFocus()

Me.str_ActionItem = DMax("ActionItemNr", "tbl_Worksheet", "WorkGpId = " &
Me.WorkGpID)

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mac@440th said:
I am a Access and Visual Basic novice and, as such, need some
assistance. What I assumed would be an easy task is proving to be a
headache for me.

Problem: Obtain a MAX(xx) record from the database based on user input
to a field (Me.WorkGpID), then display that record in another field
(str_ActionItem) on the same form. Here is the code I am currently
using:

Private Sub WorkGpID_LostFocus()
Dim dbs As Database
Dim rs As Recordset
Dim seldata As String

seldata = "SELECT MAX(W.ActionItemNr) FROM tbl_Worksheet W WHERE
W.WorkGpID = '" & Me.WorkGpID & "';"
' MsgBox (seldata)

Set dbs = CurrentDb.OpenRecordset(seldata)

' >>> Here is where I would like the results of my query to be
placed '

[str_ActionItem] = ???

rs.Close
dbs.Close
End Sub

If anyone can point me in the right direction, it would be greatly
appreciated!!

Thanks in advance for all your assistance.
Click to expand...
Click to expand...
 
You will see both the . and the ! used when referring to objects. Basically,
the . indicates the following is a property or method of the object. The !
indicates a user defined object. for example
rs!LastName refers to a field named LastName in the recordset object.
rs.MoveNext is a method of the recordset object that causes it to advance to
the next record in the recordset.
This applies to VBA. Note that SQL references are different.
TableName.FieldName is valid in SQL.

Now we get to Me (it is, of course, all about Me)

You will see Me.txtLastName and Me!txtLastName both used, and are both the
same thing. The period is allowed for the shortcut Me object which always
referes to the current form or report. It does not work external to the
current form or report. If you are referring to a control on a form other
than the current form, you have to use Forms!frmAnotherForm!txtLastName.

Mac@440th said:
Doug,
Thanks for the assist! Works greats!

One more question: As I stated in my original query, I am very new
to Access and VB and my learning curve is very huge! Can you please
explain the meaning of the exclamation point in: rs!MaxValue

Thanks again.

Mac

Since you didn't provide an Alias name for the field, you can't address it
by name, so you'll have to use its ordinal position:

Me.str_ActionItem = rs.Fields(0)

Okay, that's not strictly true: normally Access will alias the field for
you, usually as Expr1, so you might be able to use rs.Fields("Expr1").
Realistically, though, you'd be best off aliasing the field yourself:

seldata = "SELECT MAX(W.ActionItemNr) As MaxValue FROM ...

and then using rs.Fields("MaxValue") or rs!MaxValue

Note, though, that you really don't need the recordset at all: you could use
the DMax function:

Private Sub WorkGpID_LostFocus()

Me.str_ActionItem = DMax("ActionItemNr", "tbl_Worksheet", "WorkGpId = " &
Me.WorkGpID)

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mac@440th said:
I am a Access and Visual Basic novice and, as such, need some
assistance. What I assumed would be an easy task is proving to be a
headache for me.

Problem: Obtain a MAX(xx) record from the database based on user input
to a field (Me.WorkGpID), then display that record in another field
(str_ActionItem) on the same form. Here is the code I am currently
using:

Private Sub WorkGpID_LostFocus()
Dim dbs As Database
Dim rs As Recordset
Dim seldata As String

seldata = "SELECT MAX(W.ActionItemNr) FROM tbl_Worksheet W WHERE
W.WorkGpID = '" & Me.WorkGpID & "';"
' MsgBox (seldata)

Set dbs = CurrentDb.OpenRecordset(seldata)

' >>> Here is where I would like the results of my query to be
placed '

[str_ActionItem] = ???

rs.Close
dbs.Close
End Sub

If anyone can point me in the right direction, it would be greatly
appreciated!!

Thanks in advance for all your assistance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top