DAO coding question opening a record set.

[Aldred@office]

Hi,
I have a piece of codes like this:

Private Sub Submit_Click()

Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef(CheckPartNum, "Select PartNum from PartNum
where PartNum='" & PNum1 & "'")
Set rst = dbs.OpenRecordset("PartNum") 'PartNum is a table name

If Not rst.EOF Then
MsgBox ("Found")
Else
MsgBox ("Not Found")
End If
End Sub


I'd like to check if the submitted partnum is already existed in the PartNum
Table. However, how could I have the Select query executed? With
OpenRecordset("PartNum") it just return the first record in the PartNum.
How could fix this?

Thanks.

 

[Michel Walsh]

If you just want to check for existence, you can test the count:

if 0 = DCount("*", "PartNum" , "PartNum = " & PNum ) then
MsgBox "Not found"
else
MsgBox "found"
end if


Sure, you can also use your query, but with SELECT COUNT(*) FROM ... and
open the recordset from the querydef:


Set rst = qdf.OpenRecordset( type, options, lockedits)


... note that the arguments are all optional.





Vanderghast, Access MVP

 

[Vincent Verheul]

Hi,

In your code you're creating a new query in the database. You would have to
create it with a parameter and execute it later with a value for that
parameter.

There is a much easier way to do this in VBA without creating a new query in
Access:

Function FindPart(PNum1 As String)
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("PartNum", dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.FindFirst "PartNum='" & PNum1 & "'"
FindPart = Not rs.NoMatch
End If

rs.Close

End Function