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.

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



"Aldred@office" <aldred> wrote in message
news:(E-Mail Removed)...
> 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.

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



"Aldred@office" <aldred> wrote in message
news:(E-Mail Removed)...
> 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.