Record verification

[jayceecatbagan]

Hi all,

I have never programmed in VB and I created a database that has a search field. The problem is when a user types in a data that is not in the database, it would still look give a result. For example, the form has a field to search for a lot number. If the user puts a lot number that is not in the database, how would I code it in VB? I appreciate the help I can get. Thanks.

[Hack]

Welcome to the forums.

Moved from the FAQ section

I made a guess in terms of where to put this. I need to know the version of Visual Basic that you are using. Is it VB6 or VB.NET?

Also, what database are you using?

[jayceecatbagan]

I created a MS Access database and using VB6. Thanks for the help.

[MartinLiss]

If you have the answer you need you can help us by pulling down the Thread Tools menu and clicking the Mark Thread Resolved button.

[wes4dbt]

You could use a select query.

Dim rs as New ADO.Recordset

rs.Open "select field1,field2  from sometable where field1=<somevalue>",yourconnection
if rs.Recordcount then
    msgbox "found item"
else
    msgbox "Not found"
end if

[jayceecatbagan]

This is the code for the find button:

Private Sub FindCmd_Enter()
     Dim rs As Object
     
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[UDLLotNumber] = '" & Me![Text142] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
End Sub

UDLLotNumber is a field in the form and Text142 is the field where users can type in UDLLotNumber to search. My concern is that how would I code a situation wherein the user typed in a UDLLotNumber that is not in the database? I created the database using MS Access. Thanks for the help.

[wes4dbt]

Not sure I understand, but this is how you handle both situations

If <item is found> then
' do something when found
else
' do something when not found
endif

[jayceecatbagan]

The algorithm would be something like the one below:

If user enters a UDLLotNumber that is not in the database in the search field

Then

Display a message "UDL Lot No. is not found or not in the database."

Else

Find the record


How would I code this on the OnClick method of the Find command button I created in the MS Access form?

Thanks again for all the help.

[MartinLiss]

...How would I code this on the OnClick method of the Find command button I created in the MS Access form?

Thanks again for all the help.

Based on your last post you make it clear that you are not using VB6 but rather VBA (VB 6.3) and so I've moved your thread to this forum.

[wes4dbt]

I described that in my first post.

[jayceecatbagan]

But how would I code it in VB? I don't have any experience coding in VB. Thanks for the help.

[wes4dbt]

Look in the "FAQ" section of the Visual basic 6 and Earilier and Database Development forums. You'll find alot of help there on General VB6 programming and ADO programming.

[MartinLiss]

Which one of these statements is correct for you?

1. I am doing my coding with Access in an Access form.
2. I am doing my coding in the VB6 IDE.
3. I am doing my coding in both places.

[jayceecatbagan]

I would say in both places. I created the command button in the access form and on the OnEnter event procedure, I inserted the code that I mentioned on the previous thread. I tested the Find button with the above mentioned code and it works. My concern is that when a user enters in the search field (control source is a column in the table I created, UDLLotNumber) a UDLLotNumber that is not in the table, it should give a message that the UDLLotNumber the user is searching for is not in the database. How can I accomplish this with a VB code, which is tied to the Find button?