my record got OrderFormNo and CashBillNo.(one to one insert)
if 1st record OrderFormNo is 123 and CashBillNo is ABC, if 2nd record also same OrderFormNo, the CashBill must be same.
As a result, I want detect if got same OrderFormNo in database but different CashBillNo, will not insert to database.
Your post is a little confusing. Let me paraphrase to see if I understand what you're after.
1. You have a table that includes two columns: OrderFormNo and CashBillNo.
2. You have a row in the table with values OrderFormNo=123 and CashBillNo='ABC'.
3. You want to make sure that every other row you subsequently insert into the table with OrderFormNo=123 also has CashBillNo='ABC'.
4. You want to detect if there are any rows which contain a different CashBillNo, and prevent the row insert if there is (and show an error to the use?).
If the above is incorrect, please clarify what you actually do need.
If the above is correct, I have some questions.
1. What is the DBMS you are using?
2. How are you doing your inserts? E.G. using an ADO data control with bound data grid, using ADO recordset objects, using a SQL statement directly with ADO connection "Execute" method.
Based on the answers to these two questions, I may have more questions.
I'm using Microsoft Access, using Sql query to insert.
My purpose is want detect if CashBillNo already exist in database, but the OrderFormNo is different, will pop out message box and no perform insert command.
No, he's saying that he WANTS to allow multiple rows with the combination 123 and 'ABC'. It's just that if there already exists a row with CashBillNo = 'ABC', then every subsequent row needs to have the same OrderFormNo.
My suggestion is you run a query to test for the existence of rows with the CashBillNo you're trying to insert, where the OrderFormNo is different. It might look something like this:
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Working\InsertFun.mdb;Persist Security Info=False"
Set rs = New ADODB.Recordset
rs.Open "select 1 from MyTable where CashBillNo = '" & Me.txtCashBillNo.Text & "' and OrderFormNo <> " & Me.txtOrderFormNo.Text, cn
If Not (rs.EOF) Then
MsgBox "There's a row with the same CashBillNo and a different OrderFormNo. Insert aborted", vbCritical + vbOKOnly, "Insert Aborted"
cn.Execute "insert into MyTable (CashBillNo, OrderFormNo) values ('" & Me.txtCashBillNo.Text & "', " & Me.txtOrderFormNo.Text & ")"
Set rs = Nothing
Set cn = Nothing
Of course, your connection string will be different, and probably your form control names too. This is just an example to illustrate the technique.
That may be the requirement, but to me the requirement itself is weird. This can never be a real time scenario. It really does not make any sense to me to have OrderFormNo=123 and CashBillNo='ABC' for all the subsequent records. This will end up with a scenario where all the records in the table will have the same data repeated across all records. Why not store this combination in a master table and refer the same by implementation of rerefential integrity from any dependent child table.