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?

You can set up a dummy table to hold the user input.

Have a query to perform a left join Dummy->Master and add the records only if the Master key column is null.

To see how this is done, in Access, using the QBE grid, join two tables. put your cursor on the blue bar of one of the tables, and drag it down a little, so that the connecting line is crooked.

Place the cursor directly over the connecting line, and right click. There will be three options. Play around with the options and you'll be able to get it.


Good Luck
DerFarm

[moose]

Thank you. I will give this a try.

[murtagh]

this is roughly how I do it;



All I am doing is running a SQL query (dbtBatchIn is the table u are checking, RmaNumber is the field u are checking) to see if the entry exists. Then Recordcount counts the number of entries (In this case how may times unit appears in the database table). I used DAO here but u can also use Recordcount with ADO. I use this method all the time and it does work. If you have any more problems with it mail me at; [email protected] .
hope this helps


Private Sub Form_Load()

'Use the OpenDatabase function to open our database
Set connConnection = OpenDatabase("c:\database\CCRNew_Con")

'private declarations for the database and recordset
Private connConnection As Database
Private rsRecordSet As Recordset



Dim unit As String

unit = InputBox("Please enter your Rma Number..... ?", "Batch In") ' Get name from user, via InputBox


Set rsRecordset = connConnection.OpenRecordset("select * from dbtBatchIn where RmaNumber = '" & unit & "';", dbOpenDynaset)
If rsRecordset.RecordCount = 0 Then
MsgBox "This UNIT is new ..... !!"

call SaveNew'call your function to save the info



Else

MsgBox "This UNIT already exists. Please edit it and then select the edit button to save .. or else quit !!!"

exit sub
end if
end sub

[murtagh]

this is roughly how I do it;



All I am doing is running a SQL query (dbtBatchIn is the table u are checking, RmaNumber is the field u are checking) to see if the entry exists. Then Recordcount counts the number of entries (In this case how may times unit appears in the database table). I used DAO here but u can also use Recordcount with ADO. I use this method all the time and it does work. If you have any more problems with it mail me at; [email protected] .
hope this helps


Private Sub Form_Load()

'Use the OpenDatabase function to open our database
Set connConnection = OpenDatabase("c:\database\CCRNew_Con")

'private declarations for the database and recordset
Private connConnection As Database
Private rsRecordSet As Recordset



Dim unit As String

unit = InputBox("Please enter your Rma Number..... ?", "Batch In") ' Get name from user, via InputBox


Set rsRecordset = connConnection.OpenRecordset("select * from dbtBatchIn where RmaNumber = '" & unit & "';", dbOpenDynaset)
If rsRecordset.RecordCount = 0 Then
MsgBox "This UNIT is new ..... !!"

call SaveNew'call your function to save the info



Else

MsgBox "This UNIT already exists. Please edit it and then select the edit button to save .. or else quit !!!"

exit sub
end if
end sub