detect 2 condition v6

[lianpiau]

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.

[BitBlt]

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.

[lianpiau]

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.

[debasisdas]

Ok, got it.

But i also have a question.

Say you have 3 records like this

OrderFormNo=345 and CashBillNo='ABC'
OrderFormNo=123 and CashBillNo='ABC'
OrderFormNo=234 and CashBillNo='ABC'

Which one to delete.

[lianpiau]

let said I got a record

OrderFormNo=123 and CashBillNo='ABC'

if I want insert same order, then record must have OrderFormNo=123 and CashBillNo='ABC'
if the OrderFormNo = 456 and CashBillNo='ABC', it will pop out message and disallow to insert.

[debasisdas]

Simple,

make the field CashBillNo the primay key of the table.

It will not allow duplicate records.

[BitBlt]

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"
Else
    cn.Execute "insert into MyTable (CashBillNo, OrderFormNo) values ('" & Me.txtCashBillNo.Text & "', " & Me.txtOrderFormNo.Text & ")"
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Of course, your connection string will be different, and probably your form control names too. This is just an example to illustrate the technique.

Hope this helps! Good luck!

[lianpiau]

thanks all~~~

[debasisdas]

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.