Comparing Entries in DB Tables

[moose]

I have a slight problem that I need to know if it possible to overcome:

I have written a VB program using Access as data storage. I have a column in a table that requires unique entries. (Meaning that no 2 fields can have the same data.) I have a primary key set on that field. Is it possible to check what the user enters against the entries in the database before the new entry gets added to the database? If so, how is it done?

One more question: Here lately most of my programs I have written with a VB front end and Access back end. I'm using Access to develop the queries and create the reports from those queries. Is there a downside to doing this? Should everything be done in VB and just use Access for storage of the data? Or does it really matter?




[Edited by moose on 09-28-2000 at 11:10 AM]

[xmin]

Is the following helpful?

With rstYourTableRecordSet
'Text1.Text=User's entry
.Findfirst ¡°YourColumnLable = ¡° & Trim(Text1.Text)
If .NoMatch Then
.AddNew
!YourColumnLable = Trim(Text1.Text)
.Update
Else
MsgBox ¡°Entry invalid!¡±
End If
End With

[moose]

Xmin,

I took your code and made the changes below:

.NoMatch is not defined by VB. Is this something that you wrote?


With rsRecordSet
.Open "[Client Info]", conn, , , adCmdTable
.Find ("Client_No") & Trim(txtClientNumber.Text)
If .NoMatch Then
.AddNew
.Fields("Client_No") = txtClientNumber.Text
.Fields("Client Name") = txtClientNa.Text
.Update
.Close
Else
MsgBox "Entry Invalid"
End If

End With

[murtagh]

If it was me I would not use Access to develop the queries but try SQL queries from within the VB programme. A lot more powerful and flexible espically with ADO which supports more or the basic SQL and Oracle query commands such as sum,Max etc

If you look at my reply to your previous post you will see a simple sql query in there.

hope this helps

[xmin]

True,Moose. I was referring to DAO 3.51. .NoMatch is a property of Microsoft Jet. I hoped the idea could be helpful to you. With DAO, the complete code should be something like this:

Dim ws as Workspace
Dim db as Database
Dim rst as RecordSet
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(¡°DatabaseName.mdb¡±)
Set rst = db.OpenRecordSet(¡°TableName¡±)
¡®Then the other code