DAO recordset object

[Guest]

I'm relatively new to VBA. I thought it would have been very similar to VB
but I'm finding it a bit more difficult than anticipated. I am trying to
select one record using the value in a combox and have the result showing in
a text box. I get error 13 when I run the following code.

Private Sub Combo0_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intNum As Integer
Dim strSQL As String

intNum = Me.Combo0
strSQL = "SELECT Picture.PositionDescription from picture where
Picture.pictureid=" & intNum


Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

If Not rst.EOF Then
Text5 = rst
End If

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I'm not sure if I'm missing a reference or syntax.

 

[Guest]

Use something like:

Me!TextBoxName = rst!FieldName

The assumption is the form is open.

 

[Dirk Goldgar]

In

I'm relatively new to VBA. I thought it would have been very similar
to VB but I'm finding it a bit more difficult than anticipated. I am
trying to select one record using the value in a combox and have the
result showing in a text box. I get error 13 when I run the
following code.

Private Sub Combo0_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intNum As Integer
Dim strSQL As String

intNum = Me.Combo0
strSQL = "SELECT Picture.PositionDescription from picture where
Picture.pictureid=" & intNum


Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

If Not rst.EOF Then
Text5 = rst
End If

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I'm not sure if I'm missing a reference or syntax.

I may be missing something else, but at the least it should be something
like:

Text5 = rst!PositionDescription

or (more qualified syntax):

Me!Text5 = rst!PositionDescription

or (since you know the field you want is the only one in the recordset):

Me!Text5 = rst.Fields(0)

Incidentally, for quick lookups like this, you can use the DLookup
function:

'---- start of example code ----
Private Sub Combo0_AfterUpdate()

Me!Text5 = DLookup("PositionDescription", "Picture", _
"PictureID=" & Me!Combo0)

End Sub

'---- end of example code ----

That's not quite the same, as it will assign Null to Text5 if there is
no Picture on file with that PictureID. That may be good enough for
your purposes, or you could use just a little more code to avoid that.

 

[Tony Toews [MVP]]

or (since you know the field you want is the only one in the recordset):
Me!Text5 = rst.Fields(0)

Ack, I'd never do that. You never know when you might change that
query a month or two later and "whoops, there goes my foot."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

Thank you fro the response. Programmatically what you instructed me to do
makes sense. (I'm assumed because it was only one field I didn't need to
specify the field name. However I get an invalid use of "null error.
Secondly if I use the exclamation mark in "rst!FieldName" should I be using
it in my select statement. even though the DLook up function works, I need
to be able to run a select statement for future use. The DAO reference is
selected by default, is it not?

 

[Guest]

Thanks for your response. it worked perfectly but it is only a band-aid as
this particuar form must be expandable and flexible for later use.

 

[Dirk Goldgar]

In

Thanks for your response. it worked perfectly but it is only a
band-aid as this particuar form must be expandable and flexible for
later use.

You'd need to describe what you're trying to do, in order for us to give
you advice in making your form more flexible.

 

[Guest]

Modify to:
Me!TextBoxName = Nz(rst!FieldName,"")